84

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

Video #2 – Investigating Your Data with Pivot Tables

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)

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.

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.

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.

PivotPal is now available!

What Do You Think?

Jon Acampora

Don - September 22, 2017

Ron,

Thank you for these informative videos. I refer people to them often and several of my associates have purchased PowerPivot.

Common requests I hear from some of our more advanced users:

We get data from several sources (say 2 as an example, updated regularly). Each of these is in its own worksheet.

There are a number of additional columns which get populated on this data, some through data profiling, cleansing, validation, and lookup results elsewhere. Let’s call it “additional attribution” from several sources, including users.

These 2 worksheets, each with their “additional attribution”, are combined, and additional attribution is added to their combined results (user inputs). This final combination powers sales planning, ops, and is reflected in several dashboards.

Do you have a more involved tutorial(s), free or paid, which could address this? The design aspects of this are important, especially for the regular updates. We have healthy debates about whether Pivot Tables are appropriate in this situation, with the punch line being “they get us 90% of the way there” and then we have to do something different. Spreadsheets are 150+ worksheets, 30MB. Lots of macros and VBA, much of it legacy and incrementally modified.

Please point me to materials that you provide or services that you offer.

Jean - July 29, 2017

Hello,

Thank you for your videos, sincerely.
I just wanted to ask this small question, why i can not see all the data when i download it. I just see december?

Thank you!

Terrie Marie Ferguson - August 19, 2017

hello Jean, There are actually 4 files
Intro to Pivot Tables (parts 1 – 3)
and Sales-Data-for-January-2015

Part 2 website has 2 files.

Hope this helps!
Terrie Marie

Ernie - July 16, 2017

Excellent presentation, very informative.
Ernie

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.

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?

Thanks for The Second Video.

Appreciated.

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.

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!

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

Chris Kirkegard - March 17, 2017

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

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!

Elizabeth - March 14, 2017

Thansk so much, Jon, for these turiorials.

Thanks again.

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!

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

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

Simon - January 24, 2017

Great job, fella.

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

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

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

Jon Acampora - November 21, 2016

Hi Matt,

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

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

Bennett Khoza - November 4, 2016

thanks… is well appreciated

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?

Hasib - October 24, 2016

Jon Sir,

Thanks a lot.

mohamed salah - October 23, 2016

thanks alot

Jean Baptiste Bizimungu - October 14, 2016

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

stella - August 24, 2016

Thank you so much for making it very easy

Pat - July 5, 2016

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

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

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!

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

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.

Mike - May 6, 2016

Outstanding…

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

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!

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?

Trixie - January 23, 2016

kongs. - December 16, 2015

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

Khaled - December 16, 2015

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

SMT - December 12, 2015

Hi Jon,

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

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.

Echo - October 31, 2015

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

Jaafar - September 11, 2015

Thanks a lot for everything.
Amazing!

Long Nguyen - August 5, 2015

Great tutorial. Thanks so much Jon.

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.

Igor - July 10, 2015

Great video tutorial. Thank you

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.

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.

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!

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.

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!

Don - February 24, 2015

Great video

Dan - February 24, 2015

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

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.

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!

Sigmund Puchacz - February 20, 2015

Will PivotPal support LibreOffice?

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

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

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