Excel cell references – relative, absolute and mixed

A cell reference in spreadsheet programs such as Excel and Google Sheets identifies a cell’s position in the spreadsheet.

A cell is one of the box-like structures that fills a worksheet, and you can recognize it by its references like A1, F26, or W345. A cell reference is the column letter and row number that intersect at the cell location. When entering a cell reference, the column letter always appears first.

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

The information in this article applies to versions of Excel 2019, 2016, 2013, Excel for Mac, Excel Online, and Google Sheets.

Using the cell reference enables automatic updating

One of the benefits of using cell references in spreadsheet formulas is that when the data in the referenced cells changes, the formula or chart will usually automatically update to reflect the change.

If a workbook has been configured not to update automatically when you make changes to a worksheet, you can update it manually by pressing F9 on the keyboard.

Referencing cells from different worksheets

Cell references are not limited to the worksheet that the data resides in. Other worksheets in the same file can reference each other by including a notation that tells the program which sheet to extract the cell from.

You don’t need sheet notation when referencing a cell in the same worksheet.

When you reference data in another workbook, the workbook name and worksheet name are included in the reference along with the cell location.

  How to create a column chart in Excel for Windows or Mac

To refer to a cell on another sheet, set the cell reference to “Sheet[number] followed by an exclamation mark, then the name of the cell. So if you want to extract information from cell A1 of Sheet 3, you would ” Sheet3!A1. »

A notation that refers to another workbook in Excel also includes the workbook name in parentheses. To use the information in cell B2 of Sheet 2 of Workbook 2, you must ” [Book2]Feuille2!B2. »

area of ​​cells

Although references often refer to individual cells, e.g. B. A1, but they can also refer to a group or row of cells. You identify cell ranges based on the start and end cells. For ranges that span multiple rows and columns, use cell references in the upper-left and lower-right corners of the range.

Separate the boundaries of a range of cells with a colon ( : ), which tells Excel or Google Sheets to include all cells between those start and end points. So to type everything between cells A1 and D10, you would type: ” A1:D10. »

To enter an entire row or column, you still use cell range notation, but you only use column numbers or row letters. To include everything in column A, the interval is ” A: A. To use line 8 you must ” 8:8. For everything in columns B through D, you must ” B:D. »

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 ($) in the cell reference. A dollar sign tells the program to use that value every time it runs a formula.

  • cell references do not contain dollar signs (e.g. A1).
  • Mixed cell references Append dollar signs to either the letter or number of a reference, but not both (ie $A1 or A$1).
  • Absolute cell references Add dollar signs to each letter or number in a reference (e.g. 1 $A$).
  How to remove formula in excel

When you create a formula, you generally use an absolute or mixed cell reference. For example, if you have a number in cell A1, multiple numbers in column B, and column C containing the sums of A1 and each of the values ​​of B, you would use “$A$1” in the SUM formula so the program knows when autofill that it always uses the number from A1 instead of the empty cells below.

Copy formulas and various cell references

Another benefit of using cell references in formulas is that they make it easier to copy formulas from place to place in a spreadsheet or workbook.

Relative cell references change when copied to reflect the new position of the formula. The name relatively is because they change relative to their location when copied. That’s usually a good thing, and that’s why relative cell references are the default reference type used in formulas.

Sometimes cell references need to remain static when copying formulas. Copying formulas is the other main use of an absolute reference like =$A$2+$A$4. The values ​​of these references do not change when you copy them.

In other cases, you may want to change part of a cell reference, e.g. E.g. the column letter while keeping the row number unchanged, or vice versa if you copy the formula. In this case you would use a mixed cell reference like =$A2+A$4. The part of the reference that has a dollar sign appended to it remains static, while the other part changes as it is copied.

  How to create, edit and view Microsoft Excel documents for free

So if $A2 is copied, the column letter will still be A, but the row numbers will change to $A3, $A4, $A5, etc.

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

Switch between cell reference types

The easiest way to change cell references from relative to absolute or mixed is by pressing the key F4 on the keyboard. To change the references of existing cells, Excel must be activated editing modewhich you enter by double-clicking on a cell with the mouse pointer or by pressing the key F2 on the keyboard.

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

  • To press F4 once to create an absolutely absolute cell reference, e.g. B. $A$6.
  • To press F4 a second time to create a mixed reference where the line number is absolute, e.g. B. 6 Australian Dollars.
  • To press F4 a third time to create a mixed reference where the column letter is absolute, e.g. B. $A6.
  • To press F4 a fourth time to make the cell reference relative again, like A6.