In Excel (and other spreadsheets), most of the time we refer to a cell or a range by using it’s address, for example A1 or A1:B10. However, assigning a “name” to a cell or range provides a number of benefits.
The quickest way to assign a name to a cell or range is to select the cell/range, click in the “Name Box” which is just above the column headings, type the desired name (which must start with a letter and cannot contain spaces) and press Enter.
There are numerous benefits of using cell names. These include:
- Quick Navigation
- As an Alternative to using Absolute References
- To ensure that a macro won’t “break” when a user adds/deletes rows/columns or moves data around
For more information about each of these, click the appropriate item in the above list.
I’m aware of the advantages, but I also know a huge disadvantage: when you delete a named range, Excel shows #REF! errors. Even Lotus123, Quatrro Pro and other Jurassic products restored the underlying cell references. Does anyone have a tip for that “omission”?
Mike,
One “problem” (or “Challenge if we’re being PC!) that some people have is when data needs to be added to the “list” – they either forget to increase the range that the name relates to or they set the range name to full columns which has an adverse impact on file size.
Two ways round this are either to use dynamic ranges making use of the OFFSET function & the other (only recently available in Excel 2007 & 2010 is to “Format as a Table” which then automatically names the range (boringly as Table1, table2, etc)
Perhaps an explanation of these would be useful?
There’s been a couple of comments about dynamic named ranges on the LinkedIn groups. I’ll definitely do a quick tutorial about it, along with one on tables.
Cheers
Mike