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
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 🙂