Workbook Level

Workbook level named ranges can be used (i.e. referenced) from any worksheet in a workbook.
The most common named ranges and the easiest ones to create are workbook level named ranges.
Most named ranges are defined at workbook level.
These named ranges are always listed in the (Insert > Name > Define) dialog box regardless of what worksheet is active.

Editing Named Ranges

You cannot use the Name Box to redefine any existing named ranges. This has to be done from the (Insert > Name > Define) dialog box.
Select the named range from the list, edit the cell reference in the Refers to box and then press Add or OK.
You can either type in the new reference or you can select a range of cells directly.

Removing a Workbook Level Named Range

Display the (Insert > Name > Define) dialog box.
Select the named range from the list and press Delete.
If you want to rename a named range then you can should first select the named range, change the name and press Add.
Remember that after you delete a named range any formulas that refer to that named range will return #NAME?.


Avoid copying a formula that includes a named range from one workbook to another as this creates a hidden link between the two workbooks.
If you have formula using a named range and then delete the named range, the formula will return the #NAME? Error.
When deleting named ranges or rows and columns - ensure the names are not used in any functions or formulas. If they are then #REF! will appear.
It is possible to create more than one name of the same cell or range of cells. The Name box will display all the names although the name displayed when the cell is selected is the first name alphabetically.
You can define names that refer to non contiguous cells. Hold down the Ctrl key to select these cells before displaying the (Insert > Name > Define) dialog box.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext