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:

The spreadsheet so far

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 inside cell B5
From the menu bar, click on Insert > Name > Define
When the dialogue box appears, click inside the top text box and type in Monthly_Result. (Don't forget the Underscore!)
Click OK
Click inside cell C5 and do the same. This time, though, type in Tax_Result as the Name.
Your Define Name dialogue box will then look like this one:

Four Named ranges have been defined

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: