Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 93 ยท Lookup and Reference

OFFSET, INDIRECT and Dynamic References

Build references from numbers and text with OFFSET, INDIRECT, ADDRESS, ROW and COLUMN, and learn why these volatile functions should be used sparingly.

Most formulas point at fixed cells: =SUM(B2:B6) always adds those five cells. Sometimes you need a reference that moves: "the sum of the last three months", "the total from whichever sheet the user picked", "the cell two columns right of this one". For that, Excel offers functions that build a reference at calculation time: OFFSET, INDIRECT and ADDRESS, helped by ROW and COLUMN.

They are powerful and they are also the most over-used functions in Excel. They are volatile, they hide dependencies from auditing tools, and in modern Excel there are usually better options. This lesson shows exactly what each one does, lets you run a small stand-in engine, and ends with a list of safer replacements. You should already know cell references and ranges.

Two ways to build a reference

OFFSET Start at a cell, then move by rows and columns.
INDIRECT Turn a piece of text into a reference.
ADDRESS Turn a row and column number into text.
1 / 2

ROW and COLUMN: the numbers behind a cell

ROW(reference) returns the row number of a reference and COLUMN(reference) returns its column number (column A is 1). With no argument they describe the cell that holds the formula.

  • =ROW(C7) returns 7
  • =COLUMN(C7) returns 3
  • =ROW() typed in cell F12 returns 12

They are not volatile. They are useful as counters (=ROW()-1 numbers a list that starts under a header row) and as inputs to OFFSET, INDEX and ADDRESS.

OFFSET: move from a starting point

OFFSET(reference, rows, cols, [height], [width]) starts at reference, moves rows down (negative means up) and cols right (negative means left), and returns the cell or block it lands on. height and width resize the result; if you leave them out, the result has the same size as the starting reference.

TEXT
=OFFSET(A1, 1, 1)           one down, one right of A1  -> B2
=OFFSET(B2, 0, 2)           two columns right of B2    -> D2
=OFFSET(A1, 1, 1, 3, 1)     a block 3 rows tall, 1 wide starting at B2 -> B2:B4

Two facts matter. First, OFFSET returns a reference, so you can wrap it in SUM, AVERAGE or COUNT. Second, it never changes any cell; it only describes where to look.

A classic use is a range that grows with your data:

TEXT
=SUM(OFFSET(C2, 0, 0, COUNTA(A2:A100), 1))

COUNTA counts the filled cells in A2:A100, and that count becomes the height of the block that starts at C2. Add a row of data and the sum includes it. Today an Excel Table does the same job with no formula tricks, but you will meet this pattern in older workbooks.

Another use is a rolling window, such as "the three cells ending at the current month". If the offset points above row 1 or left of column A, the result is #REF!.

INDIRECT: a reference written as text

INDIRECT(ref_text, [a1]) takes text that looks like a reference and returns whatever is in that cell. =INDIRECT("B2") is the same as =B2. The point is that the text can be assembled by other formulas:

TEXT
=INDIRECT("B" & 5)               -> B5
=INDIRECT(A1 & "!B2")            -> B2 on the sheet named in A1
=INDIRECT("'" & A1 & "'!B2")     -> same, safe for sheet names with spaces

Sheet names that contain spaces or punctuation must be wrapped in single quotes inside the text. The optional second argument is TRUE (the default) for A1 style and FALSE for R1C1 style text. If the text is not a valid reference, the result is #REF!.

The most common real-world use is a dropdown-driven report: a data validation list holds sheet names such as Jan, Feb, Mar, and a summary cell uses INDIRECT to read the matching cell from the chosen sheet.

ADDRESS: build the text first

ADDRESS(row, column, [abs], [a1], [sheet]) returns a cell address as text. The abs argument picks the anchoring: 1 gives $C$7, 2 gives C$7, 3 gives $C7 and 4 gives C7. A sheet argument adds a sheet prefix.

TEXT
=ADDRESS(7, 3)               -> $C$7
=ADDRESS(7, 3, 4)            -> C7
=ADDRESS(7, 3, 4, TRUE, "Jan") -> Jan!C7

Because it returns text, ADDRESS is usually wrapped in INDIRECT: =INDIRECT(ADDRESS(ROW()-1, COLUMN())) reads the cell directly above the formula. That is a convoluted way to write =A1-style references, but it shows how the pieces fit together.

Run it: a small engine with these functions

Excel cannot run on this site. The stand-in below is a few dozen lines of JavaScript that parses A1 references, ranges and sheet prefixes and implements SUM, COUNTA, OFFSET, INDIRECT, ADDRESS, ROW and COLUMN the way the descriptions above say. It is our model, not Excel's code, and it handles only simple formulas. A blank cell is treated as an empty string.

JS OFFSET, INDIRECT and ADDRESS in a mini workbook

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

No output.

Read the last block of output carefully. When the sheet is renamed, the direct reference =Feb!B2 is rewritten by Excel itself and keeps working, while the text inside INDIRECT("Feb!B2") is only a string. Nobody updates it, so it turns into #REF!. This is the single most common way INDIRECT workbooks break.

Volatile functions: why recalculation matters

Excel normally recalculates only the formulas that depend on a cell you changed. A volatile function is recalculated at every calculation, no matter what changed. OFFSET and INDIRECT are volatile, like NOW, TODAY and RAND. So are all formulas that depend on them. In a small workbook you will not notice. With thousands of formulas, one volatile function in the middle of a chain can make every edit feel slow. See Performance and Spreadsheet Best Practices for measuring the damage.

There is a second problem: auditing. The dependency arrows from Trace Precedents show what a formula mentions. For OFFSET that is only the starting cell, and for INDIRECT it is nothing at all, because the target exists only as text until the formula runs.

The next stand-in finds the static precedents of each formula (the cells its text names) and marks a formula as dirty if one of them changed or if it is volatile. This is a simplified picture of Excel's smart recalculation, not its real algorithm.

JS Which formulas recalculate after an edit?

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

No output.

Editing the unrelated cell F9 still recalculates D3 and D4. Imagine those two formulas sitting in 20,000 rows and you see the cost. Note also that D4 names no precedents at all, so an auditing tool could not draw an arrow for it.

Better alternatives

Before you reach for OFFSET or INDIRECT, check this list:

If you decide to keep INDIRECT, remember that it cannot read from a closed workbook (the result is #REF!), that it is evaluated as text so renaming sheets breaks it, and that you should document it in a note next to the formula.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =OFFSET(A1, 2, 3) return?

    OFFSET moves 2 rows down and 3 columns right from A1, so it lands on D3. Without height and width it returns just that one cell.
  2. 2. Cell A1 holds the text Feb, which is also a sheet name. What does =INDIRECT(A1 & "!B2") do?

    INDIRECT turns the assembled text Feb!B2 into a real reference and returns the value stored there.
  3. 3. Why can a workbook with many INDIRECT formulas feel slow?

    Volatile functions, and every formula that depends on them, are recalculated at each calculation, whatever cell you edited.
  4. 4. What does =ADDRESS(7, 3, 4) return?

    ADDRESS returns text, not a value. The abs argument 4 means fully relative, so the text is C7.
  5. 5. You rename the sheet Feb to February. Which formula keeps working?

    Excel updates real references when sheets are renamed, but the text inside INDIRECT is just a string and is left alone.

Summary

  • ROW and COLUMN return the position of a reference (or of the formula's own cell); ADDRESS turns numbers into address text.
  • OFFSET(ref, rows, cols, [height], [width]) returns a moved and resized reference; INDIRECT(text) turns text into a reference.
  • Both are volatile: they recalculate on every change, and so does everything that depends on them.
  • Auditing arrows cannot follow INDIRECT, and text references break when you rename sheets or close the source workbook.
  • Prefer Tables, INDEX, lookups and the new array functions; keep OFFSET/INDIRECT for cases where nothing simpler works.

Next: ROWS, COLUMNS, TAKE, DROP, CHOOSECOLS and Reshaping Data.