What’s in the name?
If you are working with Excel spreadsheets, apparently a lot of time saving and efficiency.
In this tutorial, you’ll learn how to create and use Named Ranges in Excel.
Named Ranges in Excel – An Introduction
If someone has to call me or refer to me, they would use my name (instead of saying a male staying in so and so place with so and so height and weight).
Similarly in Excel, you can name a cell or a range of cells. Now, instead of using the cell reference (such as A1 or A1:A10), you can simply use the name that you assigned to it.
Benefits of Creating Named Ranges in Excel
When you create Named Ranges in Excel, you can use these Names instead of the cell references.
Have a look at ṭhe formulas listed below. Instead of using the cell references, I have used named ranges.
- Sum of all the sales done by Tom: =SUMIF(SALESREP,”Tom”,SALES)
- Number of sales with value more than 500: =COUNTIF($C$2:$C$13,”>500″)
- Commission earned by Joe (sales by Joe multiplied by Sales Commission):
You would agree that these formulas are easy to create and easy to decipher.
One big benefit of using Named Ranges in Excel is that you don’t need to go back and select the cell ranges. You can simply type a couple of alphabets of that named range and excel will show the matching named ranges (as shown below):
Another benefit of using named range is that you can make formulas dynamic by using named ranges. For example, in the case of Sales commission, instead of using the value 2.5%, you can use the named range. Now, if your company later decides to increase commission to 3%, you can simply update the named range.
How to Create Named Ranges in Excel
Here are 3 ways to create Named Ranges in Excel:
- Select the range for which you want to create a Named Range.
- 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.
- Click OK.
This will create a Named Range SALESREP.
- Select the range for which you want to create a name (do not select headers).
- Go to the Name Box on the left of Formula bar and Type the name of the named range you want to create.
- 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.
This method is recommended when you have data in tabular form and you want to create named range for each column/row. For example, in the dataset below, if you want to quickly create three named ranges (Date, Sales_Rep and Sales), then you can use the method shown below.
Here are the steps to quickly create named ranges from a dataset:
- Select the entire data set (including the headers).
- Go to Formulas –> Create from Selection (Keyboard shortcut – Control + Shift + F3). It will open the ‘Create Names from Selection’ dialogue box.
- In the Create Names from Selection dialogue box, check Top Row option, and make sure all other options are unchecked. In this case, we select top row only as the header is in the top row. If you have headers in both top row and left column, you can select both. Similarly, if your data is arranged when the headers are in the left column only, then you only check the Left Column option.
This will create three named ranges – Date, Sales, and Sales_Rep.
Note that it automatically picks up names from the headers. If there are any space between words, it inserts an underscore (as you can’t have spaces in named ranges).
Named Range Syntax Rules
There are certain naming rules you need to know while creating named ranges in Excel:
- The first character of a named ranges should be a letter, and underscore character(_), or a backslash(\). If it’s anything else, it will show an error. The remaining characters can be letters, numbers, special characters, period, or underscore.
- You can not use names that also represent cell references in Excel. For example, you can’t use AB1 as it is also a cell reference.
- You can’t use spaces while creating named ranges. For example, you can’t have Sales Rep as a named range. If you want to combine two words and create a named range, use an underscore, period or uppercase characters to create it. For example, you can have Sales_Rep, SalesRep, or SalesRep.
- While creating named ranges, Excel treats uppercase and lowercase the same way. For example, if you create a named range SALES, then you will not be able to create another named range such as sales or Sales.
- A named range can be up to 255 characters long.
Too Many Named Ranges? Don’t Worry
Now that you know how to create Named Ranges in Excel, feel free to create as many as you want.
But what if they are too many to handle?
Don’t worry – here are some useful tips.
To get the names of all the Named Ranges:
- Go to Formulas –> Use in Formula –> Paste Names (Keyboard Shortcut – F3)
- This will give you a list of all the Named Ranges in that workbook. To use a named range (in formulas or in a cell), double click on it.
Show Matching Named Ranges
- If you have some idea about the Name, type a few initial characters and Excel will show a drop down of the matching names.
Useful Named Range Shortcuts (the Power of F3)
Here are some useful shortcuts that will come handy when you are working with Named Ranges in Excel:
- To get a list of all the Named Ranges and pasting it in Formula: F3
- To create new name using Name Manager Dialogue Box: Control + F3
- To create Named Ranges from Selection: Control + Shift + F3