How to Create a Pivot Table in Excel (Step-by-Step Guide)

You’ve got a spreadsheet with hundreds of rows of sales, expenses or survey answers, and someone wants a summary by tomorrow. Adding it all up with SUM formulas would take forever. That’s exactly the job a pivot table does in a few clicks. This guide shows you how to create a pivot table in Excel step by step, even if you’ve never touched one before.

The steps below work in Excel for Microsoft 365, Excel 2021 and Excel 2019 on Windows. Mac and Excel for the web use the same ideas, though a few button names are in slightly different spots.

What a pivot table does (in plain words)

A pivot table takes a long list of data and summarizes it into a small table you can rearrange by dragging fields around. You don’t write formulas. You tell Excel which column to group by, which column to add up, and it does the math.

For example, if you have a list of orders with columns for Date, Region, Product and Amount, a pivot table can tell you in seconds:

• Total sales per region
• Total sales per product in each region
• Sales per month
• Which product sold the most

Before you start: get your data ready

Pivot tables are picky about how the source data looks. Spend two minutes on this and you’ll avoid most errors later.

• One header row. Every column needs a short, unique name in the first row (Date, Region, Product, Amount).
• No blank rows or columns in the middle of your data.
• No merged cells. Unmerge them and fill in the values.
• One type of data per column. Don’t mix numbers and text in the Amount column, and keep dates as real dates, not text.

A smart extra step: click anywhere in your data and press Ctrl + T to turn it into an Excel Table. When you add new rows later, the pivot table picks them up automatically after a refresh. Without this, you’d have to change the source range by hand.

How to create a pivot table in Excel step by step

1. Click any cell inside your data.
2. Go to Insert > PivotTable. In newer versions you may see a small menu; pick From Table/Range.
3. Check that the range or table name in the dialog is correct.
4. Choose New Worksheet (the cleanest option) and click OK.
5. A blank pivot table appears on the left and the PivotTable Fields pane opens on the right.

That pane is where everything happens. At the top you’ll see a list of your column names. At the bottom there are four boxes: Filters, Columns, Rows and Values.

Building your first summary

Let’s say you want total sales by region.

1. Drag Region into the Rows box.
2. Drag Amount into the Values box.

Done. Excel lists each region once and shows the total next to it, plus a Grand Total at the bottom.

Want to split it further? Drag Product into Columns. Now you have a grid with regions down the side and products across the top. Drag Product to Rows instead, under Region, and you get a nested list. Moving fields around like this is the whole point, so experiment. Nothing you do here changes your original data.

What each box does

• Rows: the groups listed down the left side.
• Columns: the groups spread across the top.
• Values: the numbers being calculated (sum, count, average).
• Filters: a drop-down above the table that limits what’s shown, like one year or one salesperson.

Changing Sum to Count, Average or Max

By default, Excel adds up number fields. If you drop a text field into Values, it counts them instead. To change this:

1. Click the field in the Values box (for example, Sum of Amount).
2. Choose Value Field Settings.
3. Pick Count, Average, Max, Min or another option.
4. While you’re there, click Number Format and choose Currency or Number with a thousands separator so the totals are easy to read.

You can also show values as a percentage. In the same window, open the Show Values As tab and pick % of Grand Total. That’s handy when you want to see which region brings in the biggest share.

Grouping dates by month, quarter or year

If you drag a Date column into Rows, recent versions of Excel often group it by years, quarters and months on their own. If yours shows every single day instead:

1. Right-click any date in the pivot table.
2. Choose Group.
3. Select Months and Years (select both, or months from different years will be merged together).
4. Click OK.

If Group is grayed out or gives an error, at least one cell in the date column is text or blank. Fix those cells in the source data and refresh.

Refreshing the pivot table when data changes

This catches almost everyone the first time: pivot tables don’t update by themselves. After you edit or add data, right-click the pivot table and choose Refresh, or go to Data > Refresh All.

If you added rows below your data and they don’t appear after refreshing, your source wasn’t set up as a Table. Go to PivotTable Analyze > Change Data Source and extend the range, or convert the data with Ctrl + T and point the pivot table to it.

Adding slicers for easy filtering

Slicers are clickable buttons that filter your pivot table. They’re much friendlier than drop-down filters, especially if you’re sharing the file with someone who doesn’t use Excel much.

1. Click inside the pivot table.
2. Go to PivotTable Analyze > Insert Slicer.
3. Tick the fields you want buttons for, such as Region or Product.
4. Click a button to filter. Hold Ctrl to pick several.

If you also want a chart, use PivotTable Analyze > PivotChart. The chart follows the pivot table, so filters and slicers change both at once.

Common pivot table problems and quick fixes

• “The PivotTable field name is not valid”: one of your columns has an empty header. Give every column a name.
• Numbers are counted instead of summed: the column contains text or blank cells. Clean them up, refresh, then switch the field back to Sum.
• You see (blank) in the table: there are empty cells in the source. Fill them in or filter (blank) out.
• The layout looks cramped: go to Design > Report Layout > Show in Tabular Form for a cleaner look with each field in its own column.

Once you’re comfortable with pivot tables, the next skill worth learning is pulling matching data from another sheet. Our guide on how to use VLOOKUP and XLOOKUP in Excel walks you through it. And if you’re looking for a free spreadsheet program instead of Excel, check our list of free alternatives to Microsoft Office.

FAQ

Do pivot tables change my original data?

No. A pivot table only reads your data and builds a separate summary. You can delete the pivot table sheet at any time and your original list stays exactly the same.

Why doesn’t my pivot table show new data?

Pivot tables don’t update automatically. Right-click the table and choose Refresh. If new rows still don’t show up, the source range doesn’t include them, so update it under PivotTable Analyze > Change Data Source.

Can I make a pivot table in Google Sheets?

Yes. In Google Sheets, select your data and go to Insert > Pivot table. The editor works a little differently, but the ideas of rows, columns, values and filters are the same.

What’s the difference between a pivot table and a regular table?

A regular Excel Table (Ctrl + T) is your raw data with formatting and filters. A pivot table is a summary built from that data. Most people use both: a Table to store the data and a pivot table to analyze it.