Bottom Line: In this episode of the Ultimate Excel Dashboard Tutorial Series you will learn how to make the Pivot Tables and Pivot Chart of your Dashboard automatically refresh when the source data changes.
Skill Level: Advanced
Video Tutorial
Disclosure: Some of the links below are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
The ultimate Excel Dashboard Tutorial Series
In our Ultimate Excel Dashboard tutorial series you will learn how to create a state-of-the-art interactive Excel Dashboard with many outstanding and custom-built features. This tutorial series will cover how to
- create a basic interactive Excel dashboard with pivot charts and slicers
- design a beautiful dashboard background and gradient tile design
- create a modern dashboard slicer design
- auto-refresh dashboard pivot charts when source data changes [you are here]
- create interactive dashboard info buttons
- create interactive dashboard tabs
- create a custom interactive settings menu with modern radio and toggle buttons
By completing this tutorial series you will learn how to visualise your data and bring it to life in a completely stunning way. The great variety of different topics covered during the process will develop and strongly improve your overall Excel skillset.
Info: In this dashboard we use features that are only available in the latest version of Excel (included in Microsoft 365). If you want to are still using an older version of Excel, we recommend to upgrade to Microsoft Office 365 to have full access to all features.
Manually Refreshing Pivot Tables and Pivot Charts
Everyone who has worked with Pivot Tables and Pivot Charts most likely already had a situation in which the source data changed. When that’s the case, Pivot Tables and Pivot Charts tend to be static and do now update automatically to display the new data. To handle that issue, the common approach is to Pivot Table Tab > Refresh (All). The issue with that is, you have to do that every single time the source data changes. For a dashboard based on Pivot Tables and Pivot Charts for which the source data is regularly updated and expanded, manually updating each time is not only a time-consuming operation, but also bears the risk of unintentionally spread outdated information when that manual update is forgotten once.
An automated solution on the other hand can guarantee an up-to-date status of dashboards and report after every change of source data. In the following sections you will learn how to implement it.
Automatically Refreshing Pivot Tables and Pivot Charts
The most powerful solution for that issue requires Excel VBA. You can write your own scripts with ease in the Visual Basic Editor that you can find in the Developer Tab (the Developer Tab can be activated in the Excel Preferences > Ribbon & Toolbar). Once you have the Visual Basic Editor open, you will find a list of all worksheets in the left part of the Editor. Search for the sheet with your source data and double click on it to open a worksheet-related empty script. For automatically refreshing all pivot tables and pivot charts in the whole workbook, you only need a few lines of code.
The following option is the fastest and easiest.
Private Sub Worksheet_Change (ByVal Target As Range)
ThisWorkbook.RefresAll
End Sub
This simple line of code will refresh all pivot tables (and by that also the related Pivot Charts), but also all Queries in the Workbook.
The following code is a more cleaner solution, that only refreshes the Pivot Tables and Charts in the workbook (but not the Queries).
Private Sub Worksheet_Change (ByVal Target As Range)
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
Both these solution will work fine for most cases. However, the second one is preferable for big amount of data and workbooks that use Pivot Tables and Queries.
Automatically Refreshing Single Pivot Table or Pivot Chart
If you only want to automatically refresh a specific Pivot Table or Pivot Chart, you have to directly reference the respective object. In that case, you will need one line of code per object with the following structure.
Private Sub Worksheet_Change (ByVal Target As Range)
Worksheets(“Sales Line”).PivotTables(“PivotTable1”).PivotCache.Refresh
End Sub
Workaround without VBA
It sometimes is the case (e.g. in companies for security reasons) that you are not allowed or don’t want to use VBA in your Excel file. In such a case, there is workaround that doesn’t do the whole job but at least makes sure all Pivot Charts and Pivot Tables are automatically refreshed when the file is opened.
For this setting, you have to right-click on one of your Pivot Tables and click on Pivot Table Options. That will open a pop-up window where you have to go to Data > Refresh data when opening file.
This workaround is not as beautiful as the VBA solutions since it doesn’t automatically refresh the Pivot Tables and Pivot Charts in realtime when source data changes, but it might help you out for specific use cases.
Conclusion
That’s already it for this fourth episode of the Ultimate Excel Dashboard tutorial series. We recommend to continue with the next episode of this Ultimate Excel Dashboard tutorial series in order to make this dashboard even more awesome than it already is.
You can download the Basic Dashboard Excel File in our Download Area.
