What are PivotTables and when would you use one?

A PivotTable summarises a large table by grouping and aggregating, without writing a single formula. You drag fields into four areas — Rows, Columns, Values, and Filters — and Excel produces the cross-tabulation.

When to use one: any time the question is "what is the total, average, or count of X, broken down by Y". Sales by region and month, headcount by department, spend by category — these take seconds in a PivotTable and would need a page of SUMIFS otherwise.

What the source data must look like — this is what people get wrong:

• One header row, no blank rows or columns, no merged cells.
• Flat, not cross-tabulated. Months should be values in one column, not twelve separate columns.
• Consistent data types per column.
• Formatted as an Excel Table so the range grows automatically.

Beyond the basics: Value Field Settings changes the aggregation and can display values as a percentage of total or a running total. Slicers and timelines give clean interactive filtering. Calculated fields add derived measures. Grouping turns dates into months or quarters automatically.

Note: Two practical points: PivotTables do not refresh automatically, so stale numbers are a common error; and for multiple related tables, adding them to the Data Model lets one PivotTable span several tables through relationships.