Level 1 · Your first PivotTable

A PivotTable turns a long table into a short summary without a single formula. You tell it which field to group by and which numbers to add up; it finds every distinct value, adds up each group and puts a total underneath. In Excel it lives under Insert → PivotTable.

Rows and Values

Every PivotTable answers a sentence like “revenue per category”. The two halves map to two boxes:

Box	What goes in	Here
Rows	the field you group by: one row per distinct value	category
Values	the numbers to summarise for each group	revenue, as a Sum

The sheet holds the 31 completed order lines from June 18 in A1:E32. Click PivotTable under the grid, pick category for Rows and revenue for Values, and keep “Summarise values by” on Sum. The table appears at H1, where the ticket asked for it.

In Excel you make the same choices by dragging fields into the boxes of the PivotTable Fields pane, after choosing the source range and “Existing worksheet: H1” in the Create PivotTable dialog.

Reading the result

category      Sum of revenue
Decor               346.23
Furniture         1,439.30
Kitchen           1,122.95
Lighting          1,284.94
Outdoor           3,898.06
Textiles            703.05
Grand Total       8,794.53

Three things to notice:

• the categories are sorted A→Z, not by size. Excel does the same; you can sort by value later;
• the value header names the summary: Sum of revenue. Switch to Count and it says Count of revenue;
• Grand Total is the sum of every line, which is also the sum of the category rows.

Outdoor brings in 3,898.06, almost half the day, from 8 lines. A buyer reads that as “patio season is here”.

Layouts and refresh

Excel’s default layout (“compact form”) labels the first column Row Labels. This sheet shows the field name instead, which is what you get in Excel with Design → Report Layout → Show in Tabular Form. The numbers are identical.

One honest difference: here the PivotTable recalculates as soon as the data changes. In Excel and Google Sheets a pivot does not update by itself when you edit the source. You press Refresh (Alt+F5 in Excel), and if you added rows below the data you also widen the source range, or format the data as a table first (Ctrl+T) so it grows on its own.

Common mistakes

• Numbers in Rows. revenue in Rows lists every distinct amount. Rows is for labels.
• Forgetting to refresh. A pivot that looks right but was built before the last data fix is the most common stale number in a monthly report.
• Including a total row in the source. If the export ends with a “Total” line, the pivot counts it as one more record and doubles the Grand Total. Keep the source to data rows only.
• Trusting Sum blindly. If a column holds numbers stored as text, Excel quietly switches the default to Count. Always read the value header.