Free computer Tutorials
|HOME||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:
In the Results Row, cell B5 is a result of adding up cells B2 to B4. The formula used is just this:
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:
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.
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
To apply your new name to a formula, do this:
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
Step Two - Apply 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.