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.

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!

190 comments

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

  • Jon – very nice video series!

    The My Pivot Layouts feature in PivotPal looks great. I’ve always found setting layout options confusing (and a little annoying, since some combinations don’t even make sense) so I love the idea of defining my own layouts, then applying then to a new pivot table whenever I want. Very cool.

  • Thank you, Jon!
    Great video. Especially the grouping functionality is a very cool feature I wasn’t aware of. It’s so easy – now 😉

  • Hi Jon,
    thanks very much for your support. I do appreciate my excel skills has evolved rapidly.
    One thing, I don,t yet have Pivot Pal. Just wish to know if it is an add in?

    Thanks

    Hiltonel

  • Very helpful and clear. I am interested in knowing what it takes to creat tools like pivotpal? Your pivotpal is seems very useful.

    • Thanks Abhishek! I created the PivotPal add-in with VBA. These add-ins can be created in VBA without any additional tools. If you are interested in learning how to create add-ins, I teach this step-by-step in my VBA Pro Course. The course is not open for enrollment right now, but you can get started with my free VBA training series. Please let me know if you have any questions.
      Thanks again!

  • This is very helpful. Thanks a lot.
    You have my Email, please keep updating me whenever there is something new. Have a nice day!

  • This is so cool! It took me a while mainly because of the type of information that I wanted from my data- I still need to play with it to see exactly how to get what I need from my data but the videos are very easy to follow along and you explain everything very well. Thank you so much, Jon!!!

  • I am trying to figure out why I cannot connect a slicer to multiple charts/pivot tables on a dashboard. Wen I right click on the slicer border, it does not have a choice for Report connections. Rather it says, Pivot table connections. I am finding a number of sources that want to tell me how to do something, but very few that tell me what to do when something does not work as described. (I am using excel 2010.)

    • Hi David,
      Hopefully I can help. In Excel 2010 that option does say Pivot Table Connections instead of report connections. It leads to the same basic menu though. You can only connect a slicer to multiple pivot tables if they share the same pivot cache. This basically means they need to share the same data source. Are your pivot tables all from the same data source?

  • Really awesome Mr. Jon. These videos are helping me a lot and polishing my skills of data analysis. Especially slicer feature added an amazing part. I would like to appreciate your efforts and sharing such precious knowledge.

    Keep it up. Wish you all the very best.

    Thanks & Regards,

  • Hi John

    Thank you for your informative & powerful introduction to Pivot Tables. It was easy to follow (mind you I need the practice you recommend)

    Look forward to sketching out my data design & expected outcomes, then exploring PivotPal.
    Ciao
    Malcolm

  • Hi Jon (spell check didnt auto correct this time)

    I have a pivot table that has a list of all companies in the stock market from which I have extracted two data columns (measures of success)
    -Return on Assets
    -Return on Equity
    I wish to see only the companies that pass BOTH criteria of being over a certain number, and if a stock doesn’t pass BOTH of those individual numbers it is left off the pivot report listing
    I can now easily limit one data requirement but advancing and now need to filter out stocks in the pivot table with more than one filter.
    Appreciate whatever guidance you can give.
    Currently using Excel 2007 but happy to upgrade to Excel 2016 if the answer is in that software release.
    Ciao
    Malcolm

  • Hi Jon
    More on the stockmarket data pivot report extraction.
    Excel 2007
    When i group the numbers in the pivot table, and sort them, it sorts from the left hand side of the number
    example
    11128
    comes before
    9285
    and this with he lowest number forst.
    Is this a version issue or a limitation?
    Appreciate whatever insight you can give.
    Ciao
    Malcolm

  • Jon, thanks SO MUCH for this content! Beautifully explained and best part… its FREE! You’ve helped a desperate soul prepare for an interview with an analytics timed test, wish me luck!

    Thanks again.
    Jorge

  • Hi Jon! Thanks for sharing your knowledge in PIVOT. I am managing a warehouse and inventory control that’s i am looking forward to present my report in a very concise dashboard. That is why really want to learn how to create those items in PIVOT. Your clear tutorial videos help me to learn and have knowledge on how to do these things. But I cannot create on my own because i couldn’t see PIVOT upon opening my Excel. I am using 2013 version. How about the PIVOTCampus?

    • Hi Kenji,
      I am not exactly sure I understand your question. You should see the Pivot Table tools ribbon appear when you click a cell inside the pivot table.

      If you are looking for the XL Campus tab on the ribbon, that will appear when you install my PivotPal Add-in. You can learn more about PivotPal here.

  • John, excellent series of videos about working with Pivot Tables.
    I learn a lot with this information, and this will help me to build a Dashboard for my projects.

    Thanks,
    Carlos

  • Hi Jon,
    This is one of the best video on Pivot Tables so far i have ever come across in years. I learned a number of things on this topic, from the basics of Pivot table to all the way some of the complex things, including the Dashboard, and slicers. etc. It was simply superb presentation. PivotPal is even more exciting. Very very clear, concise, and accurate. You are simply amazing! Thanks for teaching this to us for free. You will be go far and will be benefited by the best wishes of all of us.

    • Hi Ravi,
      Thank you so much for your kind words. I really appreciate the great feedback, and it’s comments like this that keep me motivated to keep creating more videos. Thanks again and have a great day! 🙂

  • Jon – Excellent video tutorials. Captured details so well and made it look so easy – love it! Appreciate all your efforts in creating these to help the community.

    Thanks so much!!

      • Everyone studder and laughed at me as a newcomer in my office. Why, with less than one year in database training, I did learn from early grad school, the power of excel comes from how smartly I learn to use- the simplest of tools, and how those tools could be organized to answer questions based on my source data. The main lesson! But even as grad school newbie, intelligence requires excel tools and a dashboard.

        Everyone was quiet, and stoic, when Jon Acampora knowledge took me to center stage!! Thanks for the promotion. No one is laughing now! I am feeling better with my first 60+k salary coming toward my next paycheck.

        That is honest and big money when you from the ghetto. Trust me, if you are selling, I am buying!! Thank you!!!!!!!!!!!!!!!!!!!

        ps. This guy can teach old dogs, new tricks!!!

        • Hi Terry,
          CONGRATS on the promotion! That is awesome! You made my day. I am so excited to hear that your Excel and pivot table knowledge helped get you there.

          I think the willingness to learn new things, and take the risk to apply those skills, is what separates a lot of Excel users in the workplace. It’s great to see that you are doing this. To not only learn something new, but to also put yourself on center stage. These are the skills that will keep you in high demand in the future.

          I really appreciate your nice feedback and it’s great to have you here. Thanks again Terry! Keep me updated on your progress in the future. I’m stoked for you! 🙂

  • Thank you. Your videos are extremely helpful.
    I am, however still stuck on something: my raw data comes in with product names in rows and dates in columns + many other column headings that categorize the products further.

    Since dates are in columns, and product lists slightly changes from month to month, how do I combine the worksheets?

    Product Description Offer Jan 1/16 Jan 2/16 Jan 3/16
    1 Cars A 234 1234 568
    1 Cars B 564 4634 8987
    1 Cars C 789 534 697
    2 Planes B 5234 234 986
    2 Planes C 567 982 8634
    3 Boats D 634 8234 5685
    4 Bikes A 973 374 613
    4 Bikes B 4658 234 328

  • Very information. Love the videos. a couple questions… how can I create a table and not a chart so that I can copy and paste in my dashboard and and slicers. Second question – I am trying to create a pivot table with year, open, closed and the total $ of all by year but not by open or closed.. any suggestions?

  • Hi Jon,
    You are really a guru! For years I have been using excel and couldn’t get anyone to explain what the Pivot table was all about. But under 30minutes you have deconvoluted the entire stuff in such an easy to follow way, that Made it easy for me to apply immediately. The pivot pal is even cooler. Thank you Jon, you deserve your MVP status.

    • Hi DEI,
      Thanks for the nice comment. I really appreciate your support, and I’m so happy to hear that you are learning pivot tables. They are one awesome feature of Excel. Thanks again! 🙂

  • Jon,
    Awesome awesome course!
    Our company has 12 companies bidding for 13 job positions in 29 countries, and with your help, I was able to build a slicing/dicing tools so the managers can choose which companies to work in which countries. I have a few questions, for example, how to purge unneeded fields from slicers and how to sort pivot table charts. Thank you so much for sharing your superb knowledge.

    • Thanks Jewel! I’m so excited to hear that you are building dashboards with this training. In regards to the unneeded fields, you can hide the deleted items.
      Right-click the slicer and choose Slicer Settings… Uncheck the box that says: Show items deleted from data source.

      I hope that helps. Thanks again!

  • I like the tutorials!
    I copy two charts from two different sheets into a dashboard and try to insert a slicer. The slicer connects to the chart
    I selected but when I try to connect to the other chart, I get only one chart title in the Report Connections box.

    • Hi Jim,
      Both pivot tables/charts need to share the same source data range. They actually need to share the same pivot cache, which can be different from the source data range. But in most cases both pivot tables need to have the same source data range. I hope that helps.

      Thanks!

  • I have pivottable1 and pivottable5 in the same workbook. Don’t know why.
    When I try to connect a slicer, only the chart I select shows up in the Report Connection box. What did I do wrong?

  • Hi Jon

    Thank you for your wonderful lectures on pivot tables. I hope to use Pivotpal and learn how to use it.

    But I want to know how you are able to demo and narrate without making a mistake. It is edited – the speech?.

    • Thanks for the nice comment Cecil! Most of my videos are NOT scripted. I sometimes edit out small mistakes, or do a few takes to get it right. I do plan and outline the major points I want to cover first. A lot of it comes with practice. I have probably produced over 400 videos for Youtube and my courses now. When I first started I was terrible at it. But it got easier with practice, lots of practice! 🙂 I hope that helps. Thanks again and have a good one!

  • Hi Jon,
    It was amezing videos and I would like to thanks personally for all the help. I am new to the excel world and your videos so easy to understand and work on it.
    Look forwared for more videos and I want to be a part of your campus.
    Thanks again.

    Mohammed

  • Jon I just finish see your video 3 on pivot table. Wow You Know I have data just the same way it goes for a whole year. Now that I can change the month and year it will save me time. I use hade to do month by month and copy and paste the same month for the whole year which took a long time. Being able to minimize the year is going to be a great help. Currently I have PivotPal. How do I obtain Pivot Layouts? Lastly can these video be download?
    You are a life safer. Thank you again
    Elba

    • Hi Elba,
      I’m happy to see you learning so much about Excel, and saving a lot of time in the process. I also recommend using Excel tables for your source data to save even more time. I will have an article about that in the future. You should see the Pivot Layouts button the XL Campus tab of the ribbon if you have PivotPal installed.
      Thanks again Elba! 🙂

  • Jon Just to let you know I have the pivot layouts. I am so glad I listen to these video. My data files are at least 65000 records for each year. Grouping the year and the cost save me so much time. Wow thank you Again!

  • Hi sir,

    Thanks for this demonstration, it was really useful for me. But I’ve a question about data base. In your exemple, you used a data base of december 2014. But if i want to do it each month or each week. I need to remake everything or you’ve means to do it automatically ? Because the only way that I find it was to copy and paste the new data base, but the problem was that the new one doesn’t have the same numbers of lines…

    Sorry for my english, and thanks a lot 🙂

    • Hi Djilali,

      You do NOT have to remake everything after adding new data to the data source. You just have to make sure the source data of the pivot table(s) includes the new data, then Refresh the pivot table.

      If you use an Excel table for the source data, then you do not have to change/update the source data range when new data is added. Just refresh the pivot table. Here is a video on Excel Tables that might help. Thanks!

  • Dear John,

    Thanks for the educative tutorial that was rely helpful and saved a lot of my time. I am looking into improving my excel and my data analytical in generals. Any suggestions for courses or books. By the way i like your clarity and in depth content you have covered for such a short time. Keep up the good work.

    thanks
    Mahamed.

  • Thanks you Jon!!!
    Great videos and wonderfull explanations!
    I am learning a lot of details and developping great skill in Excel 2013, because of your lessons!!

  • Your videos helped me a great deal; I was preparing for an interview that focused on creating pivot tables. Your videos are clear, concise, and at the same time detailed.

  • Thanks Jon!
    Your tutorial is really awesome.
    I just wanted to ask you, I am in working HR field.
    Which all skills will be good to learn to improve my position?

    • Hi Iftekhar,
      I think pivot tables are a great skill to learn for the HR field. You will typically be working with a lot of data sets from different sources. This means you have to match and combine data, and do a lot of lookups. You will also need to know data cleansing and preparation. I have a free video series on the lookup formulas that teaches a lot of these skills. I hope that helps.

  • Hi Jon,

    This Pivot table, chart & Dashboard video was useful. Thanks a lot for sharing the excel file as well. This really helps me when I train people.

    I thought of learning advanced excel like Macros, VBA, etc., and was wondering where I can learn with best examples. I hope I have landed a right place finally. Looking forward for more videos and exercises like this. 🙂

    Thanks once again.

  • Appreciate Jon put all three videos for the Pivot table skills. I followed all of them and learned a ton! Just one questions, when creating the pivot bar chart, for example sales by region, I can’t get the region in the Y axis and revenue on the Y axis with region in the rows and sum of revenue in the values. When I tried to switch the Row/Column, the data layout changes with switch but chart doesn’t. Is there anything I did wrong and how to fix it?

    • Thanks Bing. I appreciate your support. Great question! In this case we will need to change the chart type to a Bar chart. It is currently a column chart.

      1. Select the pivot chart.
      2. Select the Design tab in the Ribbon.
      3. Click the “Change Chart Type” button.
      4. Choose Bar on the left side.
      5. Click OK.
      6. The chart will change to a Bar chart with the Regions in Y Axis. It might be difficult to see the region labels. If so, select the plot area of the chart. There are a few ways to do this. One easy way is to go to the Format tab in the ribbon and then select Plot Area from the drop-down on the left side. You will see a box around all the bars and y-axis. You can then resize the plot area with the mouse to make the plot area smaller and the axis labels bigger. I hope that helps.

  • Really, really great, excellent, perfect, detailed, wide-knowledge job, Jon.

    Thanks a lot, Jon! I stay with you…

  • Jon, thank you so much for taking the time to prepare and explain all features of Pivot Table, extremely helpful.

  • Hi Jon,

    Really like your presentations.

    I will be buying your VLOOKUP, Macro and Pivot table education videos soon!

    Take care,
    Ron

    • Hi Ron,

      Thank you for the nice feedback. My apologies for not responding sooner. I am behind on blog comments due to the large volume I get every day.

      I look forward to you joining the courses, and helping you learn more about Excel & VBA. Please let me know if you have any questions.

  • I finished the series and I have to thank you for this third video especially, I don’t know if my boss will get as interactive and fancy but I sure enjoyed this video! Im still surviving my first internship job haha

  • Thanks Jon,

    This has been a very informative session. I’ve overlooked pivot tables for too long, so, this has made that step a lot easier.
    Well done.

    Dave

  • Jon – Excellent work. Very clear, comprehensive instruction with good examples. Pls consider creating a workbook with exercises to enable your audience to confirm their understanding and maintain these skills

  • Thanks Jon,

    The my pivot layout feature & also pivotpal looking superb,
    onces again thank you very much for sharing Excel files.
    keep healthy & god bless you 🙂

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter