How to Create a Pivot Table in Excel (Step-by-Step Tutorial)

By the XLsheetAI Team · Updated August 7, 2026 · 10 min read

What a pivot table actually does

A pivot table takes a long list of individual records and collapses it into a summary you choose: totals by region, averages by month, counts by product. You pick which field becomes the rows, which becomes the columns, and which number gets aggregated. Nothing in the source data changes.

That last point matters more than people expect. A pivot table is a read-only view sitting on a cached copy of your records. Rearrange it, break it, delete it, and the underlying rows stay exactly as they were. It is the safest way to explore a dataset you did not build yourself.

The sample dataset used throughout this tutorial

Every example below refers to one small sales table: eight rows with Date, Region, Salesperson, Product, Units, and Revenue. Real files have thousands of rows, but the structure is identical, and eight rows show what each pivot setting produces.

How to create a pivot table in Excel, step by step

Creating the pivot takes about thirty seconds. Preparing the data takes longer, and it is where almost every problem starts. Work through these six steps in order and the result will be correct the first time, and stay correct as rows are added.

1. Fix the headers. Row 1 must have a short, unique label in every cell above your data: Date, Region, Salesperson, Product, Units, Revenue. A single empty header cell makes Excel refuse to build the pivot at all.
2. Remove blank rows and columns inside the range. Excel decides the source range by expanding outward from your selected cell until it hits an empty row or column. A stray blank row truncates the data silently, so half your records never reach the pivot.
3. Keep one data type per column. Units and Revenue must hold numbers, not "12 units" or a stray "n/a". Date must hold real dates that right-align by default. Mixed columns are why pivots count instead of summing, and why date grouping stops working.
4. Press Ctrl+T to format the range as a Table. Click any cell in the data, press Ctrl+T, check that My table has headers is ticked, and click OK. On the Table Design tab, rename it SalesData. This step is optional but strongly recommended: a Table expands automatically when you add rows at the bottom, so the pivot picks up new data on refresh without you editing the source range.
5. Insert the pivot table. With a cell inside the Table selected, go to Insert > PivotTable (choose From Table/Range in Microsoft 365). Excel pre-fills the source with your Table name. Confirm it looks right.
6. Choose New Worksheet or Existing Worksheet. New Worksheet is the safer default, since the pivot gets room to grow. Pick Existing Worksheet only when you are building a dashboard, and leave several empty columns to its right: a pivot that expands into occupied cells throws an error instead of overwriting them.

Click OK and Excel drops an empty pivot frame on the sheet with the PivotTable Fields pane on the right. The pane lists your six field names, with four drop zones underneath. Nothing appears until you move a field into one of them.

The four field areas, explained plainly

Rows, Columns, Values, and Filters are the whole interface. Rows and Columns decide how the data is grouped, down the side and across the top. Values holds the number being calculated. Filters narrows the entire report to a subset. Drag a field into an area, or drag it back out to undo.

Two habits help. Use Rows for the field with more distinct values and Columns for the one with fewer, or the report scrolls sideways forever. And drag numeric fields into Values only: put Revenue in Rows and you get a row per distinct amount.

Not sure whether a pivot or a formula fits your question? Describe what you want in plain English, such as "total revenue by region for Q1", and XLsheetAI writes the formula for you and explains the pivot setup that gets the same answer.

Eight pivot table examples using the same data

The fastest way to learn pivot tables is to build several from one dataset and watch what each change does. Each recipe below starts from the sales Table and takes under a minute. Drag fields back out between recipes to reset.

1. Revenue by region. Region into Rows, Revenue into Values. Excel labels it "Sum of Revenue" and adds a Grand Total row. This is the default pivot, and the one worth building first on any new dataset.
2. Units by product and month. Date into Rows, Product into Columns, Units into Values. Right-click any date inside the pivot, choose Group, and select Months (add Years when the data crosses a year boundary). Recent Excel versions group dates the moment you drop the field in, so Months may already be there.
3. Top salesperson per region. Region then Salesperson in Rows, Revenue in Values. Click the filter arrow on the Row Labels header, pick Salesperson, then Value Filters > Top 10, and change the count to 1 by Sum of Revenue. Each region now shows only its strongest seller.
4. Each region's share of total revenue. Region in Rows, Revenue in Values. Right-click any number in the Values area and choose Show Values As > % of Column Total. Amounts become percentages adding to 100%. Drag Revenue into Values twice to show currency and percentage side by side.
5. Average order value. Product in Rows, Revenue in Values. Right-click a value and choose Value Field Settings > Summarize Values By > Average, setting a currency format in the same dialog. Because each source row is one order, this is average revenue per order.
6. Count of orders. Salesperson in Rows, then drag any field, Date works fine, into Values and set it to Count. Counting a text or date field counts rows. Counting a numeric field with blanks skips the blanks and undercounts.
7. Add a slicer. Click inside the pivot, go to PivotTable Analyze > Insert Slicer, and tick Region. You get clickable filter buttons whose current selection is visible at a glance, and one slicer can drive several pivots through Report Connections.
8. Add a calculated field. Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, name it "Revenue per unit", and enter =Revenue/Units. Note how it evaluates: it divides the sum of Revenue by the sum of Units for each group, not the average of each row's ratio. Calculated fields also operate on whole fields, so they cannot reference an individual pivot item such as the North row.

Refreshing data, and the gotcha nobody warns you about

Pivot tables do not update themselves. Edit a number in the source, and the pivot keeps showing the old total until you refresh it. Right-click anywhere in the pivot and choose Refresh, or use PivotTable Analyze > Refresh > Refresh All to update every pivot in the workbook at once.

This catches people out constantly, because nothing looks wrong. No error, no visual cue, just a stale number in a report someone is about to send. If a total ever disagrees with the source, refresh before debugging anything else.

The second half of the problem is the source range. A pivot built on a fixed range like A1:F9 will never see row 10, however often you refresh. You would have to open PivotTable Analyze > Change Data Source and extend the range by hand each time data arrives. That is why step 4 recommended Ctrl+T: a Table grows when rows are appended, so a refresh is all you need.

For pivots feeding a shared dashboard, open PivotTable Analyze > Options > Data and tick Refresh data when opening the file. It costs a moment at load time and removes a whole category of mistakes.

Pivot table or SUMIFS: which should you use?

Pivot tables and SUMIFS answer the same questions in different ways. A pivot is an interactive report you rearrange by dragging; SUMIFS is a formula that writes one number into one cell and recalculates instantly. Choose based on whether the output needs to be explored or embedded.

• Pivot wins for exploring. When you do not yet know which breakdown matters, dragging fields beats writing a dozen formulas. Grouping dates by month is one right-click instead of a helper column.
• Pivot wins for many-dimensional summaries. Region by product by month with subtotals and percentages takes a large block of SUMIFS to replicate, and needs rewriting whenever a new region appears.
• SUMIFS wins inside a live model. Formulas recalculate automatically, so a template, invoice, or forecast that others fill in should use SUMIFS rather than a pivot someone forgets to refresh.
• SUMIFS wins for fixed layouts. When cells must land in exact positions, a formula is predictable in a way a pivot that expands and contracts is not.

Plenty of workbooks use both: a pivot to find the pattern, then SUMIFS to pin the finished number into a report. Our Excel formulas cheat sheet covers SUMIFS syntax and the other functions worth knowing alongside pivots.

Common pivot table problems and how to fix them

Nearly every pivot table complaint traces back to the source data rather than the pivot itself. The table below lists the six that come up most often, with the cause and the fix. Work down the Cause column first, because correcting the source usually resolves several symptoms at once.

Duplicate-looking rows deserve extra attention, because they quietly split totals in two without any error appearing. If your source has repeated records rather than inconsistent labels, our guide to removing duplicates in Excel covers cleaning them before you build the pivot.

A note on Google Sheets

Google Sheets has pivot tables too: select your range and choose Insert > Pivot table. The four concepts carry over exactly, since Sheets also uses Rows, Columns, Values, and Filters. The interface differs, though. Instead of dragging fields into drop zones, you click Add beside each area in a side editor panel. Aggregation lives under "Summarize by", the equivalent of Show Values As is the "Show as" dropdown, and date grouping sits in the Rows area's "Group by" option. Sheets also updates as the source changes, so the stale-data problem largely disappears.

Bottom line

Clean the data, press Ctrl+T, then Insert > PivotTable, and drag one text field into Rows with one number into Values. That covers most real reporting needs. Everything else, grouping dates, percentages, slicers, calculated fields, is a variation on the same four areas. Refresh after edits, and build on a Table so new rows are picked up on their own.

Keep learning as you build. XLsheetAI turns plain-English questions into working Excel formulas, explains any formula you paste in, and helps you clean messy source data so your pivot tables total correctly the first time.