Excel Yourself: Formula tips (part 2)

Excel Yourself

Getting your $ signs right in Excel means being able to create formulas that can be more easily copied.

The first three tips in the previous article focused on techniques to speed up formula entry. The next three tips look at getting the $ signs in your references correct.

Formula Tip 4: The F4 key

When you create a formula you should aim to make it so that it can be copied down and across in the range it applies to. To do that, you typically need to add $ signs to the cell references. The $ sign in front of a column letter or a row number in a cell reference will fix that column or row when the formula is copied down and/or across. When using the $ signs there are two:

•    Fixed reference eg $A$1, $A:$A, $1:$1
•    Mixed reference eg $A1, A$1

A cell reference that doesn’t have a $ sign is called a relative reference, as it changes relative to its original position when copied.

If you are creating or editing a formula, then pressing the F4 function key when in, or next to, a cell reference will fix the reference, eg $A$1. Pressing F4 again will fix just the row, eg A$1, and pressing again will fix just the column, eg $A1, whilst pressing again to return it to the relative reference A1. The sequence as you press the F4 key is
A1 > $A$1 > A$1 > $A1 > A1

To remember it’s the F4 key, just look at the keyboard. The $ sign is above the number 4.

As a simple example in the range in Figure 1, we want to calculate the superannuation amounts for the income levels noted.

Figure 1

Figure 1

The formula for cell B2 is: =B$1*$A2

This can be copied across and down as shown in Figure 2.

Figure 2

Figure 2

Figure 2

If you select a range reference and then press F4 it fixes the whole range.

The F4 key also works slightly differently if you are creating a range reference rather than editing a reference. If you have just selected a range reference and press F4, Excel will fix the whole range reference (both the start and end cells).

If you edit the range reference and are at the end of the reference and press F4, Excel will only fix the cell reference at the end of the range reference, leaving the start reference unchanged.

Formula Tip 5: Copy the formula in the formula bar

If you want to copy a formula with relative references to another cell and you don’t want those relative references to change, there is a technique you can use. Simply edit the formula and use the mouse to select the whole formula and press Ctrl + c to copy it then press the Esc key and then select the destination cell and press Ctrl + v.
This technique only works for a single formula.

Formula Tip 6: Ctrl + ‘ (single inverted comma)

If you need to copy the formula from the cell above without changing the formula at all, then use the shortcut Ctrl + ‘ (single inverted comma -- the key to the left of the Enter key).

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.

How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps

Making data relationships work

Easy steps to make data Relationships work in Excel

Excel Yourself

Easy tips for matching salary to job class in Excel