Hello and welcome back to the Excel Tip of the Week. This week, we have a Creator post in which we're taking a definitive look back at everything to do with named cells and named ranges. You might want to look back at the original posts on this topic - TOTW #32 and #95.
Excel allows you to assign a name to a cell or range of cells. This has several advantages - for example, you can use the name in a formula and bypass the need to use $s to fix the reference; or even make a formula easier to read by assigning a reader-friendly name to a constant or range of inputs.
The simplest way to add a name is to select the cell(s) you want to name, then type in the Name Box at the top left of the Excel pane and hit Enter:
Once defined, the name can be used freely throughout the entire workbook.
Names are also convenient sometimes over using ranges, because they can refer to a selection of cells that isn't rectangular, or even aren't contiguous:
Named ranges are also essential for VBA programming. References to specific cell addresses in VBA code are quite unreliable, breaking easily if data is moved or rows / columns are inserted. But a named cell is more easily found and tracked. Named ranges are also the only way to easily pass non-contiguous ranges such as those above to a VBA routine.
Going further with naming
You can review, amend, and delete the names in your workbook using the Name Manager, under the Formulas heading:
If you define a name from this menu, you can also add a comment to it (e.g. a reminder of what the name is referring to). What's more, you can also define names using formulas such as CHOOSE or OFFSET. This allows you to create 'dynamic ranges' which can change in size based on variables and adjust to your needs.
Creating names automatically
Finally, there is a tool in Excel that can automatically create names for cells, based on nearby cells which Excel believes are intended as labels (usually the leftmost and topmost rows of a range). The tool is called Create from Selection:
(note that the date names automatically use underscores instead of hyphens, which are ambiguous with minus signs)
There's one final trick we can do with this - because a space can be used as a 'union' operator for ranges, listing two overlapping ranges separated by a space returns the value at the intersection:
You can check out all these examples in the attached file.
Previous post - VBA case study: TESTSUM and arrays TOTW index Next post - Revisiting CHOOSE
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Nice to see you in Oxford David.
Another great benefit of named ranges, is that should the named range be deleted, then you will still retain the name of what you were trying to link to. Referencing a cell directly will just replace, say, sheet2!A1 with #ref.
So if you only use named ranges to reference areas of sheet2, you could delete sheet2, replace it with a newer version, provided the new version has the same named references, and links will be restored.