Cell Referencing in Excel
This hint and tip revolves around Excel cell references. Cell references are covered in our Basic Excel training course. 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 References
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 References
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!
For more details go to our web site here or contact us on johnlegge@jplcomputer.co.uk.
Why not take a look at our previous post on analysing lists of data in Excel?