Pivot Tables and Dashboards

Introduction

SUMIF formulas work, but writing one for every combination is slow. A pivot table does the grouping and totalling for you. You drag fields around, and the summary rebuilds instantly. It is one of the most powerful spreadsheet features, and it works with the tidy data you learned to prepare.

Prepare the data

Pivot tables need a tidy table: one header row, one record per row, no blank rows or merged cells. Our sales table is ready, with columns Month, Region, Product, Units, Price and Revenue.

Create a pivot table

1. Click any cell inside the data.
2. Choose Insert, then Pivot table (in Google Sheets, Insert, Pivot table).
3. Choose a new sheet for the result.
4. A field list appears. Drag or add fields to four areas:
   • Rows: the categories down the side
   • Columns: categories across the top
   • Values: the numbers to calculate, such as the sum of Revenue
   • Filters: limits the whole table

Put Region in Rows and Revenue (sum) in Values. The result:

These match the SUMIF results you checked earlier. Add Product to Columns, and the table breaks revenue down by region and product at once. For example, the cell for region N and Pen is 36.

Change the calculation

Values can be summed, counted, averaged, or show the minimum and maximum. Right-click a value (or use the field settings) to change it. You can also show values as a percentage of the total, which is useful for shares.

Group and filter

• Group dates by month, quarter or year. Real dates are required, not text.
• Group numbers into ranges, such as units 0 to 4, 5 to 9.
• Use filters or slicers (clickable buttons) to look at one region, product or period.
• Sort the pivot by value to show the biggest first.

Refresh

A pivot table does not always update itself when the source data changes. Use Refresh (or Data, Refresh all). If you add rows, make sure the source range includes them. Using a table object or a range that expands makes this easier.

Add a chart

Select the pivot table and insert a chart. It follows the pivot, so when you change the layout or filter, the chart changes too. Remember the rules for honest charts: a clear title, labelled axes and bars that start at zero.

A simple dashboard

A dashboard is a one-page overview for decisions.

1. Create a new sheet called Dashboard.
2. Add key numbers at the top, such as total revenue and number of orders, using formulas that refer to the pivot tables.
3. Add two or three charts that answer the main questions.
4. Add slicers or filters so users can explore.
5. Write a short note about the data period and source.
6. Hide scratch sheets, or protect them, if appropriate.

Keep it simple. Too many charts hide the message.

Limits and cautions

• A pivot table summarises, so errors in the data still show up as wrong totals. Check the source first.
• Inconsistent spelling creates separate groups ("North" and "north ").
• Blank cells in categories appear as "(blank)".
• Pivot tables work on tidy data. Raw reports with subtotals do not work well.
• Do not share a file with private data inside hidden sheets or pivot caches.

Try it yourself

Create the pivot table for revenue by region and product. Add a slicer for Month, group the months, and insert a column chart. Check that the grand total is 248.5.

Summary

• A pivot table groups and totals tidy data by dragging fields into rows, columns, values and filters.
• You can change the calculation, group dates, filter and refresh when the data changes.
• Combine pivot tables, charts and key numbers into a simple dashboard, and always check the source data.