Convert PDF to Excel Without Losing Formatting: Fixes
Quick Answer: A PDF stores text at positions on a page, not cells in a grid, so every converter has to guess where columns and rows are. Formatting survives best when the PDF has real text (not a scan), when you extract the table rather than the whole page, and when you save to .xlsx rather than CSV. What still breaks, such as merged cells, text numbers and swapped dates, can be fixed in Excel in minutes.
When a PDF converts badly, the converter is rarely broken. A PDF has no idea what a column is; it knows only that a string of characters sits at a certain x and y on the page. The converter infers a table from gaps in the text, and every symptom you see in Excel traces back to a wrong inference. Know the cause and the fix is usually quick.
Drop your bank statement here
PDF or OFX · Max 4.4 MB
Uploaded over an encrypted connection. We never ask for your bank login. Privacy
Preview free with no account. Free account to download.
Why is my PDF not converting to Excel properly?
Most failures come from one of four sources:
- The PDF is an image. Scanned pages and phone photos contain no text at all. The converter must run OCR first, and OCR misreads characters (0 as O, 1 as l, a comma as a decimal point).
- The layout is not a grid. Descriptions that wrap onto two lines, totals printed between rows, logos and footers sharing a band with the data, or two tables side by side all confuse the column guesser.
- The tool converted the page, not the table. Whole-page conversion tries to recreate the visual layout, so it merges cells to imitate the look and keeps page furniture you do not want.
- Excel reinterpreted the values. Even a perfect extraction can be spoiled on the way in when Excel turns dates and long numbers into something else.
The first three happen inside the converter; the fourth happens in Excel and is the easiest to prevent.
Symptom, cause and fix
| What you see in Excel | Likely cause | Fix |
|---|---|---|
| Columns shifted one place to the right on some rows | A blank cell in the PDF (no debit on a credit line) was skipped | Sort by the shifted column to group the bad rows, then cut and move them; or re-extract choosing the table option |
| Merged cells across the top or down the side | Whole-page conversion imitating the visual layout | Find and unmerge them (steps below), then fill down |
| One transaction split over two rows | A wrapped description became a new line | Join the continuation text to the row above, then delete the extra row |
| Numbers left-aligned, SUM returns 0 | Values imported as text, often with hidden spaces | Remove non-breaking spaces, then Convert to Number or VALUE |
| Dates show as text, or day and month swapped | Date order differs from your regional setting | Convert with the source locale |
| Long reference numbers end in 000 or show as 1.23E+15 | Stored as a number beyond Excel's 15-digit precision | Re-import with the column as Text |
| Header row repeated every 30 or so lines | Each PDF page printed its own headers | Filter the column for the header word and delete those rows |
| Garbage characters or wrong digits | Scanned PDF read by OCR | Use a text-based PDF if you can get one; otherwise proofread totals |
| Fonts, shading and borders gone | CSV output, which stores no formatting | Export to .xlsx, or reapply a table style |
Fixing merged cells
Merged cells stop you sorting, filtering or making a table. Microsoft's guide to finding merged cells uses the Find dialog: Home > Find & Select > Find, then Options > Format, then Alignment > Merge cells > OK, and Find All. Press Ctrl+A in the results list to select them all, then use Merge & Center > Unmerge Cells.
Unmerging leaves the value in the top-left cell and blanks below it. To fill those blanks with the value above: select the column, press F5, choose Special > Blanks, type = and the up-arrow key, then Ctrl+Enter. Copy the column and paste as values so the formulas do not shift later.
Fixing numbers that will not add up
The classic sign is a column of amounts that are left-aligned, with SUM returning zero. Sometimes Excel flags them with a green triangle and Microsoft's Convert to Number option works straight away. When it does not, the culprit is usually a hidden character copied from the PDF.
Microsoft's data cleaning guide explains why: TRIM removes the ordinary space (character 32) but not the non-breaking space (character 160), and CLEAN removes only control characters 0 to 31. PDFs are full of character 160. A formula that handles all three:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))
If the amounts carry a currency symbol, a trailing "CR", or parentheses for negatives, strip those with SUBSTITUTE as well, or with Find and Replace, before the VALUE step.
Fixing dates
Text dates are left-aligned and will not sort chronologically. If the order matches your own regional setting, DATEVALUE converts them. If the PDF came from a country that writes day first and your PC writes month first, DATEVALUE will swap them silently for any day up to 12. The dependable fix is to bring the data through Power Query and set the column with Change Type > Using Locale, choosing the source country, as described on Microsoft's Power Query data types page. Our article on importing bank statements into Excel walks through that dialog.
How do you convert PDF to Excel properly in the first place?
Prevention beats repair. Before converting:
- Get the best source. Download the PDF again from the original system rather than using a scan or a printed-and-rescanned copy. Text you can select with the cursor means there is a text layer.
- Extract tables, not pages. In Excel's Get Data from PDF, pick the table entries in the Navigator rather than the page entries; in other tools, look for a "table" or "spreadsheet" mode. Our guide to converting a PDF table to Excel compares the table-focused options.
- Choose .xlsx when you care about appearance. A CSV keeps only values; any shading or bold is lost by design.
- Load as text, then type the columns. Turning off automatic type detection during import stops Excel converting values before you have checked them.
- Check one page before converting fifty. If page one is wrong, every page will be.
Is there a free way to convert PDF to Excel without losing formatting?
There are free routes, each with a catch. Excel's Get Data from PDF costs nothing extra if you already have Microsoft 365 for Windows, and it is good with clean, ruled tables. Adobe offers a free online PDF to Excel conversion with limited use. Google Drive can open a PDF as a Google Doc using OCR, which keeps text but rarely keeps the table. Our overview of how to turn a PDF into Excel compares them side by side.
Bank and credit card statements are a special case: dense rows, wrapped merchant names, a running balance and page headers repeated throughout, which is exactly the mix that triggers most rows in the table above. A tool built for statements sidesteps the layout guessing by reading the transactions themselves. QuickBankConvert is one; it returns date, description, category and signed amount columns, and an on-screen preview is free before you sign up. The PDF to Excel format page shows the result, and our statement cleanup tips cover what to check after any conversion.
What "formatting" can and cannot survive
It helps to be clear about the goal. Data structure, meaning values in the right rows and columns with the right types, can survive conversion. Visual formatting, meaning fonts, colours, logos and exact column widths, is a different matter: Excel is a grid, a PDF is a page, and converters that try hardest to copy the look are the ones that produce merged cells. For anything you plan to sort, filter or total, aim for clean structure first and restyle afterwards with a table style.
Related guides
Frequently Asked Questions
Why does my converted PDF have merged cells everywhere?
The converter tried to reproduce the page's appearance. Choose a table-only extraction if the tool offers one, or unmerge the cells in Excel and fill the blanks down.
Why won't my converted numbers add up in Excel?
They are stored as text, often with non-breaking spaces from the PDF. Replace CHAR(160) with a normal space, trim it, then convert with VALUE.
Can a scanned PDF be converted without errors?
Not reliably. OCR has to guess every character, so check totals and spot-check digits, and get a text-based copy of the PDF whenever possible.
Does saving as CSV lose formatting?
Yes. CSV holds only values, so bold, colours and column widths are dropped. Save as .xlsx if you want the look preserved.
What is the most reliable way to convert a bank statement PDF to Excel?
Start from the PDF downloaded from your bank rather than a scan, and use a tool that reads transactions rather than page layout. Then check that opening balance plus transactions equals closing balance.
Ready to convert your bank statement?
Free to preview. No bank login. Results in about a minute.
Convert a statement