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

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts