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.
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.
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!
- In part 1 of the series we learned how to setup our source data and create our first pivot table and chart.
- In part 3 of the series we create the dashboard and add slicers to make it interactive.
What Do You Think?
Please leave a comment below with any questions or suggestions. Thanks!