Table of Contents
This tip is about the how to Create Dynamic Defined Range on Excel. So read this free guide, How to Create Dynamic Defined Range on Excel step by step. If you have query related to same article you may contact us.
How to Create Dynamic Defined Range on Excel – Guide
This article will show you how to build a dynamic named range in excel and how to use it in formulas to automatically include new data in calculations. We looked at various approaches to defining a static named range in Excel in last week’s session. Since a static name always refers to the same cells, you will have to manually update the range reference whenever you add or remove data.
If you are working with an ever-changing dataset, you may want to make your named range dynamic so that it expands to accommodate new entries and contracts to exclude data that has been dropped. You will find extensive step-by-step instructions on how to achieve this later in this article.
How to create a dynamic named range in excel
To get started, let’s build a dynamic named range that consists of a single column and a variable number of rows. To do this, follow these steps:
OFFSET formula to define an Excel dynamic named range
The generic formula for creating a dynamic named range in Excel is as follows:
OFFSET(first cell, 0, 0, COUNT(column), 1)
Where:
At the center of this formula, you use the COUNTA function to get the number of non-blank cells in the column of interest. This number goes directly to the height argument of OFFSET(reference, rows, cols, [height], [width]) telling you how many rows to return.
INDEX formula to create a dynamic named range in Excel
Another way to create an Excel dynamic range is to use COUNTA in combination with the INDEX function.
first_cell:INDEX(column,COUNTA(column))
This formula consists of two parts:
For our example dataset (see screenshot above), the formula is as follows:
=$A$2:INDEX($A:$A, ACCOUNT($A:$A))
Because there are 5 non-blank cells in column A, including a column header, COUNTA returns 5. Consequently, INDEX returns $A$5, which is the last used cell in column A (usually an index formula returns a value, but the reference operator forces you to return a reference). And because we set $A$2 as a starting point, the final result of the formula is the range $A$2:$A$5.
To test the newly created dynamic range, you can have COUNTA fetch the item count:
=COUNT(Items)
Final note
I hope you like the guide How to Create Dynamic Defined Range on Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.