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

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 125 comments
Vishal Nikale - February 6, 2017

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 🙂

Reply
Lynda - February 1, 2017

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

Reply
    Jon Acampora - February 4, 2017

    Thank you Lynda! I appreciate the feedback. I’m working on a course for pivot tables that will have more practice exercises. Thanks for the suggestion! 🙂

    Reply
dave - January 31, 2017

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

Reply
Julian Riano - January 25, 2017

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

Reply
    Jon Acampora - February 4, 2017

    Thank you Julian! Congrats on finishing your job. I’m sure you will be able to use these skills in your future career. 🙂

    Reply
Ron - January 25, 2017

Hi Jon,

Really like your presentations.

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

Take care,
Ron

Reply
    Jon Acampora - February 4, 2017

    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.

    Reply
Rosa - January 2, 2017

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

Reply
Rudi - January 2, 2017

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

Thanks a lot, Jon! I stay with you…

Reply
Bing - December 27, 2016

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?

Reply
    Jon Acampora - December 27, 2016

    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.

    Reply
edy sentoso - November 28, 2016

Great Videos, very helpfull. Thanks alot. I’am waiting the next videos.

Reply
Gayathri - November 22, 2016

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.

Reply
Iftekhar Hussain - November 20, 2016

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?

Reply
    Jon Acampora - December 1, 2016

    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.

    Reply
Monna - November 1, 2016

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.

Reply
Miguel Angel Gregorio - October 21, 2016

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

Reply
Mahamed Hussein - October 9, 2016

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.

Reply
Janelle - October 8, 2016

It was a very good video tutorial, great help for a beginner like me 🙂

Reply
Djilali - October 2, 2016

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 🙂

Reply
    Jon Acampora - October 4, 2016

    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!

    Reply
Elba - September 30, 2016

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!

Reply
    Jon Acampora - October 4, 2016

    Ok great! I read this after I responded to your last comment, so you can disregard. I’m excited to hear about all the time you are saving. Awesome! 🙂

    Reply
Elba - September 30, 2016

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

Reply
    Jon Acampora - October 4, 2016

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

    Reply
Sohom - September 14, 2016

Best Excel tutorial I have seen so far!
Thank you Sir.

Reply
Mohammed - September 4, 2016

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

Reply
Cecil - August 31, 2016

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?.

Reply
    Jon Acampora - September 7, 2016

    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!

    Reply
Jim - August 29, 2016

Please disregard my questions from 8/27. I found my mistake.

Reply
Jim - August 26, 2016

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?

Reply
Jim - August 26, 2016

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.

Reply
    Jon Acampora - August 30, 2016

    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!

    Reply
Tobie - August 22, 2016

What a great tutorial! Easy to follow and easy to understand. Thank you!

Reply
Jewel - August 17, 2016

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.

Reply
    Jon Acampora - August 20, 2016

    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!

    Reply
DEI - July 27, 2016

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.

Reply
    Jon Acampora - July 31, 2016

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

    Reply
margo - June 26, 2016

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?

Reply
G0sia - May 30, 2016

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

Reply
Nandu - April 30, 2016

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

Reply
    Jon Acampora - May 9, 2016

    Awesome! I’m happy to hear it helped. Thanks Nandu! 🙂

    Reply
      Terry - May 15, 2016

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

      Reply
        Jon Acampora - May 17, 2016

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

        Reply
Ravi Sutrave - April 10, 2016

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.

Reply
    Jon Acampora - April 11, 2016

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

    Reply
Carlos Carle - March 28, 2016

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

Reply
bakht - March 17, 2016

Thanks jon. I learned a lot from your videos; well presented and explained.

Reply
Kenji - March 10, 2016

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?

Reply
    Jon Acampora - March 16, 2016

    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.

    Reply
Jorge Echeverria - February 20, 2016

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

Reply
    Jon Acampora - February 23, 2016

    Thanks Jorge! Best of luck with the interview. I hope you knock em dead with your new pivot table skills! 🙂

    Reply
Malcolm Gay (Australia) - February 17, 2016

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

Reply
    Jon Acampora - February 18, 2016

    Hi Malcolm,
    It might be that your numbers are stored as text. If so, you will need to convert them to numbers for the sorting. Let me know if that helps.

    Reply
Malcolm Gay (Australia) - February 17, 2016

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

Reply
Malcolm Gay (Australia) - February 17, 2016

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

Reply
Asif - January 14, 2016

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,

Reply
Mush Haruna - December 18, 2015

Useful exercises. Am still working on it.

Reply
David Adams - October 23, 2015

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.)

Reply
    Jon Acampora - October 26, 2015

    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?

    Reply
Lynda Saul - October 11, 2015

This was a big help. Thank you

Reply
Marilyn - October 4, 2015

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

Reply
    Jon Acampora - October 7, 2015

    Thanks Marilyn! I’m happy to hear you were able to create it, and I really appreciate the positive feedback.

    Thanks again and have a great day! 🙂

    Reply
REEM - September 5, 2015

very useful thank you …

Reply
Slimane - August 27, 2015

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

Reply
ABHISHEK - August 9, 2015

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

Reply
    Jon Acampora - August 9, 2015

    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!

    Reply
Mohamed LABIOD - July 24, 2015

Great work, thank you very much.

Reply
Hiltonel - February 23, 2015

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

Reply
    Jon Acampora - February 24, 2015

    Hi Hiltonel,
    Thank you! I am happy to hear that the videos helped you.
    Yes, PivotPal is an add-in for Excel and I just released it today! You can learn more about it on the PivotPal product page. Please let me know if you have any questions.

    Reply
Christian - February 19, 2015

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

Reply
Dave Bruns - February 18, 2015

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.

Reply
    Jon Acampora - February 19, 2015

    Thanks Dave! I wish I would have developed the layouts feature sooner because it has come in real handy the last few days. Looking forward to sharing it soon…

    Reply

Leave a Reply: