Pivot tables explained for beginners

The short answer

A pivot table takes a long list and turns it into a summary. You pick which column to group by, which column to add up, and Excel does the rest. No formulas.

The whole feature is four boxes. Drag a field name into Rows to group by it, drag a number into Values to total it, and you have your answer. Everything else is refinement.

Your first one, in five steps

Start with a list that has headers in row 1 and no blank rows.

1. Click any single cell inside the data.
2. Insert > PivotTable. Excel guesses the range, which is why the tidy layout matters.
3. Choose New Worksheet and click OK.
4. On the right you get the field list. Drag your category (say Region) into Rows.
5. Drag your number (say Amount) into Values.

You now have total amount by region, calculated from however many thousand rows, in about eight seconds.

What the four boxes do

• Rows: the groups going down the page. Region, product, month, person.
• Columns: a second grouping going across. Use it for a small number of values, like year or status, or the table gets unreadable.
• Values: what gets calculated. Sum by default for numbers, count for text.
• Filters: a dropdown above the table that filters the whole thing. Good for switching between one department and another.

Drag fields between the boxes and the table rebuilds instantly. That is the "pivot" in the name, and it is why exploring data this way is so fast: you can ask five questions in a minute.

Pivot tables are the closest thing a spreadsheet has to a magic trick, and they are one of the easiest features in the whole program. The intimidation is mostly the name.

The five settings you will actually use

1. Change sum to average or count. Right-click a value, choose Summarize Values By.
2. Show a percentage. Right-click, Show Values As > % of Grand Total. Instantly more useful than raw numbers for most reports.
3. Group dates by month or year. Right-click a date row and choose Group. This alone answers most "how did this trend" questions.
4. Sort by value. Click a value cell, then sort largest to smallest. A sorted pivot is readable, an alphabetical one is not.
5. Refresh. Alt+F5 after the source changes. Pivot tables do not update on their own, which catches everyone once.

The data layout that makes it work

Pivot tables are fussy about their source, and almost every "it will not work" is one of these:

• One header row, with a name in every column. Blank headers break it outright.
• No merged cells anywhere in the data.
• One row per record. Not a report layout with subtotals already in it. If you already summarized it by hand, the pivot has nothing to do.
• Consistent types. A column of numbers with a "n/a" in it becomes a text column, which is why your pivot counts instead of summing.
• Ctrl+T first. Turning the source into a table means new rows are picked up when you refresh, instead of you repointing the range every month.

That last one is the difference between a pivot you rebuild monthly and one you refresh.

Slicers, if you want it to look built

A slicer is a set of clickable buttons that filters the pivot. Click the pivot, then PivotTable Analyze > Insert Slicer, tick a field, and you get a filter panel anyone can use without knowing what a pivot table is.

Connect one slicer to several pivot tables (right-click the slicer, Report Connections) and you have a dashboard. That is genuinely all a basic Excel dashboard is: a few pivots, a couple of charts, and a slicer wired to all of them.

Microsoft's Excel help covers the dialogs, and Microsoft Learn has free guided practice.

When a pivot table is the wrong tool

• When you need row-level detail. A pivot summarizes. If the reader needs every line, give them a filtered table.
• When the answer is one number. SUMIF is quicker than building a pivot to find one total.
• When the data is not in a list. Pivots cannot fix a badly shaped source. Clean it first, then pivot.
• When the dataset is genuinely huge. At some point the answer is a database and SQL, not a bigger workbook.

Where to learn the rest

If pivot tables are new, the next things worth learning are lookups (to join two lists before you summarize) and cleaning (so the summary is trustworthy). Excel data analysis covers all three on real datasets, the Excel certificate covers the ground before it, and one subscription opens every course in the Business & Productivity school.

Open a list you already have and drag two fields into two boxes. You will spend the rest of the week wondering why you added things up by hand for so long.