Saturday, 25 October 2014

How to Create and Select Dynamic Range for a Pivot Table or Chart

Excel OFFSET Function to Create Dynamic Range for a Column or Range of Pivot Table or Chart
Here we will discuss about how to Define a Dynamic Range for a Pivot Table. The same Technique can we use for Charts and other things wherever dynamism is required.
Let us Suppose if we have a Pivot table like as Follows...




Source data as follows...




Define Dynamic Named Range with OFFSET Function :
We can use Offset and Count Combo formula to Define the Dynamic Range.
Suppose if your data only in one Column(Say Column A) , Then for Dynamic Range we can use the following formula:

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But in our Example we have the Data in Four Columns A,B,C,D.
So we can use the below formula to define Dynamic Named Range for our Pivot Table or Chart.

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

Dynamic Named Range :
To define "Dynamic Named Range", first we have select the Data.
Next Define the Name for It from Formulas>Name Manager>Define New Name.
Next choose Refers to Range OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)
Next say "OK"


Now use Dynamic Named Range "Dyn_Data" as a Pivot Data Source as shown below :



Now Increase the Source and then refresh the Pivot and Check whether the Pivot is refers to the updated Dynamic data or not.

Yes , It will refer to the Dynamic data source as shown below :


Pivot after refresh as follows :



Note :
We may get an error if we try to use this Formula directly in Table/Range address bar. Thats why we have to use them in the Named Ranges.

Thanks,
TAMATAM




1 comment:

  1. This Post is Most Powerful and Useful forever.....in the Professional Life..................
    Promising ....Simply the Best Technique...Forever.........

    Yours
    TAMATAM

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts