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.