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
- 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.
- 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
- 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 )