Stay at Home and Learn

How to Set Up Named Ranges in Excel

Instead of using something like = SUM(A2:A5) to add up a column of numbers, you can replace the A2:A5 part of the function with a more descriptive name. This is known as a Named Range. Examine the spreadsheet below:

Adding up columns of figures

In the Results Row, cell B5 is a result of adding up cells B2 to B4. The formula used is just this:

=Sum(B2:B4)

Now examine the same spreadsheet, but with a Named Range used:

The SUM is using a named Range

This time, cell B5 doesn't have in it the formula = Sum(B2:B4). As you can see, it has =SUM(Monthly_Totals). This is the label from B1. We have created a Named Range. The formula in cell B5 is now more descriptive. We can tell at a glance what it is we're adding up. Excel has replaced the B2:B4 part with the name we gave it. Behind the scenes, though, we're still adding up the numbers in cells B2 to B4. Excel has just hidden the cell references behind our descriptive name.

You'll now see how to create your own Named Ranges.

Creating a Named Range

Start a new spreadsheet, and enter the same data as in the image below:

Create this spreadsheet

Make sure you have the same formula in cell B5 =Sum(B2:B4). We're going to create a Named Range, and then pop it in cell B5. To create a Named Range then, do the following:

Highlight the B column, from B2 to B4 (Don't include the formula when you're highlighting. Just highlight the same cells as the ones in the function)
From the menu bar, click on Insert
From the drop-down menu select Name
A sub menu appears like the one below:

There's a two-step process involved with setting up a Named Range. The fist thing to do is Define the name. You then Apply the name to your formula.

So select Define from the sub menu
The Define Name dialogue box pops up. This one:

The Define Name dialogue box

With the B column highlighted, Excel will use your label at the top as the name (Monthly_Totals for us). But you can change it if you want. Notice the narrow text box at the bottom, "Refers to". This is showing the highlighted cells.

Click OK on the dialogue box.

You are returned to your spreadsheet. Nothing will happen. This is because we have haven't done step two of the two-step process - Applying the name.

To apply your new name to a formula, do this:

Click inside the cell where your formula is, B5 in our case
Click on Insert from the menu bar
From the drop down menu, select Name
From the sub menu that appears, click on Apply
A dialogue box will appear showing a list of all the Names you have set up

You'll have only one Name set up , so there's not much to do except click the OK button.

When you click OK, Excel should adapt your formula in cell B5. If you've done it right, your spreadsheet should look like the one below:

The Named Range is in cell B5

As you can see, the cell B5 now reads =SUM(Monthly_Totals). Excel has hidden the cell references behind the Name we defined.

If you didn't get the Name, but instead got the error message below, then there are a couple of things you can do:

Before you click Insert > Name > Define, make sure you highlight only the same cells as the ones in your formula. Make sure that there is a formula in the cell B5, and that it says = SUM(B2:B4)

We can enter another Named Range for our Monthly Tax column, column C. Here's a break down of the Two-Step process involved with setting up a Named Range.

Step One - Define the range of cells

Enter your Formula (In cell C5, enter = SUM(C2:C4))
Highlight the same cells that are going in your formula
From the menu bar, click Insert > Name > Define
From the Define Name dialogue box, either accept the name Excel gives you, or type your own name for the range of cells you're going to define
Click OK

Step Two - Apply the Name

Click inside the cell where the formula is (cell C5 for us)
From the menu bar, click Insert > Name > Apply
From the Apply Name dialogue box, click on the Name you want to use
Click OK
Excel will insert the name, if it can, and hide your cell references behind the name