How to Make a Pivot Table in Excel: A Step-by-Step Beginner Guide for 2026

To make a pivot table in Excel, click any cell inside your data, choose Insert > PivotTable, pick a new worksheet, and drag one text field into Rows and one number field into Values. The summary table appears in seconds, with no formulas to write.

Here is the part most tutorials skip: when a pivot misbehaves, the pivot is almost never the problem. The source data is. If your table has one header row, no blank rows, and one kind of value per column, the five steps below just work. The menu names in this guide come from the Microsoft 365 desktop app on Windows and may differ slightly in other versions.

How do you make a pivot table in Excel, and when is it worth it?

A pivot table is a summary report that Excel builds from your data by dragging fields into place, and it recalculates instantly when you rearrange them. Your original numbers are never changed.

My rule of thumb: if you have summed the same table by region, then by month, then by salesperson, you have already done pivot-table work by hand. A formula answers one question you decided in advance. A pivot lets you keep asking.

Situation	Better tool	Why
Compare totals by region, month, rep, product	PivotTable	Drag fields to change the cut
Fill one fixed cell in a report template	SUMIFS	Cell position is fixed, updates instantly
Count how many rows per category	PivotTable	Switch the value field to Count
Pull a price or ID from another table	XLOOKUP or VLOOKUP	Pivots summarize, they do not look things up
Let a manager click through the numbers	PivotTable + slicers	Button-style filters

If the formula side is where you are stuck, the Excel SUM, SUMIF and SUMIFS guide covers the cases where a function beats a pivot, and the Excel formulas everyone should know is a good warm-up before you start auditing pivot totals.

How should you prepare your data before building a pivot table?

Pivot tables want data shaped like a database table: one header row, one record per row, one type of value per column. Microsoft’s own instructions say the same thing in its guide to creating a PivotTable from worksheet data.

I will use this small made-up sales table for the rest of the article. The figures are invented for illustration.

Date	Region	Rep	Product	Units	Sales
2026-01-05	East	Maria	Laptop	2	$2,400
2026-01-12	West	Jake	Monitor	3	$900
2026-02-03	East	Priya	Monitor	1	$300
2026-02-18	South	Maria	Laptop	1	$1,200
2026-03-07	West	Jake	Laptop	2	$2,400
2026-03-21	East	Maria	Monitor	4	$1,200

Run through this checklist before you click anything:

• The headers sit in one row, and none of them is blank.
• No blank rows or columns inside the data.
• No merged cells. A region typed once with empty cells beneath it breaks the grouping.
• Each column holds one type: do not mix “$1,200” typed as text with real numbers.
• Dates are real dates. If they hug the left edge of the cell, they are probably text.
• The range is formatted as a Table (select it and press Ctrl+T), so new rows get picked up later.

That last item saves the most grief. A pivot built on a plain range remembers the exact cells you selected. Paste next month’s rows underneath, and the pivot will happily ignore them until you fix the source.

What are the five steps to create a PivotTable?

Using the sample table above, here is the exact click path.

1. Click any single cell in the data. There is no need to select the whole range.
2. Go to the Insert tab and click PivotTable.
3. In the dialog, confirm the range (or table name) is right, choose New Worksheet, and click OK.
4. In the PivotTable Fields pane on the right, drag Region into Rows and Sales into Values.
5. Right-click a number in the Values area, choose Number Format, and apply a currency format so it reads like a report.

You should see this:

Region	Sum of Sales
East	$3,900
South	$1,200
West	$3,300
Grand Total	$8,400

If your Microsoft 365 build shows Insert > Recommended PivotTables, Excel will propose a few layouts based on your data. It is handy for a first look. I still recommend learning the manual drag, because the recommendations rarely match the exact report you need.

What do the Rows, Columns, Values and Filters areas do?

Four boxes control everything, and once you know what each one does, any table becomes a pivot candidate.

Area	What it does	Example field
Rows	Categories listed down the left	Region, Rep
Columns	Categories spread across the top	Product, Month
Values	The numbers being calculated	Sum of Sales, Count of Units
Filters	Narrows the whole report	Year, Rep

Put Region in Rows, Product in Columns, and Sales in Values, and you get a region-by-product grid. Product totals come out to $6,000 for laptops and $2,400 for monitors, which adds back up to the same $8,400 grand total as the region view.

Treat that agreement as your free sanity check. Whenever you build a pivot, build a second view and confirm the grand totals match. Dragging a field out of an area removes it, and dragging it between areas reshapes the report immediately. You cannot hurt the source data from this pane, so experiment freely.

How do you switch from Sum to Count or Average?

Click a field in the Values area, open Value Field Settings, and pick a different calculation under “Summarize values by.” The same Sales column answers three different questions depending on the setting.

Region	Sum of Sales	Count of Sales	Average of Sales
East	$3,900	3	$1,300
West	$3,300	2	$1,650
South	$1,200	1	$1,200

Sum says how much you sold, Count says how many orders there were, and Average says how big a typical order was. East leads on revenue, but West has the larger average order. You would miss that if you only looked at totals.

The Show Values As tab in the same dialog is worth a look too. It can display each region as a percentage of the grand total or as a change from the prior period, which replaces a lot of helper formulas.

Excel defaults numbers to Sum and text to Count. If you see “Count of Sales” when you expected a sum, some cells in that column are text or blank. That one detail causes more beginner confusion than anything else, and it is covered in the troubleshooting table below.

How do you group dates by month, quarter, or year?

Right-click any date in the pivot, choose Group, and select Months, Quarters, Years, or a combination. Microsoft describes the dialog in its help page on grouping and ungrouping PivotTable data.

Select both Years and Months when the data crosses a year boundary. If you pick only Months, January 2026 and January 2027 collapse into a single January row, and the totals look right but mean the wrong thing. Newer builds sometimes auto-group dates when you drop them into Rows. If you do not want that, right-click and choose Ungroup.

When the Group command is grayed out or throws “Cannot group that selection,” the date column has text entries or blanks. Clean the column so every cell is a true date, refresh, and try again.

How do slicers make a pivot table easier to use?

A slicer is a panel of buttons that filters the pivot, so people who never touch a dropdown can still explore the data. This is what turns a pivot into something a manager will actually use.

1. Click anywhere inside the PivotTable.
2. Go to PivotTable Analyze > Insert Slicer.
3. Tick a field such as Rep and click OK.
4. Click the buttons to filter. Hold Ctrl to select several.

A slicer controls only the pivot you built it from. If two pivots should respond to the same buttons, right-click the slicer, choose Report Connections, and tick the second pivot. Microsoft has more on using slicers to filter data.

How do you refresh a pivot table in Excel?

Click inside the pivot and choose PivotTable Analyze > Refresh, or right-click and select Refresh. If the workbook has several pivots, use Refresh All. A PivotTable is a snapshot, so editing the source does not change it until you refresh. Microsoft’s page on refreshing PivotTable data lists the options, including refreshing when the file opens, which you set under PivotTable Options > Data.

If you refreshed and your new rows still did not show up, the source range is the issue. Either extend it through PivotTable Analyze > Change Data Source, or better, convert the source to a Table with Ctrl+T so it grows by itself.

One small annoyance: refresh can reset your column widths. Under PivotTable Options, uncheck “Autofit column widths on update” and the layout will stay put.

Why is my Excel pivot table not working?

Check the source data first. Nine times out of ten the fix is in the cells, not in the pivot.

Symptom	Likely cause	Fix
”PivotTable field name is not valid”	Blank or merged header cell	Fill every header, unmerge cells
Shows Count instead of Sum	Text or blanks in the value column	Convert to numbers, fill blanks with 0
Sum shows 0	Numbers stored as text	Convert text to numbers, refresh
Cannot group dates	Text or blank cells in date column	Make every entry a real date
”East” and “East ” listed twice	Trailing spaces or typos	Clean with TRIM, standardize spelling
New rows missing	Fixed source range	Convert to a Table or change data source
Some rows labeled (blank)	Merged cells in the source	Unmerge and fill the empty cells

Numbers stored as text often show a small green triangle in the corner of the cell. Selecting the column and running Data > Text to Columns > Finish converts most of them in one move. If your formulas, not your pivots, are misbehaving, the guide to fixing Excel functions that are not working walks through similar causes.

A failure case: the pivot that would not add up

This is a composite of a situation I have seen many times, not a single real file. A monthly sales pivot listed “East” on two lines, and the two lines added up to $200 less than the grand total. Two things were wrong. A few region cells had a trailing space, so Excel treated them as a separate category. And several Sales cells had been pasted from an email and were text, so they were silently left out of the sum.

Cleaning the region column with TRIM, converting the Sales column to numbers, and refreshing made the totals agree. The lesson is to scan the source columns before you trust any pivot, and to cross-check the grand total against a plain SUM of the source column.

Where should you go after learning pivot tables?

A pivot is a starting point. Once your summary is right, you can add a PivotChart for a quick visual, or feed the numbers into a tracker like the one in the Excel budget spreadsheet template. If you are thinking about analysis as a career skill, the data analyst roadmap shows where pivots sit among the tools hiring managers expect, and a handful of keyboard shortcuts will make all this clicking faster.

My honest advice for beginners comes down to three habits. Format the source as a Table, keep every header filled, and verify the grand total a second way. Do those and most “my pivot is broken” problems disappear before they start.

This article is general information about Excel for US users of Microsoft 365 and recent desktop versions. The sample data is invented. Menu names and behavior vary by version and platform, so test on a copy of your workbook before relying on results for important documents.