Excel Formula Tips

This page lists some of the Excel formula tips that you may find useful when inputting large numbers of functions or complex Excel formulas:

1. Use Brackets

Many errors in Excel formulas are caused by a failure to fully understand the order in which mathematical operators are evaluated.

If in doubt, use brackets to isolate parts of the formula that you need to be executed first.

E.g.

= ( A1 + B1 ) * C1

2. Insert Line Breaks to Clarify Complex Formulas

Note:

Use ALT-ENTER to insert a line break in an Excel cell.

(i.e. press the ALT key, and while this is pressed down, press the ENTER key).

When you are using creating complex Excel formulas, it is easy to quickly become confused and lose count of brackets.

One way to clarify your Excel formulas is to insert line breaks between different parts of a formula. For example, the following formula is difficult to follow:

if(A1>8,"Very High", if(A1>6,"High", if(A1>4,"Average", if(A1>2,"Low","Very Low") ) ) )

However, if line breaks are inserted between each of the 'if' functions, the whole formula becomes much clearer:

if(A1>8,"Very High",
 if(A1>6,"High",
  if(A1>4,"Average",
   if(A1>2,"Low","Very Low") ) ) )

Note:

Use ALT-ENTER to insert a line break in an Excel cell.

(i.e. press the ALT key, and while this is pressed down, press the ENTER key).

3. Use Keyboard Shortcuts to Select Ranges of Cells In Formulas

If you want to enter a range of cells into a formula, you can use the mouse to select the range of cells that you want to enter.

However, if the range of cells that you want to select is large, it may be useful to use the following keyboard shortcuts.

CTRL - Left Mouse Button-

Selects multiple cells.

I.e. to select more than one cell (or range), first use the mouse to select on the first cell (or range), then press the CTRL key and use the mouse to select further cells or ranges.
SHIFT - Left Mouse Button-

Selects all cells between (and including) the previous active cell and the cell that is currently being clicked in.

I.e. to select a range, first use the left mouse key to click on a cell (or row or column) at the start of the range, then press the Shift key and select the cell at the end of the range.
SHIFT   ↓-Extends the current selected range down a row.
SHIFT   ↑-Extends the current selected range up a row.
SHIFT   →-Extends the current selected range right by one column.
SHIFT   ←-Extends the current selected range left by one column.
CTRL-SHIFT   ↓-Selects all cells below the current selection, up to the edge of the current data region.
CTRL-SHIFT   ↑-Selects all cells above the current selection, up to the edge of the current data region.
CTRL-SHIFT   →-Selects all cells to the right of the current selection, up to the edge of the current data region.
CTRL-SHIFT   ←-Selects all cells to the left of the current selection, up to the edge of the current data region.

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

(For further information on relative and absolute references, see the Excel Cell References page).

5. Use the Fill Handle to Copy a Formula to Different Cells of a Spreadsheet

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.

6. Use ALT + = to Automatically Enter a Sum

Use of ALT + = as a Shortcut to Sum a Column

If you want to quickly sum the contents of a column, select the cell immediately below the column and type ALT + =

(I.e. Press down the ALT key and while keeping this pressed down, type in + and then =).


7. Use CTRL-` to Display Formulas in a Worksheet

Key to show formulas
CTRL-` to display formulas

If you want to display all the formulas in your current worksheet, the easiest way to do this is through the keyboard combination:

CTRL-`

I.e. press the control key, and while holding this down, press the ` key.

Note that, on many keyboards, the ` key is at the top left of the keyboard, next to the number 1.

To hide the formulas again, simply repeat the same key combination (i.e. press CTRL-`).

8. Automatic Calculation - Use with Caution!!

By default, Excel will recalculate the formulas on your spreadsheet every time you make a change to any of the cells that the formulas depend on. This is generally useful, as it means that your spreadsheet is always up-to-date.

However, if you are working with large amounts of data, this recalculation can take several seconds. In this case, it is not practical to allow the spreadsheet to freeze while it updates its formulas, every time you enter a new value.

If you find yourself in this position, you may want to 'switch off' Excel's Automatic formula updates while you are working on your spreadsheet and then turn them back on again later.

Warning: If you do switch off the Automatic calculation option, make sure you remember to switch it back on again afterwards, or there is a risk that you will become very confused when your formulas fail to update!!

The option to activate or deactivate Excel Automatic cell calculation is located in different places in different versions of Excel:


To Find the Automatic Cell Calculation Option:

In Current Versions of Excel (Excel 2010 and later):

  • Select the File tab from the top of the workbook;
  • From the menu that appears, select Options;
  • From within the Options menu, select Formulas to view the Calculation options.

In Excel 2007:

  • Select the main menu (from the Microsoft Excel Logo at the top left of the workbook);
  • From the menu that appears, select Excel Options;
  • From within the Options menu, select Formulas to view the Calculation options.

In Excel 2003:

  • Select Tools -> Options...;
  • Select the Calculation Tab.

Further formula tips are provided on the Microsoft Office Support website.