A pivot table is a built-in Excel feature that summarizes a large dataset by grouping, sorting, and totalling your numbers automatically, so you can analyse thousands of raw rows without writing a single formula. Making one takes about two minutes once your data is laid out properly: select a cell inside your table, choose Insert > PivotTable > From Table/Range, then drag your column headings into the Filters, Rows, Columns, and Values boxes. The whole process works the same in Excel 365, Excel 2021, Excel 2019, Excel for Mac, and Excel for the web.
If a colleague has ever told you that pivot tables “don’t work,” the problem was almost certainly the data layout, not the feature. This guide walks through the whole sequence — preparing the sheet, inserting the table, placing fields, filtering and grouping, then formatting and refreshing — with the exact menu names each version of Excel uses.
Table of Contents
What You Need
You need Excel (Windows, Mac, or the free browser version) and a worksheet holding your data in a flat table. No add-ins, no formulas, no macros. If your file is a CSV or an export from another system, open it in Excel first and clean it up.
Here is a small sample dataset you can recreate in about two minutes. Everything below refers back to it, so the steps stay followable rather than abstract.
| Date | Region | Product | Category | Units Sold | Revenue |
|---|---|---|---|---|---|
| 01/03/2026 | North | Desk Lamp | Lighting | 42 | 3780 |
| 01/09/2026 | South | Office Chair | Furniture | 18 | 4410 |
| 01/17/2026 | North | Office Chair | Furniture | 23 | 5635 |
| 02/04/2026 | West | Desk Lamp | Lighting | 37 | 3330 |
| 02/11/2026 | South | Monitor Stand | Accessories | 64 | 2560 |
| 02/22/2026 | West | Office Chair | Furniture | 15 | 3675 |
Four rules decide whether Excel can read that table cleanly:
- Row 1 holds unique, descriptive headers — one per column, no blanks, no merged cells.
- Every row below is one record. Never leave a blank row or a blank column in the middle.
- Numbers are stored as numbers. A revenue figure typed as text with an apostrophe in front will not total.
- Dates are real dates formatted consistently. Text dates break grouping in Step 5.
Clean headers matter more than most beginners expect. If your header cell reads “Rev ” with a trailing space, or two columns both say “Amount,” the pivot table creates two separate fields and quietly splits your totals. Fix the headers before you insert anything.
One more habit worth building early: select your range and press Ctrl+T (Cmd+T on Mac) to turn it into a formal Excel Table. A pivot table built on a Table expands automatically when you add rows below it, which saves you a repair step every single time.
Finally, know where each field goes. This mapping is the whole mental model:
| Field type | Goes in | Why |
|---|---|---|
| Text (Region, Product, Category) | Rows, Columns, or Filters | These fields organize the table into labelled groups |
| Number (Units Sold, Revenue) | Values | Excel summarizes them with Sum, Count, Average, Max, or Min |
| Date | Rows or Columns, then group | Drop it in, right-click, and group by month and year |
Beginners almost always ask the same question here: why can’t I drop Revenue into Rows? Because a number in Rows just gets counted or listed, not totaled. Numbers belong in Values, and that single habit fixes most beginner frustration.
Step-by-Step: How to Make a Pivot Table in Excel
The full process takes six moves: prepare the sheet, select the data, insert the PivotTable, place the fields, filter and group, then format and save. Below, each step states what to do and how you can tell it worked.
Step 1: Prepare Your Excel Data
Put your headers in row 1 with no gaps, remove any merged cells, and delete stray empty rows and columns. Check that Units Sold and Revenue are right-aligned — right alignment in Excel means the cell holds a real number, not text.
To catch stray blanks fast, click any cell inside the table, press Ctrl+G (Cmd+G on Mac), choose Special, then check Blanks and press OK. Excel selects every empty cell in the region at once. Press Delete and nothing odd can hide in there.
Step 2: Select the Data
Click any single cell inside your table. Excel scans upward and leftward, detects the surrounding block, and highlights it for you. This is the method I’d use — it survives a growing dataset far better than dragging a range.
If you would rather be explicit, drag across the full range including the header row, or type the reference into the Name Box at the top left. Pressing Ctrl+A inside a table selects that table only; pressing it a second time expands to the whole worksheet, which is usually not what you want.
You can tell selection worked because every column in your block is outlined with a colored border. If only part is highlighted, Excel has found a blank row inside the range, and that gap is the reason your pivot will miss records later.
Step 3: Insert the PivotTable Step by Step in Microsoft 365 and Older Versions
The path is nearly identical across versions, with two naming differences that trip people up:
- Microsoft 365, Excel 2021, Excel 2019: Insert tab > PivotTable group > PivotTable > From Table/Range. Some installations group the button under Recommended PivotTables.
- Excel 2016 and earlier, and Excel for Mac: the same location, but the menu entry reads From Table or Range instead of From Table/Range.
- Excel for the web: Insert > PivotTable > Add a pivot table. The Create PivotTable dialog box appears without the Advanced Options button.
The Create PivotTable dialog box opens with your range already filled into the Table/Range field. Leave it alone unless the highlight looks wrong. Under Choose where to place the PivotTable, select New Worksheet for a clean report, or Existing Worksheet plus a cell reference to place the summary on the same sheet as your data.
Click OK and you should see an empty pivot table with Row Labels, Column Labels, and Values placeholders, plus the PivotTable Fields pane docked on the right.
If Excel says “We couldn’t find data in the range,” the range box is empty or pointing at the wrong cells. Close the dialog, click one cell inside your table, and insert again.
Step 4: Add Rows, Columns, and Values
The PivotTable Fields pane has four boxes and a checklist. Tick the box beside a field to add it instantly, or drag the field name into a box. Filters sits at the top as a single-select or multi-select drop-down; Rows runs down the left side; Columns runs across the top; Values holds the numbers you want summarized.
For the sample data, build a sales-by-region table: drag Region into Rows, Product into Columns, and Revenue into Values. The result should show one row per region and one column per product, with revenue totaled in each cell and a grand total down the right and along the bottom. Drop Region into Filters instead of Rows and you get the same numbers with a region selector above them.
Excel names each value field after the column heading and prefixes the summary function, so the field reads “Sum of Revenue.” Seeing “Sum of” confirms the field landed in Values and was read as a number.
To change the calculation, click the field inside the pivot table and choose Value Field Settings. From there you can switch Sum to Count, Average, Max, or Min, set a custom number format, or rename the field so it reads “Revenue” rather than “Sum of Revenue.”
Step 5: Filter, Sort, and Group the Results
Every field in a pivot table carries its own filter drop-down — the small triangle beside “Row Labels” or a field name. Click it to hide items or sort by label. To sort by value instead, right-click any number in Values, choose Sort, pick Largest to Smallest, and the whole table reorders.
Grouping is what turns daily rows into readable periods. Right-click any date in the pivot table, choose Group, and pick Months and Years. Excel adds Years and Quarters automatically, so you can roll a year of daily sales up to twelve monthly lines or four quarterly ones. The same Group dialog handles numbers — useful for bucketing values into bands such as 0–49, 50–99, and 100-plus.
For filtering that non-technical colleagues can use, add a slicer. Click anywhere in the pivot table, then PivotTable Analyze > Insert Slicer. Tick Region and Category, and two clickable buttons appear beside the report. Insert Timeline does the same job across dates, with a draggable date range.
Slicers are the most-requested addition on Excel forums because they turn a static summary into something a manager can click through without asking you to rebuild the report.
Step 6: Format and Save the PivotTable
Open the PivotTable Design tab to pick a style, turn grand totals on or off, and switch between Outline and Compact layout — Compact with one line per field is the tidiest for reports you will send to someone else.
For a percentage view, click a value field and open Value Field Settings > Show Values As > % of Grand Total. Number formatting lives in the same dialog under Number Format, so you can set currency or a decimal count once and apply it everywhere.
Save the workbook as usual — the pivot table is stored inside it, not as a separate file. A pivot chart takes one click: PivotTable Analyze > PivotChart, choose a chart type, and the chart stays linked to the pivot table, following every field change and filter.
When your data changes, refresh instead of rebuilding. PivotTable Analyze > Refresh refreshes the current selection. Tick Refresh Data When Opening the File on the same tab and the report rebuilds itself every time someone opens the workbook. If you have swapped in a different range entirely, use Change Data Source and point it at the new cells.
Common Mistakes
Nearly every pivot table problem traces back to the source sheet or to a stale refresh. The table below maps the exact messages people search for to their cause and fix.
| Symptom | Likely cause | Fix |
|---|---|---|
| Pivot table is blank | Headers excluded from the range, or numbers stored as text | Reselect the range including row 1, then convert text numbers with Data > Text to Columns > Finish |
| Rows are missing from totals | A blank row or stray heading inside the selected range | Clear the gap or convert the range to a Table with Ctrl+T so it auto-expands |
| “Cannot group that selection” | Dates stored as text, or blanks inside the date column | Convert the column to real dates, then refresh before grouping |
| Numbers not adding up | Currency symbols or stray spaces typed into the cells | Remove the text, then use Text to Columns to convert |
| Totals don’t change after editing | The pivot has not been refreshed | PivotTable Analyze > Refresh |
| Formatting keeps reverting | Styles applied are overwritten on refresh | Reapply your PivotTable Design style after refreshing, and avoid manual fills |
| Pivot is slow on a large file | Workbook is several megabytes and stored in a synced folder | Trim unused columns, turn the source into a Table, and consider Power Pivot for bigger sets |
The “Cannot group that selection” error deserves one more note because it is the most searched. It almost always means Excel sees at least one cell in the date column that is not a date. Select the column, run Text to Columns with the column set to General, and pick a column data format of Date (MDY). Then refresh the pivot table and try Group again — the order matters, because grouping reads the cached values, not the cells.
Users on r/excel and similar forums report the same pattern repeatedly: colleagues insist pivot tables are broken, and the cause is nearly always a merged header cell or a report laid out sideways rather than as one record per row. When the data is not in a clean table at all, Power Query’s Unpivot Columns command reshapes it into headers down the side and values in a column, which is the layout a pivot table expects.
A few habits keep finished reports usable. Turn off subtotals when a field has many items, or the table drowns in repeated “Total” lines. Rename the Row Labels and Column Labels headers so the report reads as a sentence. And avoid clicking inside the pivot table to type — you cannot edit its contents the way you edit a worksheet, only the source data.
Frequently Asked Questions
Do I need to sort my data before making a pivot table?
No. Excel groups and sorts the summary itself once the fields are placed, and it labels and totals them alphabetically by default. Sorting your source data first is harmless, but it changes nothing about the result. The one case where it matters is dates stored as text, which you need to convert to real dates before grouping will work.
What should go in Rows vs Columns vs Values in a pivot table?
Text fields such as Region or Product go in Rows and Columns because they organize the table into labelled groups. Numeric fields such as Revenue or Units Sold go in Values so Excel summarizes them with Sum, Count, Average, Max, or Min. Filters takes any field you want as a selector above the report instead of a row or column.
Why is my pivot table showing blank values?
Three causes cover almost every case. Your range excluded the header row, so Excel created a blank Column1 field. Your numbers are stored as text, so Sum returns nothing. Or a blank row inside the range stopped Excel reading everything below it. Reselect the range including row 1, clear the blank rows, and run Text to Columns on any column that was formatted as text.
How do I fix the u0022Cannot group that selectionu0022 error?
Select the date column, run Data u0026gt; Text to Columns, set the column data format to Date, and click Finish. Then refresh the pivot table from the PivotTable Analyze tab before trying to group again. If a few cells held blank or text values, clear them first, because Excel refuses to group any range containing a non-date.
How do I refresh a pivot table when my data changes?
Click anywhere in the pivot table, open the PivotTable Analyze tab, and choose Refresh. To refresh automatically, tick Refresh Data When Opening the File on the same tab. If you replaced your data with an entirely different range, use Change Data Source instead, and point it at the new cells.
Conclusion
Start with the source sheet, because that is where almost every failure begins: one header row, no blanks, no merged cells, real numbers, real dates. Then click a cell inside the table, go to Insert > PivotTable > From Table/Range, choose New Worksheet, and drag Region into Rows and Revenue into Values. You will have a working summary in under two minutes, and every later question about pivot tables becomes a field you move rather than a formula you rewrite.