Why Named Ranges Are A Necessary Nuisance in Excel
Named ranges are not always simple to manage in Excel, but they are worth the effort.
In Brief
• The use of named ranges strongly encourages you to use great model structure.
• Named ranges are critical in macros/VBA.
• They can avoid you having to use INDEX or VLOOKUP.
By Ian Bennett FCA
When you get to the world of advanced financial modelling, you can find very strongly held views on big topics. Emotions run so high you start to understand why most financial modelling standards have been written by one person.
Named ranges are such a topic. I’m often told named ranges are opaque and too difficult to manage. Such feedback would usually be sufficient for me to toss named ranges onto the axis of spreadsheet evil and leave them there, but in this case, the pain is worth the benefit.
Figure 2: Named Ranges – the necessary nuisance (Click image to enlarge)
Figure 3: Name Manager Box (Click image to enlarge)
I don’t have space here to teach about named ranges, but I love them because:
• they’re awfully versatile
• they’re critical in macros/VBA
• they can be created directly from their labels using Ctrl-Shift-F3 (see figure)
“I love that named ranges think like Excel thinks, and their use strongly encourages you to use great model structure and conform to modelling best practices.” Ian Bennett FCA
Excel uses typeahead and I just have to hit tab to add them to my formula.
But mostly I love that named ranges think like Excel thinks, and their use strongly encourages you to use great model structure and conform to modelling best practices.
For example, named ranges expect you to have consistent column headings on every worksheet: if July 2019 is in column U on one worksheet, then it should be in column U on every worksheet.
If I create a named range that runs the length of my timeline called Revenue, then if I type =Revenue into a cell anywhere in my model, I will get the value in the named range which is in the same column as my formula. No need for an INDEX or a VLOOKUP. Plus, if I put my formula in a column outside my timeline, it tells me I’m wrong by showing a helpful #VALUE!.
Named ranges can get away from you, so regularly hit Ctrl-F3 and run your eye down that Name Manager (see figure) and check there’s nothing untoward.
In particular, you should watch out for:
• #REF named ranges
• External named ranges
• Overlapping named ranges
• Local named ranges
• Hidden named ranges.
Your questions answered
Have a financial modelling or spreadsheet question you’d like answered in Acuity ? Send it to acuityeditorial@mediumrarecontent.com with ‘Ask Ian’ in the subject line.
This article was first published on Accuity Mag website. To view the original story click here
