named_ranges01Named Range is a range of cells that has been assigned a name. A Named Range could be a single cell, or a set of cells or a constant value.

Benefit of Creating Named Ranges

When you create a Named Range, you can use the Name instead of the cell reference in formula.

For example, you can use =SUM(Sales)  instead of =SUM(D4:D20)  for the above data set. Have a look at some examples below

  • Sum of all the sales made by Tom: =SUMIF(SalesRep,”Tom”,Sales) 
  • Sum of all the sales made in January 2014: =SUMIF(Month,DATEVALUE(“Jan-2014″),Sales) 
  • Get the commission earned by Kim: =SUMIF(SalesRep,”Kim”,Sales)*Sales_Commission 

You would agree that these formulas are easy to write and easy to decipher by anyone. This is one big benefit of using Named Ranges

How To Create Named Ranges

Here are 3 ways to create a Named Range

Method 1:
  • Select the range for which you want to create a Name
  • Go to Formulas –> Define Name
  • In the New Name dialogue box, type the Name you wish to assign to the selected data range. You can specify the scope as the entire workbook or a specific worksheet, If you select a specific sheet, the name would not be available in other sheets.
Method 2:
  • Select the range of cells for which you want to create a name (do not select header cells)
  • Go to the Name Box on the left of Formula bar and Type the Name Note that the Name created here will be available for the entire Workbook. If you wish to restrict it to a worksheet, use Method 1
Method 3:
  • If the data has header (in data above, there are three headers: Month, Sales Rep and Sales) and you want to name Named Range on these headers
    • Select the entire data set (including header cells)
    • Go to Formulas –> Create from Selection (Key board shortcut – Control + Shift + F3)
    • It will open the ‘Create Names from Selection’ dialogue box
    • Select Top Row to create a Name from header.
  • If your data is arranged differently with headers in rows, you can select Left Column in the dialogue box

Too Many Named Ranges?

Now that you know how to create Named Ranges, feel free to create as many as you want. But what if you make too many to handle? Here are some useful tips that makes it easy for you to use the Name in your day-to-day work

  • Recalling the Name: If you have created many Named Ranges and you are struggling to recall the right one, here are two ways to get hold of this
    • Go to Formulas –> Use in Formula –> Paste Names. This will give you a list of all the Names and you can select the one you need (Keyboard Shortcut – F3 )