Microsoft Excel is a powerful spreadsheet program that features calculation, graphing tools, and pivot tables. In our Microsoft Excel – Pivot Tables short course, author Erin Olsen describes the pivot table as being the “single tool in Excel that can organize, filter, summarize, and make sense of enormous amounts of data with relative ease and little technical skill.”
At the start of the course, Erin outlines a number of reasons why users will want to use pivot tables:
- To query large amounts of data
- To aggregate data from multiple data sources
- To summarize data by categories
- To subtotal numeric data
- To create custom calculations and formulas
- To expand/collapse levels of data for different presentations
- To drill down to details of summary data
- To rearrange, or “pivot”, rows to columns and columns to rows
- To filter data for specific purposes
- To conditionally format values that meet certain criteria
- To present clear, concise, and easy-to-understand data
There are number of components that make up a pivot table. These are:
- The data field: a field from the source data that contains values to be summarized
- The column field: a field from the source data that is assigned to the column orientation in the pivot table
- The row field: there can be one or more row fields, and they are collapsible
- The data area: this is where the summarized data resides – it will grow and shrink as the number of items grow and shrink
- Summary calculations: these are found within the data area and can be simple or complex
- Grand totals: these can be turned on or off for the row and column
In short, pivot tables work to do 3 key things: they simplify data by categorizing it by one or more fields, they compact it by taking out the redundancy and even allow us to view values across columns and rows, and they summarize by providing calculations for the values we choose.
To help get you started in the world of pivot tables, we’ve included three tutorial videos from our Pivot Tables short course by Erin Olsen.
Creating Pivot Tables – The Quick Analysis Tool:
Available in Microsoft Excel 2013, this tool is designed to allow users to preview different types of pivot tables that can be created. Here, Erin shows you how to use the quick analysis tool.
Creating Pivot Tables Manually:
There are times when you know exactly what you want to create, and you want to create it your way. Here, Erin shows you how to design and create your own pivot table manually.
Using General Pivot Table Tools:
In this video tutorial, Erin shows you how to use the tools that apply specifically to pivot tables.