Making Sure a Reference is Always A1

• Reading time: 2 mins read

Let’s take an example where we add up the range A1:10 – see image below.

Then someone inserts rows above A1 – see image below.

How can we make sure the range adjusts to include the inserted rows?

INDEX function solution

We can use the INDEX function to create a reference to the top left cell in the sheet – see image below.

This formula will always refer to cell A1.

Note: this will refer to cell A1 even if columns are inserted – see image below.

INDIRECT function solution

You can also use the INDIRECT to ensure you always refer to cell A1. See image below.

The INDIRECT converts the text A1 into a reference to cell A1.

Note: This also refers to cell A1 even if columns are inserted – see image below.

Volatile Warning

The INDIRECT function is volatile. This means it calculates every time Excel does. Most functions only re-calculate when their reference change. In larger models, volatile functions can impact calculation speed.

Auditing

Both these techniques impact being able to audit the formulas.