How to Use a Named Range in Excel

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 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:

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:

• 
  
  Click on Insert > Name > Apply


• 
  
  The Apply Names dialogue box appears


• 
  
  Click Monthly_Result select it


• 
  
  Click on Tax_Result to select it


• 
  
  Click the OK button


• 
  
  Excel replaces your cell references with the Names


• 
  
  Your spreadsheet will then look like this one:

The cell references in cell B7 have been replaced with our Named Ranges, making the information easier to read.