Both create dynamic cell references — but INDIRECT uses text and OFFSET uses numbers. Here's when each one is right.
Both INDIRECT and OFFSET create dynamic references — ranges that change based on other values in your spreadsheet. But they do it in completely different ways.
=INDIRECT(ref_text, [a1])
=INDIRECT("A1") references cell A1
=INDIRECT("Sheet2!A1") references A1 on Sheet2
=INDIRECT(A2) references whatever cell address is stored in A2INDIRECT converts a text string into a real cell reference. If A2 contains the text "B5", then =INDIRECT(A2) returns the value in cell B5.
=INDIRECT(B1&"!A1")
-- If B1 contains "January", this references January!A1
-- Change B1 to "February" and the formula references February!A1=OFFSET(reference, rows, cols, [height], [width])
=OFFSET(A1, 2, 3) move 2 rows down and 3 columns right from A1 = D3
=OFFSET(A1, 0, 0, 5, 1) a range starting at A1, 5 rows tall, 1 column wideOFFSET starts at a reference cell and moves a specified number of rows and columns to return a new reference. The optional height and width arguments make it return a range rather than a single cell.
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)
-- A range starting at A1 that automatically grows as data is added to column A
-- Use this as a named range source for charts or dropdownsINDIRECT and OFFSET recalculate every time anything in the workbook changes — even unrelated cells. On large spreadsheets this can significantly slow calculation. Avoid them in large datasets and prefer structured Table references or XLOOKUP instead where possible.
| Feature | INDIRECT | OFFSET |
|---|---|---|
| Creates reference from | Text string | Numeric offsets from a base cell |
| Dynamic sheet references | ✓ Best option | ✗ Not supported |
| Dynamic range sizing | Limited | ✓ Height/width arguments |
| Volatile (slow on large files) | Yes | Yes |
| Works with named ranges | ✓ Yes | ✗ No |
INDIRECT when you need to reference different sheets dynamically based on a cell value. OFFSET when you need a range that grows automatically or when you need to move a fixed number of rows/columns from a starting point. Avoid both in performance-sensitive large files.
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →