Excel pivot tables for beginners: a step-by-step guide with a worked example

Rootlet

Tue, 13 Oct 2026

A pivot table turns a long list of rows into a short summary in a few clicks, with no formulas. If you've ever spent an afternoon adding up a spreadsheet by hand, it's one of the most useful Excel skills you can learn. This guide walks you through building your first pivot table step by step, using a small set of sample data you can copy, so you can check your results against ours as you go.

What a pivot table does

Imagine a list of every sale a small shop made in a month. A pivot table can answer questions like "how much did each region sell?" or "which product sold best in each region?" by grouping the rows and adding them up for you. Nothing in your original data changes, and you can rearrange the summary as often as you like.

The steps below are for Excel for Windows (Microsoft 365), checked against Microsoft's own guide to creating a PivotTable. Excel calls them "PivotTables". Menus on a Mac or in older versions can look slightly different. There's a section for Google Sheets at the end.

Step 1: Get your data in shape

Pivot tables are only as good as the data underneath. Microsoft's guidance is that your data should be in columns with a single header row. In practice that means:

• One header row, with a clear, different name at the top of every column.
• No merged cells and no double header rows.
• One row per record, such as one row per sale.
• Numbers stored as numbers. If Excel treats a number column as text, the pivot table will count it instead of adding it up.

Here's the sample data we'll use. It's made-up sales for a small stationery shop in March 2026, with Sales in pounds. Copy it into a new worksheet starting in cell A1:

Tip: click any cell in the data and choose Insert > Table. Microsoft recommends this (it isn't required), because a table grows automatically when you add rows, so your pivot table picks them up when you refresh.

Step 2: Insert the pivot table

1. Click any cell inside your data.
2. Go to Insert > PivotTable.
3. Check the table or range Excel has selected covers all your data, including the header row.
4. Choose New Worksheet so the summary sits on its own sheet.
5. Click OK.

You'll see an empty pivot table on a new sheet and the PivotTable Fields pane on the right, listing your column headers.

Step 3: Understand the four areas

At the bottom of the PivotTable Fields pane are four boxes. Microsoft describes them like this:

• Rows: the groups listed down the left side, such as each region.
• Columns: the groups listed across the top, such as each product.
• Values: the numbers being summarised, such as sales.
• Filters: a filter above the table, so you can show just part of your data.

You add a field by ticking it or dragging it into a box, and remove it by dragging it back out.

Step 4: Build your first summary

Let's answer "how much did each region sell?"

1. Drag Region into Rows.
2. Drag Sales into Values.

Excel adds up number fields by default, so you should see this:

Twelve rows have become four, and you can see straight away that South sold the most.

Add a second dimension

Now drag Product into Columns. Your pivot table splits each region's sales by product:

This shows something the first table hid: Notebooks were the best seller overall, but in the Midlands, Pen sets sold more.

Filter the summary

Drag Product back out of Columns and into Filters instead. A drop-down appears above the table. Choose Notebook and the table shows Notebook sales only: Midlands 40, North 135, South 150. Set it back to (All) when you're done.

Step 5: Change how the numbers are calculated

Count instead of sum

To see how many orders each region had, rather than their value, click the small arrow next to Sum of Sales in the Values box, choose Value Field Settings, and under Summarize Values By pick Count. In our data each region had 4 orders. Average, Max and Min are in the same list.

Show each region's share of the total

Switch back to Sum, then right-click any number in the Sales column, choose Show Values As, then % of Grand Total, as described in Microsoft's guide to value calculations. If you want to keep the totals in pounds and see the percentage next to them, drag Sales into Values a second time and apply the percentage to the copy only:

Microsoft suggests waiting until you've finished setting things up before renaming fields like "Sum of Sales", because changing the calculation changes the name.

Step 6: Refresh when your data changes

A pivot table doesn't update by itself when you edit the original data. After any change, right-click inside the pivot table and choose Refresh. If you have several pivot tables, use PivotTable Analyze > Refresh > Refresh All. Forgetting to refresh is an easy way to end up with out-of-date numbers.

Common problems and quick fixes

• It says "Count of Sales" instead of "Sum of Sales". Excel thinks the column is text, often because of a stray word or blank-looking cell. Fix the data, refresh, then change the calculation back to Sum.
• New rows aren't included. Your pivot table is based on a fixed range. Turn your data into a table (Insert > Table) so it grows automatically, then refresh.
• The same item appears twice. Check for spelling differences or extra spaces, such as "North" and "North ". The pivot table treats them as different groups.

Doing the same in Google Sheets

Google Sheets works the same way with slightly different menus. Google's help page describes it like this: select your data (every column needs a header), go to Insert > Pivot table, then in the side panel click Add next to Rows, Columns or Values to choose your fields. With the sample data above, you'll get the same totals.

Practise and keep going

The best way to make this stick is to use it on your own data straight away: a household budget, sales from a side business, or a report you build by hand at work. Try asking it three different questions and rearranging the fields to answer each one.

Want to go further with spreadsheets and other practical skills? Browse the courses on Rootlet.

Sources

• Microsoft Support. Create a PivotTable to analyze worksheet data.
• Microsoft Support. Use the Field List to arrange fields in a PivotTable.
• Microsoft Support. Show different calculations in PivotTable value fields.
• Google Docs Editors Help. Create and use pivot tables.