As auditors we are often working with spreadsheets. Over the next few months the OAD “How To” blog series will be focusing on some cool tips and tricks to use in Excel.
We will start with a relatively simple, but vital technique to understand: the difference between absolute and relative cell references.
Absolute cell references are used when you want to refer to a specific cell the same time every time. To do this, use the prefix $. For example, =$A$2 will always refer to cell A2. This can be useful if you want to test transactions to see if they match a specified tax rate as shown in the example below.
This example can also be used to show relative references. Relative references change when you drag a formula down or across the spreadsheet. For example, C5 will become C6, C7, C8, etc. The same is true of the D5 reference. As you can see in the screenshot below, the formula maintains the $A$2 reference while the other references change based on the row number.
Sometimes you need to develop large tables within a spreadsheet with multiple column fields and row fields. You can use a combination of absolute and relative references to help you develop those quickly and efficiently.
For example, if you want a reference to stay within a column, use $A2. This will lock the reference on the A column as you drag the formula to the right.
If you want a reference to stay constant within a row, use A$2. This will lock the reference on row 2 as you drag the formula up and down.
Now you have the basic tools you need to create quick and easy tables. Just remember to think about what type of cell reference you need when you start developing your formulas. A helpful trick is to use F4 to cycle through the various types of references (e.g. $A$2, $A2, A$2, A2). For a more advanced walk-through of creating tables, see this previous post on using Sumifs.
Before Excel, Lotus 1-2-3 was the spreadsheet software of choice and it used a different cell referencing style that can be activated in Excel if you want. Just go to File > Options > Formulas > Working with formulas and click on R1C1 reference style.
The way R1C1 style works is that you use the R (row) and C (column) to create relative references. So if you were inputting a formula in cell B3 and you wanted to reference A2, you would enter = R[-1]C[-1]. B minus 1 is A and 3 minus 1 is 2 (or cell A2). This is an example of a relative reference in R1C1 style. For an absolute reference use R2C1 for A2 (A2 is the 2nd row and 1st column of the spreadsheet, hence R2C1).
See the screenshot below for how the formulas from the examples above look in R1C1:
In some instances, it might be easier to use R1C1 style to create the formulas you need so keep this trick in mind. Most people prefer to work in A1 style.
Enjoy reading our ‘how to’ posts? Subscribe to the Oregon Audits blog through the ‘Follow’ link in the right hand sidebar and keep tabs on the latest data wonk tips and tricks, audit releases, interviews, and more!