Excel INDIRECT and OFFSET

INDIRECT and OFFSET are two functions that create references to cells dynamically — meaning the reference can change based on values in your worksheet rather than being fixed. They are useful for dashboards, dependent dropdowns, and flexible formulas that adapt without manual editing.

INDIRECT — Build a Cell Reference from Text

INDIRECT takes a text string and converts it into a real cell reference. The cell or range it points to changes when you change the text.

=INDIRECT(ref_text, [a1])

ref_text → a text string that represents a cell address
a1       → TRUE (default) for A1-style, FALSE for R1C1-style

Basic INDIRECT Example

A1: "B5"           ← this is just text
B5: 1000           ← this is the actual value

=INDIRECT(A1)  →  1000

Excel reads the text "B5" from A1 and fetches the value from cell B5.

INDIRECT with a Sheet Name

Sheet names: January, February, March

="January" stored in cell A1

=INDIRECT("'"&A1&"'!B5")
→ Looks at the sheet named January and returns the value from B5

Change A1 to "February" and the formula instantly references February!B5.

INDIRECT Diagram

+--------+                     +----------+
| A1     |                     |  Sheet   |
| "Jan"  | ──→ INDIRECT ──→    | Jan!B2   | = 50000
+--------+                     +----------+

Change A1 to "Feb":
+--------+                     +----------+
| A1     |                     |  Sheet   |
| "Feb"  | ──→ INDIRECT ──→    | Feb!B2   | = 63000
+--------+                     +----------+

Using INDIRECT with Named Ranges

Named range "NorthSales" refers to C2:C10
Named range "SouthSales" refers to D2:D10

A1: "NorthSales"

=SUM(INDIRECT(A1))  →  Sums the NorthSales range

Change A1 to "SouthSales" and the SUM immediately switches to the south data.

INDIRECT with Data Validation Dropdowns

INDIRECT powers dependent dropdown lists. If a user selects a category in one dropdown, INDIRECT makes the second dropdown show only items from that category.

Cell A1 has a dropdown: Fruits | Vegetables | Grains

Named ranges:
  Fruits     → Apple, Banana, Mango
  Vegetables → Carrot, Onion, Tomato
  Grains     → Rice, Wheat, Corn

Data Validation for B1 (Source):
=INDIRECT(A1)

When A1 = "Fruits", B1 dropdown shows: Apple, Banana, Mango
When A1 = "Vegetables", B1 dropdown shows: Carrot, Onion, Tomato

OFFSET — Reference a Cell at a Distance

OFFSET starts at a base cell and moves a specified number of rows down and columns to the right, then returns the value at that new location. It can also return a range of a specified height and width.

=OFFSET(reference, rows, cols, [height], [width])

reference → the starting cell
rows      → how many rows to move (positive = down, negative = up)
cols      → how many columns to move (positive = right, negative = left)
height    → optional: how many rows tall the returned range is
width     → optional: how many columns wide the returned range is

OFFSET Examples

Starting cell: A1

=OFFSET(A1, 2, 3)
→ Moves 2 rows down and 3 columns right → returns value from D3

=OFFSET(A1, 0, 1)
→ Moves 0 rows, 1 column right → returns value from B1

=OFFSET(A1, -1, 0)
→ Moves 1 row UP → returns value from A0 (will error if above row 1)

OFFSET with Height and Width

=SUM(OFFSET(A1, 1, 0, 5, 1))
→ Starts at A1
→ Moves 1 row down (to A2)
→ Returns a range that is 5 rows tall and 1 column wide (A2:A6)
→ SUM adds the values in that range

OFFSET Diagram

    A    B    C    D    E
1 [A1]  ...  ...  ...  ...
2  ...  ...  ...  ...  ...
3  ...  ...  ... [D3]  ...  ← =OFFSET(A1, 2, 3)

Steps:
Start at A1
↓ down 2 rows
→ right 3 columns
= D3

Dynamic Range with OFFSET for Charts

OFFSET builds a dynamic named range that grows or shrinks as data is added. This makes charts automatically include new data.

Define a named range:
Name: SalesData
Refers to: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

COUNTA counts non-empty cells in column A.
OFFSET uses that count as the height.
As you add more data rows, the range expands automatically.

INDIRECT vs OFFSET Comparison

Volatile Functions Warning

Both INDIRECT and OFFSET are volatile. A volatile function recalculates every time anything in the workbook changes — even if the cells it depends on have not changed. In large workbooks with many volatile functions, this slows calculation speed noticeably. Use these functions deliberately and avoid applying them to thousands of cells where a static reference would work just as well.

For modern replacements, Excel 365 and 2021 offer dynamic array functions like FILTER and XLOOKUP that achieve similar goals without the volatility penalty.