How to Stop Cell References from Changing When Inserting Columns or Rows

We have a special treat for you today. Our second guest blogger is Yoav Ezer, who is going to share with us a technique of how to stop a cell reference from changing when inserting a column or row.  I really appreciate Yoav taking the time and sharing his insight, experience, and wealth of information with us today. Now, on to the article!

------------------------------------------------------

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!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter