When you build a formula in Microsoft Excel, you typically reference a specific cell directly. For example, if you want to add the values in column A, your formula explicitly states =SUM(A1:A10). But what if you want the user to be able to type a cell address into a completely different box, and have your formula dynamically update based on what they typed?
To convert a standard text string into a functional, calculated cell reference, you must use one of Excel’s most powerful and misunderstood tools: the INDIRECT function.
Understanding the INDIRECT Syntax
The INDIRECT function takes a string of text (like “B5”) and forces Excel to treat it as a real mathematical cell address.
=INDIRECT(ref_text, [a1])
- ref_text: The cell reference provided as a text string, or a reference to a cell that contains a text string.
- a1: (Optional) A logical value that specifies the reference style. TRUE is the standard A1 style. FALSE is the older R1C1 style. If omitted, it defaults to TRUE.
Example 1: The Basic Concept
Let’s look at the absolute simplest use case to understand the mechanics.
- In cell A1, type the number
500. - In cell C1, type the text string
A1. - In cell E1, type the formula
=INDIRECT(C1).
What happens? Cell E1 will display 500.
The formula looked at cell C1, saw the text “A1”, and indirectly routed the calculation to cell A1, pulling the value of 500. If you change the text in C1 to a different cell address, the output in E1 will dynamically change.
Example 2: Dynamically Referencing Different Worksheets
The true power of INDIRECT is realized when you have a workbook containing multiple sheets (e.g., “January”, “February”, “March”), and you want to pull a specific data point (like total revenue in cell B20) from one of those sheets into a master summary page.
Normally, a static formula looks like this: ='January'!B20.
Instead of manually typing that formula for every single month, you can use INDIRECT.
- On your Master sheet, in cell A2, type the word
January. - In cell B2, type the following formula:
=INDIRECT("'" & A2 & "'!B20")
How Excel processes this:
It concatenates (combines) the apostrophe, the word “January” from cell A2, the closing apostrophe, the exclamation point, and the cell reference B20. It builds the text string 'January'!B20, and then the INDIRECT function converts that string into a live reference.
You can now copy that formula down a column. If cell A3 says “February”, the formula will automatically pull the data from the February worksheet. No manual editing required.
A Critical Warning About Volatility
While powerful, the INDIRECT function is known as a “volatile” function in Excel. This means that every time you make a change anywhere in the entire workbook, Excel recalculates every single INDIRECT formula, even if the data they are pointing to hasn’t changed.
If you use a few dozen INDIRECT formulas, you will not notice a difference. However, if you build a massive financial model using ten thousand INDIRECT formulas, your workbook will become incredibly slow and may freeze every time you press Enter. Use it strategically, not everywhere.