OmniDocScanned Accounts to Pivot-Ready Excel: OCR First
Learn how to run OCR on a scanned accounting statement, export it to Excel, clean the table, and build a pivot table from the figures.
Accounting teams often receive bank statements, vendor ledgers and expense summaries as scanned PDFs. The figures are there, but they are stuck in an image. Retyping a 30-page statement by hand is slow, and copying from the image line by line invites mistakes that are hard to spot later.
This guide shows you how to get those figures into a usable Excel table and then build a pivot table for accounting analysis. The method is simple: run OCR on the scanned PDF, export the recognized table to Excel, clean up the layout, and pivot the result. You will end with a sheet where each row is one transaction and each column is one field.
How do you build a pivot table for accounting from a scanned statement? The short answer
Run OCR on the scanned PDF first, export the recognized tables to Excel, clean the sheet, and then pivot the cleaned block.
| Your situation | What to do | Why |
|---|---|---|
| The statement is an image-only scan | Run OCR in the PDF editor before exporting | Excel can only receive text that already exists in the file |
| The PDF already has selectable text | Skip OCR and export directly | Running OCR again adds time without adding data |
| The table spans several pages | Export all pages, then stack them into one block | A pivot table needs every transaction in one range |
| You need totals by category or month | Convert dates and amounts to real values before pivoting | Text dates and numbers stored as text cannot be grouped or summed |
| The figures must match the bank total | Compare the pivot grand total with the closing balance | This catches OCR misreads and rows lost during cleanup |
Before you start
Gather these items so the conversion goes smoothly.
- The original scan at full resolution. Blurry or skewed pages produce more recognition errors. If you can, rescan the pages at a higher setting before you start.
- The statement's closing balance and totals. You will use these to check the result. Without a known total, you cannot tell whether a row was dropped or misread.
- A list of the categories you plan to pivot by. Decide whether you need vendor, account code, expense type or month. Knowing this tells you which columns must be clean.
- OmniDoc desktop client installed. The OCR and Excel export steps run in the desktop client, which you can get from download.omnidoc.top. The PDF editor that handles these steps is part of the desktop client.
- A copy of the original file. Keep the exported Excel file separate from the original PDF so you can always go back to the source.
Converting the scanned statement, step by step
- Open the scanned PDF in the OmniDoc desktop client. Use the PDF editor, since its OCR feature works on scanned PDFs. The original file stays unchanged.
- Run OCR on the whole document. Recognition turns the page images into text that can be selected and exported. Run it on every page, not just the first, so the later pages also come through.
- Spot-check a few lines against the scan. Pick one page with numbers and compare it to the image. Look especially at 1, 7, 0 and 8, and at minus signs and parentheses used for negative amounts.
- Export to Excel. Use the export option for Excel from the PDF editor. Each recognized table should land in a sheet you can open in Excel.
- Stack the pages into one block. Copy the transaction rows from each sheet into a single sheet, one under the other. Keep only one header row at the top. Delete repeated headers that appeared at the start of each page.
- Clean up the non-transaction rows. Remove page titles, account summaries, running balance lines and subtotals. A subtotal row left in the data will be counted again in the pivot and double your totals.
- Fix the data types. Convert the date column to real Excel dates and the amount column to numbers. Remove currency symbols and thousands separators. Negative values in parentheses should become negative numbers.
- Turn the block into a range and pivot it. Select the cleaned block, including the header row, and create a pivot table from it. Put the category in rows, the date grouped by month in columns, and the amount in values set to sum.
Check the result
Before you use the pivot table in a report, run these checks.
| Check | How to see it | If it's wrong |
|---|---|---|
| Grand total matches the statement | Compare the pivot's grand total with the statement's total or net change | Look for dropped rows or a subtotal still in the data, then re-check the OCR on those pages |
| Digits read correctly | Sample five to ten amounts against the scan | Correct the cell by hand, then re-sum; check the same digit pattern elsewhere |
| Negative amounts are negative | Filter for amounts under zero and compare with parentheses in the scan | Re-enter the sign and confirm the total moves as expected |
| Dates group correctly | Group the date field by month and look for blank or error groups | Retype the affected dates as real dates, not text |
| No duplicate rows | Sort by date and amount and look for identical neighbors | Remove duplicates that came from repeated page headers or overlapping pages |
OCR is not perfect, so treat the exported figures as a draft until you have checked them against the source. For anything that goes into a filing, audit file or payment run, check every figure that affects a total.
Which Excel formulas help after the export?
Once the data is clean, a few standard formulas handle common questions without rebuilding the pivot. A SUMIFS formula can total one category for one month, which is useful for a quick check. A COUNTIFS formula can count transactions above a threshold, which helps spot unusual items. Use these alongside the pivot table, not in place of it, and check that each formula refers to the full cleaned range.
❓ FAQ
1. Why does my pivot table show a count instead of a sum?
Excel defaults to counting when the amount column contains text or blank cells. Convert the amounts to numbers as described in step 7, then change the value field to sum.
2. Do I need to keep the original scanned PDF after exporting?
Yes. Keep the original as your source record, so you can recheck any figure against the image if a total looks wrong later.
3. Why did my pivot table stop matching the data after I made corrections?
A pivot table only reflects the data as it was when it was last refreshed. After you correct cells or add rows, refresh the pivot table before you compare totals again.
4. What if OCR merges two columns into one cell?
Split the combined text by hand for a few rows, or use Excel's text-splitting tools on the affected column. Then check that the amounts still match the scan, since a merged cell often hides a misread digit.
📝 Summary
Scanned accounting statements become analyzable once the figures are text. Run OCR, export to Excel, clean the block, and then build the pivot table, checking the totals at each stage.
