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

Download the Excel file to follow along with the video.

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

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?

• Dave says:

Thank you very much, Jon, for these series of recordings.
Whenever I watch the first five minutes of each recording and I say to my self this answered all my requirements for a segment, you show more efficient way and better to do it (The pivot Pal is amazing though I have never heard of it before).
I particularly liked how you put the Sales Representatives across the column and product under each sales rep.
Here is a question I have. On Video 2, before today, whenever I attempted to put two columns (say, Revenue and percentage in the calculation types tab) side by side, my excel puts them to gather in one column – column B? Would you know what I was doing wrong? I’m using excel 2013

Dave

• Sumit Kedia says:

Thank you so much for your wonderful support.

• Minnie says:

Awesome video on Excel. For some reason, when I tried sorting the Pivot table in descending order by Revenue, the bar chart sorts the opposite (where the largest number is at the bottom). I’m using Office 365.I checked the options and it says sort in descending order by revenue but the chart shows the largest revenue at the bottom which is the opposite of what I want it to do. Any ideas on how to fix this would be much appreciated.

• Lu says:

hello,
I am using a pivot table to summarize data but I am also using a vlook up. when I create the pivot table, than do the vlook to look up certain items, the vlook up doesn’t work, even thou that item is summarize on the pivot table?

• Gabriela says:

Thank you for your wonderful explanations, they are very useful and the most important is that they are making the subject look easy to learn.

G

• Enrico says:

I wonder why I haven’t used Pivot Tables before 😉

Thank you, Jon

• Marites says:

Thanks. It helps me a lot in doing my office work easier.

• Amal Al-Slaimi says:

Jon Acampora you are awesome!
Million thanks are not enough.
May God bless you and brighten your life with faith.

• elena su says:

The best tutorials I’ve ever seen Thank you very much for your job!

• Rulber says:

Awesome tutorial. Loved the data source files. They allow you to follow along and practice as many times as needed. THANK YOU, Jon.

• Donna says:

This is awesome. Thanks so much for sharing. I am new to working with pivot tables and the information you provide is most helpful.

• Sajini says:

Hello Jon,
Thank you very much for the detailed explanation in all your videos.
I am new to Pivot tables and Dashboards and all I want to do is practice to
become efficient and fast.

My question is about the source of the data.
Is it better to have many small data tables or one raw data table with all the information?
All the shortcuts you give are only for Windows, could you add the Mac equivalent please?

Thank you

Sajini

• Nancy S Bonanno says:

I wish you had touched on how to use the FILTERS portion of pivot tables, but good video otherwise.

• Lemma YILMA says:

Hi Jon,

Thank you so much for this latest excellent Tutorial on Pivot Table. I particularly like the Tutorial about investigating blank data and adding data with Pivot Table. The Pivot Pal’s Filter Source button, as you have explained this is a quick way to filter the source data sheet. I have no question for now.

Thanks.

• shivarudra says:

Hi Sir,

I Need Samples for Formulas and Dashboards

Regards
D S Shivarudra

• Christina says:

Great stuff !

• Hakar says:

great and interesting……

• Nigel Forge says:

Thanks Jon, as always it is great to learn new processes from your video presentations, which are always clear and easy to follow.

• Maslah Abdi says:

Really this is an amazing and interesting tutors. in opposite that i did not find Pivot pal button in my sheet so is there problem the Microsoft office version am using or other route exists please help me that.

Over all from the part one to now i learned a lot thank you Jon.

• James Parson says:

I have chosen your videos to learn about pivot tables and pivot charts.

Thank you.

I wish someone would diagram the major menu options for pivot tables.

• Thank you so much Jon for your wonderful videos ,I was able to learn a lot .

• J says:

i think one thing that would boost your views is to provide the shortcuts for Mac users as well!

• Ruth Caroline STORMS says:

Great, Thank you.

• Mahavir says:

Hi Jon,

I need some help in excel. I am trying to come up with an optimized rate of production of material that is constrained by number of resources for 100 different customers but I have a very tight deadline to meet. Some products have flexibility in terms of delivery however some have no slack.

I have researched your YouTube videos and found them to be of immense wealth of knowledge however I am unable to come up with a formula based excel report that would allow me to view the results.

Please respond back if you are available to help.

Thanks and have a great day.

Mahavir

• I Jordan says:

How do I limit the number of dates on a slicer. My pivot charts has a large amount of data including multiple months and days. I only want to select 10 specific days to show on my slicer. How do I achieve that?

• Ann M DellaPorta says:

I am loving these videos. You are a talented, easy to follow instructor. Many thanks!

• Ali sheikh says:

Thanks that was so valuable information

• Dawn Garofalo says:

Do we have to purchase Pivotpal?

Hi Jon, thanks for the kind introduction of Pivot Table. I’m a newbie to Pivot table. For Video 2, I can’t figure out why the category “blank” in my excel exercise unable to capture the quality (count) as 8. I watch your video afew time, once you changed to count, it was auto captured as 8, but I can’t get it. Pls advice. Thank you and Best Regards

• Suri says:

Learned a lot.

• geng says:

Very clear details, i enjoyed it , good video , love it

• David Borja says:

Is PivotPal available for Office 365 on Mac?

• Motahar Hossain says:

Dear Jon, Everything is superfine but practice with the same dataset would help us more. How do I get the link to download the dataset?

• Jojo says:

Great Stuff Jon. You are removing my mental block on PVT.

• Jojo says:

Do you know why my data spreadsheet did not pickup the quantity for the blank which was later labelled shipping fee? instead of 6 the cell was blank even after refreshing. The category label changed to shipping fee though.

• Zak Aden says:

It was really great and I enjoyed simple tricks and short cuts.

Thank you John

• Masar says:

Great videos many thanks Jon

• Lalit Sharma says:

Hi Sir,

How can we make we do Number and Percentage in same column like in the First row in number and in Second row percentage in pivot table.

• Benny Ho says:

Hi Jon,
Your video #2 is really great. It provides me the training to post queries on the source data. Best of all………..by clicking onto the PivotPal, I am able to find all the answers to my queries or investigation in Pivot Chart.

• Alan J Moore says:

I just passed my MOS-201 Excel Expert exam. GMetrix wasn’t enough to get me through it the first time. I used your material, and others on UTube, and was able to pass the exam with time to spare. The Practice Tasks, Tricks, & Shortcuts I’ve learned from you are invaluable. Thank you.

• Eshetu Gizachew says:

Hello dear all,

I am hereby enquired to know if you can support me on my way of preparing road construction projects progress monitoring dashboard and sample files in this regard if any?
Thanks

• Christopher Wanjohi Ngiri says:

Ty so so much for the video.As i had earlier communacated to you that am just a bigginner.i believe if i continue watching your videos ill end up been one of the best Data anaysts.ty

• Ruqiyo says:

It was great tutorial. I learnt a lot from it. The Alt + ; shourtcut is not working for me though. :(.

Thank you for sharing this.

Generic filters
Exact matches only

Excel Shortcuts List

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

Excel Shortcuts List