Excel pivot tables for beginners: summarise data in minutes

By Axyra Nova Editorial · 10 October 2026 · 5 min read · Data and AI

If you have ever needed to answer a question like "how much did we sell in each region last quarter?" from a spreadsheet with thousands of rows, a pivot table is the tool for the job. Pivot tables summarise data by category in a few clicks, without writing formulas, and you can rearrange them instantly to look at the data from a different angle.

This guide explains how to use Excel pivot tables, from preparing your data to building summaries you can share.

What a pivot table does

A pivot table takes a list of records, such as sales, expenses or survey responses, and summarises it. You choose:

Rows: the categories to list down the side, such as product or region.
Columns: categories to spread across the top, such as month.
Values: what to calculate, such as total sales or number of orders.
Filters: which records to include.

Microsoft's guide to creating a PivotTable shows the steps in each version of Excel.

Step 1: Prepare your data

Pivot tables work best with clean, consistent data. Before building one:

One header row with a unique name for each column.
One record per row, with no blank rows or subtotals in the middle.
Consistent values: "Lagos", "lagos" and "Lagos " will be treated as different categories.
Proper data types: dates stored as dates, numbers as numbers.
No merged cells.

Convert your data into an Excel table (Insert, then Table). Tables expand automatically as you add rows, so your pivot table can include new data when refreshed.

Step 2: Create the pivot table

Click anywhere in your table.
Choose Insert, then PivotTable.
Select a new worksheet as the destination.
In the field list, drag fields into Rows, Columns, Values and Filters.

For example, drag Region to Rows and Amount to Values. Excel shows total sales by region immediately.

Step 3: Choose the right calculation

By default, numbers are summed and text is counted. You can change this in Value Field Settings:

Sum for totals.
Count for the number of records.
Average for typical values.
Max and Min for extremes.

You can also show values as a percentage of the total, which is useful for comparing shares.

Step 4: Group dates and numbers

Pivot tables can group dates into months, quarters and years. Drag a date field into Rows, then right-click and choose Group. Numbers can be grouped into ranges too, such as order sizes in bands.

Step 5: Filter and slice

Filters limit the pivot to certain records, such as one year or one product line.
Slicers are clickable buttons that filter the pivot visually, making reports easier for others to use.
Timelines are slicers designed for dates.

Step 6: Refresh when data changes

Pivot tables do not update automatically when the source data changes. Use Refresh (or Refresh All) after adding or editing data. Using an Excel table as the source ensures new rows are included.

Step 7: Present the results

Apply a clear pivot table style.
Format numbers with thousand separators and currency where relevant.
Rename headers to plain language, such as "Total sales" instead of "Sum of Amount".
Add a pivot chart if a visual helps.

Common mistakes

Inconsistent category names, which split totals across near-duplicates.
Blank cells in number columns, which can change how values are counted.
Forgetting to refresh after changing data.
Including total rows in the source data, which double-counts.
Text stored as numbers, which prevents sums.

Spend time cleaning data first. Most pivot table problems are really data problems.

A worked example

A freelance designer kept a spreadsheet of every invoice: date, client, service type, amount and whether it was paid. At tax time, she wanted to know her income by month, which services earned most, and which clients still owed money.

She converted the list into an Excel table and built three pivot tables:

Income by month: invoice date grouped by month in Rows, amount summed in Values.
Income by service: service type in Rows, amount summed and shown as a percentage of the total.
Unpaid by client: client in Rows, amount in Values, filtered to unpaid invoices.

The results took minutes and showed that one service type earned far more per hour than the others, which shaped her pricing for the next year. Our guide to freelance pricing covers how to use that kind of insight.

Workflow tips

Keep the source data on its own sheet, and put each pivot table on a separate sheet with a clear name.
Write down the question each pivot answers in a cell above it, so others understand its purpose.
Double-click any value in a pivot table to see the underlying rows. It is the quickest way to check a surprising number.

Beyond the basics

Once comfortable, explore:

Calculated fields for simple calculations inside the pivot.
Power Query for cleaning and combining data before it reaches the pivot.
The Data Model for building pivots across related tables.

Pivot tables in Google Sheets

Google Sheets also has pivot tables with similar concepts: rows, columns, values and filters. Google's help on creating and using pivot tables explains the steps. If you work in Sheets, the Google Sheets data analysis course on our learning platform is a good next step.

Where pivot tables fit in a data career

Pivot tables are often the first analysis tool people learn, and they remain useful even for experienced analysts. Next steps include SQL for data analysis for larger datasets and Power BI dashboards for interactive reporting. Our learning platform lists free courses in a sensible order.

Frequently asked questions

Do pivot tables change my original data?

No. They read from the source data and display a summary elsewhere.

Why does my pivot table count instead of sum?

The column probably contains text or blank cells. Check that values are stored as numbers, then change the calculation to Sum.

Can I use pivot tables with data from several sheets?

Yes, using the Data Model or Power Query to combine them, though it is simpler to combine the data into one table first.

Share the workbook, or copy the summary as values into a report. Slicers make shared workbooks easier for others to explore.