How to Setup a Named Constant in Excel

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 "Discount".

3) Select the Scope: In the image below it's the entire Workbook.

4) In the Refers to: field enter your value, in our case =0.10 for a Discount of 10%.

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. 

Excel 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.

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 click Create from Selection on Define Names group as you can see below or 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. You now have Named Ranges automatically set up using the names of your Column or Row headers.

Excel 2007 Named Range 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.

Excel 2007 Named Ranges

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name.

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(B2:B100), which requires you to go hunting around to find out what B2:B100 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.

How to Name a Range in Excel
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 "RangeName" in the graphic below, but yours will most likely display the active cell reference.

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.

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 "Expenses" which encompasses cells B2:B7. In cell D2 I've started to type a formula =SUM(exp, you can see below the syntax Excel is displaying a list of Named Ranges and Excel built-in functions that match the first three letters of the range Name. In this case there are three (EXP, Expenses, and EXPONDIST).

To select the Range name, double click on the range name and Excel will enter that name on the argument of the SUM function. After the Named Range is selected it will also highlight the range of cells it was assigned as you can see in the image below. Finally, 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.