Spreadsheet Practice

Lesson 10 of 32, Data tools

PivotTables

Summarize hundreds of rows by any field in a few clicks, with no formulas to write.

Free12 minutes, 4 exercisesPivotTableRowsValuesFilters

Watch it done

5 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Click any cell in your data, then Insert > PivotTable and choose where to put it. Excel reads the whole table, header row included, and opens the PivotTable Fields pane. Each column header becomes a field you can drop into one of four areas.

Rows makes one row per value: Region in Rows gives one row per region. Values is what gets calculated: numbers are summed by default, and text is counted. Columns splits each row across the values of a second field. Filters keeps only the rows you pick.

To change the math, open Value Field Settings. Summarize Values By switches between Sum, Count, Average, Max and Min. Show Values As > % of Grand Total turns each number into its share of the whole. To put the biggest first, click a value and choose Sort Largest to Smallest.

The practice editor works like the Fields pane: add a field to an area, then use its settings. A PivotTable doesn't update on its own when the data changes. Right-click it and choose Refresh.

Syntax

Rows: Region · Values: Sum of RevenueTotal revenue per region
Values: Count of OrderHow many orders
Rows: Rep · Columns: RegionA grid of reps by region
Show Values As: % of Grand TotalEach row's share of the total
Filters: Region = EastOnly the East rows

Watch out for

  • A PivotTable doesn't recalculate by itself. After the data changes, right-click it and choose Refresh.
  • Blank cells or text in a number column make Excel count instead of sum. Check the label: "Count of Revenue" means something in the column isn't a number.
  • Headers matter: every column needs a unique name in row 1, or Excel can't build the field list.
  • Sorting the field A to Z sorts the names. To rank by the numbers, sort by the value.

The exercises

  1. Revenue by region. Build a PivotTable from A1:F21 that shows total revenue for each region: Region in Rows and Revenue in Values, summed. Keep the grand total.
  2. Most orders first. Count the orders each rep took: Rep in Rows and Order in Values, summarized by Count. Sort the reps so the one with the most orders comes first.
  3. Reps by region. Show revenue for every rep in every region: Rep in Rows, Region in Columns, and Revenue in Values (Sum). Keep the grand totals.
  4. East product mix. For the East region only, show each product's share of revenue: Product in Rows, Revenue in Values shown as % of Grand Total, and a Region filter set to East.

Revenue by region

Build a PivotTable from A1:F21 that shows total revenue for each region: Region in Rows and Revenue in Values, summed. Keep the grand total.
Source data: A1:F21

PivotTable Fields

Rows
Columns
Values
Filters

PivotTable

Add fields to Rows and Values to build the table.