FINALLY, Stop Cell References from Changing when you don’t want them to

Posted by Colleen Ludgate on Wednesday, Mar 21st, 2018
Category : Microsoft Excel, Resources

FINALLY, Stop Cell References from Changing when you don’t want them to

One of the great things about Microsoft Excel is you can copy and paste formulas and they still work relative to where the formula now lives.

You can get around this behavior if you want to also. When you create a formula and reference a specific cell, and you want that reference to remain correct even when the formula is moved, or you add or delete columns or rows, then you should use an absolute cell reference.

But there is one situation where even absolute cell references won’t help. In most cases when you add a row or column you would want the formula to reference the original (now moved) cell, but there are some cases you don’t want the cell reference to change even when inserting a row or a column. This article shows how to work around this Excel behavior so you can force it to reference the exact cell you choose.

A Working Example

Imagine you are a swimming coach wanting to keep a track of your swimmer’s recent average swim times.

We could have columns to record the last nine swim times then calculate the average timing of the last three swims and the average time of all the swims.

Every time we monitor a swimmer their latest swim time is inserted as the first column of the spreadsheet, and the ninth time column is deleted.

Obviously want the formulas in the last two columns where we calculate the averages to always remain the same, even after all the deletions and the insertions.

An INDIRECT Solution

Our solution is to use a little-known Excel function called INDIRECT.

Indirect allows you to specify an exact cell and Excel will go and fetch the contents of that precise location.

What would you normally use?

=AVERAGE(B5:J5)

As you can see in the screenshot below, once you have added and deleted columns, this no longer works as a solution:

We would instead use:

=AVERAGE(INDIRECT("B" & ROW() & ":J" & ROW()))

Summary

Sometimes Excel seems to work so hard to be helpful that it works against what you want, but there is almost always a way to get the result you need. Lucky for us in this case the INDIRECT() function is an easy solution!

P.S.

Don’t forget to check out our PDF to Excel Converter. It can save you a lot of precious time and improve your productivity.

Latest From Our Blog