Pivot Tables in Excel: A Beginner's Guide

A pivot table turns a long list of rows into a short summary, like total sales by region, in a few clicks and without formulas. You choose Insert > PivotTable, then drag columns into areas called Rows, Columns and Values. This pivot table Excel guide is for complete beginners, and it uses one small sales table from start to finish.

What is a pivot table?#

A pivot table (or "pivot") is a summary report that Excel builds from a table of data. You tell it what to group by and what to add up, and it does the maths.

Here is the start of a sales table with 20 orders:

To answer "How much did each region sell?" with formulas, you would type a list of regions and a SUMIFS for each one. With a pivot, you tick two boxes. When the question changes to "How many units of each product?", you drag a different field, and the answer is ready in seconds.

Get your data ready first#

Pivot tables are only as good as the data under them. Check four things before you start:

1. One header row. Each column needs a short, unique name in row 1, like Region or Sales.
2. One record per row. Each row is one order, one sale or one person.
3. No blank rows or columns inside the data. A blank row can make Excel stop the range early.
4. One type of data per column. A Sales column should hold only numbers, not text like "n/a".

A good habit is to turn the range into an Excel table first: click a cell and press Ctrl+T (Mac: ⌘T). A pivot built on a table picks up new rows when you refresh it.

How to create a pivot table#

Insert the pivot from the Insert tab:

1. Click any cell inside your data.
2. Go to Insert > PivotTable. In Microsoft 365, choose From Table/Range if a menu opens.
3. In the dialog box (PivotTable from table or range, or Create PivotTable in older versions), check that the range covers the whole table, header row included, like Sales!$A$1:$E$21.
4. Keep New Worksheet selected and click OK.

Excel adds a new sheet with an empty pivot and opens the PivotTable Fields pane on the right. The pane only shows while a cell of the pivot is selected. If it disappears, click inside the pivot again.

Pivot table Excel basics: rows, columns, values and filters#

Each column of your data becomes a field in the pane. You put fields into four areas:

You can tick a field's box or drag it into an area. When you tick, Excel guesses: text fields go to Rows and number fields go to Values. If a field lands in the wrong place, drag it to the right box.

Example: total sales by region#

In the PivotTable Fields pane, tick Region, then tick Sales. The pivot shows:

Excel sorts the regions A to Z and adds a Grand Total. West sold the most. To sort by the numbers instead, right-click a value and choose Sort > Sort Largest to Smallest.

Now drag Product into the Columns area. Each region's total splits into Laptop, Monitor and Phone, with a total for each row and column. To show units instead, untick Sales and tick Units. Move Product to Rows and take Region out, and the pivot answers a new question: units sold per product (Laptop 17, Monitor 20, Phone 28).

How to change Sum to Count or Average#

The Values area adds numbers up by default, but it can also count, average and more:

1. In the Values area, click Sum of Sales and choose Value Field Settings.
2. Under Summarize value field by, pick Count, Average, Max or Min.
3. Click OK.

Count counts records, so "Count of Sales" is the number of orders. Average shows the average order: in this data, West's Laptop orders average $3,600. While you are in Value Field Settings, click Number Format to show currency or remove decimals.

If a number field shows Count when you expected Sum, the column probably has blanks or text in it.

Refresh a pivot table when the data changes#

A pivot doesn't update when you edit its data. Refresh it:

• Right-click anywhere in the pivot and choose Refresh.
• Or use PivotTable Analyze > Refresh (Windows shortcut: Alt+F5). The tab is called Analyze or Options in some older versions.

If you added rows below a plain range, the pivot won't see them. Use PivotTable Analyze > Change Data Source to extend the range, or build the pivot on an Excel table so it grows by itself. Our guide to pivot table subtotals, filters and % of total covers these next steps.

Pivot table or formula?#

Use a pivot table to explore data and answer new questions quickly. Use a formula like SUMIFS when you need one number in a fixed cell that is always up to date, such as on a dashboard. The exercise on this page builds West's total with SUMIFS so you can check it against the pivot. For a full comparison, read pivot table vs SUMIFS.

Pivots often go together with other data tools. You can filter the source data to look at a few rows, and use conditional formatting to highlight the biggest numbers in a report. Pivot tables are also one of the core Excel skills for finance jobs.

Practise it#

Pivot tables are a click skill, so the fastest way to learn them is to build one. In ExcelReady's Pivot tables 1 lesson, you create a PivotTable from the 20-order sales table in this guide, put Region in Rows and Sales in Values, split it by product, switch to Count and Average, and read answers off the pivot, all in a live spreadsheet in your browser. Build your first pivot table in the practice lesson.