Create Named Range in Excel

Create Dynamic named ranges in Excel and use them in formulas, data validations dynamically. There are multiples ways to create the named ranges in Excel.






You can create Name Range in all types of excel version. There are certain things to keep in mind while creating Name ranges. 

Click on Ribbon Formulas->Name Manager and wizard, you will see the wizard requesting for references.

Basic Rules to Create Name Ranges:

1. No Space while defining Names to Range
2. No Special Characters except underscore (_) in name
3. No Keyword or formula names
4. No Column names
5. No Duplicate Names

Advantage of Creating Name Ranges

1. Use Named Range dynamically
2. Use Named Range in formula
3. Use Named Range in Data Validation
4. Reference Named Range in Lookup function

Methods to create Name Range

1. Click on Name Box, left side to formula bar and type name
2. Click Formulas->Name Manager->Define Range
3. Create Dynamic Named Range using Formula
4. Create Dynamic Named Range using Table Name

Now let us start with some examples 

 
The above example shows how to create Name Range by using Name Box and assigning in Data Validation.

Select Range that you wish to include in Name range
Click on Name Box
Type in the name you wish, here we had mentioned name =SalesYear
Done

Now Select Data Validation from Data tab on Ribbon

Select List in Allow
Type =SalesYear in Source box

Now you will see that the drop-down holds the values that are mentioned in Named Range

Another example of using Named Range in formulas like Sum, Vlookup

Above example using SUM function I am calculating the Values of each quarter that are corresponding to year selected in drop-down.

Here assign the names to each quarter in Name Box
I have assigned Qu_1, Qu_2, Qu_3,Qu_4 to each quarter
Type formula =SUM(Qu_1, Qu_2, Qu_3,Qu_4) thats it.
Now you can cross check the sum


Below Example is showing how we can use Named Range in Vlook-up with Table.

Select the entire range of table shown
Type Name to Range in Name box, I have given "TabSales"
Use formula to lookup value for quarter
   =VLOOKUP(G10, TabSales,2,0) and so on till fourth quarter

Now let us learn creating Dynamic Named Range

This method is very important when your data keeps on changing and you want that automatically your Named Range must get updated with new values.

Formula used to create dynamic Named Range

=OFFSET($A$3,0,0,COUNTA($A$3:$A$140),1)

OFFSET function keeps offseting the rows value within the range that is counted by COUNTA. This type of Named Ranges are always useful when you are having large size of data or your data is getting updated frequently.

Now let us also check using Table 

Select the entire range of data set and press Ctrl+T to create the table
Assign the name to table if you did not assigned the name, excel will pick the name.
Check in Name Manager for the name assigned, so that you can use the same name in formulas.

Advantage using Tables is that as an when your table data is appended the data range in Named Range is auto-updated.


This is the best method when you are going to work on large size of data and this helps to get rid of referencing the ranges in formula.

Practice the methods of creating Dynamic Named Range and use them in Formulas dynamically.

Keep reading for more articles

Create Named Range using VBA Code