←Module 6
Lesson

Introduction to PivotTables

PivotTables allow you to summarize large datasets instantly and reorganize your data to answer questions quickly.

Video

Watch the lesson video, then complete the reading and challenge.

Lesson Notes

Read through the key concepts before you try the challenge.

Why PivotTables Are Powerful

When a worksheet contains many rows of data, calculating totals manually becomes difficult. PivotTables automatically summarize and analyze large datasets.

Dataset used to create a PivotTable

Step 1: Select Your Data

Select the entire dataset including the column headers. These headers will become the fields used in the PivotTable.

Selecting dataset before creating PivotTable

Step 2: Insert the PivotTable

Go to the Insert tab and click PivotTable.

PivotTable command in Excel ribbon

Excel will open the Create PivotTable dialog box.

Create PivotTable dialog box

Step 3: Understanding the PivotTable Fields Panel

A blank PivotTable and the PivotTable Fields panel will appear in a new worksheet.

PivotTable fields panel

Step 4: Add Fields to Build the PivotTable

To calculate total sales by salesperson:

  1. Add Salesperson to the Rows area
  2. Add Order Amount to the Values area
Adding fields to PivotTable
PivotTable summarizing sales by salesperson

Adding Columns to Analyze More Data

You can analyze the data further by dragging the Month field into the Columns area.

Adding month to PivotTable columns
PivotTable showing monthly totals

Changing the PivotTable Perspective

PivotTables allow you to reorganize (pivot) the data to answer different questions.

For example, remove Salesperson and instead add Region to see total sales by region.

Adding Region field
Sales totals by region

Sorting and Filtering

PivotTables allow sorting and filtering so you can analyze your data more easily.

Sorting PivotTable data

Final PivotTable Example

Completed PivotTable example

Knowledge Check

What does a PivotTable allow you to do?

Practice File

Download this file and follow along with the lesson.

Download Practice Workbook

Challenge

Apply what you've learned in this lesson.

  1. Open our practice workbook.
  2. Create a PivotTable in a separate sheet.
  3. We want to answer the question What is the total amount sold in each region? To do this, select Region and Order Amount.
PivotTable showing total amount sold by region
  1. In the Rows area, remove Region and replace it with Salesperson.
  2. Add Month to the Columns area.
  3. Change the number format of cells B5:E13 to Currency. Note: You might have to make columns C and D wider to see the values.

When you're finished, your workbook should look like this:

Completed PivotTable showing sales by salesperson and month
← Creating Charts in ExcelModule 7: Data Analysis Tools →