This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.
Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit.
However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes.
Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update.
Suppose you are working in a file with dozens or even hundreds of pivot tables (yes; there are people out there who work on such things).
If every time you changed a piece of data all of the pivot tables updated, it could bring the system to a standstill with all of the updates.
Excel elects to wait and perform the updates when you are finished with the changes and give the “all clear” signal.
Most users do not work in this world; we may only have at most one, two, or five pivot tables in a file.
PivotTables are designed to only refresh when one of two events occurs:
- You manually request an update via the Refresh button on the PivotTable Tools ribbon
- You set the pivot table to automatically update upon file open via the Pivot Table Options like this:
Auto Refresh Pivot Tables
If you would like your pivot tables to refresh automatically when you change your data source, perform the following steps:
- Save your workbook as a Macro Enabled Workbook with a .xlsm file extension.
- Right-click on the sheet tab containing your data and select View Code
- In the code window, paste the following lines of text then press the Save button in the VB Editor:
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
Example:
All of your pivot tables will refresh when you click off of the sheet holding the data.
The Worksheet_Deactivate macro code will be executed whenever the sheet holding the code loses focus, i.e. you switch to a different sheet.
Thanks again, Bryon for sharing your knowledge.
Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.
Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used. There literally seems to be no end to its usefulness.
My favorite Excel tools are difficult to narrow down. Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers. WOW! Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”
Vote for Bryon
If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂
Awesome guidance! Thank you so much.
Glad you found it useful, Rajesh 🙂
I saw your advertising and to add Ivan comments we live in a country in which we cannot have online shopping, at least can you please introduce a free source for creating dashboards?
@Ivan & @Robin,
Thanks for your kind words. I’m sorry that you aren’t able to access our online training from your country. Unfortunately I don’t have any free courses on creating dashboards at this stage. The best I can offer is my 30 Tips for Better Charts eBook which you can download here.
Kind regards,
Mynda
Thanks for the book and your super fast reply, I like the idea of share the knowledge of the book as well. I wish you and Bryan best
This is exactly what I was searching for, I appreciate if you would be kind enough and teach us how to create dashboards
Hello Bryon/Myanda,
Thank you so much for your valuable help.
You’re welcome, Dnyandeo 🙂
Very nice, this can be useful to one of my clients! Thanks!
Cheers, Michael 🙂
Bryon, thank you so much, what a great tip. Pete
Cheers, Pete. Glad you liked it. Bryon’s tip was one of my favourites too 🙂
How can i insert value in excel 2003/2007/2010 or access 2003/2007/2010 using macro.
please help.
Thanks & Regards,
Vijay Tiwari
Hi Vijay,
However eager we are to help you, VBA is outside the scope of our support services.
I hope you do understand, but we are currently working on a VBA program soon.
Cheers.
CarloE
Hello Bryon,
Thank you for above information.
But i have another problem where in I have excel files hosted in MS sharepoint and files are not getting refreshed automatically. I have selected option as refresh every 1440 mins under data tab in properties button under usage.
Please help.
Thank you in advance..
Best Regards,
Gaurav
Hi Gaurav,
I asked Bryon about your question as I am not familiar with SharePoint and this is his reply:
“Unfortunately, I’m not very educated on the inner workings of SharePoint either. I did some digging and didn’t come up with much. I did find the below observation, but I don’t know how applicable it is to Gaurav’s situation:
Sorry I couldn’t have been of more help.
Bryon”
Thanks Bryon, i was just thinking about this and you solved it.
Thank you very much 🙂
My pleasure. I wish Microsoft would simply add a “switch” to allow for automatic updates. Maybe next version (hahahahahaha!!!).