You run a PDF through a converter, open the result in Excel, and the numbers are all there, but the sheet is not usable yet. There are blank rows every page, some amounts refuse to add up, a few columns are split down the middle, and the totals do not tie to the printed figures. That gap between a raw conversion and a clean, working spreadsheet is the same handful of problems every time, and once you know the order to fix them in, it takes a few minutes rather than an afternoon of hunting.
This is the full cleanup checklist for a just-converted sheet: what to fix first, why each problem shows up after a PDF to Excel conversion specifically, and the one verification step that tells you the data is actually right and not just tidy.
How do I clean up data in Excel after a PDF conversion?
Work in a fixed order: back up the raw sheet, remove blank rows and repeated headers, split or merge columns so each value sits in its own cell, convert text-formatted numbers and dates back to real values, then strip stray spaces with TRIM and CLEAN. Doing it in that order matters, because fixing spaces or number formats before the structure is right just means redoing it after you move columns around.
The single rule that prevents the worst mistakes: keep the original conversion on its own tab and clean a copy. If a step deletes a row it should not have, you have the source to compare against.
Why is my data messy after converting a PDF to Excel?
The data is messy because a PDF stores text at fixed coordinates on a page, not in a real grid of rows and columns. The converter has to guess where the table boundaries are from spacing alone, so it turns page breaks into blank rows, reads repeated headers as data, sometimes splits one column into two or merges two into one, and often writes numbers as text because it captures the characters without knowing they are meant to calculate.
None of that means the converter failed. It captured the values correctly. The layout information that a spreadsheet needs simply is not present in the PDF, so a short cleanup pass is normal even with an accurate conversion. A stronger, more accurate PDF to Excel conversion reduces how much of this you have to do, but rarely eliminates it entirely on complex tables.
What should I fix first in a converted spreadsheet?
Fix the structure first: blank rows and misplaced columns. Sorting, filtering, and every formula depend on the sheet being a clean rectangle of rows and columns, so if you clean numbers or spaces before the structure is right, you will have to redo that work once rows shift. Get the shape correct, then fix the contents inside the cells.
A quick way to check the structure: select the whole range and look at the row count in the status bar, then compare it to the number of data rows in the PDF. A big gap usually means blank rows or a header that repeated on every page and needs removing.
Before you change anything, a quick pass with conditional formatting is worth the minute: highlight duplicate values, negative amounts, and blank cells so you can see the scale of the mess without editing a single cell. And if the conversion laid the data out sideways, with periods across the top and line items down the side, transpose it into a tall table first, because filters and pivot tables expect one header row across the top.
How do I remove blank rows without deleting good data?
Use a column filter, not Go To Special. Select the data, press Ctrl+Shift+L, open the drop-down on a column that should never be empty, uncheck Select All, tick only Blanks, delete the visible rows, then turn the filter off. This removes only rows that are empty in that column and leaves partly filled rows alone. Go To Special selects every blank cell, so it will delete any row with even one empty cell, which is risky on real data. The full method, including rows that look empty but hold hidden spaces, is covered in removing blank rows after a PDF conversion.
How do I split columns that came through merged together?
When a converter packs two fields into one cell (a date and a description, or a description and an amount), use Data then Text to Columns. Pick Delimited if a consistent character separates the values, or Fixed Width if they line up in even positions, preview the split, and finish. If the opposite happened and one value spilled across two columns, join them back with how to combine two columns in Excel, and if the sheet arrived full of merged header cells that refuse to sort, see how to merge cells in Excel without losing data for the unmerge fix. If instead your table came in with each value spread across too many columns, that is a reading-order problem rather than a merge, and the fix is different, covered in keeping columns intact when copying a PDF table.
Why won't my numbers add up after converting a PDF?
The numbers do not add up because the converter stored them as text, not as values. They look identical on screen but Excel treats "1,240.50" as a string, so SUM skips it and the total reads low or zero. Select a column, and if the status bar shows Count but no Sum, they are text. Fix it with Data then Text to Columns then Finish on a single column, which forces Excel to reparse each cell as a number. The full set of methods, including the whole-column and formula fixes, is in how to convert text to number in Excel.
Currency symbols, thousands commas, and accounting-style negatives in parentheses are the usual culprits and each has a targeted fix: see numbers imported as text, currency symbols stored as text, and negative numbers in parentheses for the exact repair formulas.
How do I fix dates that came in as text?
Dates land as text when the converter reads them in a format Excel does not auto-recognize, so they sit left-aligned and will not sort chronologically or feed a date filter. Select the column, run Data then Text to Columns, and on the final step choose the Date option with the correct order (MDY for US dates) so Excel converts each string to a real date serial. After that, you can apply whatever date format you want. The step-by-step is in fixing dates stored as text.
How do I remove extra spaces and hidden characters?
Converted text often carries leading spaces, double spaces, non-breaking spaces, and invisible line-break characters that break lookups and grouping. In a helper column, use =TRIM(CLEAN(A2)): TRIM collapses extra spaces to single ones and strips leading and trailing spaces, while CLEAN removes non-printing characters. Copy the helper column down, then paste it back over the original as Values so the formulas are replaced by clean text.
If TRIM alone leaves a stubborn space, it is usually a non-breaking space (character 160). Use Find and Replace, paste the odd space into the Find box, and replace it with nothing, or wrap the formula as =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
What is the fastest way to avoid all this cleanup?
The fastest way to cut the cleanup is to start with a conversion that gets the structure and number formats right the first time. A table-aware converter that separates columns cleanly, keeps headers out of the data, and returns numbers as real values leaves far less to fix than a generic paste or a tool that only reproduces the visual layout. You will still do a quick pass on complex tables, but you skip most of the manual repair.
Upload your file to the PDF to Excel converter at the top of this page to get a cleaner starting sheet, and for scanned or photographed documents use OCR PDF to Excel so the text is recognized before it is placed. Accounting and finance teams cleaning statements at volume can start from PDF to Excel for accountants. If your source is a stack of bank statements that always import messy, a dedicated bank statement to Excel converter handles the page breaks and repeated headers for you, and for invoice line items a purpose-built invoice to Excel tool keeps each field in its own column.
The post-conversion cleanup checklist
Run these in order on a copy of the raw conversion, and verify at the end:
- Copy the raw conversion to a new tab so you always have the source.
- Remove blank rows and any header or footer text that repeated per page. If you merged several converted files, also remove duplicate rows.
- Split or recombine columns with Text to Columns until each value is in its own cell.
- Convert text-formatted numbers to real values (Text to Columns then Finish) and check the status bar shows a Sum.
- Convert text dates to real dates so they sort and filter.
- Run =TRIM(CLEAN()) to strip stray spaces and hidden characters, then paste as values.
- Verify: compare the row count to the PDF (count the filled rows with COUNTA), spot-check the first, last, and a middle row, andsum one numeric column to confirm it ties to the printed total on the PDF. To check the extracted rows against your own records, compare the two columns with COUNTIF rather than reading them side by side.
- Attach the meaning: if the file holds codes rather than names, use VLOOKUP (or XLOOKUP on Excel 2021 and 365, or INDEX MATCH on older versions) to pull vendor names, categories, or prices in from your master list.
- Tidy the display last: freeze the header row and wrap the long description column so the sheet is readable without spilling across every column to its right.
That last check is the one people skip, and it is the one that matters. A sheet can look perfectly clean and still be missing a row or holding a mistyped value. Tie one total back to the source and you know the cleaned data is trustworthy, not just tidy.
With a clean, trusted sheet you can finally summarize it. Drop the data into a pivot table to total spend by category or count invoices by vendor in a few clicks, add up one category at a time with SUMIF, or count the cells with text to confirm every row carries a description before you report the numbers.
Before you report any of it, arrange the sheet so you can actually see what is in it. Sort the list by amount or date to bring the largest items and the period boundaries to the top, and filter it to isolate one vendor or one month at a time while you check the extraction against the original document. When the figures are ready to present, round them with a formula rather than by changing the number format, because formatting alone leaves the hidden decimals in place and the column keeps totaling to a figure the printed numbers do not support.
Two things make that summary hold up over time. If you are adding your own category or status column beside the extracted data, put a drop down list on it so the same category is always spelled the same way, otherwise Fuel and fuel become two separate lines in the pivot table. And when you turn the totals into shares or period-over-period movement, percentage formulas only work once the amounts are real numbers, so run that step before this one, not after. For a blended figure like average cost weighted by quantity, or a quantity-times-price total in one cell, reach for SUMPRODUCT, and when you are stacking several formulas on the same block, turn it into a table with a named range so every total keeps pointing at the right rows as the client sends more pages.