Free computer Tutorials
Microsoft Excel 2007 to 2013
Named Ranges in Excel
A Named Range is way to describe your formulas. So you don't have to have this in a cell:
You can replace the cell references between the round brackets. You replace them with a descriptive name, all of your own. So you could have this, instead:
Behind the Monthly_Totals, though, Excel is hiding the cell references. We'll see how it works, now.
Open up Excel and create the spreadsheet below:
The formula is in cell B5, and just adds up the monthly totals in the B column.
Define a Name
Setting up a Named Range is a two-step process. You first Define the
Name, and then you Apply it. To Define your name, do this (make sure
you have the formula in cell B5):
From the Name a Range menu, click Name a Range (Define Name again in Excel 2010/13):
You'll then get the following dialogue box:
Click OK on the New Name dialogue box. Notice that the Name is our heading of Monthly_Totals.
When you click OK, you'll be returned to your spreadsheet. You won't see anything changed. But what you have done is to Define a Name. You can now Apply it.
Apply a Name
To apply your new Name, click into cell B5 where your formula is, and do this:
When you click OK, Excel should remove all those cell references between the round brackets, and replace them with the Name you defined:
In the image above, cell B5 now says:
The cell references have been hidden. But Excel still knows about them
- it's you that can't see them!
Study the spreadsheet below, now that we have added another Named Range
to cell C5:
Using the same techniques just outlined, create the same Named Range as in our image above. Again, the formula we've used is just a SUM formula:
You need to start with this, before you Define the Name and Apply it.
Using Named Ranges in Formulas
We'll now use two Named Ranges to deduct the tax from our monthly totals.
So, to define two new Names, do the following:
You should now have two new Names defined. We'll now Apply these new names. First, add a new label to your spreadsheet:
Click in to cell B7, next to your new label, and enter the following formula:
= B5 - C5
With the formula in place, we can Apply the two new Names we've just defined:
If you look at the formula bar, you'll see the two Named Ranges. The formula is easier to read like this. But it's not terribly easy to set up! They can be quite useful, though.
In the next part, we'll take a look at how to set up your own custom names that you can use in formulas.