We know that every cell in the spreadsheet has a unique name by default. This name is nothing but the combination of the row number and the name of the column. Now the question is can I have my own name(s) assigned to a cell/ a group of cells (range) instead of the default name such as A1, C2, and D4 etc. The answer is YES and the concept is called “Named Ranges”. In this post we will see how to create named ranges, why to use named ranges and the most important thing how named ranges will improve my efficiency or make my work easier.
Why should I use Named Ranges
Naming a range in Excel has many advantages and makes our job much easier. Here are a few advantages of using named ranges.
Easy to remember – A range named as Employee Name is much easier to remember instead of identifying the reference as B2:B200.
Makes navigation and selection easier – All names can be accessed through the name manger or the name box. You can directly select/ move to the particular range.
Formulas are much easier to write/ understand – Instead of the cell references a named range can directly be used in the formula.
Rules for Creating a Name
There are few things to be noted while creating/ defining names.
No spaces are allowed. Instead you can use _ as an alternative. Ex: “Emp_Name” instead of “Emp Name”.
Name should start with a letter or underscore (_) or a backslash (\). Numbers can be used as a part of name but a name cannot start with a number.
Names are not case sensitive. “PROFIT” and “profit” is identified and treated as the same way by excel.
Cell references such as A2, F$100, R1C2 etc. cannot be used as names. Also, characters “C”/ “c”/ “R”/ “r” cannot be used as names as they are used as shortcuts to identify the row and columns.
Length of a name can be up to 255 characters.
Creating/ Defining a Name Range
Naming a range can be done in different ways.
- Using the Name Box
- Creating from a Selection/ Labels and
- Using the Name Manager.
Let’s see each of them in Detail.
Creating a Named Range Using the Name Box
This is the easiest way to create a named range.
Name Box: The box on the top left adjacent to formula bar on the spreadsheet is the name box. This shows the name of the cell selected. Know more about excel terminology in case if you are not familiar about excel terminology
Let’s see the step by step procedure.
Step 1: Select the range for which you want to give a name.
Step 2: Go to the name box and type the name you wanted in it. Press Enter.
Step 3: Click the down arrow in the name box and check if the name is being seen or not.
Step 4: Click on the name and you can see the range getting selected.
The data in the below image shows the quarterly sales for 10 products. See the below image to understand how to create a named ranged for the data.
Download the practice workbook and try creating named ranges in the same way for the rest of the quarters and products as well.
Creating a Named Range from a Selection/ Labels
Imagine in the same example if we have monthly data instead of quarterly and you need to create 12 named ranges for each month.
Heyyy! I just learnt how to create a named range. I will do it for 12 months with ease. Hold on!!!
If the data extends for 2 years (24 months) then, how much time we need to spend in creating named ranges for 24 months. Still manageable? As the size of data increases the time we need to spend increases right. Remember, we are using Excel to reduce manual work not to do donkey work.
Then comes the question, is there an easier way to do this?
The answer is YES J.
There is a much simpler way to create named range(s) from a range. Excel does all the necessary work to make our work much easier. Let’s see how to do it.
Step 1: Select the data.
Step 2: Go to Formulas tab.
Step 3: In the defined names section, Click on “Create from Selection” option.
Step 4: A pop up opens with options to choose named ranges. Check the ones which you need and click on OK.
Step 5: Check the name box and you can see the names created based on each option selected.
See the below image to understand the steps.
Try creating named ranges in the same way for the data in the sheet 2 (Named Range-Selection) in the practice workbook.
Using the Name Manger
Using Name Manager to create named ranges provides us with the option to customize our named ranges unlike the ones created using the name box and selection options.
Got to Formulasà Defined Names à Name Manager
On clicking Name Manager a dialog box opens with different options. Also, you can see if any named ranges exist in the work book.
New: To create a new named range. On clicking New a dialog opens with 3 options as shown in the image below.
Edit: To edit an existing named rage. The options of the Edit dialog box remains the same.
- Delete: To delete an existing named range.
Alternatively you can use the Key Board Shortcut “CONTROL + F3” for opening the Name Manger dialog box.
See the below image to see how to use Name Manager.
Download the Practice Workbook used in Example
What are your thoughts on Named Ranges?
Please do share your thoughts on Named Ranges in the comments.