Pivot tables in Excel: how to create one, step by step

A pivot table summarises a list without a single formula: sales by rep, by region or by month, built by dragging field names around. To create one, click inside your data, go to Insert > PivotTable and click OK. In the pane that opens on the right, drag a text field (Rep) into the Rows area and a number field (Amount) into the Values area: Excel shows the total for each rep.

A pivot table never changes the source data. You can move the fields around as often as you like to look at the figures from another angle.

The example data

Every result below comes from this sales log for the first quarter of 2026, entered on a sheet called Data:

Amounts are in pounds. Total sales: £8,880 across 14 rows.

Get the source ready

A pivot table works smoothly when the list follows three rules:

• One header row, with a heading in every column. A column without a heading stops the pivot table from being created.
• No completely blank rows or columns inside the data: Excel stops at the first blank row when it detects the range.
• One type of data per column: real dates in Date, numbers (not text) in Qty and Amount, and no subtotal rows mixed in.

Then turn the list into a table: click inside the data, choose Insert > Table, keep My table has headers ticked and click OK. On the Table Design tab, replace the default name (Table1) with Sales in the Table Name box. From then on, rows you add below the table are picked up by the pivot table at the next refresh, with no range to redefine.

Create the pivot table

1. Click any cell in the Sales table (no need to select it all).
2. On the Insert tab, in the Tables group, click PivotTable. In Microsoft 365, if the button opens a menu, choose From Table/Range.
3. Check the source Excel suggests (Sales, or Data!$A$1:$F$15 if you skipped the table step).
4. Leave New Worksheet selected and click OK.

Excel adds a sheet with an empty pivot table starting in cell A3 and opens the PivotTable Fields pane on the right. The top of the pane lists your column headings as fields. The bottom has four areas that receive them.

If the pane disappears, click inside the pivot table and it comes back. Otherwise use PivotTable Analyze > Field List.

First result: total sales by rep

Tick Rep in the field list (a ticked text field goes to Rows), then tick Amount (a number field goes to Values). You get:

Excel sorts the reps alphabetically and adds a Grand Total row. The amounts show without a thousands separator because the source formatting is not carried over. See Number format below to fix that.

Cross two fields: rep by region

Drag Region into the Columns area. The pivot table becomes a two-way grid:

Emma sold £2,020 in the North and £1,830 in the South. Row and column totals agree: 4,770 + 4,110 = 3,850 + 2,590 + 2,440 = 8,880. To flip the layout (regions in rows, reps in columns), drag the fields from one area to the other.

You could get the same figures with SUMIF or SUMIFS, but you would have to write and copy six formulas. The pivot table produces them in one move and rearranges itself without any rewriting.

Filter with the Filters area

Drag Region out of the Columns area (drop it outside the pane) and put Product in the Filters area. A drop-down appears in A1:B1. Open it, choose Monitor and click OK:

Only monitor sales are added up. To keep several products, tick Select Multiple Items in the drop-down. The arrow next to Row Labels also lets you filter or sort the reps themselves.

Change the calculation: count, average, percentage

Swap the sum for a count or an average

1. Right-click an amount in the pivot table and choose Value Field Settings.
2. On the Summarize Values By tab, choose Count (or Average, Max, Min…).
3. Click OK.

With Count, the pivot table counts the sales instead of adding them up:

With Average, Emma shows 770 (3,850 / 5) and the Grand Total row shows 634.29 (8,880 / 14).

Show percentages of the total

Switch back to Sum, then open Value Field Settings again. On the Show Values As tab, choose % of Grand Total from the list:

Emma brings in 43.36% of revenue (3,850 / 8,880). On a two-way pivot table (reps in rows, regions in columns), % of Row Total gives each rep's North/South split, and % of Column Total gives each rep's share of a region.

Number format

In Value Field Settings, the Number Format button opens the usual Format Cells options. Choose Number, 0 decimal places, with a thousands separator, for example. This format applies to the whole field and survives a refresh, unlike formatting applied straight to the pivot table cells.

Group dates by month

Remove Rep from the pivot table and drag Date into the Rows area. Depending on your version, Excel either groups the dates by month straight away or shows one row per day (14 rows). In the second case:

1. Right-click one of the dates in the pivot table and choose Group.
2. In the By list, select Months. If your data spans several years, select Years as well, otherwise January 2025 and January 2026 are added together.
3. Click OK.

The same command groups numbers into bands (quantities 0 to 9, 10 to 19 and so on). To go back to individual dates, right-click and choose Ungroup.

Refresh after the data changes

A pivot table does not recalculate on its own. If you correct an amount on the Data sheet or add a sale, nothing changes until you refresh:

• right-click inside the pivot table and choose Refresh, or press Alt + F5;
• for every pivot table in the workbook: Data > Refresh All, or Ctrl + Alt + F5.

Example: you add a sale on row 16, dated 07/04/2026, Olivia, North, Monitor, 3, £540. Because the source is the Sales table, it grows automatically; after a refresh, Olivia goes from 2,440 to 2,980 and the Grand Total from 8,880 to 9,420. If your source is a fixed range ($A$1:$F$15), the new row is ignored: fix it with PivotTable Analyze > Change Data Source, or convert the list to a table.

See the rows behind a figure

Double-click a value in the pivot table, for example Emma's 1,830 in the South: Excel creates a new sheet listing the source rows behind that total (two sales here, £750 of printers and £1,080 of monitors). It is the quickest way to check a figure that looks wrong. The new sheet is a copy, so editing it changes neither the source nor the pivot table.

From pivot table to chart and dashboard

Once the pivot table is ready, PivotTable Analyze > PivotChart creates a chart that follows the same fields and filters. For choosing a chart type and formatting it, see how to make a chart in Excel. To combine several pivot tables, charts and slicers on one page, follow our guide to building an Excel dashboard, which uses this same data.

Common errors

"The PivotTable field name is not valid"

One column in the source has no heading, or the selection starts below the heading row. Give every column a heading, or delete the empty column, then create the pivot table again.

The pivot table shows "Count of Amount" instead of "Sum of Amount"

Excel picks Count when the column contains blank cells or text (amounts imported as text, dashes, "n/a"). Fix the source, then change the calculation in Value Field Settings. If the numbers are stored as text, the sum stays wrong even after that change: convert them to numbers first.

The same rep appears on two rows

The two labels differ by something you cannot see, usually a trailing space ("Emma" and "Emma" followed by a space). The pivot table treats them as two values. Clean the column with =TRIM(B2) or with Find and Replace, then refresh.

"Cannot group that selection"

At least one cell in the Date column is not a real date: text such as "05/01/2026" aligned to the left, or a blank cell in a fixed range. Fix those cells, refresh, then group again.

New rows do not show up

Either the pivot table has not been refreshed, or the source is a fixed range that stops above the new rows. See the section on refreshing.