How to Use Pivot Tables in Excel

Introduction

You have a sales log with hundreds of rows and your manager asks three questions: total revenue by region, the best-selling product, and how sales trended month over month. You could write a SUMIFS formula for each answer, but every new question means a new formula. Pivot tables summarize, group, and filter data by dragging fields into a layout. One table answers all three questions.

The Dataset

Here's a Q1 sales log we'll use throughout:

	A	B	C	D	E
3	Rep	Region	Product	Date	Amount
4	Sarah	East	Desks	1/8	1200
5	James	West	Chairs	1/15	800
6	Sarah	East	Chairs	1/22	650
7	Maria	East	Desks	2/3	1400
8	James	West	Desks	2/10	1100
9	Priya	South	Lamps	2/18	300
10	Maria	East	Chairs	2/25	900
11	James	West	Lamps	3/5	250
12	Priya	South	Desks	3/12	1350
13	Sarah	East	Desks	3/19	1500
14	James	West	Chairs	3/24	700
15	Priya	South	Lamps	3/30	400

Twelve transactions across four reps, three regions, and three product types. The total is 10,550. A pivot table will break that number apart in different ways without touching the source data.

Creating a Pivot Table

Click any cell inside the data (say, A3).
Go to Insert > PivotTable.
Excel detects the range A3:E15. Leave it as-is and choose New Worksheet.
Click OK.

A blank pivot table appears on a new sheet with a PivotTable Fields pane on the right. The pane lists every column header from your data: Rep, Region, Product, Date, and Amount.

To see total revenue by region:

Drag Region to the Rows area.
Drag Amount to the Values area.

Excel sums the Amount column for each region:

Region	Sum of Amount
East	5650
South	2050
West	2850
Grand Total	10550

East leads at 5650, more than double the South. That took two drag-and-drop moves, no formulas.

Rearranging Fields

Swap Region out of Rows and drag Rep in:

Rep	Sum of Amount
James	2850
Maria	2300
Priya	2050
Sarah	3350
Grand Total	10550

Sarah leads at 3350. Drag Product into Rows instead:

Product	Sum of Amount
Chairs	3050
Desks	6550
Lamps	950
Grand Total	10550

Desks account for more than half of all revenue. Each view comes from the same twelve rows. You're just telling the pivot table which field to group by.

Adding a Column Field

A single row field gives you one dimension. Drag a second field to the Columns area to add another.

With Region in Rows, drag Product to Columns:

Region	Chairs	Desks	Lamps	Grand Total
East	1550	4100		5650
South		1350	700	2050
West	1500	1100	250	2850
Grand Total	3050	6550	950	10550

Blank cells mean no transactions for that combination. East sold no Lamps and South sold no Chairs. The two-dimensional layout shows where each region's revenue comes from at a glance.

Key takeaway: Rows and Columns are interchangeable. Dragging Product to Rows and Region to Columns gives the same numbers, just transposed. Try both to see which layout reads better for your audience.

Changing the Summary Function

By default, pivot tables sum numeric fields. To switch the calculation, right-click any value in the pivot table and choose Value Field Settings. You can pick Count, Average, Max, Min, or several other functions.

Switching to Count on the Region pivot shows how many transactions each region had:

Region	Count of Amount
East	5
South	3
West	4
Grand Total	12

Switching to Average shows the typical deal size:

Region	Average of Amount
East	1130
South	683
West	713
Grand Total	879

East has the highest average at 1130. South has the lowest at 683. Same layout, different question.

Filtering

Drag a field to the Filters area (above Rows in the PivotTable Fields pane) and a dropdown appears above the pivot table. It restricts the entire table to one or more values from that field.

Drag Rep to Filters. Select Sarah from the dropdown and the table recalculates to show only her transactions:

Region	Sum of Amount
East	3350
Grand Total	3350

Sarah sold only in the East. Switch the filter to James and the numbers shift to his 2850 across the West.

For a more visual option, use a Slicer. Click inside the pivot table, go to PivotTable Analyze > Insert Slicer, and check the fields you want. A floating button panel appears on the sheet. Click a button to filter, click again to clear it. Slicers are easier to spot than a dropdown tucked above the table, and anyone looking at the same screen can see which filter is active.

Grouping Dates

With Date in Rows and Amount in Values, the pivot table lists all twelve individual dates. That just reproduces the raw data. Grouping collapses them into months, quarters, or years.

Right-click any date in the pivot table and choose Group. Select Months:

Date	Sum of Amount
Jan	2650
Feb	3700
Mar	4200
Grand Total	10550

Revenue grew each month: 2650 in January, 3700 in February, 4200 in March. If the data spanned multiple years, check both Months and Years in the grouping dialog to keep January 2025 and January 2026 separate.

Refreshing the Data

Pivot tables don't update automatically when the source data changes. If you add a row to the sales log or edit an amount, the pivot table still shows the old numbers until you refresh it.

Right-click anywhere in the pivot table and choose Refresh, or go to PivotTable Analyze > Refresh. The keyboard shortcut is Alt + F5.

If you add rows below the original range, the pivot table won't pick them up because its source range is fixed. Two ways around this:

Use an Excel Table. Select the source data and press Ctrl + T. Tables expand automatically when you add rows, and the pivot table's source reference updates with them.
Update the range manually. Go to PivotTable Analyze > Change Data Source and select the larger range.

The Excel Table approach is better. Set it up once and the range takes care of itself.

Conclusion

Pivot tables turn a flat list into grouped summaries by dragging fields into rows, columns, values, and filters. One dataset answers as many questions as you can think to ask, without writing a single formula.

For formula-based summaries when you need a specific number embedded in a report, see our guide on SUMIFS. To highlight the values that stand out, check out our guide on conditional formatting.