Last updated July 2026. To make a pivot table in Excel, click any cell inside your data, choose Insert then PivotTable, confirm the range, and pick New Worksheet. Then drag one field into Rows, one into Values, and Excel builds the summary. A pivot table groups and totals thousands of rows without a single formula, which is exactly what you want after converting a PDF statement or report into a long list of transactions.
Most people reach for a pivot table the moment a converted file gets too big to eyeball. You export a bank statement, an invoice register, or a sales report out of a PDF, and now you have hundreds of rows that need to become a handful of totals: spend by category, revenue by month, count of orders by customer. A pivot table does that in about thirty seconds, and it redoes it every time the data changes.
How do I create a pivot table in Excel?
Click any single cell inside your data, then choose Insert on the ribbon and click PivotTable. Excel guesses the range and offers to drop the pivot table on a new worksheet, which is almost always the right choice. Click OK, and an empty pivot table appears next to a field list on the right.
The one rule that makes or breaks this: your source data needs a single clean header row and no blank rows or fully blank columns cutting through it. Each column should hold one kind of thing (a date, an amount, a category) with the name of that thing at the top. Converted files sometimes carry a title row or a merged band above the real headers, so delete anything above the header row before you start.
| Step | What to do |
|---|---|
| 1 | Click any cell inside the data (no need to select the whole range) |
| 2 | Insert > PivotTable, confirm the range, choose New Worksheet, click OK |
| 3 | Drag a text field (category, vendor, month) into the Rows area |
| 4 | Drag a number field (amount) into the Values area |
| 5 | Drag a second field into Columns if you want a two-way grid |
On a Mac the path is the same, Insert then PivotTable, or the shortcut Command plus Option plus P. The field list and drag areas work identically once the empty pivot appears.
What is a pivot table used for?
A pivot table summarizes a table of detail rows into grouped totals you can rearrange by dragging. It answers questions like how much did we spend per category, how many invoices came from each vendor, or what were monthly sales by region, without writing SUMIF or COUNTIF for every group by hand. The power is that the same source data can be reshaped a dozen ways in seconds: drag a field out of Rows and into Columns and the whole summary flips.
The four drop areas map to plain questions. Rows is what you are grouping by down the left. Columns is a second thing to break across the top. Values is the number being totaled or counted. Filters sits above everything and lets you narrow the whole table to one year or one department.
Why does my pivot table count instead of sum?
If a pivot table shows a count of your amounts when you expected a sum, the amount column is almost certainly stored as text, not as numbers. Excel will not add text, so it falls back to counting the cells instead. This is the single most common pivot problem on converted data, because a PDF converter often hands back figures that look like numbers but sit left-aligned as text.
Fix the source column first, then refresh the pivot. Select the amount column and use Convert to Number or Text to Columns to turn the text into real values. Once the column is numeric, click any value in the pivot, open Value Field Settings, and set it to Sum. If you want to force it manually in the meantime, that same Value Field Settings dialog lets you switch Count to Sum, Average, Max, and more.
Why is my pivot table not showing new data?
A pivot table does not update on its own. When you add rows or edit numbers in the source, the pivot keeps showing the old snapshot until you refresh it. Click anywhere inside the pivot, open the PivotTable Analyze tab, and click Refresh (or right-click the pivot and choose Refresh). Your changes appear instantly.
There is a second trap with growing data. If you added rows below the original range, a plain refresh will not pick them up because the pivot still points at the old, smaller range. The durable fix is to turn your source into an Excel Table first: select the data and press Ctrl plus T before you insert the pivot. A Table expands automatically as you add rows, so the pivot always sees every row after a refresh. This matters a lot for converted statements you append to month after month.
How do I make a pivot table from converted PDF data?
Convert the PDF to a clean sheet first, then treat it like any other source. The whole point of a pivot is a tidy rectangle of data, so the quality of the conversion decides how smooth the pivot is. Run the file through a PDF to Excel converter, then do three quick checks before you insert the pivot:
| Check | Why it matters for the pivot |
|---|---|
| Amounts are real numbers, not text | Otherwise the pivot counts instead of sums |
| One header row, no title band above it | A stray row above the headers breaks field detection |
| No fully blank rows splitting the data | A blank row makes Excel stop the range early |
| Dates are real dates, not text | Real dates let you group by month or quarter |
If the conversion dropped values into a single column, split them back out with Text to Columns first, and clear duplicate rows from any merged files with Remove Duplicates. A few minutes of cleanup here saves a broken pivot later. Teams that live in this workflow, converting a batch of bills and then summarizing the spend, often push the same clean data into an accounts payable process that codes and totals every bill once the numbers are trusted.
How do I group dates in a pivot table?
Drag a real date field into Rows, right-click any date in the pivot, choose Group, and pick Months, Quarters, or Years (you can select more than one). Excel rolls the daily rows up into the periods you chose. This only works when the column holds genuine dates. If your converted file has dates stored as text, the Group option will be missing or throw an error, which is the tell that you need to convert that column to real dates first.
What is the difference between a pivot table and a formula?
A formula like SUMIF answers one fixed question and updates live as data changes. A pivot table answers many questions you can reshape by dragging, but it needs a manual refresh. Use a formula when you want a single number wired into a report or dashboard that must always be current. Use a pivot table when you are exploring, when the groupings keep changing, or when one function per group would mean writing dozens of them.
In practice they pair well. Many people build a pivot to explore a converted file, find the categories that matter, then write a couple of SUM or SUMIF formulas to lock those specific totals into a summary tab. If your groups depend on matching codes to names, a VLOOKUP on the source column before you pivot gives you readable row labels instead of raw codes.
Common pivot table mistakes to avoid
Three errors account for most of the frustration. Selecting a whole column of blanks as your source, which drags empty rows into the pivot; fix it by clicking one cell inside the data and letting Excel detect the range, or by using a Table. Forgetting to refresh after editing the source, so the totals look wrong when they are just stale. And leaving amounts as text, which turns every sum into a count. Clear those three and a pivot table is the fastest way to turn a converted PDF into an answer.
For a full pass on getting a converted file pivot-ready, from blank rows to text numbers to duplicate lines, work through the steps to clean up data after a PDF conversion. A clean sheet in makes every pivot after it trivial.