How to Make a Dynamic Defined Range in Excel

Storing data in Excel simply make things easier for you or anyone who uses it. The data you store in Excel can be updated as well, enhancing its importance. Creating data every time consumes so much time, and that is why making a dynamic defined range could help you in increasing your data automatically. It can also help you in the making shorten the range of data. With the help of dynamic defined range, you don’t need to create or edit the data again from formulas, PivotTables and charts.

A dynamic defined range will do several things automatically and now let’s know more about it. To make a dynamic defined range, you need to use two formulas, which is a dynamic defined range OFFSET & INDEX.

Make a Dynamic Defined Range in Excel

To understand the process, let’s take an example and put the name of countries on the single-column list. Now to need to dynamic because if you add more name, then the range will automatically update. Without using the header cell, try the range $A$2:$A$6, but as dynamic. Now click on “Formula” bar and then “Define Name.” the next thing you need to do is in the “Name” box type name of “Countries” and now put the formula in “Refers To” box.

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Type the above equation on the spreadsheet cell and copy it in a New Name box to do things faster for next time.

How does the Process of Work?

The formula in first part only specify the beginning cell of range (A2 in our term) and that the range operator is –

=$A$2:

INDEX function forced by the usage of range operator to not value a cell and returns the range instead. The INDEX function is great, and it used alongside the COUNTA function. The main function of COUNTA cells is that it counts non-blank cells in column A.

INDEX($A:$A,COUNTA($A:$A))

Formulas work on their own, and they are already settled up then you put the formula in the column. Keep the preference to save the formulas, so it will be easy for you to perform activity faster next time. The main function of INDEX is to return the range of last non-blank cell in column A ($A$6).

Because of the COUNTA function, the result is $A$2:$A$6, and now it’s dynamic and will find the final row. Now you can use “Countries” as a defined name into Data Validation rule, chart, formula and many more anything you name,  need to refer countries.

Make a Double Way Dynamic Defined Range

There are several ways that you can make modifications in height and width. In the previous example, it was only about dynamic in height; however, now we will do some extra modification and also another COUNTA function. The range can be created in various ways, but now make a range which is dynamic with both width & height. With example, it will be easy for you to understand.

• Make a Dynamic defined range that has headers. Now tap on “Formulas” bar and then “Define Name.”
• In the “Name” box write “Sales” and then enter the formula for the “Refers To” box.

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

• At the beginning cell, the formula $A$1 gets in use, and then INDEX function uses a range of complete worksheet ($1:$1048576) for looking in and return form. If it starts working and COUNTS function will start to work on counting non-blank rows, and another one starts non-blank columns to make dynamic from both sides. Even though, the formula begins with A1. Now you can make dynamic chart data with just formula by using any name.

Mathew Anderson is a creative person who has been writing blogs and articles about cyber security. He writes about the latest updates regarding McAfee.com/activate and how it can improve the work experience of users. His articles have been published in many popular e-magazines, blogs and websites.

Source : Dynamic Defined Range in Excel