Microsoft Excel :  Using Named Ranges

by Karyn Stille

Your boss, I. Juan Itnow, calls you into his office to discuss the company's financial data from the previous quarter.  You enter to see the Excel spreadsheet you created for Mr. Itnow open on his computer screen.  Mr. Itnow proceeds to wave and point around on the screen saying that it is impossible to figure out where you came up with these totals.  You click on one of the total cells and look in the formula bar.  In the formula bar, there is a sum formula that contains a slew of cell references, and you start showing Mr. Itnow how to match up the references with the cells on the spreadsheet.  Mr. Itnow, not knowing much about Excel, exclaims, "This is ridiculous!  I don't have time to figure all of this out - I'm a busy man!  Can't you make this easier?"  You calmly state, "Why certainly.  I'll have it for you by the end of the day."

After rushing nervously back to your desk, you immediately logon to the Internet and find the answer here - you need to create named ranges.

What is a Named Range?

A named range gives a range of cells an actual text name that can be used to quickly view a group of cells.  Named ranges can also be used in formulas to save time and are much more meaningful.

For example, if you want to make a total of revenue generated in four quarters of a fiscal year, it may be easier to type:

=SUM(Quarter1:Quarter4)

Actual cell references would not need to be looked up, and the formula is more meaningful when returning to the worksheet for review.  This is especially helpful for working with large workbooks and spreadsheets that contain a lot of data.

Creating a Named Range

1.  Select the cells for the named range.

2.  In the Name Box type a name for the range without spaces, as shown in the figure below, and press the Enter key.  

That's it!  You just named a range.

Note:  Hyphens and spaces should not be used in named ranges.  To indicate spacing, use an underscore.

Using Named Ranges

You can immediately locate any of the cells contained in one of your named ranges by simply selecting the name from the Name Box.  Excel highlights all the cells contained in the named range you select.

To use named ranges in formulas and functions:

1.  Select the cell that will contain the formula or function.

2.  Type the formula or function using named ranges in place of cell references, as shown in the graphic below.  In this example, we are creating a total for all four quarters of a fiscal year for the Northern region.  A different named range was created for the data in each quarter, as above.

3.  Press Enter to make the calculation based on your named ranges.  The figure below shows the result of the formula.

Deleting a Named Range

Use caution if you need to delete a named range.  Make sure the name is not being used in any formulas or functions.

1.  From the Insert menu, choose Name and Define.

2.  In the Define Name dialog box, select the named range you would like to delete.

3.  Press Delete.

4.  Choose OK.

It's 4:45pm.  You smile and take the new Excel file in to Mr. Itnow.  You show him how he can click on a total and see named ranges in the formula bar, and then explain that all he has to do is select the name of one of the ranges from the Name Box to see which cells those numbers came from.  He is very impressed and tells you to take the rest of the day off.  Wow - a whole 15 minutes all to yourself!

Free Microsoft Office Tutorials & MS Office Tips Newsletter
http://tutorials.esmartweb.com