How to Create a Pivot Table in Excel

A pivot table is one of the most powerful features in Excel, and it’s much easier to use than most people expect. It lets you take a large flat list of data — sales records, customer orders, expense logs — and instantly summarise it into a clear report without writing a single formula. You can total sales by product, count orders by month, or find your top-performing regions in seconds. This guide walks through everything from preparing your data to building, filtering and refreshing a pivot table.

What a Pivot Table Does and When to Use It

A pivot table reorganises your raw data into a summary view. Instead of scrolling through thousands of rows trying to add up figures manually, you drag fields into a layout and Excel does the calculating instantly.

Common uses include:

• Summarising total sales by product, region, or salesperson
• Counting the number of orders by category or status
• Finding monthly or quarterly totals from a full year of transactions
• Comparing performance across different time periods
• Identifying duplicates or unusual values in a dataset

If you find yourself writing SUM formulas or manually copying and pasting subtotals, a pivot table will do the job faster and more accurately.

Preparing Your Data

Pivot tables are picky about data format. Before you insert one, make sure your data meets these requirements:

Headers in the first row

Every column must have a header in row 1. The header is used as the field name in the pivot table. If any column is missing a header, Excel will either refuse to create the pivot table or produce incorrect results.

No blank rows or columns

Blank rows or columns in the middle of your data will cause Excel to treat the data as separate tables. Remove any gaps before proceeding.

Consistent data types in each column

A column should contain either all numbers, all dates, or all text — not a mixture. If a date column contains some text entries (like “TBC”), Excel can’t group it by month. Numbers stored as text also cause problems with sum calculations.

Each row is one record

Your data should be in a flat list format — one row per transaction, order, or event. Avoid merged cells, subtotal rows, or any formatting that structures the data visually rather than logically.

Inserting a Pivot Table

1. Click anywhere inside your data table
2. Go to the Insert tab on the ribbon
3. Click PivotTable
4. Excel will automatically detect your data range — check it looks correct
5. Choose whether to place the pivot table on a new worksheet (recommended) or an existing one
6. Click OK

You’ll now see a blank pivot table on the left and the PivotTable Fields pane on the right.

Understanding the Field List

The field list shows all the column headers from your source data. Below that are four areas where you drag fields to build your report:

• Rows — values that appear as row labels down the left side (e.g. product names, salesperson names)
• Columns — values that appear as column headers across the top (e.g. months, regions)
• Values — the numbers you want to calculate (e.g. sales amount, quantity sold)
• Filters — a drop-down filter that applies to the whole pivot table (e.g. filter by year or department)

Building Your First Report

Let’s say you have a sales spreadsheet with columns for Date, Salesperson, Product, and Revenue. To see total revenue by salesperson:

1. Drag Salesperson to the Rows area
2. Drag Revenue to the Values area

Excel instantly shows you the total revenue for each salesperson. To also break it down by product, drag Product to the Columns area.

Changing the Value Function

By default, Excel uses Sum for numeric fields and Count for text fields. You can change this:

1. Click the field name in the Values area
2. Select Value Field Settings
3. Choose from Sum, Count, Average, Max, Min, or other options

For example, to see the average order value instead of the total, change Sum to Average. To count how many orders each salesperson made, place a text field (like Order ID) in Values and it will default to Count.

Grouping Dates by Month or Year

If your data includes a date column, you can group it automatically rather than seeing every individual date as a separate row:

1. Drag your date field to the Rows area
2. Right-click any date in the pivot table
3. Select Group
4. Choose Months, Quarters, Years, or a combination
5. Click OK

This is one of the most useful pivot table features — it turns a year’s worth of daily data into a clean monthly summary in seconds. Note that all dates in the column must be valid Excel dates for this to work. Text dates won’t group.

Adding Filters and Slicers

To filter the whole pivot table by a particular field, drag that field to the Filters area. A drop-down appears above the table where you can select one or more values.

For a more visual filtering experience, use slicers:

1. Click anywhere in the pivot table
2. Go to PivotTable Analyze (or Options in older versions) on the ribbon
3. Click Insert Slicer
4. Tick the fields you want to filter by
5. Click OK

Slicers appear as button panels on the sheet. Clicking a button filters the pivot table instantly — much easier to use than drop-downs, especially for presentations or shared dashboards.

Refreshing When Source Data Changes

Pivot tables do not update automatically when you change the source data. After adding or editing rows, you need to refresh manually:

• Right-click anywhere in the pivot table and select Refresh
• Or go to PivotTable Analyze > Refresh on the ribbon

If you’ve added new rows beyond the original data range, you may also need to update the data source. Go to PivotTable Analyze > Change Data Source and expand the range. To avoid this entirely, format your source data as an Excel Table (Insert > Table) before creating the pivot table — Excel Tables expand automatically, so new rows are always included when you refresh.

Common Mistakes to Avoid

• Blank column headers: Always check every column has a header before inserting a pivot table
• Mixed data types: Numbers stored as text won’t sum correctly — check for warning triangles in cells
• Forgetting to refresh: If your figures look wrong after editing source data, refresh the pivot table
• Editing cells inside the pivot table: Don’t type directly into a pivot table — it can corrupt the layout. Always edit the source data and refresh
• Merged cells in source data: Unmerge all cells before using data as a pivot table source

Pivot tables take a little practice to get used to, but once you’ve built one or two, they become the first tool you reach for whenever you need to make sense of a large dataset.