Welcome to our free Advanced Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2013 Training course.
In this lesson, you will learn to work with pivot tables.
Lesson Goals
Pivot tables are interactive tables that can be used to analyze, organize, and summarize large amounts of data. Pivot tables make it easy to rearrange data, quickly displaying it in different ways. Pivot tables usually have four types of fields:
In the following image, the pivot table shows the average price of homes in different towns based on the number of bedrooms. At the top is a Page field which can be used to show only houses with a specific number of bathrooms:
Before inserting a pivot table, make sure the data you want to analyze is in an organized table. Things to consider include:
To insert a pivot table in Microsoft Excel:
Slicers make it really easy to filter data in a pivot table. Adding a Slicer is similar to adding a second Page field (Report Filter), but it's even easier to use.
To add a Slicer to a pivot table:
You can group data in a Pivot Table to show a subset of your data
To group data:
PowerPivot is an add-on that you can use in Excel 2013 to perform more powerful data analysis.
To add PowerPivot:
Once PowerPivot is installed, you can launch the PowerPivot window by selecting Manage from the POWERPIVOT tab. Here you can import and manage external data.
A new feature of Excel 2013 is the Relationships option on the DATA tab, in the Data Tools group.
To manage relationships in Pivot Tables, you must have at least two tables in your worksheet. Then, to create a relationship:
Pivot charts are simply charts that use the data in your pivot table. Pivot charts make it easy to visualize your data.
To insert a pivot chart:
Pivot tables are one of the most powerful and useful tools in Excel. Watch the presentation that follows this reading. It will demonstrate additional pivot table functionality, including:
In this exercise, you will practice using pivot tables, pivot charts, and Slicers.