Named Ranges in Excel

Home>>

A Named Range is way to describe your formulas.  So you do not have to have this in a cell:

=SUM(B2:B4)

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:

=SUM(Monthly_Totals)

Behind the Monthly_Totals, though, Excel is hiding the cell references.  We will 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):

Highlight the cells B2 to B4 (Not B5), then click the Formulas menu.

Locate the Named cells panel in Excel 2007.  In Excel 2010 and 2013, locate the Defined Names panel instead.

Click Name a Range in Excel 2007 and Define Name in Excel 2010 and 2013.

From the Name a Range menu, click Name a Range (Define Name again in Excel 2010/13):

You will 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 will be returned to your spreadsheet.  You would not 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 in to cell B5 where your formula is, and do this:

On the Named Cells panel, click Name a Range. For Excel 2010/13 users  click Define Name>Define Name

From the menu, select Apply Names

From the Apply Names dialogue box, select the Name you want and click OK:

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:

=SUM(Monthly_Totals)

The cell references have been hidden.  But Excel still knows about them – it is you that can not see them.

Exercise

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 have used is just a SUM formula:

=SUM(C2:C4)

You need to start with this, before you Define the Name and Apply it.

Using Named Ranges in Formulas

We will now use two Named Ranges to deduct the tax from our montly totals.

So, to define two new Names, do the following:

Click inside cell B5 to highlight it.

From the Formulas menu bar, locate the Named cells panel, and click Name a Range> (Excel 2007).   In Excel 2010/13, click Define Name> Define Name from the Defined Names panel.

From the New Name dialogue box, click in to the Name textbos at the top and enter Monthly_Result (with the underscore character).

Click OK

Click inside cell C5 and do the same as step 2 above.  This time, however, enter Tax_Result as the Name

You should now have two new Names defined.  We will 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 have just defined:

From the Formulas menu bar, locate the Named Cells panel, click Name a Range>Apply Names (Excel 2007).  In Excel 2010/13, click Define Name>Apply Names from the Defined Names panel.

The apply Names dialogue box appears.

Click Monthly_Result to select it

Click on Tax_Result to select it:

Click the OK button

Excel will replace your cell references with the two Names you defined.

Your spreadsheet should look like ours:

If you look at the formula bar, you will see the two Named Ranges.  The formula is easier to read like this.  But it is not terribly easy to set up.  They can be quite useful, though.

Home>>