Cell References - Relative, Absolute, and Mixed

Cell References- Definition and Use in Excel and Google Spreadsheets?

Using Relative, Absolute, and Mixed Cell References in Excel
© Ted French

Cell Reference Overview

A cell reference in spreadsheet programs like Excel and Google Spreadsheets identifies the location of a cell in the worksheet.

A cell is one of the box-like structures that fill a worksheet and each cell can be located by means of its cell reference - such as A1, F26 or W345 - consisting of the column letter and row number that intersect at the cell's location. When listing a cell reference, the column letter is always listed first

Cell references are used in formulas, functions, charts, and other Excel commands.

Updating Formulas and Charts

One advantage to using cell references in spreadsheet formulas is that, normally, if the data located in the referenced cells changes, the formula or chart automatically updates to reflect the change.

If a workbook has been set not to automatically update when changes are made to a worksheet, a manual update can be carried out by pressing the F9 key on the keyboard.

Different Worksheets and Workbooks

Cell references uses are not restricted to the same worksheet where the data is located. Cells can be referenced from different worksheets.

When this occurs, the name of the worksheet is included as shown in the formula in row 3 in the image above which includes a reference to cell A2 on Sheet 2 of the same workbook.

Similarly, when data located in a different workbook is referenced, the name of the workbook and the worksheet are included in the reference along with the cell location.

The formula in row 3 in the image includes a reference to cell A1 located on sheet 1 of Book2 - the name of the second workbook.

Range of Cells A2:A4

While references often refer to individual cells - such as A1, they can also refer to a group or range of cells.

Ranges are identified by the cell references of the cells in the upper left and lower right corners of the range.

The two cell references used for a range are separated by a colon ( : ) which tells Excel or Google Spreadsheets to include all the cells between these start and end points.

An example of a range of adjacent cells is shown in row 3 of the image above where the SUM function is used to total the numbers in the range A2:A4.

Relative, Absolute, and Mixed Cell References

There are three types of references that can be used in Excel and Google spreadsheets and they are easily identified by the presence or absence of dollar signs ($) within the cell reference:

  • relative cell references contain no dollar signs - as shown in the formula in row 2: = A2 + A4;
  • absolute cell references have dollar signs attached to each letter or number in a reference - as shown in the formula in row 4: =$A$2+$A$4;
  • mixed cell references have dollar signs attached to either the letter or the number in a reference but not both - as shown in the formula in row 5: =$A2+A$4.

Copying Formulas and the Different Cell References

A second advantage to using cell references in formulas is that they make it easier to copy formulas from one location to another in a worksheet or workbook.

Relative cell references change when copied to reflect the new location of the formula.

For example, if the formula

= A2 + A4

was copied from cell B2 to B3, the references would change so that the formula would be:

= A3 + A5

Their name relative comes from the fact that they change relative to their location when copied. This is usually a good thing and it is why relative cell references are the default type of reference used in formulas.

At times, though cell references need to stay static when formulas are copied. To do this, an absolute reference ( =$A$2+$A$4) is used which does not change when copied.

Still, at other times, you may want part of a cell reference to change - such as the column letter - while having the row number stay static - or vice versa when a formula is copied.

This is when a mixed cell reference is used (=$A2+A$4). Whichever part of the reference has a dollar sign attached to it stays static, while the other part changes when copied.

So for $A2, when it is copied, the column letter will always be A, but the row numbers will change to $A3, $A4, $A5, and so on.

The decision to use the different cell references when creating the formula is based on the location of the data that will be used by the copied formulas.

Use F4 to Add the Dollar Signs

The easiest way to change cell references from relative to absolute or mixed is to press the F4 key on the keyboard:

To change existing cell references, Excel must be in edit mode, which can be done by double clicking on a cell with the mouse pointer or by pressing the F2 key on the keyboard.

To convert relative cell references to absolute or mixed cell references:

  • Press F4 once to create a cell reference fully absolute such as $A$6
  • Press F4 a second time to create a mixed reference where the row number is absolute such as A$6
  • Press F4 a third time to create a mixed reference where the column letter is absolute such as $A6
  • Press F4 a fourth time to make the cell reference relative again such as A6.