Named Ranges - The Pros & Cons | Excel Hints & Tips
Named ranges can be very useful in Excel, although there are also users who advise against their overuse.
Here we will look at the pros and cons when using named ranges in your Excel modelling and analysis work.
Can Make Your Model More Readable
Using named ranges can help you and your users of the model easily understand what a formula is doing. For example, take a look at the two below scenarios:
Example 1 - A formula that references cells and doesn't use named ranges.
Example 2 - A formula that uses named ranges to refer to cells.
As you can see, the second example is much more readable and adds structure which assists users in understanding the logic.
They Are Easy to Use in Formulas (Along With Auto-Complete)
Simply start typing the named range into the formula bar and the auto-complete options will appear.
In the example to the left, I typed '=Com' which you can see has highlighted the named range 'Commission' at the top of the list. I can then simply press tab to auto-complete the name.
You Can Refer to Named Ranges From Anywhere in a Workbook
Similarly, you can also refer to a named range from anywhere in the workbook. This makes it useful if you have a cell value that you want to refer to without your model. You can simply use the above method of typing the name in the formula bar to refer to the named cell, regardless of what sheet you are working on within the workbook.
(Note: This is assuming that the named range is a 'global' named range, which is the default and means it's not limited to the worksheet (i.e. 'local'). If the named range was changed to 'local' then you can only refer to it within that sheet, but named ranges are all 'global' by default.
You Can Automatically Create Named Ranges
It is also possible to create named ranges based on data on a worksheet, using the 'Create from Selection' functionality within Excel.
You can then use text in cells as the named ranges, so in the below example we are creating named ranges for North, East, South and West that actually refer to the numbers, rather than the text. We do this by instructing Excel to use the labels on the left of the highlighted range.
This then enables the ability to refer to the named ranges in formulas. In the below example we are adding together North & South.
You Can Easily Manage Names via Name Manager
Excel has built in functionality that allows you to manage named ranges that have been created. This gives you the option to amend, delete or create new named ranges.
You can find this under the 'Formulas' option of the ribbon and then click on 'Name Manager'
Named Ranges Are Essential for VBA
When using VBA code, if you explicitly refer to a range, such as A1:A10 then this can create issues in future, such as if someone was to move that range within Excel to B1:B10 then VBA would not update for this change and would still be looking for the data in cells A1:A10.
It is therefore very useful to incorporate named ranges when referring to ranges within Excel and referencing them within VBA, as the code will then be able to follow the named range around if the range changes location.
They Can Help With Accuracy and Structure Within Financial Models
When using a named range for a range of cells, such as 12 months worth of data, using a named range can help you ensure that you're referencing the correct cells, especially when working across multiple worksheets.
For example, within financial models it is good practice to ensure that all dates and periods referred to within columns are consistent across all worksheets.
Named ranges can help with this in the following way:
Cells A5:L5 have been given the named range "Sales"
Below, in cell C8 you can see we have typed in =Sales into the formula bar and it has returned the cell from the same column. If we were to drag the =Sales across it would also correctly return the numbers from the other 11 months.
Whilst this is a simple example on one worksheet for illustrative purposes, it is useful as it can also be done across worksheets. So if you were to type in =Sales in column C on another worksheet it would return the exact same figure. This can help as it reduces the risk of manually linking the formula to an incorrect column if you were doing this manually etc.
So, above we've looked at the benefits that named ranges can bring to our Excel modelling and analysis work, what about the downsides?
Well, it is my view that most disadvantages can be avoided if named ranges are used correctly, and below are some of the things you should consider when using them.
A Poor Naming Structure Can Make the Model Less Readable and More Difficult to Use
Consider the above example. Sometimes, users can create naming structures that may make sense to them, but mean nothing to other users (I'd also argue the case that in many cases the creator would forget what they meant when returning to the model at a later date!)
Abbreviations that aren't instantly understandable or poor naming convention achieves the opposite of the benefit we discussed earlier - making the model less readable and more difficult to work with.
Thought should be put into the naming structure and the ease of use a good naming structure will create and one that will be easy for future users of the model to understand and work with.
Named Ranges Can Become Difficult to Manage if Not Maintained Correctly
Another downside to named ranges can be that as easy as they are to create, they are not always maintained going forward, often resulting in a mass of named ranges that are no longer used, are now returning errors (or were set up and never used in the first place!)
This makes it more difficult for other users of the model to work through, as they will need to understand if everything is working correctly and are the erroneous or obsolete named ranges causing any issues.
This all comes down to good housekeeping and using the name manager ensuring that everything is as clean and accurate as you would like it when picking up an unfamiliar model from another user.
Named Ranges Can Copy Across to Another Workbook Unintentionally
Sometimes you may need to copy across sheets into another workbook you have open. You may have different intentions at different times when doing this, however, one thing is consistent and that is that named ranges from the source workbook can make their way across to the new workbook.
When looking at the name manager, this can often result in a lot of named ranges that you don't even recognise (especially if the source model wasn't one you were familiar with and you were just taking sheets/data from it for another use).
Caution must therefore be used when doing this and again keeping an eye on the Name Manager within Excel will help you keep on top of this.
As the above shows, named ranges can be a useful addition to Excel and can help provide structure and readability when used correctly.
Although there are considerations when looking to avoid the common pitfalls, these can be easily avoided and I believe the benefits then far outweigh the negatives.
If you would like to receive further content from Insightful Dynamics then please sign up to receive notifications. You can also visit /contact us at: www.insightfuldynamics.co.uk / email@example.com