About four years ago I was asked to give a presentation on Excel tips and tricks. That can be tricky because of the various backgrounds of the participants. One person’s tip is old hat to someone else. I needed a system to determine what tips and tricks to include. I decided to ask some fellow Excel users to give me their best Excel “stuff” be it a formula, function, methodology or whatever that has helped him or her to become more productive using Excel. I wanted to compile the best tips and tricks I received back from them. The answers were varied and ranged from the simple to the complex, from the common to the obscure, but one response stood out from all of the rest. It came from my friend and mentor Gregg Bashur.
He had put together a template using the reference function “INDIRECT”. I called Gregg when I received the template to discuss it. We both agreed that the INDIRECT formula is one of the most powerful yet underused formulas there is. Indeed I demonstrated Gregg’s template to a class last week. The class size was approximately 180 and not one person had ever used or heard of the INDIRECT formula.
Go to the formula tab and click on INDIRECT under the Lookup and Reference group.
Click on “Help on this function” and the following definition is found:
“Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.”
Simply put the INDIRECT formula returns the value of a cell reference.
In the Excel sheet to the left the formula in cell B7 is =Actual!P7 (the value of the cell P7 in the worksheet tab labeled “Actual”).
I can change the formula to =INDIRECT(“Actual!P7”) which will return the same value as =Actual!P7.
Further, I can split apart the components of the INDIRECT formula by stringing them together with ampersands.
Thus, the formula can be revised to =INDIRECT(“Actual”&”!”&”P”&7) which again will return the same value as =Actual!P7. Notice that labels have to be enclosed in quotation marks within the formula. Finally, I can make reference substitutions to one or more of the components within the INDIRECT formula.
The formula now reads =INDIRECT(B6&”!”&”P”&7) which yet again will return the same value as =Actual!P7. That is because the value in cell B6 is “Actual”.
Now I can change the value in B6 by using a data validation list.
The value of my INDIRECT formula in cell B7 has changed from 5,048 to 5,204 when the value in cell B6 changed from “Actual” to “Plan”. The formula =INDIRECT(B6&”!”&”P”&7) now returns the value =Plan!P7. It is now returning the value of the cell P7 on the worksheet tab labeled “Plan”.
Imagine the wonderful things that can be accomplished by using the INDIRECT formula; quick comparisons of actual versus budget, profit center combinations, financial statement consolidations. The limits are boundless. The reason is INDIRECT.