How to Make a Pivot Table in Excel From a Messy List

A PivotTable is the closest thing Excel has to a magic trick. You hand it a long, messy list of orders, enquiries or social posts, and in a few clicks it tells you which product sold most, which month was busiest or which platform brought in the most leads. No formulas to write.

Excel spells it PivotTable, as one word, so that's what you'll see on the menus. Here's how to build one from scratch, how the four boxes in the field pane work, and the refresh step that catches almost everyone out.

Getting your data ready for an Excel PivotTable

Microsoft says your data should be organised in columns with a single header row. In plain terms, that means:

• One row per record, such as one order or one enquiry.
• A short, unique heading at the top of every column, like Date, Product, Channel and Quantity.
• No merged cells in the header and no gaps in the middle of the block.

Get this right and the rest is easy. Most PivotTable headaches trace back to a header row that's split over two lines or a column with no heading.

Inserting a PivotTable in Excel for Windows

1. Click any cell in your data, or select the whole block.
2. Go to the Insert tab and select PivotTable.
3. Choose where it should go: New Worksheet keeps things tidy, or Existing Worksheet if you want it beside your data.
4. Select OK. An empty PivotTable appears, with the PivotTable Fields pane on the right.

Building the PivotTable layout in the fields pane

The fields pane lists every column heading from your data. Underneath are four areas: Filters, Columns, Rows and Values. This is where the work happens.

Tick a field's box and Microsoft says Excel places it for you: non-numeric fields go to Rows, date and time fields go to Columns, and numeric fields go to Values. You can drag any field from one area to another to change the layout.

Area	What it does	Example
Rows	Lists each item down the side	Each product name
Columns	Spreads items across the top	Each month
Values	The numbers being totalled or counted	Quantity sold
Filters	Narrows the whole table	One sales channel only

A simple first PivotTable: drag Product into Rows and Quantity into Values. You'll instantly see a total for each product. Then drag Channel into Columns to split each total by where it came from.

Changing SUM to COUNT or average in an Excel PivotTable

Microsoft says fields in the Values area show as SUM by default. If Excel thinks your data is text, it shows COUNT instead, which is often the first sign that some of your numbers are stored as text.

To change it, select the arrow next to the field in the Values area, choose Value Field Settings, and pick a different option under Summarize Values By. COUNT is handy for questions like how many enquiries came from each platform, where you're counting rows rather than adding numbers.

There's also a Show Values As tab in the same settings. Microsoft gives the example of showing a value as a share of the grand total, which is great for seeing which product makes up the biggest slice.

Refreshing an Excel PivotTable after new data

This is the step everyone forgets. A PivotTable doesn't update itself when you add new rows to your source data. Microsoft's fix is simple: right-click anywhere in the PivotTable and select Refresh.

If you've got several PivotTables in the workbook, go to the PivotTable Analyze tab, select the arrow under Refresh and choose Refresh All. I'd make this a habit every time you open a report you share with a client or your team.

Making a PivotTable in Excel for the web

In the browser, Insert then PivotTable opens a pane instead of a pop-up box. You'll see an option to create your own PivotTable on a new sheet or an existing one, plus some recommended PivotTables. Microsoft says the recommended ones are only available with certain Microsoft accounts, so if you don't see them, build your own as above.

On a Mac, Microsoft notes that PivotTables work a little differently by platform, so the menus may not match these Windows steps exactly. Look for PivotTable on the Insert tab.

PivotTable ideas for small business owners and creators

• Orders by product and month, to see what sells when.
• Enquiries by source, to see whether Instagram, email or referrals bring in the most leads.
• Posts by platform and content type, with average engagement in Values.
• Survey or poll answers by option, counted rather than summed.

That last one is a lovely use for quick audience research. If you've gathered answers with a quick poll in Messenger, copy the results into Excel and let a PivotTable count them for you. And if the finished summary is going into a branded report, clearing an image background in Canva helps a screenshot of it sit neatly on the page.

If your Excel PivotTable isn't working

1. New rows missing? Right-click the PivotTable and select Refresh.
2. Values showing COUNT instead of SUM? Some numbers are probably stored as text. Fix the source cells, then refresh.
3. A field called Column1 or blank? A heading is missing in your source data.
4. Fields pane disappeared? Click inside the PivotTable and it should come back.

Frequently asked questions

How do I make a pivot table in Excel?

Click a cell in your data, go to Insert and select PivotTable, choose New Worksheet or Existing Worksheet, then select OK. Tick fields in the fields pane to build the layout.

Why doesn't my PivotTable show new data?

PivotTables don't update by themselves. Right-click inside the PivotTable and select Refresh, or use Refresh All on the PivotTable Analyze tab.

Why does my PivotTable count instead of sum?

Microsoft says Values show as SUM by default but switch to COUNT when Excel reads the data as text. Check your numbers aren't stored as text.

What layout does my data need for a PivotTable?

Microsoft says the data should be in columns with a single header row. Give every column a short, unique heading.

Can I make a PivotTable in Excel for the web?

Yes. Insert then PivotTable opens a pane where you can create your own on a new or existing sheet.