Clean PDF-to-Excel data: numbers, columns and totals

Before sorting or calculating, duplicate the extracted worksheet and keep the original PDF beside it. First fix the table structure, then check cell types, and only then reconcile totals. Formatting a cell as a number does not necessarily convert text into numeric data.

This guide starts after conversion. For file selection and extraction limits, see how to extract PDF tables to Excel or open PDF to Excel.

1. Repair columns before formulas

Find a source row that has an empty field and compare every cell across it. If an amount has moved into the description column, fix the alignment against the PDF; sorting the sheet now would make that mistake harder to trace. Remove repeated page headers from the working data range and keep summary rows outside the transaction range.

Unmerge headings only when necessary for sorting or filtering. Preserve their meaning as a single header row. Do not fill a blank cell from the row above unless the PDF clearly indicates that the label applies to both records.

2. Separate amounts from identifiers

Amounts need numeric values; account codes and reference numbers usually need text. Use =ISNUMBER(B2) on an amount cell to check its type. Where Excel flags numeric text, use its conversion control or test =VALUE(B2) in a separate column. Check the result before replacing the original.

Decimal and grouping separators depend on the source and your settings. The text 1,234 can represent different values in different conventions. Confirm the intended value from the PDF instead of removing punctuation across the entire worksheet.

Microsoft documents how to convert numeric text. Keep identifiers as text when leading zeros matter. Excel has a 15-significant-digit precision limit for numbers; see Microsoft's guidance on leading zeros and long identifiers. Formatting afterward cannot recover digits already lost.

3. Reconcile a small example

Consider three illustrative source amounts: 125.50, -20.00 and 9.25. Their total is 114.75. If the negative amount is stored as text and omitted by a sum, a numeric-only total could incorrectly show 134.75. Check both the individual values and their cell types when a total differs.

This is a worked example, not a measured converter accuracy score. In a real report, reconcile each page subtotal before the grand total. Ensure you have not included both transaction rows and their printed subtotal in the same calculation.

4. Verify dates and missing values

A date such as 03/04/2026 is ambiguous without a known day/month convention. Preserve the source text until you can confirm the convention from an unambiguous date or the report instructions. A blank, a dash and zero may have different meanings; keep those distinctions unless the source defines them as equivalent.

5. Keep a review trail

• Retain the source PDF and raw extraction.
• Record corrections in a separate sheet with the source page and row.
• Compare the row count, a negative amount, a long identifier and the final total.
• Save the cleaned workbook as a new file. Check it again after reopening.

If extraction repeatedly loses structure, return to the source-selection checks. If you need a fixed-layout copy after cleanup, follow the Excel-to-PDF print preparation guide.