Intro to Pivot Tables and Dashboards Video Series 2 of 3
74

Introduction to Pivot Tables and Dashboards [Video 2 of 3]

Bottom line: Learn how to use the different calculation types to quickly answer questions about your data.  I also explain how to update your pivot table with more data.

Skill level: Beginner

Answer Questions with Pivot Table Calculation Types in Excel

Video #2 – Investigating Your Data with Pivot Tables

Downloads

Download the Excel file to follow along with the video.

Intro To Pivot Tables Part 2.xlsx (101.5 KB)

The following file contains the additional data that I add to the pivot table source data in the video.

Sales Data For January 2015.xlsx (13.6 KB)

Pivot Tables Answer Questions About Your Data

This video is all about learning how to answer questions about your data.  As we learned in the first video, Pivot Tables allow you to quickly summarize your data.  Since we don’t have to spend time doing this manually, we can focus more of our efforts on discovering trends and analyzing our data.

Here are some of the questions we answer in this video:

  • “What are the top 10 product categories?”
  • “What is the average unit price for each category?”
  • “How many orders did we have for each category?”
  • “Who are the sales reps selling in each category?”
  • “Which categories make up over 50% of our total revenue?”

We can answer all these questions in a matter of seconds with a Pivot Table.

I also explain how to investigate your source data.  Our Pivot Table contains a (blank) row, and I show you why this is happening and how to fix it.

Investigate the Source Data of Your Pivot Table

Update Pivot Tables with More Data

When you receive new data periodically (week, month, year) you will want to update your pivot table to include the new data.  To do this you have to Change the Data Source range of the Pivot Table to include the new data.

Update Pivot Table with New Data in Excel

I explain how to do this in the video and show you a few shortcuts to make this process faster.  I also explain how to make changes to your source data and refresh your Pivot Table to display the updates.

Keyboard Shortcuts Used in the Video

Here is a list of all the keyboard shortcuts I used throughout the video.  I love keyboard shortcuts! 🙂

  • Ctrl+Drag Right with Mouse – Copy/Duplicate a Worksheet
  • Alt+; (semicolon)Select Visible Cells
    • I just added a new feature to Paste Buddy that allows you to paste to a range with hidden cells.
  • Ctrl+Enter – Fill Values/Formula to Selected Cells
  • Alt+F5 – Refresh Pivot Table
  • Ctrl+Shift+End – Select Cells to Last Cell in Data Range
  • Ctrl+Down Arrow – Go To Last Cell in Column
  • Ctrl+A – Select All Cells in Data Range
  • Alt+A+C – Clear All Filters

Filter Pivot Table Source Data with PivotPal

In the video I show a few features of PivotPal that make it easier to work with your source data.

PivotPal has a Filter Source button that allows you to quickly filter the source data sheet.  It filters the source data for the details of the selected cell in the values area of the pivot table.

I also show how you can build pivot tables while on the source data sheet.  This saves a lot of time from flipping back and forth between your source data and pivot table sheets.

Build Pivot Table from Source Data Sheet with PivotPal

PivotPal is now available!

Click here to learn more about PivotPal!

Additional Resources

What Do You Think?

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 74 comments
Ernie - July 16, 2017

Excellent presentation, very informative.
Ernie

Reply
Camylle - June 11, 2017

Really great information, well presented … makes you want to go to the next level. Maybe slow down the pace just a little bit to leave time for people to actually apply the functions. Thank you. Until soon.

Reply
Jeff Estes - May 24, 2017

Great tutorial. Thanks.
Question: In my version of the spreadsheet, everything seems to match and thanks to the tutorial I am able to perform the various steps explained.

However, for some reason, the Blanks, despite appearing in and being selected in the various filters for Source Data and Pivots does not show up in the Pivot Tables.
What am I doing wrong?

Reply
Adel - April 15, 2017

Thanks for The Second Video.

Appreciated.

Reply
Princewill Banigo - March 29, 2017

Thanks for the first video (#1). I really enjoyed it that I earnestly look forward for the second video. knowledge packed tutorial. thanks again Mr. Jon.

Reply
tom dachel - March 28, 2017

Hi John!!
You have got me hooked on all things that pivot or even rhyme with pivot! Seriously – I am at 36 hrs w/o sleep working on a Dashboard to post on SharePoint. Thanks to you – I have the Dashboard stuff more than covered, H O W E V E R… How would I invoke a process or whatever that would grant me a text area for sharing with managers the steps involved as well as the holdups that we encounter with some of our internal processes?

I tell many of your mentoring/teaching skills. The word is getting out.

Thanks, Jon!

Reply
    Jon Acampora - March 29, 2017

    Hi Tom,
    Thank you for the nice feedback! I’m really happy to hear you are learning and enjoying pivots. 🙂

    I’m not sure I understand your question. Are you wanting to add a text box to the sheet with instructions? If so, you can do that from the insert menu by inserting a text box shape. I actually explain that in my latest videos on the Doughnut Progress Chart. This is another chart you might want to use in your dashboards in the future. I hope that helps. Let me know if I did not understand correctly. Thanks again for your support! 🙂

    Reply
Chris Kirkegard - March 17, 2017

Great tutorial. I have recommended your tutorials to others. Thank you !

Reply
KATHY SMIT - March 14, 2017

Great tips and step-by-step instructions. Thanks so much! Need Video #3 for my Business Analytics class asap.
Really looking forward to learning more…you make it look easy!

Reply
Elizabeth - March 14, 2017

Thansk so much, Jon, for these turiorials.

They have been quite helpful.

Thanks again.

Reply
joaquina - March 4, 2017

It’s great! Thanks for this video learning tips it is very helpful. Keep on sharing with us Sir, Have a nice day!

Reply
Nancy - February 1, 2017

Jon, your video is so helpful! I have bookmarked it so that I can refer to it quickly. You are making it so easy to learn how to use this function. Also, PivotPal looks like a great tool!!

Reply
Lucy - January 24, 2017

Hi Jon, Thank you so much for these wonderful videos! They’re so clear and helpful. One follow-up question for you: I wanted to start from scratch as practice so I copied and pasted just the values of the “data” tab into a new workbook, and then I created a pivot table from this data successfully. However, I was not able to get the drop down options at the top of each column on the “data” tab in my new workbook. Would you be able to tell me how to get these drop downs to appear? Thank you so much!!

Reply
Simon - January 24, 2017

Very much enjoying your video’s and the data downloads which go along with your tutorials.

Great job, fella.

Reply
Phil - January 13, 2017

Hi Jon,

Thanks very much for these 3 starter lessons, they have nicely and clearly de-mystified pivot tables for me.

Phil

Reply
Rashmi - January 10, 2017

Hi Jon,

Thanks a ton, the video was a great help, do you have more videos in learning formulas and data validation and tools i can use in MIS.

Regards
Rashmi

Reply
Matt - November 15, 2016

Hi Jon,

Thanks for the tutorials – very helpful!! I do have one query that I hope you can help with please…

I can copy across the pivot table using your method without any problems (i.e. click + cntrl c, drag and let go). The copy of the work sheet is created OK. However, whenever I change the data in the pivot table, it doesn’t change it in the pivot chart. So for example, if I make a copy of the ‘Sales by Revenue’ worksheet, and then want to change the value in the ‘Row Labels’ section from Region to Order Date, the Pivot Table updates itself with the Order Dates, however the Pivot Chart still has the Region data in it.

I’ve tried refreshing but that doesn’t work. Am I missing something?? It’s probably something small, but can’t see where I’m going wrong!

Thanks,
Matt

Reply
    Jon Acampora - November 21, 2016

    Hi Matt,

    I’m not sure what would be causing that. What version of Excel are you using?

    Reply
      Matt - November 22, 2016

      Hi Jon,

      I’m using Excel 2007. I had an Analysis test at an interview the other day which focused a lot on Pivot tables (hence the reason why I was watching your tutorials!) – I tried the same thing on their computer as well and again, the same thing happened. I also believe this was on Excel 2007. So not sure if this shortcut only applies to Excel 2010 + ??

      Thanks,
      Matt

      Reply
Bennett Khoza - November 4, 2016

thanks… is well appreciated

Reply
Geoffrey Ogutu - October 29, 2016

Good tutorials, thanks. It got me exploring with pivot tables. What version of excel(office) do you use in the turorials?

Reply
Hasib - October 24, 2016

Jon Sir,

Thanks a lot.

Reply
mohamed salah - October 23, 2016

thanks alot

Reply
Jean Baptiste Bizimungu - October 14, 2016

Thank you so Much for this wonderful video. God Bless you

Reply
stella - August 24, 2016

Thank you so much for making it very easy

Reply
Pat - July 5, 2016

Thanks for another easy to understand & follow video, Jon!

Reply
Ahmed Babalola - June 3, 2016

Hi Jon,

Thanks for the great work. The videos are on point. self learning. This is what i call my Learning Pal. Just like the PivotPal

Thanks a bunch

Reply
Awuor PONGE - May 21, 2016

Thank you Jon for these wonderful videos. I just love how Pivot Pal is simplifying the work. I’m as mesmerised as Andy is and enjoying the videos even more. Keep it up!

Reply
    Jon Acampora - May 22, 2016

    Thanks for your support Awuor! I am happy to hear you are enjoying the videos and having fun too. Pivot tables are awesome! 🙂

    Reply
Nancy - May 11, 2016

Thanks very much for creating these videos. This was a great intro to pivot tables. The step-by-step instructions and demonstrations were clear and I learned a lot about this powerful functionality.

Reply
Mike - May 6, 2016

Outstanding…

Reply
Nirali - April 27, 2016

Hey Jon,

Thank-you so very much. I am new to excel world but am learning and enjoying your videos. So far I have completed 2 videos of pivot chart and I cant believe I created many files following you . I cant believe it. Thanks to you

Reply
    Jon Acampora - April 27, 2016

    That is awesome Nirali! I’m so excited to hear that you are creating pivot tables. They are addicting… 😉

    Thanks for the great feedback and have a good one!

    Reply
Prabh singh - March 23, 2016

Hello sir

I Like your videos very much and they are very helpful. I m using Excel 2007 and after copying “Sales By rep” while i m trying to change salesperson to category, data is changing in PT but in Pivot Chart there is no change. Pivot table is still linked to Old sheet (Sales by Rep). Is this function support 2007 version?

Reply
Trixie - January 23, 2016

Very helpful! Great tutorial!

Reply
kongs. - December 16, 2015

Nice, it”s very good for me to learn more and get the experience!
thank you for the knowledge

Reply
Khaled - December 16, 2015

I like your way to present, also you make bivotbmore easier

Reply
SMT - December 12, 2015

Hi Jon,

I have just watched your 2nd videos. Its really interesting and very useful specially using Pivotpal.

Reply
Marilyn - November 2, 2015

Hi. Now I figured out how to see all of December in the category but where did you store January? I don’t see it on my sheet! Sorry.

Reply
Echo - October 31, 2015

I saw other video tutorials about pivot table, your video tutorial is the best! Thank you for those free stuff!

Reply
Jaafar - September 11, 2015

Thanks a lot for everything.
Amazing!

Reply
Long Nguyen - August 5, 2015

Great tutorial. Thanks so much Jon.

Reply
Gero - July 22, 2015

Hi Mate. Thanks for this tutorial. But I’m experience a little problem. When I make a duplicate of the main source or from your tutorial the “Sales by Rep” sheet, the chart from the duplicate (Sales by Rep (2)) is still link in the main source (Sales by Rep). How can I make the data of the chart in the copied sheet be linked in the data on the copied sheet? In short, how can I make a copy of the same sheet without any link from the original? Thank in advance.

By the way, the video or tutorial is great.

Reply
Igor - July 10, 2015

Great video tutorial. Thank you

Reply
David - June 30, 2015

Outstandng tutorials Jon thank you so much for providing them. A wealth of knowledge to learn for sure but simple enough for beginers to learn. I’ll have to watch the videos over and over to eventually learn plus I downloaded the worksheet so I can practice. Thanks again.

Reply
Bob - April 1, 2015

Great tutorial Jon. But I am experiencing a very strange phenomenon. I am using my own set of data to make a similar dashboard for practice. When I create my first PT in a new tab, everything goes according to plan. The PT is created and I am able to easily create a PivotChart to illustrate my data (in preparation for creating a dashboard)

As in the video, rather than start a new PT I copied the 1st tab (just created) for modification that will lead to a second pivotchart. But as soon as I make ANY modification to my new (2nd) tab, the 1st tab changes as well.

I tried creating a new tab from the raw data tab, and have the same phenomenon. Changes to any new, or copied, tab with a pivot table changes the pivot table and pivot chart in the previous tab.

I have created several dashboards but this is the first time I have encountered such a strange phenomenon. Can you shed some light on this issue?

Thanks. And thanks for these nice tutorials.

Reply
    Jon Acampora - April 9, 2015

    Hi Bob,
    My apologies for not getting back to you sooner! I didn’t see your comment come in. Anyways, that is weird behavior. What version of Excel are you using? If possible, can you send me the file? jon@excelcampus.com.

    Thanks!

    Reply
rishabhsharma - February 26, 2015

Greetings from India Sir 🙂 I have seen so many Pivot Table videos but you are simply awesome.
-The way of your teaching is nice and different.
-I have never heard the keyboard Shortcuts that you have used in this video, i really like them.
-Do you have a separate video for some awesome Keyboard Shortcuts like these?
– I am looking forward to watch all videos.
-Thank You for your help.

Reply
    Jon Acampora - March 1, 2015

    Hi Rishabhsharma,
    Thank you for your comment. I really appreciate that and I am happy to hear you are enjoying the videos.

    I have another video series that contains a lot of shortcuts for working with the worksheets in your workbooks. This video series is for my Tab Hound add-in, but I also explain a lot of useful shortcuts that are built into Excel.

    I also have a six part video series on how to navigate Excel with the keyboard. This is a bonus with the purchase of Tab Hound.

    And I have a ton of articles on keyboard shortcuts. It’s one of my favorite topics. 🙂

    https://www.excelcampus.com/keyboard-shortcuts/

    Please let me know if you have any questions. Thanks again!

    Reply
Don - February 24, 2015

Great video

Reply
Dan - February 24, 2015

These videos are very concise and easy to follow. Thank you for developing them.

Reply
YW - February 24, 2015

Hi Jon, Thanks for providing such wonderful tutorials. God Sent!! I got stuck on Pivot Table #2 video the first segment, ‘Top 10 Categories chart’. I use Mac Excel 2011, somehow the chart does not update with the pivot table, it remains the same as that for ‘sales by Rep’ and the data source stays with the original pivot table not the new one. Does Mac version lack the capacity to auto update a chart when the pivot table changes? I tried for a long time to no avail.

Reply
    Jon Acampora - February 24, 2015

    That’s a great question. Unfortunately pivot charts are not available in Excel 2011 for Mac. You can use a regular chart and choose the pivot table as the source data range. The only issue with this is that if the pivot table changes size (more rows/columns) the chart will not include those new rows/columns. However, you could use a dynamic named range as a workaround for this. I hope that helps. Please let me know if you have any questions. And thank you for the wonderful comment!

    Reply
Sigmund Puchacz - February 20, 2015

Will PivotPal support LibreOffice?

Reply
    Jon Acampora - February 24, 2015

    Hi Sigmund,
    No, I don’t believe so. I have not used LibreOffice yet so I’m not really sure. Does it support VBA? Thanks

    Reply
Rob Vermelis - February 13, 2015

Thanks for these very usefull tips. Still haven’t found the solution for my pivot problem, but already looking forward to the next chapter. 🙂

Reply
    Jon Acampora - February 17, 2015

    Hi Rob, What is your pivot problem? I’d love to help. Thanks for the comment! I’m glad to hear you are enjoying the videos. 🙂

    Reply
pam - February 10, 2015

enjoyed this one….good short cut tips
looking forward to #3 and slicers
thanks!

Reply

Leave a Reply: