## 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