Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Dynamic named range in excel are the ranges which change as the data in the range changes and so does the dashboard or charts or reports associated with them, that is why it is called dynamic and we can name the range from the name box so the name as dynamic name range, to make a table as dynamic named range, select the data and insert a table and then name the table.
Table of Contents
What is Dynamic Named Range in Excel?
Excel Dynamic Named range is one of the powerful techniques in Excel. Before we see the meaning of Excel Dynamic Named Range look at the below image.
In Figure 1 Sum, the range is from D2:D16 and in Figure 2 range are not there but it is showing as Unit_Sold. The reason it is showing as Unit_Sold is that we Named this Range (D2:16) as Unit_Sold.
As a new user, you may find it scary and weird, even the old users may avoid they do not see the advantage at any point in time. However, Excel Dynamic Named ranges are actually a cool feature. Excel Dynamic Named ranges make life easier especially when you are working in large sets of data.
The reason why we need to start using Dynamic Named ranges in excel is we need not go back to find our targeted range of cells instead we can just type the name which we have given for that range.
How to create Excel Dynamic Named Range?
Excel Dynamic Named Ranges is the name given to a range of cells in excel which are readable by the user. For example, if I name A1:A10 as Data I need not select the range of cells from A1:A10 when I am applying the formula, instead I can write only Data. Let us learn how to create a dynamic named range in excel with the help of examples.
Example #1 – SUM
Below image shows the same example.
Ways to Name Cells
There are two ways to use Excel Dynamic Named ranges.
- Typing a name on Name Box in Excel.
- Define the name under Formula bar.
Typing names on Name Box in excel: Creating an Excel Dynamic Named range is easy in this type. We just need to type the name in Name Cell. Select the range you want to name and go to name box and give your desired name to the range. If you click on the drop-down list in the name box, you can see all the Excel Dynamic Named ranges.
Define the name under Formula bar: This includes the bit of a process and you can create Dynamic Range excel here. To begin with, following below steps.
- Go to Formula bar and select Define Name.
- After you click on Define Name below box will open.
- Now give your name to the range and select the range you want to name. Here we have given the range from D2:D16
- Click Ok. Now, you have defined the name for the range of cells from D2:D16 as Country_Sales.
- Apply Sum formula for Units Sold now. Instead of selecting the range from D2:D16 we have given the defined name.
Example #2 – Creating Named Ranges Automatically
We can create Excel Dynamic Named range based on the heading of the column. Excel now automatically creates named ranges for that column. In order to do that follow these steps.
- Go to formula bar and select Create from Selection.
- Select the entire data. As soon as you click on that option below box will open up.
- Select Top Row from the box. That means whatever the heading name of the column it will be treated as the named range for that column.
- Now, we can see all the named ranges under the name manager.
- Once you click on that, we will see all the defined names.
Example #3 – Using Dynamic Formula for Named Range
In a simply named range, it will take only the pre-determined range. In the case of data expansion, it will not expand dynamically. Now, we will learn how we can create a dynamic range in excel.
If you do not want to use a named table, you can use a dynamic formula to define an Excel Dynamic Named range. As new items are added, the range will automatically expand.
- Create a list of months from Jan to Jun.
- Go to Define Name tab.
- Click on that and give a name to it.
- Create data validation for Month List.
- Click on the data validation and below box will open.
- Select LIST from the drop-down.
- Select list and in Source give you the name you defined for the month list.
- Now, the drop-down list has created.
- Now add remaining 6 months to the list.
- Now, go back and check the drop-down list you have created in the previous step. It is still showing only the first 6 months is. Whatever you have added later it will not show.
In order to create dynamic ranges follow below steps.
- Define new name under define name section.
- Give name to you list and select the range.
- Now, under Refers to section apply the formula as shown in the below image.
- Now, go back and check the drop-down list you have created in the previous step. Now it will show all the 12 months in the drop-down list. You can anything in that column.
- Named Manager will dynamically update the dropdown list whenever the data expands.
Example #4 – Create Excel Dynamic Named Range for Rows
Now we knew how to define name manager for columns. We can define rows too. Follow the below steps.
- Create data as shown in the below picture.
- Select the entire data click on Formulas > Define Name > Create From Selection
- Click on that option and below box will open
- Uncheck everything except LEFT COLUMN.
- Now it has created the name manager for left column row data.
- You can check all the defined names from the drop-down list of Name Box.
Rules to Creating dynamic Named Range in Excel
You need to keep in mind certain things while defining the names. Follow below rules from Microsoft, which are pre-determined.
- The first letter of the name should begin with a Letter or underscore (_) or backslash (\)
- You cannot give any space in between two words.
- You cannot give cell reference as your name, for example, A50, B10, C55 etc..
- The letter like C, c, R, r cannot be used because excel uses then as selection shortcuts.
- Define Names is not case sensitive. MONTHS and months both are same.
This has been a guide to a dynamic named range in excel. Here we discuss how to create a dynamic range in excel along with practical examples and downloadable templates. You may learn more about excel from the following articles –