Dynamic Source Data for Pivot Table Using Offset Function

you can use offset function to create a dynamic source for the pivot . Dyanmic source here means every time you add anew rows of data to the pivot table you need to click on pivot then go to change data source option and increase the range of data in source (In Excel 2007 select pivot then go to Option menu -> change source data.) Snapshot below




to avoid above step every time you add a new data or rows in source for pivot

we can use a dynamic source and create a pivot using that

Steps to create a dynamic source for pivot

1. right click on source data and choose name a range. you will see a window like below.




here is the formula to make a dynamic range

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


Data!$A$1 = is the staring cell of data
0 keep it zero always
0 keep it zero always
COUNTA(Data!$A:$A) = it will count no of rows filled
4 = no of columns in source data
and click on ok

(Data! - is sheet name of source data)

type source( or the name you have given to dynamic source data) in the source data of pivot



Now whenever you will add new data to source you don't need to go again and agian to change pivot source data it will automatically pic it up