Have you ever written a formula that was perfectly correct, but it slowed down your workbook as more data and formulas were added? In some cases, it's not the size of your data set or even the complexity of your calculation. It can be a function that you use to be more flexible when writing formulas.

An example of one such function is “ INDIRECT() ”.

It is very handy to have when you want to dynamically create cell references. You may use it to refer to different worksheets, to change between ranges or to make dropdowns that rely upon a customer's selection. However, there is a problem. The INDIRECT() function is a volatile function in Excel. This means it may be recalculated whenever the spreadsheet recalculates, even if the cells that it references have not changed. This can be a wasteful calculation if this is done in a large workbook that is used a lot.

So, is it time to put away INDIRECT() ? Not necessarily. The true lesson is figuring out when its flexibility is justified and when it's preferable to take another tack.

When a INDIRECT() function is used?

Imagine you're managing inventory for several different stores. Each store has its own worksheet containing product names, prices, and stock information. You also have a central Admin sheet where users select a store and view its products.

It can be challenging to create an "INDIRECT()", or reference, that will change with the store selection. With it, the formula is not so complicated. If, for instance, you have the name of a worksheet (Store_A) in cell B2, you can get the value in cell A2 of that worksheet by writing.

You could use:

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

B2 evaluates the reference to 'Store_A'! when it is in B2. When B2 has Store_A, Excel evaluates 'Store_A'.A2. If the chosen sheet changes, the formula can refer to the appropriate cell on the new sheet. The key feature of INDIRECT is that it converts text to a cell reference. When data comes from various sources, as in dynamic dropdowns, multi-sheet reports and workbooks, it can be very convenient. The difficulty starts with using this convenience more than once in a workbook.

The Hidden Performance Cost

Let's first examine how Excel evaluates formulas. By default, Excel keeps track of cells that are dependent on others. If something changes, it can recalculate the formulas which are affected, but not need to recalculate everything unnecessarily.

But INDIRECT() is volatile. The result is displayed in Excel as an entity that can be recalculated whenever a recalculation event is triggered; even if the cell that it references does not change. Additionally, Microsoft recognizes INDIRECT as a single-threaded function and may not be able to parallelize its calculation.

Now, suppose there is a workbook with hundreds or thousands of formulas that are using INDIRECT(). Now suppose there is a workbook that has hundreds or thousands of formulas using INDIRECT(). Each formula may be perfectly valid. However, when combined, they can make Excel's job more difficult. When this becomes more evident it is when:

• A workbook contains many formulas using INDIRECT().

• Those formulas are copied across thousands of rows.

• Other calculations depend on their results.

• Users frequently edit data, filter tables, or interact with the workbook.

• Complex formulas repeatedly combine INDIRECT() with other functions.

The key point is that INDIRECT() doesn't cause any workbook to be slow. It's effect will be based on the extent of use and what relies upon its outcome and how much work the formulas do.

Real-World Example: Dynamic Product Lists in an Inventory Workbook

Imagine a retail business that manages inventory for seven stores. Each store has its own worksheet containing product names, stock quantities, and reorder details. The business also uses a central worksheet where staff can select a store from a dropdown and view its product list.

To make this work, a user can use INDIRECT() in a formula to build a reference to the worksheet. For example:

=INDIRECT("'"&B2&"'!A2:A100")

If cell B2 contains Store_A, the formula returns a reference to the range A2:A100 on the Store_A worksheet.

This approach is flexible because users can switch between stores without manually changing the formula. However, the workbook may become harder to maintain as the business adds more stores, reports, and dynamic dropdowns that rely on similar references.

In Excel, INDIRECT() is a volatile function, meaning it recalculates whenever Excel recalculates, even when its referenced cells have not changed. If the workbook contains many such formulas, the additional recalculation work can contribute to slower performance.

A more scalable approach might be to combine the inventory records into one structured table with columns such as Store, Product, and Stock. The workbook can then filter the table by the selected store instead of constructing references to separate worksheets.

This change can make the data easier to maintain and reduce the need for dynamic worksheet references. The key lesson is not that INDIRECT() should never be used, but that a formula chosen for flexibility in a small workbook may become a maintenance or performance concern as the workbook grows.

Four Ways to Reduce Dependence on INDIRECT()

You don't need to remove every INDIRECT() formula. Start with the formulas that are repeated frequently or contribute to a noticeable performance problem.

1. Use a consolidated data table

If every store's worksheet contains the same type of information, consider combining the records into one structured table. For example:

Now, instead of dynamically switching between worksheets, you can filter the same table according to the selected store.

In Excel, assuming the table is named Products and the selected store is in cell E2, you could use:

=FILTER(Products[Product],Products[Store]=E2,"No products found")

This removes the need for INDIRECT() in this particular operation.

It also makes adding a new store easier: new records can be added to the same table without creating another worksheet reference for the formula to handle.

This approach works particularly well when the sheets contain similar records that can naturally be stored together.

2. Use direct references when the source is known

If a formula always needs information from a particular worksheet, a direct reference is often simpler.

Instead of:

=INDIRECT("'Store_A'!B2")

Use:

='Store_A'!B2

The second formula explicitly identifies its source and doesn't require Excel to interpret a text string as a reference. Direct references are not suitable for every dynamic-reference scenario, but they are a sensible choice when the source is already known.

3. Calculate dynamic results once and reuse them

Sometimes you genuinely need a dynamic product list. Instead of rebuilding the same list in multiple formulas, generate it in one helper range and reference its results elsewhere.

For example, one formula could produce a filtered product list, while dropdowns or reports reuse that list. This approach can reduce duplicated calculations and makes the workbook easier to troubleshoot.

It also follows a useful principle:

Calculate once → Reuse many times.

The same idea applies to lookups, filtered datasets, summary calculations, and other expensive operations.

4. Use CHOOSE for a small, fixed set of alternatives

When a user can choose from a small, predefined number of worksheets, CHOOSE() may be an alternative to INDIRECT().

For example, if cell E2 contains a number from 1 to 3:

=CHOOSE(E2,Store_A!B2,Store_B!B2,Store_C!B2)

This returns the value corresponding to the selected option.

Unlike INDIRECT(), the references are explicit in the formula. Microsoft lists CHOOSE() as one possible alternative when reducing volatile functions.

However, CHOOSE() is best suited to a small, fixed set of alternatives. It becomes cumbersome when you need to manage dozens of sheets or a frequently changing list of sources. Choose an alternative that simplifies the design rather than replacing one complicated formula with another.

What About Google Sheets?

Google Sheets also supports INDIRECT(), using it to return a reference specified by a text string. Its documented syntax is similar to Excel's.

You can use the same general design principles in Google Sheets:

• Avoid rebuilding the same dynamic reference in multiple places.

• Consolidate similar datasets when practical.

• Use direct references when the source is fixed.

• Generate shared results once and reuse them.

• Keep formulas as simple as the underlying workflow allows.

However, there is an important distinction: Excel's documented volatility and calculation-engine behavior should not automatically be assumed to describe Google Sheets identically. In Google Sheets, test the actual workbook to determine whether INDIRECT() is contributing to a performance issue. Dataset size, formula structure, dependencies, and the number of calculations all matter.

How to Know Whether INDIRECT Is Your Actual Problem

Before rewriting your workbook, take a few minutes to investigate.

Step 1: Search for INDIRECT()

Determine the number of formulas that involve it and where it is found. Be sure to pay special attention to formulas copied in a large range or repeated in multiple sheets.

Step 2: Look at what those formulas do

Are they using different sheets, building dynamic ranges or repeating filters and lookups? Determine if it is possible to accomplish the same task in a simpler manner.

Step 3: Test one alternative

Use a representative sample of formulas in place of the data references or a combined data method, as appropriate.

Step 4: Compare performance

Record the time taken to recalculate or answer common user actions with the workbook before and after the change.

If there is no real improvement, this could be because INDIRECT() is not the primary limitation. This may be due to another formula, connection to an external source, formatting or workbooks. Avoid optimizing a function just because its reputation is that it's slow. Optimize it if you have reason to believe that it is a problem.

Conclusion

INDIRECT() isn't the wrong function. It addresses a particular dilemma: translate text into a function that can be calculated by Excel or a Google Sheet.

The issue gets complicated when we apply this flexibility in all situations – even when a simpler structure would be equally effective.

Before adding another INDIRECT() formula, ask yourself:

• Is the reference genuinely dynamic?

• Could a direct reference solve the problem?

• Could the data be organized into one consistent table?

• Am I repeating the same calculation in multiple places?

• Have I measured whether this formula is actually affecting performance?

These questions will guide you to go beyond merely making the formulas function. Avoiding INDIRECT() is not the objective. Use of dynamic references should be reserved for when they have a real problem to solve, not when they don't.