INDIRECT Function in Excel: Build References From Text

The INDIRECT function in Excel builds a reference from text. You give it an address written as text, such as "B5" or "Feb!B5", and it returns whatever is in that cell. This lets one formula switch sheets or ranges when you change a word in a cell. This guide is for anyone who has seen INDIRECT in a template or a job test and wants to know how it works, when it helps and when a simpler formula is better.

What does INDIRECT do in Excel?#

INDIRECT reads text and treats it as a cell reference. A normal formula like =B5 points straight at B5. =INDIRECT("B5") points at the text "B5" first, then follows it to the cell. The result is the same, but now the address is just text, so you can build it with other formulas.

=INDIRECT(ref_text, [a1])

INDIRECT works in every version of Excel for Windows and Mac, and in Excel for the web. If ref_text is not a valid reference, it returns #REF!.

How to build a reference from text#

You build the text with the & operator, which joins pieces of text together. Say cell D1 holds the number 4:

=INDIRECT("B"&D1)

Excel joins "B" and 4 into "B4", then returns the value in B4. Change D1 to 7 and the formula reads B7 instead. The same idea works for a whole range: =SUM(INDIRECT("B2:B"&D1)) adds B2 down to the row number in D1.

If joining text is new to you, the lesson on joining text with & and CONCAT covers it step by step. It is the skill INDIRECT depends on.

How to use INDIRECT with a sheet name in a cell#

The most common use of INDIRECT is to read the same cell from a sheet whose name sits in a cell. Imagine a workbook with tabs named Jan, Feb and Mar. Each tab has its monthly total in B5. On a summary sheet, A2:A4 hold the words Jan, Feb and Mar. In B2, type:

=INDIRECT("'"&A2&"'!B5")

Fill it down to B4 (Windows: Ctrl+D; Mac: ⌘D). Each row now shows the B5 total from its own tab.

Look closely at the quotes. The text being built is 'Jan'!B5:

• "'" adds a single quote before the sheet name.
• A2 adds the sheet name.
• "'!B5" adds the closing single quote, the ! and the cell address.

The single quotes are only required when a sheet name has a space or a special character, like Last Year. Adding them every time is a good habit, because they never hurt. To see how normal cross-sheet references look, read how to reference another sheet in Excel, or try the worksheets and cross-sheet references lesson.

How to use INDIRECT with named ranges#

INDIRECT also turns a word into a named range. A named range is a label you give to cells, like calling B2:B5 "Feb". The guide to named ranges in Excel shows how to create them.

If the columns in the exercise table below were named Jan, Feb and Mar, and G1 held the word Feb, this would total February:

=SUM(INDIRECT(G1))

The result is 37,000 (13,100 + 9,200 + 8,050 + 6,650). Type Mar in G1 and the total switches to March.

This is how a dependent drop-down list works. The first list picks a category, and the second list uses =INDIRECT(A2) as its source, so it shows only the named range that matches the first choice.

Why INDIRECT returns #REF! (and other problems)#

INDIRECT fails when its text no longer matches a real reference. Check these causes:

1. A renamed or misspelled sheet. If the tab Feb becomes February, the text "Feb!B5" points at nothing. Normal references update when you rename a sheet. INDIRECT does not.
2. Missing single quotes. =INDIRECT(A2&"!B5") fails when A2 holds Last Year, because of the space.
3. A deleted or renamed named range.
4. A closed workbook. INDIRECT can read another workbook only while that file is open. When it is closed, the result is #REF!.
5. Inserted rows. =INDIRECT("B5") always reads B5, even after you insert a row above it. Sometimes that is what you want, but often it quietly reads the wrong cell.

There is also a speed cost. INDIRECT is volatile, which means Excel recalculates it after every change anywhere in the workbook, not just when its inputs change. A few INDIRECT formulas are fine. Thousands of them in a large model can make every edit feel slow.

What to use instead of INDIRECT#

Most INDIRECT formulas can be replaced by a non-volatile formula that does the same job. Use these when you can:

In the INDEX version, MATCH finds the position of Feb in the headers (2). INDEX with 0 as the row number returns that whole column, and SUM adds it. The result is the same 37,000, with no volatility.

The guides on summing the same cell across sheets and VLOOKUP across multiple sheets show more of these patterns.

When INDIRECT is still the right tool#

INDIRECT earns its place when the sheet or range name must come from a cell and there are too many options for CHOOSE. Typical cases are a summary sheet that reads 12 monthly tabs by name, a dependent drop-down list, and a template where users type a sheet name to report on. Keep the workbook small, keep sheet names stable, and add IFERROR around the formula if a missing sheet should show a message instead of #REF!:

=IFERROR(INDIRECT("'"&A2&"'!B5"),"No such sheet")

Practise it#

The exercise on this page uses the non-volatile INDEX and MATCH pattern, because INDIRECT isn't part of the practice grid. The two skills behind INDIRECT are practised there, though. Practise cross-sheet references in a live workbook, then practise joining text with & so building addresses like 'Jan'!B5 becomes second nature. Each step checks your formula instantly.