Excel Named Ranges enable you to give one, or a group of cells a name other than the default D3 for a single cell, or E3:F50 for a range of cells.
Let’s look at a simple example of how you might use a Named Range. Say you had a range of cells containing sales figures, you could highlight the cells and name the range ‘Sales’.
If you then wanted to Sum the range you could simply enter a formula that read =SUM(Sales). Not only is this easier than entering =SUM( and then highlighting the range and hitting enter, but it’s also more intuitive if you ever have to revisit the spreadsheet weeks or months later.
If you’ve ever endured the frustration of trying to decipher a spreadsheet someone else has created you will love how easy Named Ranges make reading and interpreting formulas. For example, rather than say =SUM(D5:D600), which requires you to go hunting around to find out what D5:D600 represents, imagine how much quicker and easier it is to read =SUM(Sales). You instantly know what that formula is doing.
Ok, that was a really simple example. I’ll list some uses for Named Ranges later in this tutorial and cover the rules, and some advanced features, but first let’s take a look at how to name a range in Excel.
Inserting Excel Named Ranges
Naming a Range in Excel is actually quite straight forward.
1) Highlight the range you want to name
2) Type a name in the Name Box and hit Enter.
You’ll find the Name Box just to the left of the formula bar. I’ve entered the name ‘NameBox’ in the graphic below, but yours will most likely display the active cell reference.
Now you’re ready to use your Named Range in your formulas.
Named Range Example
In the image below I have set up a Named Range called ‘Viewers’ which encompasses cells C2:C30. In cell E2 I’ve started to type a formula =SUM(vi, you can see below the syntax Excel is displaying a list of Named Ranges that match the first two letters of my name (there’s only one in this case). To accept the first name in the list I can hit the TAB key and Excel will enter the remainder of the name for me, and I can simply hit ENTER to finish the formula.
It’s often the case that you will use the same ranges in multiple formulas, so you can see that a little work setting up some Named Ranges can save you time in the long run when creating formulas.
Once you’ve set up some Named Ranges you can click on the drop down arrow on the Name Box to see a list of Names. If you click on a Name Excel will take you to the sheet the range is on and highlight the cells for you.
Named Range Rules:
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 above, it will have a scope of the whole Workbook.
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 Qtr1, Qtr2 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, then click on the drop down list on in the Scope: field and select the Worksheet you want the name available in.
What does Scope mean and how will it affect my Named Ranges? Let’s say on Sheet 1 you select a range A1:A10 and give it a name ‘Jobs’, and you then select Sheet 2 as the Scope. This means when you’re in Sheet 1 you will NOT see the Named Range ‘Jobs’ 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 =SUM(Jobs) and it would actually sum the range A1:A10 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.
Managing Your Named Ranges
There’ll come a time when you want to edit or delete a Named Range. To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open.
From here you can Edit and Delete your Named Ranges, or even create new ones. Remember, once you delete a name you cannot undo that action.
Named Range Advanced Features:
1. You can set non-contiguous Named Ranges by holding down the CTRL key while you highlight the cells you want to name.
2. Set Dynamic Named Ranges that update automatically as your data grows using the OFFSET function. That’s a lesson for another day.
3. Shortcut to create named ranges from a table of data. If you have your data laid out in columns or rows with headers Excel can interpret this and set up Named Ranges for you automatically. Simply highlight all of your data and press CRTL+SHIFT+F3 and the following dialog box will open.
Excel will ask you if you want the names created from values in the Top Row, Left Column, Bottom Row or Right Column. Simply tick your choice(s) and click ok. Bob’s your Uncle, you now have Named Ranges automatically set up using the names of your Column or Row headers.
Different uses for Named Ranges in Excel:
1) Formula References; Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas
2) Multiple Print Areas on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges. Note: Excel 2007 onwards allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area.
3) Reduce worksheet clutter with Named Constants. Sounds complicated but it’s not. A Named Constant is just a fancy name given to values you might use repeatedly in your formulas. For example, let’s say you’re preparing a budget for the coming year and you want to increase this year’s figures by 5%, you could give the value ’5%’ a name like ‘Markup’ and then you could:
a. Write your formulas like this =C1*Markup. This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.
b. Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.
c. Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you’ve got many of these key figures your spreadsheets can become crowded.
To set up a Named Constant
1) Click on the Define Name button on the Formulas Tab
2) In the New Name dialog box enter the name of your value, in our example it’s ‘Markup’
3) Select your scope
4) In the Refers to: field enter your value, in our case =1.05 for a Markup of 5%.
To update the value simply edit the Named Constant in the Name Manager
4) Navigate quickly to a section of your workbook/worksheet
5) Automate Named Ranges; by inserting a Table (Insert tab, Table) Excel automatically gives the table a name like Table1. Go to the Name Manager to change the default name to something more meaningful. Note: you can’t make a reference to a cell in a table absolute, so there is a time and a place for Tables. See my shortcut to create named ranges if the limitation of Tables are causing you hassles.
Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub.
Post a comment and let us know how you use Named Ranges in Excel?
Please share the knowledge with your friends and colleagues on Twitter, and Facebook etc.