Yes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named in its text reference have not changed. That can add unnecessary work in a large workbook, although a few INDIRECT formulas in a small file are usually not a problem.
What volatility means in Excel
Excel normally tracks which cells a formula depends on. If a value changes, Excel can recalculate formulas that rely on that value without recalculating every formula in the workbook. A volatile function is an exception: Excel recalculates it during each calculation cycle, whether or not its apparent inputs changed.
That distinction matters because recalculation cycles happen for many reasons, including edits to cells, opening a workbook, and requesting a calculation. A volatile formula does not necessarily recalculate every time the screen refreshes, but it can run far more often than a formula whose inputs have remained unchanged. Formulas that depend on the volatile result may also need to recalculate.
INDIRECT is volatile because it turns text into a cell or range reference. Excel cannot treat the text argument as a normal, fixed reference in the same way it can track a direct reference such as A1. Volatility helps Excel keep results current when the text describes a different address, but it can reduce the benefit of Excel’s usual dependency-based calculation.
How INDIRECT works
INDIRECT accepts text that describes a reference and returns the reference’s value or range. Its syntax is INDIRECT(ref_text, [a1]). The required ref_text argument is the address written as text. The optional a1 argument tells Excel whether that text uses A1-style notation; TRUE or omitted means A1 style, while FALSE means R1C1 style.
For example, suppose cell A2 contains the sheet name North, and cell B2 on a worksheet named North contains 42. The formula below turns the sheet name and address into a reference. With those cells arranged as described, it returns 42:
=INDIRECT("'"&A2&"'!B2")
The single quotes around the worksheet name let the reference work when a sheet name includes spaces, and they are also permitted when the name has no spaces. The exclamation mark separates the sheet name from the cell address. Because A2 supplies part of the reference, changing A2 to another valid sheet name makes the formula point to that sheet instead.
INDIRECT can also return a range for a function such as SUM. Suppose A2 contains North, and North!B2:B100 contains the values 12, 18, and 20, with the remaining cells in that range empty. With that layout, this formula returns 50:
=SUM(INDIRECT("'"&A2&"'!B2:B100"))
This flexibility is the reason people use INDIRECT, and also why it can be harder for Excel to optimize than a formula with a direct range reference.
Why INDIRECT can affect workbook performance
One INDIRECT formula is rarely enough to make a workbook noticeably slow. The concern is scale and repetition: a workbook with thousands of volatile formulas may recalculate them repeatedly, and each formula may in turn make other calculations necessary. If each INDIRECT formula points to a large range or feeds a complicated calculation, the cost can grow.
The impact depends on more than the number of formulas. Relevant factors include how often the workbook recalculates, how large the referenced ranges are, how many formulas depend on the results, and how quickly the underlying calculations run. A workbook with a few INDIRECT formulas that refer to small ranges may feel instant. A model with many such formulas, long dependency chains, and other expensive calculations may pause during edits or recalculation.
Volatility does not mean Excel is always performing a complete calculation of every cell. Excel still uses its calculation engine and dependency information for other formulas. The practical issue is that each volatile formula is repeatedly marked for recalculation, and formulas downstream from it may also need attention. This can consume time even if the text address has not changed.
When using INDIRECT is reasonable
INDIRECT can be a sensible choice when a reference genuinely needs to change based on text, especially in a modest workbook where the formula is easy to understand and maintain. It can help make a report switch between a small number of similarly structured worksheets, or let a user choose a worksheet from a validated list.
Before using it, consider whether the workbook actually needs a reference that changes as text. If users rarely change the target sheet, a direct reference may be clearer and faster. If there are just a few fixed choices, a formula that explicitly handles those choices may be easier to audit. If a workbook is shared with others, the extra flexibility should be weighed against the fact that a misspelled sheet name or invalid address can cause an error.
- Use INDIRECT when a text-built reference provides useful flexibility that would otherwise be awkward to maintain.
- Keep the number of INDIRECT formulas and the ranges they reference as small as practical.
- Make the source of the sheet name or address predictable, such as a validated dropdown, rather than free-form text.
- Check calculation speed after adding the formulas, especially in a workbook with many linked calculations.
Alternatives to consider
The best replacement depends on why the formula uses INDIRECT. For a fixed reference, use a direct reference such as North!B2 rather than constructing the same address from text. Direct references are easier to inspect and let Excel track dependencies normally.
If the goal is to select among a limited set of ranges, consider a formula that names those choices explicitly, or use a suitable lookup design. For calculations across similar worksheets, a consistent workbook structure and ordinary references may be simpler than dynamically composing sheet names. When many worksheets hold records of the same kind, combining the records into one Excel Table can often make analysis easier than switching between sheets.
INDEX can be useful when the goal is to choose a position within a known range, rather than turn arbitrary text into a reference. It is generally nonvolatile, though the exact formula design matters. XLOOKUP can find a matching value in a defined lookup range, but it is not a drop-in replacement for every INDIRECT formula: it looks up data rather than interpreting text as a worksheet address. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
Power Query is another option when the task is to bring data from multiple tables or worksheets into a combined result. It is often a better fit for repeatable data preparation than creating large networks of text-built references. It does require a different workflow: users refresh a query to update its output, rather than relying on a formula to recalculate cell by cell.
Common errors and limitations
INDIRECT usually returns #REF! when its text does not describe a valid reference. Common causes include a misspelled sheet name, a missing exclamation mark, an invalid cell address, or a cell containing an unexpected blank or extra space. If the reference is assembled from several cells, inspect the final text logically: each piece must combine into a valid address.
Worksheet names containing spaces should be enclosed in single quotes within the reference text, as in the examples above. Names with apostrophes need particular care because an apostrophe inside a quoted sheet name must be represented correctly in the reference. A controlled list of worksheet names can reduce these errors. Also check whether the name in the source cell matches the tab exactly; leading or trailing spaces can prevent a match.
INDIRECT has an important external-reference limitation: it generally cannot retrieve a reference from a closed external workbook. A text reference to another workbook may return #REF! if that source workbook is not open. This makes INDIRECT less suitable for workbooks that depend on files users may close or move.
Finally, INDIRECT does not make a formula immune to changes in workbook structure. If a referenced sheet is renamed or deleted, text that still contains the old name may stop working. Direct references are often adjusted by Excel when a referenced sheet is renamed; text stored inside a formula is not necessarily updated in the same way. That maintenance risk is another reason to use it only where its dynamic behavior is genuinely needed.
How to assess and reduce the impact
If a workbook feels slow, do not assume INDIRECT is the only cause. Large array calculations, repeated lookups, conditional formatting across oversized ranges, external links, and other volatile functions can also contribute. Identify where INDIRECT appears, how many formulas use it, and whether those formulas are copied across large areas.
In Excel for Microsoft 365 on Windows, check the calculation setting at Formulas > Calculation Options. Automatic calculation keeps results current as you edit; Manual calculation can defer recalculation until requested, but it also means displayed results may be out of date. Manual mode is a temporary performance choice, not a fix for inefficient formulas, and a workbook saved with that setting may affect expectations when another person opens it.
To reduce unnecessary work, replace fixed text-built references with direct references, avoid copying INDIRECT into cells that do not need it, and narrow large referenced ranges where possible. Keep source data organized consistently, and test changes on a copy if the workbook is important. Compare the time needed for common edits and recalculation before and after changing the design; a small controlled comparison is more useful than assuming that every volatile function causes a visible slowdown.
Frequently Asked Questions
Is INDIRECT volatile in every version of Excel?
INDIRECT is documented as a volatile function in Excel, including Microsoft 365. Its volatile behavior is not a setting that can be switched off while continuing to use the function.
Does INDIRECT recalculate every time I type in a cell?
It recalculates during Excel calculation cycles. In Automatic mode, edits commonly trigger calculation, but the exact work depends on the workbook and calculation engine. It is more precise to say that INDIRECT recalculates whenever Excel recalculates, not that it runs on every screen action.
Will a few INDIRECT formulas make Excel slow?
Not necessarily. A small number of formulas in a small workbook may have no noticeable effect. Performance depends on formula count, range size, dependent calculations, and how often recalculation occurs.
Can I make INDIRECT nonvolatile?
No. There is no argument or calculation option that changes INDIRECT itself into a nonvolatile function. To avoid its volatile behavior, redesign the formula using direct references, a lookup, INDEX, a table, or another approach that fits the task.
Why does INDIRECT return #REF! for another workbook?
A common reason is that the external source workbook is closed. INDIRECT generally requires an external workbook reference to be available while that workbook is open; also verify the workbook path, sheet name, and address.
Related
- Excel SUM and Hidden Cells: What Gets Included in Totals?
- Excel SUM and Multiply: How to Combine Them
- Excel Sum Minus: How to Add and Subtract Numbers in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Variance Formula in Excel: Calculate Percentage VarianceTo calculate percentage variance in Excel, subtract the reference value from the actual value and divide by the…
- Excel IF OR Formula: Combine Multiple Conditions EasilyTo use Excel’s IF with OR, put OR in IF’s logical-test position: Excel checks whether at least one…
- DATEDIF Excel Function: Examples and Common IssuesExcel’s DATEDIF function calculates the time between two dates in years, months, or days. For example, with a…
- Clear Excel Sort: Remove Sorting or Restore Original OrderTo clear a sort in Excel for Microsoft 365 on Windows, select a cell in the sorted data…
- Excel INDEX MATCH With Table Structured ReferencesYes—Excel INDEX MATCH works with table structured references. For example, if a table named Inventory has columns Item…
- Excel INDEX Formula Examples for Rows, Columns, and MatchesExcel’s INDEX function returns the value at a chosen position in a range. With values in A2:C5, =INDEX(A2:C5,3,2)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.