Hello all. Let’s look at one of the cool / handy features in excel – Named Ranges. Named ranges makes the formula more easy to create, read and re-use. In simple terms, any cell data can be substituted with a easy name of our choice.
Let’s look at a simple example. In the below data set, we have a simple table with player names and their scores. In case, if we need find the highest run scorer, we would normally use =max(B2:B6). Instead, we can create a name of B2:B6, say scores. The formula would then read, =max(scores)
Before we explore more, I hope you understood what a named range is and what its used for.
Let’s see how to create a named range. There are 3 ways to create a named range.
- Using Name Block.
- Creating names automatically for selected data range
- Create a name with Name Manager dialog box
Using Name Block:
Select the range, in our case, B2:B6 and simply write “scores” in the Name Block. To verify the name was created, hit CTRL+F3 to see if the name is listed. Shown below:
Creating automatically for selected data
Select the range, this time with B1:B6. Click CTRL+SHIFT+F3 to get the create names dialog box. Note this is column range with the title in the top (B1 = Test Runs). Select “Top Row” in the dialog box. The name is automatically created as Test_Runs as we clicked “Top Row”. Verify the name by checking name manager (CTRL+F3). Shown below:
Manually create a name via Name Manager dialog box
Navigate to Formulas tab -> click Name Manager or CTRL+F3 to bring up the Name Manager dialog box. Click on New to bring the new name dialog box. Type a name of your choice, Select the scope (whether the name is for the entire worksheet or a particular sheet, Select the data range under Refers to. Shown below:
Let’s look at some use cases. Name range comes handy when one needs to create a drop down (a.k.a) data validation. We have created a name for the player column as “Player”. To create a drop down list of players, we can simply use the name in the source section of the data validation tab. Shown below:
One of the other common use case is name range within Vlookup. For this, we need to create a name for the whole table, let’s say “MyData”. For Vlookup function, we can replace the table array with just the name rather than selecting the range manually. Shown below:
Did we notice one thing – the created name would appear automatically as formula. The moment you start tying the first letter of the name, it would appear in the formula. Shown below:
Named range can also be constant values. While creating a new name, create a name and provide a value in the refers to argument – this will assign the constant value to the created name. Shown below
Name ranges work as hyperlink as well. In the below example, we have created a cell in Sheet2, inserted a hyperlink to come back to the table which has a name “MyData”. Shown below:
I hope you liked this tutorial on the name ranges. I would recommend you to get your hands dirty with name ranges to get yourself familiarized. Replace all your ranges with names and replace the ranges in the formula with names – once you are fully aware of name ranges, it comes super handy.
Please let us know your valuable feedback that would help in improving the content. Thanks for reading.