What a pivot table is
A pivot table takes a flat data table — rows of transactions, bookings, sales records, survey responses — and summarises it interactively. You choose what to group by, what to aggregate, and how to filter, and Excel reorganises the data accordingly. No formulas. No manual sorting. No copy-pasting.
The name comes from the ability to pivot — rotate — the data: what was in rows can become columns, what was a filter can become a row grouping, all by dragging field names between zones.
When to use one
Use a pivot table whenever you have a flat table of records and need to see subtotals, counts, averages, or other aggregations by category. Sales by region. Bookings by month. Survey responses by age group. Support tickets by status and priority. Any question of the form "how much of X by Y" is a pivot table question.
Creating your first pivot table
Click any cell inside your data table. Go to Insert → PivotTable. In the dialog, confirm the data range is correct and select New Worksheet. Click OK. Excel creates a new sheet with the PivotTable Fields panel on the right and an empty pivot table placeholder in the middle.
The four field areas
Rows — fields dragged here become the row labels of your pivot table. Each unique value in the field becomes a row. Drag Salesperson here to show one row per salesperson.
Columns — fields here become column headers. Drag Quarter here to see a separate column for each quarter. Use sparingly — too many columns makes the table hard to read.
Values — the numbers you want to aggregate. Drag Net Sales here to sum sales for each row label. Drag the same field twice if you want both Sum and Count side by side.
Filters — fields here appear as dropdown filters above the pivot table. Select a value to restrict the entire table to that category. Drag Region here to be able to switch the whole table between regions with a dropdown click.
Changing the summary function
Values default to Sum. Right-click any value in the Values area → Summarise Values By to switch to Count, Average, Max, Min, Product, or Standard Deviation. Use Count for transaction counts (how many orders), Average for performance metrics (average order value), and Max/Min for range analysis.
To show percentages rather than absolute values: right-click → Show Values As → % of Grand Total, % of Row Total, or % of Column Total.
Grouping dates and numbers
Right-click a date field in the row or column area → Group. Excel can group by days, months, quarters, and years simultaneously — check multiple options to create a hierarchy. This is how you go from a daily transaction table to a monthly or quarterly summary without creating any helper columns in the source data.
Numeric fields can also be grouped: right-click a number row → Group → set Start, End, and By values. Useful for creating age brackets, value tiers, or frequency bands.
Pivot charts
Select any cell in your pivot table → Insert → PivotChart. The chart updates automatically when you change the pivot table's filters, row groupings, or value fields. A pivot chart connected to a pivot table with a Region slicer gives you a chart that changes when the user clicks different regions — interactive reporting without any code.
Slicers — interactive filtering
Slicers are visual filter buttons that sit alongside the pivot table. Click Insert → Slicer while the pivot table is selected, choose a field, and Excel creates a set of buttons — one per unique value. Clicking a button filters the pivot table. Multiple slicers can be connected to the same pivot table for multi-dimensional filtering.
Refreshing when data changes
Pivot tables do not automatically update when source data changes. Right-click the pivot table → Refresh, or go to PivotTable Analyse → Refresh All. If your source data has grown (new rows added), right-click → PivotTable Options → Change Data Source to extend the range.
Better practice: convert your source data to an Excel Table (Ctrl+T) before creating the pivot table. Tables automatically expand when new rows are added, and the pivot table will include new data on the next refresh without needing a range adjustment.
Practical tips
Double-click any value cell in a pivot table to see all the underlying records that contribute to that total — Excel creates a new sheet showing just those rows. Useful for drilling into an unexpectedly high or low number. Right-click the row or column labels and choose Sort to order by value descending — so your highest performers always appear at the top. Use the Field Settings dialog (right-click a field label) to rename it to something more readable than the database column name.
`