Dynamically refreshing pivot tables when data ranges change

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

I had a colleague ask me this question today. (edited to protect the innocent – you know who you are!)

I copy data out of E_____ (am sure you have heard of it), and paste it into an excel spreadsheet.  I have a couple of pivot tables hanging off the back of this data.  As this is project data, the longer the project goes on, the larger the dataset gets.  Is there any way in VBA that I can get the pivot table to auto-expand the data set that it is using? 

IMGP7080Here is my answer.

Hey W__

There is a simple solution that doesn’t require any vba at all

First you will need to make a dynamic range name that covers the data being pasted.

Basically this uses volatile functions to calculate the number of rows of data when you paste the data in.

Then the second thing you do is change your pivot tables source data to read from the range name not a cell reference.

 

Ok how to make a dynamic range name.

Press CTRL F3 or go to Edit Insert Name Define

Name your range – e.g. pivot_table_data1

In the refers to section type or copy and paste this formulas

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

clip_image002

Assuming that your data starts in cell A1 of Sheet1. Edit accordingly before pasting in

What this formula does is creates an array that starts at cell a1 goes zero cells down and zero cells right, and is x cells deep and y cells wide. x = the count of all non blank cells in column A and y the count of all non blank cells in row 1. Assumes that column A and row 1 are contiguous data blocks the same length and width as the whole data set. If not use columns and rows that are.

Now in your pivot table right click and go to Pivot Table Wizard

clip_image004

Hit Back and go to the range selection

clip_image006

Type = and the range name = e.g. =pivot_table_data1

In my case my range name was called test.

Hit Finish and it is done.

Now whenever you paste more data into the data sheet the pivot table just needs to be refreshed to pick it up

Click anywhere on the pivot table and click the exclamation mark in the toolbar.

The instructions above assume you are using office 2003. In office 2007 the Name Manager is a little more confusing, but can be accessed with the same keyboard shortcut and you will need to create a new range name and then use the same formula listed above.

Cheers

Tim

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Tim Mayes's picture

Even easier than dynamic

Even easier than dynamic range names is using a List (Excel 2003) or Table (Excel 2007) as the source for the pivot table. The list or table (same thing, different name) will automatically expand, and it will also automatically fill in any formulas and formatting in the range.

Carolyn's picture

Hi, Can you please tell me

Hi,
Can you please tell me how to use the List or Table option for dynamical refresh of pivot table

jethro's picture

Carolyn - thats what this

Carolyn - thats what this article does - im not sure what you mean. Can u tell em what you need that this article doesn't show you?

Alastair's picture

Hi Tim, Offset is a volatile

Hi Tim,

Offset is a volatile which means it recalculates every time the sheet changes - like a SUM formula.

You should use a variation of INDEX instead, it's more efficient:
Where you would have used: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)
You should use: =Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6)

Here's a breakdown:
=Sheet1!$A$1 - the top left corner.
=Sheet1!$A$1: - the colon means 'to' as in from here to here
=Sheet1!$A$1:INDEX - Index is the non-volatile component it finds the intersection between two points
=Sheet1!$A$1:INDEX(Sheet1!$A:$F, - the first part is the area your looking at, in this case, all the 6 columns in the dataset
=Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A) - the first intersect is the bottom of the first column, determined by counting the contents
=Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6) - the second intersect is the number of columns along.

=Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6)
So this says: find me the range from the top left corner to the bottom right corner, which is the dataset.

jethro's picture

Thanks Alistair - i never

Thanks Alistair - i never thought of using index in that way - i will try it. volatile functions are always a worry in large spreadsheets as the degrade performance - particularly on slower machines so anything that can reduce that is always good.
Cheers

Zoran's picture

Wow thanks, this is a big

Wow thanks, this is a big help as I feel I am quite weak when it comes to pivot tables.The foreign exchange market (currency, forex, or FX) trades currencies. It lets banks and other institutions easily buy and sell currencies.forex marketThe forex market, offers a completely different investment asset class that offers leverage and virtually unrestricted access 24 hours a day.

Anonymous's picture

Thanks! Actually, word2007 is

Thanks!

Actually, word2007 is not confusing at all...
just an acquired taste :)

Anonymous's picture

This is big time. Thanks!!!!

This is big time. Thanks!!!! Somewhat a tutorial for me since I'm having a problem regarding this.

Anonymous's picture

Excellent solution - very

Excellent solution - very elegant. Thank you very much!

H.Hegazy's picture

Thanks a lot, it's really

Thanks a lot, it's really smart way "saved my time"
and no need for Macros, just simple Dynamic Range :-)