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.
