Bottom line: Learn how to quickly create an interactive dashboard using pivot tables and pivot charts. It's easier than you think!
Skill level: Beginner
In video #1 and video #2 we learned how pivot tables and pivot charts work. I explained all of the basics to get you started. Then we saw how to use pivot tables to quickly investigate your data and answer questions about your business.
In this video we finally help Andy put the dashboard together. This dashboard will help communicate our findings and tell a story about the performance of our business. Plus, the boss will LOVE it! 🙂
The great part about using pivot tables and pivot charts is that they are very easy to update. When next month rolls around, we simply paste the new data to the bottom of our source data sheet, update the source range, and hit the Refresh button. It will only take a few minutes a month to maintain.
Video #3 – Building the Dashboard
Download the file to follow along.
Topics Covered in the Video
This video is packed with tips for working with Pivot Tables and Pivot Charts. Here are some of the topics covered.
- The Report Filters area explained.
- Group dates into months and years to create a summary trend report and chart.
- Group amounts to create a distribution chart (histogram). One of my favorites!
- Resize all charts to be the same size.
- Prevent charts from resizing when column widths and row heights are changed.
- Add slicers to make the dashboard interactive.
New Features Added to PivotPal
Based on the responses from the pivot tables survey, I added a new feature to PivotPal.
If you are frustrated with having to change the layout settings for your pivot table every time you create one, then you are not alone!
Pivot tables have over 30 options and settings you can choose to get your pivot table looking and behaving the way you want it. These are some of the settings you probably change frequently:
- Turn off the automatic subtotals and grand totals
- Show a zero for empty cells
- Turn off the autofit column widths
- Use the classic pivot style
This usually requires you open the pivot table options menu and click different checkboxes on/off. That menu alone has 6 tabs of options.
To help save time with this I added a new feature in PivotPal called “My Pivot Layouts”.
This feature allows you to create custom layouts with your favorite settings and options, and then quickly apply those settings to any pivot table with the press of a button.
The nice part is that you can create as many custom layouts as you like. Some pivot tables you might want to format to look like a financial report, other times you might want it to look like a tabular format with repeating labels. These layouts can all be saved and applied anytime with this feature.
See it in action in the video below.
PivotPal now works with PowerPivot!
I have a PowerPivot data model that has 8 tables and 226 fields in it! You might have even more than that. Using PivotPal with these large models makes it extremely fast to find fields with the Search feature, and then add them to your pivot tables.
PivotPal is now available!
- In part 1 of the series we learned how to setup our source data and create our first pivot table and chart.
- In part 2 of the series we learn how to use the different calculation types to investigate our source data.
If you aren't already, subscribe to my free email newsletter to stay updated.
Please leave a comment below with any questions or suggestions. Thanks!