Excel Tips and Tricks

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

Insert a New Line Inside an Excel Cell:

To insert a new line while editing an Excel cell, simply press ALT-ENTER (or ALT-RETURN).

Press Alt-Return to Insert a New Line in an Excel Cell

I.e. Press the Alt key and keep this pressed down while you press the Enter (or Return) key.

Freeze Rows and Columns in Excel:

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, from the Excel menu at the top of your workbook:

  • In current versions of Excel: Select Freeze Panes->Freeze Panes from the View tab of the ribbon
OR
  • In Excel 2003: Select Window->Freeze Panes.

Similarly,

  • To freeze columns at the left of your spreadsheet, Select the column to the right of the columns that you want to freeze and then select the Freeze Panes option.
  • To freeze rows at the top and columns on left of your spreadsheet, Select the cell below and to the right of the rows and columns that you want to freeze and then select the Freeze Panes option.
  • To unfreeze the rows or columns in a spreadsheet, select Freeze Panes->Unfreeze Panes
    (or Window->Unfreeze Panes in Excel 2003).
Freeze Panes Option
Example: to freeze top row and first column, select cell B2 and select the Freeze Panes option

For further details, see the Excel Freeze Panes page.

View Two Sheets of a Workbook Simultaneously

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):

New Window Button in Excel Ribbon

(In Excel 2003, the "New Window" option is found in the 'Window' drop-down menu).

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, or the 'Window' drop-down menu of Excel 2003). Alternatively, you could size the windows manually.

Having two windows open for one Excel file is much more convenient than having to repeatedly switch between worksheets in a single window!

Use Zoom to View the Named Ranges in a Worksheet

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.

Zoom at Less Than 40% Displaying Named Ranges

This is a convenient way to quickly check out all your named ranges.

Use F4 to Cycle Through Relative and Absolute References

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.

This will change your reference from A1 to $A$1. Repeatedly pressing the F4 key cycles your reference through the 4 different combinations of absolute and relative references:

A1
$A$1
A$1
$A1

Use CTRL-PageDown and CTRL-PageUp to Cycle Through Worksheets

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).

To quickly move to the previous worksheet in your workbook, use CTRL-PageUp.

Copy a Function Down to Other Rows in a Spreadsheet:

If you want to copy a function down to further rows, there are a number of ways to do this:


1. Use the fill handle

Fill Handle

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:

  • Selecting the fill handle with the mouse and dragging it down to further rows.
    This copies the contents of the top cell to the cells below.
OR
  • Selecting the fill handle with the mouse and then double-clicking with the left-hand mouse button.
    This copies the contents of the top cell to the cells below up to the end of the current data range.

2. Use CTRL-D to Copy

Pressing CTRL-D copies data, depending upon the current selection:

  • If a single cell or cells in a single row are selected, CTRL-D copies the contents from the row above the selected range, into the selected range.
  • If a cells in more than one row are selected, CTRL-D copies the contents from the top row of the selected range, into all other rows of the selected range.

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.


3. Use Copy & Paste

The copy and paste commands can be performed by the following shortcuts:

  • CTRL-C copies the current selected cell or range of cells;
  • CTRL-V pastes the current selected cell or range of cells.

Therefore, in order to copy a function down to other rows in a spreadsheet, first copy (CTRL-C) the first cell (or range), then select the cell (or range) that you want to copy into, and paste (CTRL-V).

Fast Ways to Select a Range of Cells

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!

A number of faster methods are listed below:

1. Shift Key + Left Mouse Button

  • Use the left mouse key to click on a cell (or row or column) at the top of the range to be selected
  • If the end of the cell range is outside of the visible range, use the scrollbars to navigate to the end of the region to be selected
  • Press Shift and while keeping the Shift key depressed, select the cell at the end (bottom right) of the range to be selected
Example:
Select a Range Using the Shift Key and Mouse Selection

2. Type the Range Into the Name Box

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.

Select a Range by Typing in the Name Box

For example,

  • Typing A1:A500 into the name box selects the cell range A1 - A500
  • Typing A:E into the name box selects the columns A - E
  • Typing 1:500 into the name box selects the rows 1 - 500

3. Ctrl / Shift Keys + Arrow Keys

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