Excel Pivot Tables: Step-by-Step Guide with Examples

A PivotTable lets you summarize thousands of rows in Excel by dragging fields into place, with no formulas required. In this guide you will build one from scratch, then learn the techniques that make PivotTables genuinely useful for business reporting: grouping dates, showing percentages, adding calculated fields, filtering with slicers and keeping everything up to date. Each step uses the same simple sales example so you can follow along with your own data.

What a PivotTable does (and when to use one)

A PivotTable takes a flat list of records and aggregates it. Instead of writing dozens of SUMIFS formulas to answer "revenue by region by month," you drag Region to Rows, Month to Columns and Revenue to Values. Excel groups the matching rows and calculates the totals for you.

Use a PivotTable when you want to explore data, compare categories, find top performers or build a recurring summary. Use regular formulas when you need a single number in a specific cell of a fixed report layout. Many analysts combine both: PivotTables for exploration, formulas or GETPIVOTDATA for the final report.

The example dataset

The steps below use a sales table with one row per order and these columns: OrderDate, Region, Store, Product, Category, Units and Revenue. Your own data can have different columns, but it should follow the same rules: one header row, one record per row, no blank rows and no merged cells.

How to create a PivotTable step by step

1. Turn your data into a Table. Click any cell in the data and press Ctrl+T. Name it tblSales in the Table Design tab. Tables grow automatically, so your PivotTable will include new rows when you refresh.
2. Insert the PivotTable. With a cell in the table selected, go to Insert > PivotTable. Confirm the source is tblSales and choose New Worksheet.
3. Add a row field. In the PivotTable Fields pane, drag Region into the Rows area.
4. Add a value field. Drag Revenue into Values. Excel uses Sum for numeric fields by default.
5. Add a column field. Drag Category into Columns to split each region's revenue by category.
6. Format the numbers. Right-click any value, choose Value Field Settings > Number Format and pick Currency or Number with a thousands separator. This formatting stays when the PivotTable refreshes, unlike formatting applied directly to cells.
7. Sort the results. Right-click a value in the Grand Total column and choose Sort > Largest to Smallest so the biggest regions appear first.

The four PivotTable areas explained

A good rule: put the field with many items (products, customers) in Rows, and the field with few items (years, categories) in Columns. Wide tables with dozens of columns are hard to read.

Choose a readable report layout

By default, Excel shows PivotTables in Compact Form, which stacks several row fields into one indented column. That saves space but is awkward when you want to copy the results elsewhere. Go to Design > Report Layout and choose Show in Tabular Form to give each row field its own column, then select Repeat All Item Labels so every row is complete. In the same Design tab you can turn subtotals and grand totals on or off. A clean tabular layout is much easier to read, filter, print and paste into another report or presentation.

Five techniques that make PivotTables powerful

1. Group dates by month, quarter and year

Drag OrderDate into Rows. In current versions of Excel, dates are often grouped automatically. If not, right-click any date, choose Group, and select Months, Quarters and Years. Selecting Years together with Months matters: otherwise January 2025 and January 2026 are added together.

2. Show values as percentages

Drag Revenue into Values a second time. Right-click the new column, choose Show Values As > % of Grand Total (or % of Column Total, % of Parent Row Total, Difference From, and others). You now see both the amount and each item's share side by side.

3. Add a calculated field

To calculate average revenue per unit, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. Name it Revenue per Unit and enter:

= Revenue / Units

Calculated fields operate on the summed values, so this returns total revenue divided by total units for each row of the PivotTable, which is exactly what you want for a weighted average. For more complex measures, the Data Model and DAX are a better fit; see our guide to Power Pivot and the Excel Data Model.

4. Filter with slicers and timelines

Slicers are clickable filter buttons. Select the PivotTable, go to PivotTable Analyze > Insert Slicer and choose Region or Category. For dates, use Insert Timeline to filter by year, quarter or month with a slider. You can connect one slicer to several PivotTables through Report Connections, which is the foundation of an interactive Excel dashboard.

5. Drill down to the details

Double-click any value in a PivotTable and Excel creates a new sheet listing every source row behind that number. It is the fastest way to check an unexpected total or investigate an outlier.

Keeping PivotTables accurate

• Refresh after data changes. PivotTables do not update automatically. Right-click > Refresh, or use Data > Refresh All to update every PivotTable and query in the workbook.
• Refresh on open. In PivotTable Options > Data, check "Refresh data when opening the file" for reports other people open.
• Use a Table as the source. A fixed range like A1:G500 will silently miss row 501.
• Check the Grand Total. Compare it against the total in your source system before you share the report.
• Be careful referencing PivotTable cells. A normal reference such as =B5 breaks when the layout changes. Use GETPIVOTDATA, which Excel creates when you click a PivotTable cell while writing a formula.

=GETPIVOTDATA("Revenue", $A$3, "Region", "East")

That formula returns East region revenue from the PivotTable that starts at A3, wherever that value moves.

Next steps

Build three PivotTables on your own data: one ranking products by revenue, one showing monthly trends by region with dates grouped by year and month, and one with % of Grand Total to show each category's share. Add a slicer and connect it to all three. Once that feels natural, learn to prepare the source data automatically with Power Query, and when you hit the limits of a single table, move on to the Data Model and, eventually, Power BI.

Watch: video lessons

▶

▶

Frequently asked questions