OmniDocHow to Format Source Data for a Pivot Table
Learn how to format data for a pivot table: set up headers, remove blank and subtotal rows, fix data types, and check the source before you summarize.
Your pivot table is only as reliable as the sheet behind it. When a summary drops a category, shows a total that looks too small, or refuses to group dates by month, the cause is usually the source range rather than the pivot itself. This guide shows you how to format data for a pivot table so the summary works the first time.
It covers what the source range should look like, what to check before you build the summary, and how to confirm the result. The method is general and works in any spreadsheet (.xlsx). If your numbers start inside a PDF, the steps include getting them into a sheet first.
How to format data for a pivot table? The short answer
Arrange the data as one flat list: one header row, one record per row, one field per column, and clean values in every cell. Then confirm the totals match the source before you trust the summary.
| Your situation | What to do | Why |
|---|---|---|
| Data is a long list of records | Keep one record per row and one field per column | A pivot counts and groups rows by column |
| Headers are titles, merged cells, or two-row labels | Use one header row with short, unique names and no merged cells | Each header becomes a field name, and merged cells leave blanks |
| Blank rows or subtotals sit inside the list | Delete them and keep totals out of the source range | Blank rows split the range, and subtotals get counted twice |
| Numbers or dates are stored as text | Convert them to numeric values and real dates | Text cannot be summed or grouped by month or year |
| The data came from a PDF table | Run OCR on scanned pages, export to a spreadsheet, then clean the result | Pasting table text by hand often shifts values into the wrong columns |
Before you start
Gather a few things before you change anything. Each one saves a round of rework later.
- Decide the questions first. List the fields you want to group by (region, month, product) and the values you want to add up (revenue, units). The columns you need depend on those questions.
- Work on a copy. Cleanup touches many cells at once, and an untouched original lets you compare totals if something goes wrong.
- Confirm the source is complete. A pivot only summarizes rows it can see, so a missing page of records will produce a total that looks plausible but is wrong.
- Note where the data came from. Some exports add footer lines, report titles, or extra empty columns. Knowing the origin tells you what to look for.
Preparing your source sheet, step by step
- Copy the records to a clean sheet. Put the data on its own sheet with nothing else around it. Keeping report titles, notes, and charts out of the range prevents them from being read as data.
- Make the header row one row. Give each column a short, unique name such as "Order date" or "Unit price." Duplicate or generic names like "Column1" make the field list hard to read.
- Remove blank rows and columns. Delete empty lines inside the list so the data forms one continuous block. Leave no gaps between the header and the last record.
- Remove subtotals, notes, and footers. Delete any row that holds a total or a comment. If you need totals, calculate them in the pivot instead.
- Unmerge cells and fill values down. Each record needs its own value in every column, so if a region name appears once above a group of rows, copy it into each row beneath.
- Convert text numbers and dates. Check that numbers are right-aligned and dates are not. Left-aligned values are often text, so use the spreadsheet's conversion options or re-enter them in a consistent date format.
- Standardize category spellings. Trim extra spaces and pick one spelling for each category, such as "West" instead of "West " and "W." Fixing this in the source keeps the pivot from splitting one category into several rows.
- Turn the range into a table, then build the pivot from it. A table gives the range a stable name and expands automatically when you add rows. Build the pivot from the table rather than from a fixed block of cells.
Check the result
Once the pivot is built, check it against the source before you use the numbers in a report.
| Check | How to see it | If it's wrong |
|---|---|---|
| Record count | Count the data rows in the source and compare with the count the pivot reports | Extra counts usually mean blank or subtotal rows slipped in; delete them and refresh |
| Grand total | Add a simple formula on the source numeric column and compare it with the pivot grand total | A mismatch usually means text numbers or blank cells; fix them and refresh |
| Field names | Look at the field list in the pivot | Generic or duplicate names mean a header needs renaming in the source |
| Date grouping | Try grouping the date field by month | If grouping is unavailable, the dates are probably stored as text |
| Category labels | Scan the row labels for near-duplicates | Fix the spelling in the source, not in the pivot |
If you pulled the data from a scanned PDF, compare a sample of the converted values with the original page. OCR can misread characters, especially in small print, so check any figures that matter before you summarize them.
❓ FAQ
1. Can a pivot table read merged cells?
It can read them, but only the top-left cell of a merged block holds a value. The other cells come through blank, so the pivot cannot tell which group each row belongs to. Unmerge the cells and fill each value down.
2. Do I need to refresh the pivot after I edit the source?
Yes. A pivot shows the data as it was when you last built or refreshed it, so changes to the source do not appear until you refresh. Refreshing after every edit keeps the totals in step with the sheet.
3. Can I keep a two-row header?
A pivot works well with one header row. If your report uses a group label above each column, move that label into its own column, or combine the two labels into one name such as "2024 Revenue."
4. Can I build a pivot straight from a PDF?
Not directly in most cases. Get the table into a spreadsheet first. The OmniDoc PDF editor can run OCR on scanned PDFs and export to Excel, and you can clean the result with the steps above.
📝 Summary
A pivot table works reliably when the source is one flat list with clear headers, consistent data types, and no blank or subtotal rows. Format the data for a pivot table before you build it, then compare the grand total with the source to confirm the summary is complete.
