Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

IT Basics - Understanding Excel formulae

by
26th Jan 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

"Write one, get 15 free," says Simon Hurst in this brief guide to how absolute and relative formulae work in Excel spreadsheets.

Do you need to read this?
Not if you understand how to enter and use absolute and relative formulae in Excel. However, if you're not sure what absolute and relative mean, or have always wondered why some Excel cell references include dollar signs, then you should find this useful.

Introduction
When you enter a formula that refers to another Excel cell it will generally be a "relative" formula. This means that the formula doesn't really refer to the cell itself, but to the position of that cell relative to the cell containing the formula. Often this is exactly what we need, For example, if we have several adjacent columns, we can enter a formula to add up the leftmost column, and then copy it to all the other columns. Because the formula uses the position of the cells, as we copy it, it will adjust to refer to each column in turn. However, sometimes we want to refer to a particular cell and be able to copy the formula without changing which cell the formula points to.

An example
Enter 1000 into cell A1 of a new workbook, and then go to cell B3 and enter the formula:
=A1.

Not surprisingly, Excel returns the value 1000.

&nbsp A B C D
1 1000 &nbsp &nbsp
2 &nbsp &nbsp &nbsp
3 &nbsp =A1 &nbsp
4 &nbsp &nbsp &nbsp
5 &nbsp &nbsp &nbsp
6 &nbsp =B4 &nbsp
7 &nbsp &nbsp &nbsp

Now right click on cell B3 and choose Copy. Right click on cell C6 and choose Paste. When we copy our cell that shows 1000 to C6 does it still show 1000? No, it now shows zero, yet cell C6 is an exact copy of cell B3.

This is because, although in cell B3 Excel displays the formula as "=A1", it's actually thinking 2 up 1 left, and then translating that back to the cell that occupies that position ' which is of course A1. When we copy it to C6, it still thinks 2 up 1 left. 2 up 1 left from C6 is B4.

In fact, if you change the appropriate setting in Excel (Tools-Options-General-Settings-R1C1 style reference), then Excel will display each formula in terms of row and column position. So our 2-up-1 left will be shown as:
=R[-2]C[-1].

An absolute formula
So, what do we do if we want our formula to always point at A1, wherever we copy it to? Perhaps surprisingly, it has nothing to do with the way we copy the cell, but rather we have to change the original formula that we enter. Instead of typing '=A1' we need to type '=$A$1'.

The dollar sign in front of 'A' fixes the reference to column A, and the dollar sign in front of '1' fixes the reference to row 1. We can now copy the cell containing our new formula to any other cell on the sheet and it will stay pointing at cell A1.

You can either enter the formula using the keyboard and including the dollar signs, or you can press "=" on the keyboard and then click on cell A1. To make the formula absolute rather than relative you then press function key 4 - F4. This toggles the formula between 4 absolute/relative possibilities:
=A1
=$A$1
=A$1
=$A1

As you can see from the 4 different formulae shown above, it's not just a question of a formula being totally absolute or totally relative, it can also be a mixture of the two, with the column reference remaining relative whilst the row reference is absolute, or the column reference being absolute whilst the row reference is relative. The dollar sign fixes the reference that follows. So a dollar sign in front of the column letter reference stops the column reference changing, and a dollar sign in front of the row number reference stops the row reference changing. Dollar signs in front of both stop either reference changing.

So what?!
That's how absolute and relative references work, but why does this help us use Excel more efficiently?

Consider the following example:

&nbsp A B C D
1 Interest rate 10% 12% 15%
2 &nbsp &nbsp &nbsp
3 Amount &nbsp &nbsp
4 10,000 =B$1*$A4 &nbsp
5 15,000 &nbsp &nbsp
6 20,000 =B4
7 25,000 &nbsp &nbsp
8 30,000 &nbsp &nbsp

In this sheet we want to create a table showing the amount of interest payable for five different loans, and three different interest rates.

Without understanding absolute and relative formulae we would need to type in 15 different formulae. If we just entered a default Excel relative formula into B4 and copied it down and across, then each formula would be '3 up same column' x 'same row 1 left' which would, of course, give us one correct result (B4) and 14 incorrect results.

If we enter our formula using the dollar signs to fix the row reference of the first half of the formula, and the column reference of the second half:
=B$1*$A4

Then we can copy the formula down to row 8, and then select B4 to B8 and copy across to column D - 15 formulae for the price of one!

A row by any other name
If a cell includes a value that you need to use throughout your workbook, then consider naming that cell. By so doing, you can refer to the cell by its name, without having to worry about entering dollar signs. It will also make your formulae easier to understand e.g.:
=A5*VATrate

Rather than:
=A5 *$B$2

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of AccountingWEB's Office ProductivITy Kit and '100 Time-saving Tips for Microsoft Office'. For more information, visit the The Knowledge Base website. AccountingWEB members can also access his back catalogue and IT Clinic online email support via the Office ProductivITy service.

Subscribe to the ExcelZone newswire
To keep up with all spreadsheet-related developments, click the button below to subscribe to the free fortnightly ExcelZone newswire. The system will take you back to the AccountingWEB home page after it adds your name to the ExcelZone subscription list.

Subscribe to the ExcelZone Newswire

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.