Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 29 ยท Formulas and Functions

Relative, Absolute and Mixed References

Understand relative, absolute and mixed references in Google Sheets, what happens when you copy a formula, plus named ranges and references to other sheets.

You write one formula in the top row of a table, drag it down a thousand rows, and every row calculates its own answer. That works because Sheets adjusts the references when a formula is copied. Sometimes that adjustment is exactly what you want, and sometimes it breaks the sheet, for example when every row should use the same tax rate. The dollar sign ($) is the tool that decides which parts of a reference move.

This lesson covers relative, absolute and mixed references, named ranges and references to other tabs. The real behavior happens in Google Sheets. The TryIt blocks are stand-ins that implement the shifting rule in JavaScript, so you can see the exact logic. They are simplified: they handle plain A1 references and ignore details such as function names that look like addresses.

Three kinds of reference

A dollar sign pins a part

A1 relative Column and row both move when copied.
$A$1 absolute Column and row are both pinned.
A$1 mixed Row pinned, column moves.
$A1 mixed Column pinned, row moves.
1 / 2

Relative references: A1

A plain reference like B2 means "the cell in this column, that row" relative to where the formula sits. If D2 contains =B2*C2 and you copy it to D3, it becomes =B3*C3: one row down, so every reference moves one row down. Copy it one column right and B2 becomes C2.

That is why you can fill a formula down a table and every row calculates from its own data.

Absolute references: $A$1

A $ before the column letter pins the column, and a $ before the row number pins the row. $E$1 never changes wherever you copy it. Use this for a single input every row needs, such as a tax rate in E1: =B2*$E$1, filled down, becomes =B3*$E$1, =B4*$E$1 and so on.

Mixed references: A$1 and $A1

You can pin just one part. B$1 keeps row 1 but lets the column move; $A2 keeps column A but lets the row move. Mixed references make a multiplication table or a price grid possible with a single formula copied across and down: =$A2*B$1 takes its row value from column A and its column value from row 1.

While you edit a formula and the cursor touches a reference, press F4 to cycle A1, $A$1, A$1, $A1. If the shortcut does not respond on your keyboard, type the dollar signs by hand.

A stand-in that shifts references

The function below does what Sheets does when you copy a formula: it moves each reference by the distance between the source cell and the target cell, but leaves pinned parts alone. If a shifted reference would fall off the sheet, it becomes #REF!.

JS Copying a formula shifts the references

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check the grid examples by hand: in =$A2*B$1, the column of A2 and the row of B1 never change. Those two anchors make one formula work in a whole table.

What goes wrong without the dollar sign

The most common beginner bug is a rate in one cell and a formula that forgets to pin it. If D2 holds =B2*E1 and you fill down, D3 becomes =B3*E2, which multiplies by an empty cell, giving 0, and every row below the first is wrong. The fix is =B2*$E$1. When a copied formula gives odd results, press Ctrl with the backtick key to show all formulas, or double-click a cell to see colored borders around the cells it points to.

Copying and moving are different. Cut and paste moves a cell and keeps its formula text unchanged. Copy and paste adjusts relative references. Inserting or deleting rows and columns also updates references to follow the data they point to.

Ranges and named ranges

A range such as B2:B10 is two references, and the same pinning rules apply to each end: $B$2:$B$10 is a fully pinned range, and B2:B (no end row) means "from B2 to the bottom", which grows when you add rows.

Typing addresses all day gets confusing. A named range gives a cell or range a readable name. Select the cells, choose Data > Named ranges, and type a name such as TaxRate. A name:

  • must start with a letter or underscore, and cannot contain spaces
  • cannot look like an address (A1) or be a reserved word such as TRUE
  • behaves like an absolute reference, so =B2*TaxRate is safe to copy
  • shows up in the Name box and in formula suggestions, and can be edited in the Named ranges panel

Named ranges make formulas self-explanatory (=Revenue-Costs), but a sheet with fifty of them is hard to audit. Use them for the key inputs.

References to other sheets

To use a cell on another tab, put the tab name and an exclamation mark in front: =Prices!B2. If the tab name contains spaces or special characters, wrap it in single quotes: ='Price List'!B2. Ranges work the same way, for example =SUM(Orders!C2:C100). The same pinning rules apply, so =Settings!$B$1 is a pinned cell on a Settings tab.

Typing =, switching tabs and clicking a cell inserts all of this for you. To read a range from a different file, use IMPORTRANGE, covered in Importing Live Data.

If you rename a tab, formulas that refer to it update by themselves. If you delete the tab, those formulas show #REF! (see Understanding Formula Errors).

The next stand-in resolves named ranges and cross-sheet references against a tiny workbook. It is a model of the idea, not the real engine.

JS Named ranges and other tabs

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In a real sheet, the same total would be a formula such as =SUMPRODUCT(UnitPrices, Quantities)*(1+TaxRate); the stand-in just shows how names and tab references lead back to cells.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. D2 contains =B2*C2. You copy it to D5. What does D5 contain?

    Relative references move with the copy. D5 is three rows below D2, so both references move down three rows.
  2. 2. Which formula can be filled down so that every row multiplies by the same rate in E1?

    $E$1 is pinned in both column and row, so it points at E1 in every row.
  3. 3. What does B$1 mean when copied to another cell?

    The dollar sign before the row number pins the row; the column letter has no dollar sign and moves.
  4. 4. How do you refer to cell B2 on a tab named Price List?

    Tab names with spaces go in single quotes, followed by an exclamation mark and the address.

Summary

  • Relative references (A1) move when you copy a formula; absolute references ($A$1) do not.
  • Mixed references (A$1, $A1) pin only the row or only the column, which is ideal for grids.
  • Pin fixed inputs such as a tax rate; press F4 to cycle the forms while editing.
  • Named ranges give cells readable names and act as absolute references.
  • Reference other tabs with Sheet!A1, with single quotes if the name has spaces.

Next: SUM, AVERAGE, MIN, MAX and COUNT