Home > Excel > Define Name Range In Excel 2007

Define Name Range In Excel 2007

A Named Range is way to describe your formulas. So you don’t 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’ll see how it works, now.

Open up Excel 2007, 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
  • Click Name a Range

From the Name a Range menu, click Name a Range :

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:

  • On the Named Cells panel, Click Name a Range
  • 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:


The cell references have been hidden. But Excel still knows about them – it’s you that can’t see them!

Categories: Excel Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: