Excel Indirect Function Tutorial
Welcome to our Excel Indirect Function Tutorial!
BY CHRIS MORTIMER
Welcome to Tiger’s Excel Indirect function tutorial. The Indirect formula has super powers that could be a game-changer for you. This example is inspired by a recent work situation where the formula saved me a huge amount of time.
How could we summarise Indirect’s ‘magical powers’. Well, let’s suppose you wish to apply a formula to multiple sheets. In my example, I wish to see how many times data appears on datasets from different US states. We can use Countif to do this, as Chris demonstrates in the video. And, voila! The analysis is complete.
So, what’s the problem? The problem is that we need to apply the same analysis to more than one sheet. In the example we have four sheets, but you might have fifty, or more. Can you imagine building the formula fifty (fifty!) times, in order to apply it to different sheets. This would be intensely frustrating!
The fact is that there are too many people out there trying to do this kind of manual task in Excel. I wish a few more of them watch the Tiger channel 🙂
This is where Indirect changes the game. Indirect converts the contents of a cell into a text string and allows us to use the text string in a formula. Such a technical exposition does not sound exciting; so, how else can we express it? How about ‘Indirect allows you to input sheet names into column headers, and to directly reference the sheet names in formulae’?
Perhaps this sounds more exciting. No matter, Chris demonstrates how the formula works in the video. Correctly implemented (the formula is notoriously tricky to work with), Indirect allows us to control the sheet that a formula looks at by simply changing the value in a cell. Crazy! And incredibly cool.
The implications are significant. I managed to speed up a task that would have taken most an afternoon, and complete it in just a few minutes.
Did you manage to complete the task in the video? What have you managed to accomplish using the Indirect formula? We hope the tutorial was helpful for you.