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 cell reference looks like is a2 or b3. See below for an example of how relative cell references are used in a calculation:

Cell referencing in Excel - Relative Cell references

An example of a relative cell referenced formula

 

 

 

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 absolute references are used in a calculation:

Cell referencing in Excel - Absolute cell references

Example of Absolute cell referencing in Excel

 

 

 

 

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 www.jplcomputer.co.uk or contact us on johnlegge@jplcomputer.co.uk.

 

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy