141

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 141 comments
Melody - July 17, 2018

Hi Jon,
I tried to recreate the Pivot tables from your intro to Pivot Tables and Dashboards Part 3. For the Sales by Product tab I am encountering a problem with the % of Total Revenue. When I add revenue to values and change it show values as ‘% of column total’ it doesn’t add up to what you have. For example for beverages = $110,577.11 your % of total revenue shows 25.4% of a total revenue of $435,036.16.
However my result shows 24.66%. Also all the following percentages I have are incorrect. And I don’t know what I am missing here, since the manual calculations I do match with what your pivot table shows me.

Reply
OSBERT - July 3, 2018

Awesome presentation…making it exciting and easy on the mind to grasp and understand..GOOD JOB!! Jon

Reply
Frederico Alberto Stuckenbruck - May 20, 2018

Needless to say how grateful I am for these wonderful courses. I really appreciate all of them.

Thanks a lot.

Fred

Reply
Marcus - May 18, 2018

I have two coloumns: “Country” in one column and “Expenses Per Country” in another column. I am using “Summarize Values By Sum” to get the total Expenses for each country. For example, in Germany the total expense is $498.78. And in Hungary it is $221.50.

In the two other columns I have “Country” in one column and “Average Daily Expenses Per Country” in the other column. I am using “Summarize Values By Average”. My intention is to get the average expense per day in each country. For example, in Germany there are 5 days (27-Sept, 30-Sept, 1-Oct, 7-Oct, and 8-Oct). Excel provides the average as $24.94. This is wrong. The average should be $498.78/5 days = $99.76.

The same issue occurs with Hungary which has 6 days (Sept 24, 25, 27, 28, 29, 30). The average expense per day for Hungary should be $221.50/6 days = $36.92 (not $6.92, which is value that Excel provides).

Is there a step I am missing to get the intended result?

Reply
phatman - May 2, 2018

Thank you so much

Reply
Steven - April 17, 2018

Jon,

Let me also express my gratitud to you for high quality videos and clear instructions and the fact that you allowed shared your work on PivotTables. The toturials have made my work easier and engender appetite to learn and adopt Excel not only for work even for my personal life.

For that Jon, if you plan a Safari to Botswana (Jewel of Africa), give me a shout then we take from there.

As they say the “Blessed is the hand that Giveth”

Best Regards,

Reply
Ismayil Əhmədov - March 11, 2018

Dear Jon, thank you very much for such a tremendous effort you’ve taken to put together these beautiful videos and neat files. Pivot table has always been a dark side of Excel for me and now with your gift I will be able to learn and apply it in my work. Thank you very much! God bless you! Ismayil.

Reply
Sujeeva Hubert - March 2, 2018

Thanks a lot for clear explanation of using pivot tables in Excel.

Reply
Brandy - February 25, 2018

I learned a great deal from your video, and I plan to utilize this info in my new position. I also plan to obtain the Pivot-Pal add-in. Thank you for the info and your time!

Reply
Jalal H Jafri - February 14, 2018

The best videos. Very focused, concise and precise. I am impressed!! I learned so much in an hour. Thank you.

Reply
M Jane - February 2, 2018

Hi Jon, Thank you so much! This is site is a tool for success!

Reply
V S VENKATRAMAN - January 29, 2018

Thanks Jon … You have ignited the interest for doing Pivot …. All the videos are excellent and easy to understand … I have downloaded the files for practice … Thanks again

Reply
Nieke S Copes - January 28, 2018

Hi Jon;

I am a lean six sigma black belt. I love your presentation because I can save a lot of time to do the analysis and the dashboard is the visual management that the upper level in the company want to look at. I have not using the PivotPal yet and I am exciting to do the practice. Can I have your data to practice? I would appreciate if you do.

Your presentation is very clear and easy to follow.

Thank you again.

Reply
Connie - January 23, 2018

Very good, clear, easy to follow, just what I needed to put some pizzazz on what would have been a very boring exercise. You made it seem just about a breeze and fun. Thank you.

Reply
oli - January 11, 2018

Good Evening,can i get the sample data for practice?

Reply
Bea - January 3, 2018

You are awesome!! Thank you for making this so easy to follow!!!

Reply
Zakir - November 30, 2017

Jon, You are a philanthropist in a real sense.
Your videos are really very helpful.

Regards

Zakir

Reply
Doina Danaiata - November 17, 2017

Thank you Jon!
You did such a good job. Your tutorials are excellent!
I am a teacher and I recommended my students to follow your videos about Pivot Tables.
Sharing is caring. Your work is very useful!

Reply
T - November 6, 2017

Awesome stuff! I feel like I can walk right in & do it all – right now! Confidence is on 1000 thanks to you! Thank you!!!

Reply
Dan - November 1, 2017

Jon –

I just watched this series as a refresher. I’d forgotten how well the information is conveyed – and how you cater to the neophyte. Thank you so much for making there series available. Just bought PivotPal. Lock and Load!

Reply
Ricky - October 24, 2017

Hi Jon,

Thank you so much for so many free but valuable info provided. I have learned lots from you and will learn more from you.

Much appreciated.
Ricky

Reply
Alvaro Celis - October 2, 2017

Dear Jon,

Great valuable information that you assembled together in easy logical explanation. The best part is free! I’m a beginner this is really helping me. Great hard work I really appreciate your time.

Thank you!
Alvaro

Reply
Nam - September 20, 2017

Dear Sir,

I have try do some same job with file by slicer but this button is not hightlight to permit click on it. Can you explain and help me fix it. Thanks

Reply
Gabriel - September 14, 2017

Hey Jon, thanks for the great work. Looking at yout source data, I discovered, that you have only one Product per Order. Like “Beer” 5x. That way you have only one line per order and a revenue for it. In my source I have multiple rows per one order, one row per product of an order. That way I do not have a revenue for the complete order.

However I would like to create a chart like yours looking at the grouped revenues: 1-500$…. Any Idea how to solve that?

Reply
Ahmed - September 3, 2017

A big thanks in advance for you, your videos are very useful next time please

Reply
Kingsley Adjei - August 31, 2017

You tutorials are very insightful. Thanks for sharing .

Reply
Kingsley Adjei - August 31, 2017

Very insightful. Thanks!

Reply
Attiq Rehman` - August 19, 2017

MAN.U R SIMPLY AWESOME!

Reply
Sang - August 12, 2017

Hi Jon,
Thanks for the great video and tutorials. I learned and applied to my work right away.
The slider is pretty awesome. But, it disappeared when I opened the sheet again. How to keep it on the sheet?

Thanks,
Sang

Reply
Mary Land - July 1, 2017

I learned some new tweaks with this tutorial. Thanks for Sharing.

Reply
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: