Pivot Tables for Beginners: Summaries in Seconds

In lesson 6 you built a summary with SUMIF formulas, one cell at a time. A pivot table does the same with a few clicks and goes much further. You can summarise thousands of rows by any column, rearrange the report by dragging fields, and add a filter or a chart. Pivot tables are a headline skill in Excel job adverts, and interviewers love to ask about them. They look hard, but the idea is simple, and you can learn to build one in ten minutes.

What a pivot table does

It takes a table of detailed rows and creates a summary table that groups the rows by the values in one or more columns, and calculates something such as the total, count or average for each group. The original data is never changed.

Your first pivot table

1. Click inside your Sales table (it should have headings and no blank rows).
2. Choose Insert, PivotTable. Accept the range or the table name, and choose New Worksheet. Press OK.
3. A new sheet appears with an empty report on the left and a PivotTable Fields panel on the right, listing your column headings.
4. Drag Region into the Rows box.
5. Drag Amount into the Values box.

The pivot table shows each region with its total sales.

Row Labels   Sum of Amount
East               4600
North              7800
South              7750
Grand Total       20150

Compare with lesson 6: the numbers match the SUMIF results, and the Grand Total is 20150. If you tick the field boxes instead of dragging, Excel makes a guess where to put them. Text fields go to Rows and number fields to Values.

The four areas

• Rows: the groups listed down the side.
• Columns: the groups listed across the top. Use it to compare two things.
• Values: what to calculate. Total, count, average, minimum or maximum.
• Filters: a drop-down at the top to look at part of the data, such as one staff member.

Drag Staff into Columns and you get a table of regions by staff member. Each cell shows the total amount for a region and person pair. Drag Item into Rows below Region and each region splits by item. Change the layout until it answers your question.

Change what is calculated

Click the small arrow next to “Sum of Amount” in the Values box, choose Value Field Settings, and select Count, Average, Max or Min. To show a percentage, open the Show Values As tab and choose “% of Grand Total”. With the Region rows, North becomes about 38.7%, South 38.5% and East 22.8%. Check the percentages add up to 100.

You can drag the same field into Values twice and show a total and a count side by side. Rename a heading by clicking it and typing, for example Total sales.

Number formats and tidy layout

Right-click a number in the pivot table, choose Number Format, and set a thousands separator. Formats set this way stay when you refresh. Formatting cells the normal way may be lost. Use PivotTable Design to choose a style and a report layout, such as Tabular, which looks more like a normal table.

Filters and slicers

Drag Staff into the Filters box. A drop-down appears above the pivot table to choose one person. For a friendlier option, use PivotTable Analyze, Insert Slicer and tick a field. Buttons appear. Click one to filter the whole pivot table.

Group dates

If your data has real dates, put the date field in Rows. Right-click a date in the pivot, choose Group, and select Months, Quarters or Years. A year of daily sales becomes twelve monthly lines. This works only if every date is a true date. A single text value in the column stops the grouping.

Refresh when the data changes

A pivot table does not update by itself. After you change the source data or add rows, right-click the pivot table and choose Refresh, or use Data, Refresh All. If you add rows to a normal range outside a Table, the pivot may not include them. A source that is an Excel Table (lesson 10) grows automatically, so refreshing picks up the new rows. This is a good reason to build pivot tables from Tables.

Pivot charts

With a cell in the pivot table selected, choose Insert, PivotChart. The chart follows the pivot table, and changes if you change the fields or use a slicer. The next lesson covers charts in more detail.

Rules for a pivot-friendly source table

• One heading row, with a name in every column heading. A blank heading causes an error.
• No blank rows or merged cells in the data.
• One kind of value in each column. Numbers should be real numbers, not text.
• No subtotals or grand totals mixed in with the data.
• Consistent spelling. “North” and “north ” will be counted as different groups if there are spaces.

Typical questions a pivot table answers

• Which product sells the most? (Item in Rows, Sum of Qty in Values, sort largest to smallest.)
• Which salesperson produces the most revenue? (Staff in Rows.)
• How do sales change month by month? (Date grouped by month.)
• How many orders did each region have? (Region in Rows, Count of Item.)

Your task

1. Build a pivot table with Region in Rows and the Sum of Amount in Values. Check it against the totals from lesson 6.
2. Add Staff in Columns and see the totals for each pair.
3. Create a second pivot with Item in Rows and the Sum of Qty. Sort so the largest is at the top. Pen pack should be first with 55.
4. Change a price in the data, then refresh the pivot table and see the totals change.

How to check it worked

• The grand total is 20150.
• The Qty by item totals are Pen pack 55, Notebook 45, Mouse 6, Backpack 3 and Desk lamp 3.
• You know how to refresh a pivot table and why it does not update by itself.