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

Turn Data into a Dashboard with Pivot Tables

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

Download the file to follow along.

Intro To Pivot Tables And Dashboards - Part 3.xlsx (160.5 KB)

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.

Custom Layouts for Your Pivot Tables - PivotPal

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 with PowerPivot Data Model

PivotPal is now available!

Click here to learn more about PivotPal!

Additional Resources

  • 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!

152 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon

    Thanks for the great training videos. These turn an onerous task into an easy one and produce a very usable dashboard!

  • Hi jon!
    These videos have been so extremely helpful to me in preparation for a new job opportunity. I did have a quick question/issue i’m running into with the 3rd video follow along packet. The beginning of the video where you’re separating the sales by region, i have all my pivot table fields set to the correct field names however my tables and graph aren’t changing to show the order dates in detail. It only lists Jan-Dec with a grand total. Any advice?

  • Hi Jon,
    I must say that your lessons are insightful. Thank you and keep up the good work.

    Regards,
    Flavio

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly