Intro to Pivot Tables and Dashboards Video Series 3 of 3
111

Interactive Dashboards with Pivot Tables & Charts [Video 3 of 3]

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 111 comments
Mark Chesney - June 15, 2017

(in response to my own comment)
I see how easy it was to generate the slicer that was missing. Just adding Salesperson and other fields to the Filter box of the Pivot Table Field List.
Alright, no worries! I figured it out 🙂

Reply
mchez - June 15, 2017

Hi Jon. I notice at the 2-min mark of video 3, that the Sales by Region worksheet doesn’t have the slicer in the .xls file, though it already does in your video. There are other nuanced differences like dashboard chart headers on the first worksheett, so it’s clear it’s a different file. I’m gonna continue watching — hope this still works out ok.

Reply
Venky - May 23, 2017

Great Video, very easy to follow and learn…Great job Jon

Reply
KC Heng - May 11, 2017

How yo get PivotPlus ?

Reply
JS - April 20, 2017

Great tutorial videos, easy to follow!

Reply
Adel - April 16, 2017

Dear Mr.Jon

Really it`s more than wonderful.

Appreciated.

Reply
Jason White - April 2, 2017

Hi Jon,

First, Thank You. Your knowledge and willingness to share has really provided me with the essentials I need to begin working with Pivot Tables & Charts. I’m slowly integrating into my work and getting a handle on how these can help make my job easier.

I’d like to throw one question at you, and hope you could point me in the right direction to finding my answer.

I’m working with a massive data table, and have got it working the way I want with many different pivot tables and a dashboard. Our business is fuel delivery, and I’m tracking litres delivered.

I have it set up similar to ABC Global, but my “Sales Trend” is litres delivered. I’d like to incorporate our litre budget into the dashboard graph and show two lines(or bars) – one actual and one budget.

My data table is generated from entering the data from each delivery slip – which doesn’t include the budget of course.

How would I go about adding a budget column to the “Sales Trend” pivot table and plotting it alongside the “sales” data on the chart?

Any direction you could provide would be awesome.

Cheers

Reply
    Jon Acampora - April 10, 2017

    Hi Jason,
    Thank you for the nice feedback. Adding budget data to the source data can be tricky because you typically have budget data at a higher summary level than the actual transaction data. You can add a new column for budget, then only enter numbers for the budget row where it aligns to the summary level that the budget is at. Enter 0 for all other cells.

    This is where Power Pivot shines because it allows you to create relationships between data tables. I don’t have a specific article on this yet, but I will add it to the list.

    Thanks again!

    Reply
James - March 26, 2017

Great job Jon, Your lessons are very helpful in creating Pivot Tables and in creating Slicers. Thanks for time and effort in creating these training videos for Pivot Tables and Slicers.

Reply
Hasma - March 17, 2017

Hi Jon, I am new at 2013 window and I find this very helpful. I am sure I will have lots of questions once I dig deep into it. Hasma

Reply
Juan Sanchez - February 23, 2017

Hi! i would like to thank you! your video’s did what you said they would… I’m only having one issue, I cant seem to lock the sheet so they can have access to the Slicers and the charts information but not letting them move them or enter data. How can i lock the sheets and only give them access to what i want? I’ll be waiting for your response. Thanks!

Reply
Lori - February 21, 2017

Genius! Thanks so much! You have saved me.

Reply
Louise W - February 14, 2017

Hi Jon
Thank you for your tutorials and videos – easy to follow, clear and concise instructions and to the point.

One thing I always struggle with when using Pivot Tables is connecting two tables together by creating a relationship between them. I find it very temperamental – some times they work and some times they don’t. Also, and this may be something I am doing wrong, when I update either of the original tables and then refresh the pivot table, it doesn’t bring any of the new data through.

In short, if you are thinking of creating a Pivot Table Relationship tutorial, it would be really useful.

Thanks again.
Louise

Reply
    Jon Acampora - February 15, 2017

    Hi Louise,
    What method are you using to create the relationships?

    Reply
      LouiseW - February 27, 2017

      Hi Jon

      Apologies it has taken me so long to get back to you. I have one workbook with two worksheets, both containing different data but there is a common theme between them – they both have membership numbers on.

      I want to create one pivot table that contains fields from both worksheets – what’s the best way to do it? Do you have any videos to explain?

      Thanks again.
      Louise

      Reply
        Jon Acampora - February 28, 2017

        Hi Louise,
        To create relationships you can either use lookup formulas like vlookup, or you can use Power Pivot instead of vlookup. Those links are to articles that explain more about each solution. I hope that helps.

        Reply
          LouiseW - February 28, 2017

          Hi Jon

          Thank you for the links, I will take a look. I was exploring the option where you can create a pivot table, add it to the data module and then add more tables to it, rather than using a vlookup. Is this something you’re familiar with?

          Thanks.
          Louise

          Reply
Fasi Ud Din - February 8, 2017

Hi Jon – I am more than just glad that I have found this blog. I appreciate you for making such comprehensive tutorials. You really made this a lot easier. I would appreciate if you can let me know how to get started with Macros and VBAs. I am really interested in learning them but unable to get a head start.

Once again – Very Well Done and Thanks for Your Help 🙂

Regards,
Fasi

Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x