PivotTable basics: build a summary report in Excel the right way

Prepare the source data

Good source data follows a few rules:

• One header row, with a unique name in every column.
• No blank rows or columns, and no subtotals inside the data.
• One kind of data per column: dates as real dates, numbers as numbers.
• Format the range as an Excel table (Ctrl+T), so new rows are included when the PivotTable is refreshed.

The four areas

• Rows: the categories listed down the side.
• Columns: categories across the top.
• Values: the numbers being summarized, by Sum, Count, Average and so on.
• Filters: a filter applied to the whole report. Slicers do the same job visually.

Settings people miss

• Refresh: a PivotTable does not update automatically when its source data changes. Refresh it, or set it to refresh when the file opens.
• Summarize Values By: a numeric field that shows Count instead of Sum usually has blank or text cells in the source column.
• Show Values As: displays results as a percentage of the total, of the row, or as a running total, without extra formulas.
• Grouping: dates can be grouped by month, quarter and year, and numbers into ranges.