Free computer Tutorials
|HOME||Stay at Home and Learn|
How to Use a Named Range in Excel
In the previous part, you saw how to define and apply Named Ranges. In this part, you'll learn how to use the Named Ranges you have just set up.
Using Named Ranges
The spreadsheet you have created so far looks like this:
We have a Named Range in Cell B5 Called Monthly_Totals, and a Named Range in Cell C5 Called Monthly_Tax. We can use the Named Ranges to deduct the Tax from the Monthly Totals. But we need to set up two new Named Ranges. One for cell B5 and one for cell C5.
You might question the need to set up two more Ranges, on the grounds that we have just set up two Named Ranges in cell B5 and C5.
Strictly speaking, we don't need the new names. To deduct the Tax from the Monthly Total using Named Ranges we could just do this:
=Sum(Monthly_Totals) - SUM(Monthly_Tax)
And it would work. Excel would take one from the other. (The following, however, would not: = Monthly_Totals - Monthly_Tax.)
But if we set up two more Named Ranges, we can just use the single totals, instead of telling Excel to use a Function to add them up first.
So, to define two new Names, do the following:
Click the OK button when you're done.
Now that we have the Names defined, we can enter a formula and Apply the names into the formula. First, amend your spreadsheet so that it looks like this one:
As you can see, a new label has been added, along with the formula in cell B7: = B5 - C5.
Now that we have a formula we can replace the cell references B5 and C5 with the names we set up. So click on B7 and do this:
And that's enough of Named Ranges. We'll move on to Pivot Tables.