• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Excel named ranges: What, how and why

Get PDF file

by on
in Excel Training,Office Technology

Naming ranges in Excel can make anything from formulas to dashboards easier to build and data easier to find.

Name boxWhat is a named range?

A named range is one or more cells given an alternative name from their column-row reference. For example, a cell containing the sales tax rate can be named sales tax. A lookup table of product information A2:F278 can be named product table.

How do you create one?

Select the cell or cells, and type the name in the Name Box. Press Enter. The Name Box is on the far left of the Excel window on the same line as the Formula Bar. Names may not contain spaces or special characters, other than an underscore (_) and may not begin with a number. Another way to name cells is to use the Create from Selection tool in the Defined Named groups on the Formula tab. Using this tool, you can name several ranges at the same time, based on the values in the top row (column titles) or left column (key data).

Why create one?

Easy finds: Find these ranges easily, no matter where you are in the workbook by clicking the dropdown arrow on the name box and selecting the named range from the list. One application for this technique would be in the accounting realm. For example, if you name the ending balance of a ledger account something like travel expenses, you could easily find that amount in the Name Box dropdown menu, no matter where it is in the workbook.Create names

LookUp sanity: One of the annoying things about creating a VLookUp formula is that you must “lock down” the lookup table with Absolute Cell Reference notation ($A$1). The worst part about making a mistake here is that you don’t get an error message; you will get incorrect value. Error messages are easier to see, wrong values, not so much. Named ranges by default are absolute. So, if you name your lookup tables, you don’t have to click around to navigate to it, plus the formula will copy down with worry-free accuracy.  

Easy math: If you create a named range containing the sales figures for a business, say Accessories and Repairs, you could find the total of those two lines with a formula like =SUM(Accessories,Repairs). Unless your named ranges are single cells, always use a function like SUM to calculate their total. Otherwise, the result will be incorrect or result in an error.

Dropdown listsDropdown lists: One issue with Data Validation is that if you are not using named ranges, the values for a List must be on the same worksheet. If you use named ranges, you can put that list anywhere in the workbook. For example, you could create a worksheet tab called Lists! To specify a named range for Data Validation, type = and then the range name, Source field.





Leave a Comment