01Get your data ready first
- Row 1 holds headings, and each column holds one kind of data (date, category, amount…).
- No blank rows or merged cells inside the data.
- Each column has one format, for example every value in the amount column is a number.
Tip Select the data and press Ctrl+T to turn it into a table. New rows are then included automatically when you refresh the pivot table.
02Create the pivot table
- Click any cell in the data.
- Go to Insert, then PivotTable, and click OK. Excel opens a new sheet.
- On the right you will see the field list and four areas: Filters, Columns, Rows and Values.
03Drag fields to summarize
- Drag "Category" to Rows. Each category becomes a row.
- Drag "Amount" to Values. The default is the sum.
- To see months, drag "Date" to Columns. Excel groups it by month automatically.
- To count or average instead, click the field in Values, choose Value Field Settings, then pick Count or Average.
04Refresh and chart it
- After the source data changes, right-click the pivot table and choose Refresh.
- Click the pivot table, then Insert and choose a chart. The pivot chart updates when you filter.
- To make it report-ready, pick a style on the Design tab and show numbers with thousands separators.
Q&AFAQ
Does a pivot table change my original data?
No. A pivot table is a separate summary, and your source data is never modified.
Does Google Sheets have pivot tables?
Yes. Use Insert, then Pivot table. It works almost the same way as in Excel.
Rather have it done for you?
Document and Excel help, from about us$9. Report writing, data organizing and basic Excel work: tables, formulas, pivot tables and charts, usually delivered within a day or two.