Why Some Google Sheets Formulas Use INDIRECT() Instead of Direct Cell References

When I first learned Google Sheets, I assumed the shortest formula was always the best one.

Photo by Rubaitul Azad on Unsplash

If I could write a simple reference like this:

=A2

instead of something more complicated, why wouldn’t I?

Then I started working with spreadsheets that other people edited every day.

Rows were inserted. Rows were deleted. Data was moved around. Entire sections of worksheets were reorganized. Before long, I realized that a formula that looked cleaner became unreliable.

Sometimes, a more complicated formula exists because it’s designed to survive changes in the spreadsheet.

A simple example

Suppose you want to convert values from another worksheet into plain numbers. A straightforward solution is this because it is easy to read:

=MAP('Compiled'!B2:B, LAMBDA(value,
  IF(value<>"", TO_PURE_NUMBER(value), "")
))

For every value in column B, if it isn’t blank, convert it into a pure number. Otherwise, leave the result blank. So, if you’ve recently discovered MAP() and LAMBDA(), this formula feels like a huge improvement over dragging formulas down an entire column.

So what’s the problem?

The formula itself isn’t the problem. The spreadsheet is.

Imagine a shared Google Sheet where multiple people regularly add new data, insert rows, delete rows, or reorganize existing information. And then, Google Sheets tries to help by automatically updating references.

If your formula points to: ‘Compiled’!B2:B and someone deletes rows or inserts new ones, Google Sheets may adjust that reference automatically.

Most of the time, that’s exactly what you want. The spreadsheet stays consistent, and you don’t have to fix references manually. There are situations, however, where you want the reference to remain exactly as you wrote it.

Enter INDIRECT()

Instead of referencing a range directly, you can store the reference as text.

=INDIRECT("'Compiled'!B2:B")

Because the reference is now a text string, Google Sheets generally doesn’t rewrite it when rows or columns are inserted or deleted. You can even combine this approach with MAP().

=MAP(
  INDIRECT("'Compiled'!B2:B"),
  LAMBDA(value,
    IF(value<>"", TO_PURE_NUMBER(value), "")
  )
)

This gives you the readability of MAP() while making the source reference less likely to change automatically.

Isn’t INDIRECT() supposed to be slow?

Yes. INDIRECT() is considered a volatile function, which means it recalculates more frequently than ordinary references. In very large spreadsheets with thousands of formulas, excessive use of INDIRECT() can affect performance.

But that doesn’t mean you should avoid it completely. Every spreadsheet is a balance between performance, readability, and reliability.

If a workbook is used by several people who frequently restructure data, preventing references from changing automatically may be more valuable than saving a small amount of calculation time.

As with most spreadsheet functions, context matters.

Choosing the right approach

If your spreadsheet is relatively small and only you maintain it, a direct reference is usually the simplest option.

• If you’re applying the same calculation across an entire column, ARRAYFORMULA() or MAP() can eliminate the need to drag formulas down manually.
• If you’re working in a collaborative workbook where rows are regularly inserted, deleted, or reorganized, using INDIRECT() may help keep important references stable.

None of these approaches is universally better than the others. They solve different problems.

The lesson I learned

I used to assume that every long formula could be shortened. Now I know better.

Sometimes a formula looks complicated because someone added extra pieces after encountering real problems in a production spreadsheet. A function that seems unnecessary at first glance may actually be protecting the workbook from accidental changes months later.

Before simplifying an existing formula, it’s worth asking one question: Why was it written this way in the first place?

Understanding the purpose behind a formula is often more valuable than making it a few characters shorter.

The contents of external submissions are not necessarily reflective of the opinions or work of Maven Analytics or any of its team members.

We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.

*Submit your own writing here if you’d like to become a contributor.*

Happy learning!

-Team Maven