How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete data in your data set the pivot is instantly refreshed.
There is no need to manually refresh the pivot table or changing cell references if the data source table grows or shrinks. The Excel defined table uses structured cell references that adjust automatically.
How to create an Excel defined table
- Select a cell in your data set.
- Press with left mouse button on "Insert" tab on the ribbon.
- Press with left mouse button on table button.
- Press with left mouse button on "My table has headers" if needed.
- Press with left mouse button on OK button
Adding new records to the table is easy, simply type on the row right below the last record and the table will expand automatically.
You can also press with right mouse button on on a cell in the table to open a context menu, press with left mouse button on "Insert" and then "Table Rows Above".
Recommended article:
Recommended articles
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
How to auto-refresh pivot table
VBA offers a solution how to automatically refresh pivot table every time you activate "pivot table" sheet, there are other ways to solve this as well like refreshing pivot table every time a cell in data source table is edited.
Press with right mouse button on on the sheet name where you placed the pivot table.
- Press with left mouse button on "View code".
- Paste code to module.
- Change sheet name and pivot table name in VBA code, you probably have a different sheet name and pivot table name.
VBA code
Private Sub Worksheet_Activate()
Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub
Recommended article:
Recommended articles
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Excel 2003 and earlier versions: Create a dynamic named range
This also works in Excel 2007/2010 but it is easier to create a table.
- Press with left mouse button on "Name Manager"
- Create a new named range, named Rng.
- Type in "Refers to:" window:
Press with left mouse button on Close button!
Recommended article:
Recommended articles
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
Pivot table category
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Excel categories
18 Responses to “How to create a dynamic pivot table and refresh automatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Moment you use tables you are restricting it to 2007/2010
Instead create a 2d dynamic name
Data
='1'!$A$1:INDEX('1'!$1:$65536,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1)) and use it in the Pivot Range
Great comment, thanks!
Hi Oscar,
I was able to get this to work on Excel 2011 (Mac) but when I open up the same file in Excel 2010 (PC) the macros don't run. I've enabled Macros and set the security settings to run all Macros.
Essentially what I have is multiple pivot tables across 6 worksheets all using the Data worksheet as the data source.
I tried to update the Macro to use the code you mention in the post "https://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/". Using this the macros runs but I get a run-time error '1004': Application-defined or object defined error.
Any thoughts on what I can do in Excel 2010 to get my macro to run?
Thanks - Tim
I'm not sure that I'm in the right location for this, and I'm working handicapped with v2003 and I don;t know vba.
I have an xml file thats exported from a third party tool. I import that into excel and add a column to the far left. In this column I do a match fucntion. This function matches specific data in one column of the xml data to "sheet2" which containes a list of codes that I care about. Then I create a pivot table that uses the match (1-30), ingnores blanks and NAs to filter my pivot. What gets displayed is all the information I care about. Works great. Problem is I'm doing this on a weekly basis and realize there must be a better way to link to the xml, still make my match to my codes and create a piviot table.
Question is how to acheive this. No luck treating the xml as a database. Excel is not seeing it.
Please advise and thanks for the expertise.
RE,
Is it possible in excel 2003 to import xml using Get external data, from other sources?
Import multiple XML data files as external data
If it is then you should be able to refresh external data source if the xml file name is the same.
There is a much much easier way to keep the pivot table dynamic guys!!
Simply reference only the top row of your data. Leave out the column references.
Example- Before: $A$1:$Z$5000
Example- After: $A:$Z
Notice we are completeley leaving the numbers out...works like magic!
JamesO,
Yes, it works but only when the data begins on row 1.
I have not tried this in excel 2003 but if it works you can skip the "named range" part.
You still have to press with right mouse button on your pivot table and select "refresh" to refresh pivot table.
Thank you for commenting!
Hi dear,
How to Number form Sentencce which in celll
Example:- In cell a sentacne
Problem Summary(Cell 1)
spk to mr r k chabra call 4 want to shift the conection in same room alt no is 91-9818786350 plz chk dn do need full asap.............
REQUEST RELATEDTariff MigrationContact Person:tarun Contact Number:9871431939Authentication Done:YesCheck OG Status - activeSegment Check:YesCurrent Plan :Airtel - UL - Smart One Combo 8GB 799(2Mbps/256Kbps) DSL Plan (Subscription
Disturbance-All Times (Cell 5)
Ateeq
91-9891608537
[...] Have a look at the following... How to create a dynamic pivot table and refresh automatically in excel | Get Digital Help - Microsof... [...]
Thank you so much you are a lifesaver!
such a helpful thread.
i used "OFFSET" instead. much easier and dynamic!
Ali Hasnain,
How did you get the pivot table to update automatically?
How can it be easier than an excel defined table?
example i have 3 sheet student's attendance.
i use sheet 1 to check 30 student from class A: attend or absent and sheet 2 to check same 30 student but from class B
and i use sheet 3 as a record and make option Class : A or B. If i choose Class A, i can see which student whose absent from Class A only. And if i want choose Class B, THE TABEL WILL AUTOMATIC change to Class B and i can see which student whose absent from Class B
Can u give me what's the tutorial to make that?
Simply use vloopup like
IF($B$1="A",VLOOKUP(Sheet3!A2,A!$A:$B,2,0),VLOOKUP(Sheet3!A2,B!A:B,2,0))
I have a template that I have created to process phone bills. There are may lines of GL coding for particular individuals. The amounts are different every month. I have created a pivot table in the template (both data and pivot table under one tab). I have created a refresh button to refresh the PT. It works in my original tab but when I copy to create a new tab to process a new invoice it does not refresh on the copy. My pivot table is still connected to the original tab. When I enter data in my original tab and refresh on my copy it moves. How do I keep the connection between my source data and pivot table in the copy together and not the original tab?
Dear sir I have copied the vba code here and it done the magic . Thank u .
But I have 2 more worksheets in my inventory , i am unable to do the same again for the respective pivot tables
Kindly help me in this regard
Thank you
Hi Oscar,
Please I am hoping you'll be able to assist me. I designed a template whose data is driven by a Pivot table source. The pivot Table is in turn driven by a source data in the same workbook. I used "IF" formula to pull the data from the pivot table(for example "=if('Sheet1'!B17="","",'Sheet1'!B17). the challenge is that source data are dynamic on a month to month basis and each time I update the the pivot table based on the source data. The 'IF' formula is static and does not change with the template row heading. Please what can i do to make the template(&'IF' formula) row heading to be dynamic with the pivot table when refreshed.
Just to note that I am not that too vb savvy.
Please reply.
Thanks
[…] this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named […]