←Module 6
Lesson · 35 min

Introduction to PivotTables

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

By the end of this lesson you can

  • Build a PivotTable from a clean data range
  • Arrange fields across Rows, Columns, Values, and Filters
  • Change how values are summarized
  • Refresh a PivotTable when the source data changes

Video

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

Lesson Notes

Read through the key concepts before you try the challenge.

Answering four questions in three minutes

On the job

You are asked for a spending breakdown at Lakeside Medical Associates.

Your supervisor asks: what did we spend per department last month, what are the top categories, which vendors got the most, and how does May compare with April? Answered with formulas and manual summary tables, that is most of an afternoon. Answered with one PivotTable, it is about three minutes.

Your task: Learn to summarize a large data set by rearranging fields rather than writing formulas.

A PivotTable summarizes a data set by whatever fields you drag into it, without altering the source in any way. Drag Department to Rows and Amount to Values and you have spending per department. Drag Month to Columns as well and you have a full cross-tab. Each rearrangement is a new question answered, and the underlying data is never touched.

AreaField goes here toExample
RowsBecome the row labelsDepartment, one per row
ColumnsBecome the column headingsMonth, one per column
ValuesBe summarized — summed, counted, averagedAmount, summed
FiltersFilter the whole tableYear, to show one year at a time
The four areas
PivotTables do not update automatically when the source data changes. You must click Refresh on the PivotTable Analyze tab, and forgetting is one of the most common ways a stale figure reaches a report. Building the PivotTable on an Excel Table rather than a fixed range at least ensures new rows are included when you do refresh.
If Excel summarizes a numeric column with Count instead of Sum, that column contains text somewhere — often a stray note or a number stored as text. The PivotTable is telling you about a data quality problem worth fixing at the source.

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

Check your understanding

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

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

← Creating Charts in ExcelModule 7: Data Analysis Tools →