Blog / Pivot tables

How to create a pivot table in Excel: step by step

4 November 2026 · 9 min read

Create a PivotTable in Excel step by step: prepare your data, insert it, drag fields into place, change the calculation and refresh it, with a worked example.

A PivotTable summarizes a long list in a few clicks. Select any cell in your data, go to the Insert tab, click PivotTable, then drag one category field into Rows and one number field into Values. Excel groups the matching rows and totals them for you, with no formula for each group.

This guide shows how to prepare your data, build a PivotTable from start to finish, change what it calculates, keep it up to date and check its numbers. The steps follow Microsoft's instructions for Excel for Windows and Microsoft 365.

What a PivotTable does

Suppose you have 500 sales in a list, one row per sale, with a Region column and a Sales column. You want the total for each region. You could write a SUMIF formula for each region, then add a new one whenever a new region appears. A PivotTable does that grouping for you. It finds each unique region, adds up the matching sales and lays the result out in a new table.

If you later want the totals by product instead of by region, you move one field and the answer changes. You do not rewrite any formulas.

A PivotTable does not change your original data. It is a report built from that data, so when the data changes you refresh the PivotTable to update it.

Before you start: prepare the data

A PivotTable needs a clean list. Check these points before you insert one:

One header row. Every column needs a unique, non-blank name in the first row.
No blank rows or columns inside the data.
One type of data per column. Do not mix dates and text in the same column.
No merged cells and no second header row. Microsoft advises against both because they confuse the field list.

It also helps to turn the list into an Excel table. Click any cell in the data, press Ctrl + T, and tick My table has headers. A table grows when you add rows, and when you refresh a PivotTable built on it, Excel includes the new rows automatically.

Step by step: create a PivotTable

Click any cell inside your data. Excel selects the whole list for you.
On the Insert tab, click PivotTable.
In the dialog box that opens, check the table or range Excel has chosen. Adjust it if needed.
Choose where the PivotTable goes. New Worksheet is the simplest choice. To use the current sheet, choose Existing Worksheet and click the cell where the top-left corner should sit.
Click OK. An empty PivotTable appears, and the PivotTable Fields pane opens on the right.
Add fields to the four areas at the bottom of the pane:
- Rows: the categories to group by, such as Region or Product.
- Values: the numbers to add up or count, such as Sales.
- Columns: an optional second category, shown across the top.
- Filters: an optional field that lets you show only some of the rows.

To add a field, tick its box, or drag it into an area. Excel usually makes a sensible first guess: text fields go to Rows, date fields go to Columns, and numeric fields go to Values. You can always drag a field to a different area.

Worked example: sales by region

Here is a small sales list with a Region, a Product and a Sales column:

Region	Product	Sales
North	Notebook	120
South	Pen	60
North	Pen	75
West	Notebook	80
South	Notebook	150
West	Pen	45
North	Notebook	110
South	Pen	35

Put Region in Rows and Sales in Values. Excel adds up the Sales for each region and shows a grand total at the bottom:

Row Labels	Sum of Sales
North	305
South	245
West	125
Grand Total	675

Now drag Product into Columns. The same totals split into a grid, one column per product: Notebook adds up to 460 and Pen to 215. You changed the layout by dragging, without writing a new formula.

Change the calculation

For numbers, a PivotTable adds them up by default. For text, it counts them. You can change the calculation for any value field:

Click the arrow beside the field name in the Values area, and choose Value Field Settings.
On the Summarize Values By tab, choose a calculation, such as Average or Count.
Click OK.

You can also select a value in the PivotTable, go to the PivotTable Analyze tab, and click Field Settings in the Active Field group.

With Average instead of Sum, the West region shows 62.5, which is its two sales (80 and 45) divided by two. Count shows how many sales each region has: North 3, South 3 and West 2. Use Count when you want to know how many records there are, not how much they add up to.

Keep the PivotTable up to date

A PivotTable is a snapshot. If you edit, add or delete rows in the source data, the PivotTable does not change until you refresh it.

To refresh one PivotTable, right-click a cell inside it and choose Refresh.
To refresh every PivotTable in the workbook, click any cell in a PivotTable, go to the PivotTable Analyze tab, click the arrow under Refresh, and choose Refresh All.

Check the totals with formulas

A PivotTable is easy to trust once you have checked a total yourself. A SUMIF formula gives the same answer as the pivot, so it is a good way to confirm the numbers. Each example below uses the sales list above, in columns A to C.

The formula below adds up the North sales. It should match the pivot total of 305:

F2=SUMIF(A2:A9,"North",C2:C9)

	A	B	C	F
1	Region	Product	Sales	North total
2	North	Notebook	120	305
3	South	Pen	60
4	North	Pen	75
5	West	Notebook	80
6	South	Notebook	150
7	West	Pen	45
8	North	Notebook	110
9	South	Pen	35
10

Result: 305. SUMIF adds the Sales only where the Region is North.

To check an average, use AVERAGEIF. The West average should match the pivot:

F2=AVERAGEIF(A2:A9,"West",C2:C9)

	A	B	C	F
1	Region	Product	Sales	West average
2	North	Notebook	120	62.5
3	South	Pen	60
4	North	Pen	75
5	West	Notebook	80
6	South	Notebook	150
7	West	Pen	45
8	North	Notebook	110
9	South	Pen	35
10

Result: 62.5. Two West sales, 80 and 45, average 62.5.

Counting the Notebook rows is a quick check on a Count pivot. Put Product in Rows and Count of Sales in Values, and the Notebook line should show 4:

F2=COUNTIF(B2:B9,"Notebook")

	A	B	C	F
1	Region	Product	Sales	Notebook count
2	North	Notebook	120	4
3	South	Pen	60
4	North	Pen	75
5	West	Notebook	80
6	South	Notebook	150
7	West	Pen	45
8	North	Notebook	110
9	South	Pen	35
10

Result: 4. Four rows say Notebook.

Finally, the grand total at the bottom of the pivot should equal the sum of the whole column:

F2=SUM(C2:C9)

	A	B	C	F
1	Region	Product	Sales	Grand total
2	North	Notebook	120	675
3	South	Pen	60
4	North	Pen	75
5	West	Notebook	80
6	South	Notebook	150
7	West	Pen	45
8	North	Notebook	110
9	South	Pen	35
10

Result: 675. The regions add up to 305, 245 and 125.

If a SUMIF check and the pivot disagree, the cause is almost always a stale pivot (refresh it) or a value stored as text (see the mistakes below).

Common mistakes

Forgetting to refresh. The numbers in a PivotTable stay the same until you refresh it, even after the source data changes.
Sum shows as Count. Excel counts a field by default when it contains text. A number stored as text, such as a value imported with a leading apostrophe or a stray space, is treated as text. Convert the column to numbers in the source data, then refresh.
Blank rows or a second header row. A blank row can split the list into pieces, and a second header row becomes a fake record. Remove both before you insert the PivotTable.
Expecting the source to change. Sorting, filtering or dragging fields in a PivotTable does not change your original data. Only editing the source cells does that.
Building on a range that does not grow. If you add rows below a plain range, the PivotTable will not include them. Use an Excel table, or select the new range and change the source.

Frequently asked questions

Do I need formulas to create a PivotTable? No. You build it by choosing fields. Formulas are useful only for checking the results.

How do I refresh a PivotTable in Excel? Right-click a cell inside it and choose Refresh. To refresh every PivotTable in the workbook, use PivotTable Analyze, then Refresh, then Refresh All.

What is the difference between a PivotTable and SUMIF? SUMIF gives one total from a formula, which updates automatically when the data changes. A PivotTable builds a whole summary table at once and lets you rearrange it, but you refresh it to update it. For one total in one cell, SUMIF is simpler. For many totals that you want to rearrange, use a PivotTable. See SUMIF for the formula version.

Why does my PivotTable count my sales instead of adding them up? At least one value in the field is text, or the field has a non-numeric value such as a dash. Fix the source data so every value is a number, refresh the PivotTable, and check that the field is set to Sum in Value Field Settings.

Can I sort or filter a PivotTable? Yes. Click the arrow beside Row Labels or Column Labels to sort the items or to tick only the ones you want to show. This changes only the report, not the source data.

Can I count how many rows each category has? Yes. Use Count in Value Field Settings. Use COUNTIF when you want the same count as a formula. See COUNTIF.

Practice building PivotTables

Reading the steps is only the start. The PivotTable basics lesson gives you a sales list to summarize, and checks your PivotTable as you build it. When you are ready for more, the more PivotTables lesson covers the next steps.

For the formulas that check your totals, see the guides to SUMIF, AVERAGEIF and COUNTIF. If you are deciding between SUMIF and SUMIFS, read SUMIF vs SUMIFS: what's the difference?.

Practise it: PivotTable basics

Type the formulas yourself in a real spreadsheet, with instant feedback on every answer. Your first five lessons are free.

Start the lesson