Cell Referencing in Excel
This hint and tip revolves around Excel cell references. Although this is clearly a basic premise in Excel we find how little it is understood in our Excel training courses and often have to re-address the premise no matter what level we teach!
There are 2 types of cell reference used in an Excel formula: Relative and Absolute.
Relative cell references change when a formula containing them is copied into another cell. An example of what a relative reference looks like is a2 or b3. See below for an example of how these are used in a calculation.
Absolute cell references do not change when a formula containing them is copied into another cell. There are 2 ways this can be achieved: using a $ symbol and using named cells. An example of what an absolute cell reference looks like is $d$6 or $g$2 using the $ symbol and VAT or PRICE using the named cell method. See below for an example of how these are used in a calculation
It is possible to use an absolute range which may include columns, rows or a range of cells. Examples of this include $A:$A and $a$1:$d$5.
Excel training courses
A good understanding of this topic, which is covered in our Basic Excel training course and Intermediate Excel training course in more detail, can give you a great deal of flexibility in constructing all kinds of time saving functions. A lot of people are concerned with learning about absolute references; but with 2 ways in which you can use them, you can select the method that you prefer so there is no need to worry!
Why not take a look at our previous post on analysing lists of data in Excel?