If you need to simplify your Excel worksheets, you can do it by reducing the number of formulas and data ranges. Here, excel dynamic range can help you achieve it. Because you are automating your formulas and data ranges here. Since Dynamic Named Ranges update automatically, your data stays up-to-date without any manual intervention.
Table of Contents
In this article, we will guide you through what is Dynamic Named Ranges in Excel. From the definition, how to create them, how to use them, and their benefits, to the frequently asked questions, we have got you covered.
Introduction to excel dynamic range
Dynamic Named Ranges are named ranges automatically adjust the size of the range based on the data in it. In other words, Dynamic Named Ranges expand or contract according to the size of the data. Unlike static named ranges, which have a fixed range of cells, dynamic named ranges adapt to the size of the data.
You can use them for various purposes. Such as creating charts, PivotTables or simply simplifying your formulas. They can be created using either a formula or VBA code.
Short Answer: Dynamic Named Ranges in Excel
Dynamic Named Range is good for users to create a named range. And it can automatically adjust its size based on the number of items in the range. With Dynamic Named Ranges, users can create formulas. They basically reference a range of cells without manual update for each adding or removal.
How to define dynamic range? dynamic range excel
To create a Dynamic Named Range in Excel, here users can use the OFFSET and COUNTA functions. The OFFSET function returns a reference to a range. And these are in number of rows and columns from a starting cell. While the COUNTA function counts the number of non-empty cells in a range.
If you wanna illustrate how this works, let’s say we have a table of sales data with columns A through D and rows 2 through 10. We want to create a Dynamic Named Range for the sales data in column B.
dynamic ranges in excel
- You will select cells in column B to include in the named range.
- In the Name Box, it is located to the left of the Formula Bar, you will type the name you want to give (e.g., “SalesData”).
- You will press Enter to create it.
- Also, you should go to the Formulas tab and click on Define Name.
- Here in Define Name dialog box, you must enter the following formula in the Refers to field:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
But you should note that Sheet1 should be replaced with the name of the sheet where your data is.
How do I create a dynamic chart name in Excel?
Creating Dynamic Named Ranges in Excel is relatively easy with below steps.
- You will select the cell range and click on Formulas tab.
- Then you will click on Define Name in the Defined Names group.
- In New Name dialog box, you will enter the name in Name field.
- In the Refers to field, you enter the formula that defines the range. For example, “=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)”
- So you will click OK to create the Dynamic Named Range.
Alternatively, you can create a Dynamic Named Range using VBA code. You can use below code.
Sub CreateDynamicRange()
Dim lastRow As Long
lastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Range("MyDynamicRange").RefersTo = Range("Sheet1!$A$1:$A$" & lastRow)
End Sub
How to Use Dynamic Named Ranges
Once have your Dynamic Named Range, you can use it in formulas, charts and PivotTables. To use a Dynamic Named Range in a formula, you will enter its name instead of the cell reference. For example, instead of using “=SUM(Sheet1!$A$1:$A$10)”, you can use “=SUM(MyDynamicRange)”.
What is a dynamic name in Excel?
- You will create a chart as usual.
- Then all you need to do is to right-click on the chart and select Select Data.
- You can click on Add in the Legend Entries (Series) section.
- Here, you will enter the name of your Dynamic Named Range.
- In Series values field, you will enter the formula that defines the range such as. “=Sheet1!MyDynamicRange”.
If you wanna use a Named Range in a PivotTable, you can do below.
- You can create a PivotTable like always do.
- But this time in PivotTable Fields section, you will drag the field you want to use as a Dynamic Named Range to Row Labels or Column Labels section.
- You will right-click on the PivotTable and select PivotTable Options.
- Here in this box, you can click on Data tab.
- In Retain items deleted from the data source section, you will select None.
- And you can click OK to apply the changes.
Benefits of Using Dynamic Named Ranges in Excel
Using Dynamic Named Ranges in Excel has numerous benefits, such as:
- Instead of using complex formulas with fixed ranges, Dynamic Named Ranges will simplify your formulas on behalf of you.
- These ranges update automatically. Hence, this is reducing the risk of errors when you may do manually update fixed ranges.
- They will definitely help you regarding manual updates and adjustments.
- Also, Dynamic Named Ranges is good for creating dynamic charts and PivotTables automatically adjust to changes in your data.
Conclusion
Dynamic Named Ranges are way smarter way to create tables which will always need updates. So, if you can learn and try steps in this article, you can easily create and benefit from Dynamic Named Ranges tables.
If you have any further questions or comments, please leave them below.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
