Relative, Absolute, and Mixed Cell References

Their usage in Excel and Google Sheets

An image showing a selected cell in Excel

slobo/Getty ImagesĀ 

A cell reference in spreadsheet programs such as Excel and Google Sheets identifies the location of a cell in the worksheet.

A cell is one of the boxlike structures that fill a worksheet, and each cell can be located by its cell references, such as A1, F26, or W345. A cell reference consists 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.

Note The information in this articles applies to Excel versions 2019, 2016, 2013, Excel for Mac, and Excel Online. This information also applies to Google Sheets.

Cell Reference Use Enables Automatic Updating

One advantage of 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 update automatically when changes are made to a worksheet, a manual update can be carried out by pressing the F9 key on the keyboard.

Referencing Cells From Different Worksheets

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

Using Relative, Absolute, and Mixed Cell References in Excel

When this occurs, the name of the worksheet is included as shown in the formula in cell C6 of the image above.

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 cell C7 in the image includes a reference to a cell located on Sheet1 of Book2, the name of the second workbook.

Cell Range

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 Sheets 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 where the SUM function is used to total the numbers in the range A2 through A4.

Relative, Absolute, and Mixed Cell References

The three types of references that can be used in Excel and Google Sheets 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 Different Cell References

Another advantage of 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. TheĀ 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, cell references need to stay static when formulas are copied. To do this, use an absolute reference, such as =$A$2+$A$4, which does not change when copied.

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 such as =$A2+A$4 is used. 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 is always A, but the row numbers 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.

Toggling Between Types of Cell References

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 entered 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.