Relative, Absolute and Mixed Cell References

Contents
  1. Their use in Excel and Google Sheets
  2. Using a cell reference enables automatic updating
  3. Links to cells from different worksheets
  4. Mobile range
  5. Relative, Absolute and Mixed Cell References
  6. Copy formulas and links to different cells
  7. Switch between cell reference types

Their use in Excel and Google Sheets

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

A cell is one of the box structures that fill the worksheet, and each cell can be found by references to its cells, such as A1, F26, or W345. A cell reference consists of a column letter and a row number that intersect at the location of the cell. When displaying a cell reference, the column letter is always listed first.

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

Remark † The information in this article applies to Excel 2019, 2016, 2013, Excel for Mac, and Excel Online. This information also applies to Google Sheets.

Using a cell reference enables automatic updating

One of the benefits of using cell references in spreadsheet formulas is that in general, if the data in the referenced cells is, the formula or chart is automatically updated to reflect the change.

If the workbook is configured to update automatically when changes are made to the worksheet, a manual update can be performed by pressing the key F9 on keyboard.

Links to cells from different worksheets

Cell references are not limited to the same worksheet where the data resides. Cells can be referenced from different worksheets.

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

Likewise, when referencing data that is in another workbook, the name and sheet of the workbook are included in the link, along with the location of the cell. The formula in cell C7 in the illustration contains a cell reference on sheet 1 of book 2, the title of the second book.

Mobile range

While links often refer to individual cells, such as A1, they can also refer to a group or range of cells. Ranges are identified by cell references 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 cells between those start and end points.

An example of a range of adjacent cells is shown in row 3 of the figure, where the SUM function is used to sum numbers in the range A2 to A4.

Relative, Absolute and Mixed Cell References

The three types of links that can be used in Excel and Google Sheets are easily identified by the presence or absence of dollar signs ($) in a cell reference:

  • Relative Cell References do not contain dollar signs, as shown in the formula on line 2, =A2 + A4.
  • Absolute Cell References have dollar signs attached to each letter or number in the link, as shown in the formula on line 4, = $A$2 + $A$4.
  • Mixed Cell References have dollar signs associated with the letter or number in the reference, but not both, as shown in the formula on line 5, = $A2 + A$4.

Copy formulas and links to different cells

Another benefit 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. Name family member stems from the fact that when they are copied they change relative to their location. This is usually a good thing, which is why relative cell references are the default type of cell reference used in formulas.

Sometimes cell references need to stay the same when copying formulas. Use an absolute reference for this, for example = $A$2 + $A$4, which does not change when copied.

In other cases, you may need to change part of a cell reference, such as a column letter, while leaving the row number unchanged, or vice versa when copying a formula. This is when a mixed cell reference is used, such as =$A2 + A$4. Whatever part of the reference has a dollar sign, stays the same, while the other part changes when copied.

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

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

Switch between cell reference types

The easiest way to change cell references from relative to absolute or mixed is to press the key F4 on the keyboard. To change existing cell references, Excel must be enabled edit mode which can be accessed by double-clicking the mouse pointer on the cell or by pressing the key. to push F2. Key on keyboard.

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

  • Click F4 once to create an absolute cell reference, such as $A$6.

  • Click F4 the second time to create a mixed link with an absolute line number, such as A$6.

  • Click F4 a third time to create a mixed link where the column letter is absolute, such as $A6.

  • Click F4 a fourth time to make the cell reference relative again, such as A6.

Leave a Reply

Your email address will not be published. Required fields are marked *