Once you learn how to use the indirect function in Excel, you can pull information from other sheets, reference named ranges, and combine it with other functions to create a truly versatile tool. It may take some practice to master, but you can do more with the indirect function than you might think.

The indirect function is a way to convert a text string into a reference. That is, it extracts information from a reference to another cell or range. It creates a reference from text and does not change when cells, rows, or columns are changed, added, or deleted from a referenced range. The references it creates are evaluated in real time, so the reference is always accurate for the data it is drawing from.

If this feels a little confusing, don’t worry. The indirect formula can be easier to understand with valid examples and practice. If you’re in doubt, try following the steps below and you’ll get the hang of it in no time.

Named ranges in Excel are a great way to collect data under a single reference, and the indirect function makes extracting that information from them a lot easier. Here's how to do it: