## 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:

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:

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.

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.