Pivot Tables in Excel: Step-by-Step Guide
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019
A pivot table summarises thousands of rows in seconds, with no formulas. Drag a field into place and Excel totals, counts or averages it by any category you choose: sales by branch, by month, by salesperson, or all three at once. This guide walks you through creating your first pivot table, then grouping, filtering, calculating and charting.
Prepare your data
Pivot tables need a clean list: one header row, one record per row, no blank rows or columns and no merged cells. See Excel Data for the full checklist. Convert the list to an Excel Table first (Ctrl + T); then new rows are included automatically when you refresh.
Create your first pivot table
- Click any cell in the data.
- Choose Insert > PivotTable.
- Check that the table or range is correct, choose New Worksheet and click OK.
- In the PivotTable Fields pane, tick Branch; it goes to Rows.
- Tick Amount; it goes to Values as "Sum of Amount".
You now have total sales for each branch with a grand total, and the source data is untouched.
The four areas explained
| Area | What it does | Example |
|---|---|---|
| Rows | Lists each item down the left | Branch |
| Columns | Lists each item across the top | Month |
| Values | The numbers being summarised | Sum of Amount |
| Filters | Filters the whole report | Year = 2026 |
Drag Product into Columns to create a cross-tab of branch by product. Drag Salesperson under Branch in Rows to see each person within their branch. Rearranging fields is instant, so experiment freely; this is the "pivot" in pivot table.
Change the calculation and number format
Click the field in Values and choose Value Field Settings. On Summarize Values By you can switch from Sum to Count, Average, Max or Min. On Show Values As you can display each figure as a % of Grand Total, % of Column Total or a Running Total. Click Number Format to apply a thousands separator so the format stays even after refreshing.
Group dates and numbers
When you place a date field in Rows, Excel usually groups it into Years, Quarters and Months automatically. To change this, right-click a date in the pivot table and choose Group, then select the levels you want. Numbers can be grouped into bands too: right-click an Amount in Rows, choose Group and set, for example, a range from 0 to 50,000 in steps of 5,000 to see how many sales fall in each band.
Filter with slicers and timelines
Click inside the pivot table and choose PivotTable Analyze > Insert Slicer. Tick Branch to add a set of buttons; clicking a button filters the report, and Ctrl-click selects several. Insert Timeline adds a date slider for filtering by month, quarter or year. Right-click a slicer and choose Report Connections to let one slicer control several pivot tables, which is the basis of a simple dashboard.
You can also sort a pivot table by its values (right-click a number > Sort > Largest to Smallest) and use the Row Labels filter to show, for example, the Top 10 products.
Add a calculated field
If your data has Amount and Cost but no Profit column, choose PivotTable Analyze > Fields, Items & Sets > Calculated Field, name it Profit and enter =Amount-Cost. It behaves like any other field. For margin percentage, create =(Amount-Cost)/Amount and format it as a percentage.
Double-click any number in a pivot table to see the underlying rows on a new sheet. This is a quick way to check exactly which sales make up a total.
Refresh, design and chart
- Pivot tables do not update automatically. After changing the source data, right-click the pivot table and choose Refresh, or press Alt + F5. Data > Refresh All updates every pivot table in the file.
- On the Design tab, set Report Layout > Show in Tabular Form and Repeat All Item Labels for a layout that is easy to copy elsewhere.
- Choose PivotTable Analyze > PivotChart to create a chart that follows the pivot table's filters and slicers.
- To get a single value from a pivot table into a formula, type
=and click the cell; Excel writes a GETPIVOTDATA formula that keeps working even when the layout changes.
Summary
- Start from a clean Excel Table, then use Insert > PivotTable.
- Rows, Columns, Values and Filters control the layout; drag fields to rearrange.
- Use Value Field Settings for averages, counts and percentages.
- Group dates by month or quarter and add slicers for one-click filtering.
- Refresh with Alt + F5 after the data changes.
Back to: all Excel tutorials.