How to Make Data Range Dynamic in Excel

Excel is a powerfull tool for data analysis and reporting and has given a wide set of formulas and VBA coding functionality to make it more customizable and usefull. While creating Pivot reports in excel, we usually take range of data manually like   =$A$1:$B$11 for below set of data but what will you do when your data exceded to 15 records from 11, you need to change the range again. And if you are having lots of pivots in your Dashboards, you need to change the range for each of them separately every time. 

 

But we can develop solution to this situation manually using excel’s Name Manager feature, which is one of my favorites feature’s in excel.  To make it easy I will summarize the way in below points -

1)      Open the Name Manager using CTRRL+F3 in Excel.

2)      Click on New to define a new Range and later we will make it dynamic using Excel functions.

3)      In the Name Field give any name to your data range, Scope can be taken as Workbook if you want to use this range in this workbook. In the field refers to give the range .

4)      As in above example our range is A1:B11, where A, B are column and 1 to 11 is range so now we have to make our last row selection as dynamic as it can be from 1 to 11 or 15 or 20.

5)      For this, we use COUNTA and INDIRECT function of Excel , remember to use COUNTA on primary key which is not having null value, here IN_ID column will not have null values so we use counta function in this column to calculate no. of rows dynamically.

Note : COUNTA – Calculate all the cells except blank cells.

           INDIRECT – Returns the reference specified by text string.

6)      Now write the Range $A$1:$B$11 to =Sheet1!$A$1:$B$"&COUNTA(Sheet1!$A:$A)

(We have deleted 11 from $A$1:$B$11, and calculated it dynamically using COUNTA function)

7)      Now Put the whole Formula inside INDIRECT Function

=INDIRECT("Sheet1!$A$1:$B$"&COUNTA(Sheet1!$A:$A))

8)      Now press OK it will save this range in Name Manager. Select Pivot Table -> Options -> Change Data Source it will open below window, type yhe name of your range here “My Range”

Now you don’t need to change range again and again, just refresh the pivot you will get revised output in report  :).

Feel free to comment if you have any query and how this method is useful to you.