PRATYUSHA HOME

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:

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:

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:

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:

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:

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

So go ahead and insert a Named Range for cell C5. When you're finished, the spreadsheet should look like the one below:

As you can see, cell C5 no longer reads = Sum(C2:C4). Instead, we have a Named Range in cell C5.

In the next part, we'll see how to use the Named Ranges you have just set up.