This page describes 8 cool Excel tips and tricks (including some of the most useful Excel keyboard shortcuts), that will help you to work more effectively and efficiently in Excel.
If you spend just a few minutes learning these tips and tricks, you can, in the long-term, save hours of time when developing your spreadsheets. In just a short time, you will look back and wonder how you ever managed without them!
|Excel Tips & Tricks Index:|
|Insert a New Line in a Cell|
|Freeze Rows and Columns in a Worksheet|
|View Two Worksheets at Once|
|Zoom to View Named Ranges|
|Use F4 to Cycle Through Relative & Absolute References|
|Keyboard Shortcuts to Cycle Through Worksheets|
|Copy a Function Down to Other Rows|
|Fast Ways to Select a Range of Cells|
To freeze one or more rows of data at the top of your spreadsheet, select the row below the row(s) that you want to freeze. Then, select Freeze Panes->Freeze Panes from the View tab of the ribbon
If you want to view more than one page of a workbook, you can use the "New Window" option, from the View tab of the ribbon (see below):
This opens up a second window for the current workbook, so that you can view different worksheets of the same workbook at once.
Note that the two windows simply provide two views of the same workbook. Therefore, any changes that are made in one window will instantly be updated in the second window.
The best way to arrange the two windows is to use Excel's 'Arrange' command (also located in the View tab of the Excel ribbon). Alternatively, you could size the windows manually.
If you use the Excel Zoom to view your worksheet at a zoom level of less than 40%, Excel automatically displays all the named ranges in the worksheet.
If you type a simple reference in your cell (e.g. A1) and then decide you want to change this to be a relative reference, while your cell is still in edit mode, simply press F4.
To quickly move to the next worksheet in your workbook, use Ctrl + PageDown (i.e. press the Ctrl key and while this is held down, press the PageDown button).
If you want to copy a function down to further rows, there are a number of ways to do this:
The fill handle is the tiny square on the bottom right of a selected cell or selected cell range. Use this to copy a function to other rows by either:
Pressing Ctrl + D copies data down, depending upon the current selection:
Therefore, in order to copy a function down to other rows in a spreadsheet, select the range that you want to copy the cell down to including the original cell to be copied and then press Ctrl-D.
The copy and paste commands can be performed by the following shortcuts:
There are several ways to select a large range of cells. The most obvious is to simply highlight one cell and press the left mouse key, while dragging the mouse across the range you want to select. However, if you want to select hundreds of rows at a time, this can take a long time!
The name box is situated on the top left of your Excel spreadsheet, and typically shows the address of the currently selected cell or cell range.
One quick way to select a range is to simply type the range into the Name Box and press the Enter (or Return) key.
A combination of the Ctrl key, the Shift key and the Arrow keys can be used to select ranges on your spreadsheet. The key combinations work as follows:
|Shift + ↓||-||Moves the current selected range down a row.|
|Shift + →||-||Moves the current selected range right by one column.|
|Ctrl + Shift + ↓||-||Selects all cells below the current selection up to the end of the current data range (or up to the next cell containing a value if you are starting from the end of the current data range).|
|Ctrl + Shift + →||-||Selects all cells to the right of the current selection up to the end of the current data range (or up to the next cell containing a value if you are starting from the end of the current data range).|
The best way to familiarise yourself with these commands is to experiment with them.
For more Excel tips and tricks, visit the exceltips.com website