Excel Named Ranges Rules

There are 7 Named Range Rules that we'll be tackling in this tutorial.

1) You can set a Named Range to have the scope of a single Worksheet or the whole Workbook. The whole Workbook is the default, so if you enter a Named Range directly into the Name Box, as explained in the Named Range Example, it will have a scope of the whole Workbook.

This means that:

a. If you set the Named Range to have a scope of the whole Workbook you can reference it on any sheet in the Workbook, and you can only use the name once.

b. Or if you set it to have a scope of the Worksheet, you can only reference it on the Worksheet you specify when setting up the Name Range.

One advantage of this is you can have a range with the same Name on each Worksheet. This would be useful if you had a sheet for each region of your business with quarterly figures on each sheet. You could then have a Named Range for Q1, Q2, etc... on every sheet. This would make creating formulas very quick.

To enter a Named Range with the scope of the Worksheet only you need to insert it using the Define Name button on the Formulas tab of the ribbon. 

When you click on the Define Name button the New Name dialog box will open.

Enter your range name in the Name: field which in this case I entered "DEPARTMENTS", then click on the drop down list on in the Scope: field and select the Worksheet you want the name be available in.

What does Scope mean and how will it affect my Named Ranges? Let's say on Sheet 1 you select a range A2:A7 and give it a name "DEPARTMENTS", and you then select Sheet 2 as the Scope. This means when you're in Sheet 1 you will NOT see the Named Range "DEPARTMENTS" in the Name Box or be able to use it in any formulas.

But when you're in Sheet 2 you will be able to pick it from the Name Box list and use it in formulas. For example, on Sheet 2 you could enter a formula =COUNTIF(DEPARTMENTS,"*") and it would actually count the range A2:A7 on Sheet 1.

2) Named Ranges cannot have any spaces in the name, so for names containing multiple words use UpperCase letters to distinguish the separate words, or use an underscore e.g. Name_Box. Of course you don‟t have to format your names in this way, but I recommend you do as it makes them easier to read.

3) Named Ranges are not case sensitive. When you‟re entering a formula using a Name, say „Sales‟, you can enter it as =SUM(sales) and Excel will correct your entry to match your Name e.g. =SUM(Sales) when you hit enter.

4) Named Ranges must be 253 characters or less in length. Excel will let you enter a name that is 255 characters long, but it won‟t let you choose it from the Name Box.

5) Named Ranges cannot be single letters „C‟ or „R‟. You can however use other single letters, although this is not recommended because using single letters defeats one of the main purposes of making formulas easy to follow.

6) Once you delete a Named Range in the Name Manager you cannot undo the action using CTRL+Z or the Undo key and any formulas using the name will return a #NAME? error.

7) If you delete the cells containing the Named Range any formulas referencing the Name will return a #REF! error, however the Named Range will remain in the Name Manager, where you can Edit it and correct the range of cells.