Use the INDIRECT route
The Excel INDIRECT function is one many people don’t know about. And for those who do, many/most will tell you not to use it. But I beg to differ. Used correctly and sparingly, this can be a great way to make your models more understandable, and also prevent errors creeping into your models in the future.
Let’s say you’ve selected cell E20 and want to refer to the cell above it. And let’s say that, no matter what you do to the cells above E20, such as insert rows, delete rows, delete cells, etc., you always want the formula in E20 to refer to the cell directly above it.
The way to achieve this is to enter the following formula
This is effectively saying “give me the value of the cell one row above me and in my column”.
The thing that many experts have against this function is that it is a “volatile” function. That is, it is recalculated every time Excel recalculates, regardless of whether or not the cells the formula is dependent upon have changed. Volatile functions can reduce the performance of large and complicated models. I find that many of our models are not overly large and do not suffer from slow calculation, so using INDIRECT brings benefits without any adverse effects on speed.
Note: the OFFSET function does a similar thing to INDIRECT, however, for the example used above I find the syntax of INDIRECT easier to understand. Try both and decide which works better for you.