Unit 18: Formula Referencing

Formulas provide the key to working successfully with Microsoft Excel. When you create a formula, you write it using cell references rather than actual hard-coded values – this is how spreadsheeting works. It is important to fully understand and comprehend this concept to be able to exploit the full potential of Microsoft Excel. There are indeed also some potential traps that you can fall into with cell referencing if you don’t take enough care. Excel is a calculation tool and as such is geared around the concept of formulas. Formulas are entered into a worksheet using cell references rather than actual values. Each time a formula is entered using cell referencing, a shape is created and it is this shape which determines where Excel goes to pick up values for use in the calculation.

Understanding Formula Shapes

When you create a formula, you don’t enter numbers into the formula – you enter cell references. This creates a great time advantage when you need to duplicate that formula in other cells. Formulas are merely shaping that reference cells in specific locations in the worksheet or workbook. In the example below, say you have a formula in cell E5 that is written as =D5*C5. To Excel this is interpreted as a shape that says “from my current”. position (E5) go left one cell (D5), take the value there and multiply it by the value two cells left (C5)”. This becomes the formula shape – left one multiply by left two. When this formula, or shape, is then copied or filled to adjacent cells it is the shape that is copied and the cell references within those copied formulas change relative to the shape (which doesn’t change). For example, if you copy =D5*C5 from E5 to E6 the formula in E6 changes to =D6*C6 – but the shape is still – left one multiply by left two. This is called relative referencing – because the cell address changes relative to the current location.

Absolute Referencing

There will be times however when one or more cell references in a formula are always required to be located in a specific cell, row, or column. In the example below, sales tax of 10% is located in cell B4 and must always be referenced from B4. The sales tax for dining table is calculated using the formula =B8*B4 while the sales tax for the sofa is calculated using the formula =B9*B4. These are different shapes and if you were to copy the formula from C8 (=B8*B4) to C9 it would copy the shape and the formula would be =B9*B5 – which is wrong. When dealing with cells in formulas that must be referenced from the same location, you must fix these cell references by making them absolute. This is done by placing a $ sign in front of either or both the row and column co-ordinate of the cell depending on which of these has to remain fixed (e.g. $B$4, $B4, or B$4). In the example above, both row and column co-ordinates need to be fixed and the formula in C8 should be written as =B8*$B$4 before it can be copied to adjacent cells.

UNIT 18 – Formula Referencing

There aren't any posts currently published in this category.