How to Make a Pivot Table in Excel: 7 Steps, From Raw Rows to Refreshable Summary

You make a pivot table by selecting a clean data range, clicking Insert > PivotTable, choosing where the output goes, then dragging fields into the Rows, Columns, Values, and Filters areas of the PivotTable Fields pane. The whole build takes under a minute once the source is formatted as a proper table. The real work is source hygiene and knowing how to refresh and reconfigure — those two skills decide whether the summary stays correct a week later.

Before You Insert Anything: Format the Source Data

Excel can't build a pivot table from a range with blank header cells, merged cells, or subtotal rows buried inside the data. Every column needs a unique one-row header (Region, Rep, Month, Amount), every row needs a consistent record, and no column header may be empty — Excel will name it "Column1" and you'll lose track of it later. Merged cells are the single most common cause of the "field name is not valid" error, so unmerge everything before you start.

A faster approach than a plain range: click any cell in your data and press Ctrl+T (Cmd+T on Mac) to convert it to an Excel Table. Give the table a name in Table Design > Table Name, and every future pivot table built from it will expand automatically when you add rows. Without the table, you must manually redefine the source range each time the data grows, which is where most stale pivot tables come from.

Check three things before clicking anything: no entirely blank rows (Excel treats a blank row as the end of the data), consistent data types in each column (dates stored as text won't group by month), and no duplicate headers. If a column of numbers refuses to sum and shows a count instead, the cells are text — convert them with Data > Text to Columns or by multiplying by 1 in a helper column, then paste values back.

One layout note that matters more than people expect: keep the data in "long" format, one row per transaction, with category columns rather than month-name columns. A sheet with Jan, Feb, Mar as separate columns can only be pivoted by rebuilding it. Long format lets the pivot table do the summarising, and you can add month columns to the pivot layout instead of the source.

The 7-Step Build in Real Performance Order

Here is the sequence exactly as it happens on screen in Excel for Microsoft 365 (the same path applies in Excel 2021 and 2019; the ribbon wording is unchanged since 2013). Follow the order — choosing the destination before the range is the one step people skip and regret.

1: Click any single cell inside your data — never select a partial range, or Excel will only pick up that slice. 2: Go to Insert > PivotTable. The Create PivotTable dialog opens with "Select a table or range" pre-filled and the Table/Range box populated automatically. 3: Confirm the range covers all rows and columns. If the data sits in columns A through F and rows 1 through 4,182, the box should read A1:F4182, not A1:F200. 4: Choose "New Worksheet" (the default) or "Existing Worksheet" and specify a cell. New Worksheet keeps the pivot isolated; existing is useful when the summary must sit beside the data. 5: Tick "Add this data to the Data Model" only if you need to combine multiple tables or use distinct-count aggregations — it changes the field list you'll see next. 6: Click OK. A blank pivot appears, and the PivotTable Fields pane opens on the right with every column header listed as a checkbox. 7: Drag your first field into the Rows area, your measure into Values, and any slicing field into Columns or Filters. The pivot renders immediately.

If the PivotTable Fields pane doesn't appear, right-click anywhere inside the pivot and choose Show Field List. If the ribbon command is greyed out, the active cell is outside the data or the workbook is in a mode that blocks insertion — click into the data first, then retry.

Failure handling worth knowing: if Excel shows "The PivotTable field name is not valid," a header cell is blank or contains merged formatting. Return to the source, fill or unmerge, then Insert > PivotTable again. If you get "Data source reference is not valid," the sheet or table the pivot pointed at was renamed or deleted — use PivotTable Analyze > Change Data Source to relink it.

Reading the PivotTable Fields Pane: The Four Areas That Do All the Work

Rows stacks records vertically and creates one line per unique value — this is your category axis. Values is the measure area and defaults to Count when the field contains text, Sum when it contains numbers, so a text field dropped there will quietly count rows instead of adding anything. Columns spreads a second category horizontally, which is how you get months across the top and regions down the side. Filters places a field above the pivot as a dropdown that hides entire categories from the calculation.

The default aggregation trap is worth a paragraph of its own. Excel sums numeric fields but counts text fields, and it does this without asking. If your revenue column arrives as text because it was pasted from a web page, dropping it into Values produces a count of rows rather than a total, and the number looks plausible enough that people ship it. Click the field in Values, choose Value Field Settings, and set Sum explicitly — then verify the grand total against a SUM formula on the source.

Two settings change the output more than any drag-and-drop. Right-click a value and choose Show Values As to switch to % of Grand Total, % of Column Total, or a running total — % of Parent Row Total is the one that answers "what share of this region's sales came from this product." And under PivotTable Analyze > Options, the "Show items with no data" toggle decides whether categories absent from a period appear as blanks or vanish entirely, which matters when you're comparing two months.

You can rename any field in the pane by double-clicking it, and the pivot header updates to match. Rename "Sum of Amount" to "Revenue" before sharing the sheet — a column header reading "Sum of Amount2" is the fastest way to make a summary look unreviewed.

Build a Region-by-Month Sales Summary, Field by Field

Take a typical dataset: Date, Region, Sales Rep, Product, and Amount across a year. Drag Region into Rows and the pivot lists each unique region once. Drag Date into Columns — Excel groups it into Years and Months automatically in 2016 and later, so you'll see Month headers nested under a Year header. Drag Amount into Values and you get a grid of regions down the side, months across the top, revenue in each cell.

Right-click any date in the pivot and choose Group to control the grouping yourself: select Months and Quarters (not Years) to get a fiscal view, or set a custom start and end date if your year doesn't begin in January. Ungroup via the same menu if Excel's automatic grouping guessed wrong — a common issue when dates are stored as text, in which case grouping is greyed out entirely and the source needs converting first.

Now build the second view on the same data in one minute: drag Sales Rep into Rows above Region to get a nested hierarchy, then click the minus sign beside any rep to collapse that section. This is the feature that separates pivot tables from SUMIF formulas — the same source produces a regional summary, a rep-level summary, and a product-mix summary without a single new formula.

For a quick sanity check, total the pivot's grand total and compare it with =SUM(Amount column) on the source sheet. They should match to the cent. If the pivot total is lower, you have a filter applied, a blank-row truncation in the source, or data sitting outside the selected range — those are the only three realistic explanations.

Refresh, Change Source, and Keep the Summary Honest

A pivot table does not update when the data underneath it changes. Add rows or edit a value and the pivot keeps showing the old numbers until you click anywhere in it and press Alt+F5 (or right-click > Refresh). The status quo is silent — there's no warning that your summary is stale, which is why pivots pasted into reports go wrong.

The permanent fix: format the source as an Excel Table (Ctrl+T) before inserting the pivot, then reference the table name. Excel expands the table when you add rows at the bottom or the right, and Refresh picks up the new records with no redefinition. If you built from a plain range, use PivotTable Analyze > Change Data Source and widen the range; repeat this every time the data grows, which is the maintenance cost of skipping the table step.

Set a manual refresh habit if you share the workbook: Data > Connections > Properties lets you tick "Refresh data when opening the file" for pivots built on external data. For pivots on a local table, that option isn't available, so add a note in the sheet header with the refresh keystroke rather than trusting the reader to know.

A pivot built on a table that references another workbook breaks when that workbook moves. The pivot will show "Data source reference is not valid" and offer no repair path except relinking. Keep source data in the same workbook, or on a network path the whole team can reach, if the summary has to survive the week.

Formatting and Layout Fixes People Hit on Day One

Blank cells in a pivot mean "no data for that combination," not zero. To show zeros instead, right-click the pivot, choose PivotTable Options, tick "For empty cells show" and type 0 — this is the setting that makes a month-by-region grid readable rather than a field of gaps. The same dialog holds "Show error values as," which cleans up #DIV/0! from calculated fields.

Field headers are on by default and the pivot's default layout is Compact Form, which stacks Row labels into a single column with indents. Switch to Tabular Form under Design > Report Layout > Show in Tabular Form to get one column per row field — that's the layout Excel's own Power Query output expects and the one readers parse fastest. Choose Repeat All Item Labels if the nested region names need to appear on every line.

Number formatting applied to a value field sticks with the pivot: right-click the value > Value Field Settings > Number Format, and set currency with two decimals or thousands separators. Do it here rather than on the cells — formatting the cells directly gets wiped whenever the pivot refreshes and rebuilds the grid.

If the pivot shows a column of " (blank)" under a row field, the source has empty cells in that column. Either filter them out in the source, or accept them as a legitimate "unassigned" category if the blanks mean something — a judgement call, but make it deliberately rather than letting the pivot decide.

Common Errors and What Each One Actually Means

"The PivotTable field name is not valid" points at the source header row: a blank header, a merged cell, or a header that duplicates another. Fix the source, not the pivot. Rebuilding the pivot from a corrected table takes ten seconds; fighting the error takes longer.

"Data source reference is not valid" means the referenced sheet, table, or external file moved, was renamed, or was deleted. PivotTable Analyze > Change Data Source is the repair, and it needs the new location to exist.

A value field showing Count instead of Sum means Excel classified the column as text. Confirm by checking whether the cells in the source align left by default — that's the visual tell for text-stored numbers. Convert and re-add the field.

A pivot that refuses to group dates usually has text dates in the source. Text to Columns with the date format, or Power Query's change-type step, fixes it. Once dates are true dates, Group works normally.

Slow or frozen pivots on large sources point at the Data Model or at too many row fields. The Data Model (ticked at creation) handles millions of rows better than a plain range pivot, and reducing distinct row fields from five to two usually restores responsiveness instantly.

Pivot Table vs. Alternatives: When Each One Is the Right Tool

The pivot wins whenever the questions change — "by region," then "by rep," then "by product by month" — because each new view is a drag, not a rebuild. SUMIFS wins when the layout must be pixel-fixed for a printed report and the criteria never change. Power Query wins when the source itself needs cleaning every month (renamed columns, changed date formats), since the cleaning steps replay automatically.

Pick the pivot when you're exploring; pick SUMIFS or Power Query when the output is a fixed deliverable. Mixing them in one workbook is fine — a pivot fed by a Power Query table gives you both the repeatable cleanup and the flexible view.

Advanced Moves Worth Learning After the First Table

Calculated fields let you add a measure the source doesn't contain: PivotTable Analyze > Fields, Items & Sets > Calculated Field, name it, and write the formula using field names (e.g., = Amount * 0.1 for a 10% commission). The calculation runs on the aggregated totals, not row by row — a distinction that matters when the maths isn't linear, such as averaging an average.

Slicers turn filters into clickable buttons: PivotTable Analyze > Insert Slicer, pick the fields, and the floating panel filters every pivot connected to it. Connect multiple pivots to one slicer via Slicer > Report Connections, which is how a three-pivot dashboard stays consistent with one click.

Conditional formatting on the pivot values (Home > Conditional Formatting > Data Bars) gives instant visual ranking without a chart. Formatting survives refresh as long as it's applied to the value field rather than individual cells.

For multiple tables in one pivot, tick "Add this data to the Data Model" when creating it, then create relationships under Data > Manage Data Model. That path supports distinct-count aggregations, which plain pivots cannot do — counting unique customers rather than transactions is the classic use case.

FAQ

How long does it take to build a pivot table from scratch?

Under two minutes for a clean source: roughly 30 seconds to confirm the range and insert, and 30–60 seconds to drag the fields. The variable is source prep — unmerging headers and converting text-stored numbers can add ten minutes on a messy sheet. A formatted Excel Table eliminates that prep on every subsequent rebuild.

Can I make a pivot table from two different sheets at once?

Not from a plain range — Excel accepts one contiguous source per pivot. Tick "Add this data to the Data Model" at creation and define a relationship between the tables under Data > Manage Data Model, or stack the sheets into one table with Power Query first. The Power Query route is the more robust of the two because it refreshes cleanly.

Why does my pivot table show Count instead of Sum?

Excel classified the column as text, so it counts entries rather than adding them. Check whether the source numbers align left — text-stored numbers do. Convert them (Text to Columns with the right format, or multiply by 1 in a helper column), refresh, and reset the field to Sum in Value Field Settings.

Do pivot tables update automatically when I change the source data?

No — refresh is manual by default (Alt+F5, or right-click > Refresh). The exception is pivots on external data connections, where Data > Connections > Properties offers "Refresh data when opening the file." Building from an Excel Table at least makes new rows appear on a single refresh without redefining the range.

What happens to my pivot table if I delete a row from the source?

The deleted record disappears from the summary only after a refresh, and the pivot doesn't warn you that it recalculated. Deletion also changes the bottom of the range for plain-range sources, so the pivot may drop the last row of data until you reset the source. Tables avoid the range problem entirely.

Is a pivot table better than a SUMIFS dashboard?

For changing questions, yes — a new view costs a drag instead of a formula rebuild. For a fixed printed layout that must not shift, SUMIFS is more predictable because it recalculates instantly and doesn't carry stale-state risk. Many teams run both: a pivot for exploration feeding final numbers into a SUMIFS or Power Query report.

Can I count unique values in a pivot table?

Only through the Data Model. Tick "Add this data to the Data Model" when creating the pivot, then set Value Field Settings to Distinct Count. A pivot built on a plain range offers Sum, Count, Average, Max, Min, and the rest — but not distinct count, which is the single most common reason people rebuild a pivot they'd already made.

Why is my pivot table blank or missing fields after I refresh?

The source range shrank, a filter hid everything, or the referenced table or sheet was renamed. Check PivotTable Analyze > Change Data Source for the range, then confirm no filter is active in the Filters area. A pivot fed from a moved external workbook shows "Data source reference is not valid" and needs relinking.