Convert PDF to Excel and check the table before using the numbers
Import a PDF table through Power Query, spot repeated headers and broken rows, and validate totals before relying on the Excel output.

👁 111 reads
In a supported Excel installation, Power Query’s PDF connector can identify tables and bring them into a workbook. I would treat that output as an imported draft until the row structure, identifiers, and totals agree with the source document.
Start with the kind of PDF you have
A statement generated from software and a scan of a printed statement can look similar on screen while containing very different information. Try selecting a value and searching for a distinctive phrase in the PDF. These are useful first checks, although a searchable scan can also contain an OCR text layer.
If the required text is not available to the extraction tool, address text recognition before expecting a reliable table import. Keep the original PDF throughout the process. It remains the reference for checking values and investigating conversion errors.
I would also identify the exact pages and table needed. Converting a whole report when the task concerns one schedule creates extra cleanup and makes it easier to include a subtotal or explanatory table by mistake.
Import through Power Query
Open Excel’s data-import controls and choose the PDF source where available, then select the file. In Navigator, inspect the detected tables and choose Transform Data when the output needs cleaning before loading.
Microsoft documents the PDF connector for Excel and other Power Query hosts, while noting that capabilities can differ by host and rollout. If the PDF option is absent, check the installed product and supported connectors rather than assuming every Excel edition has the same menu.
For large documents, the connector supports page-range options through Pdf.Tables. Microsoft also documents MultiPageTables for combining similar tables on consecutive pages. Those controls can help narrow the import, but they do not replace checking that the intended rows were captured.
Decide what counts as a data row
PDF layouts often repeat headers at page breaks and place totals among ordinary rows. A spreadsheet needs a consistent meaning for each row before formulas can be trusted.
Consider this deliberately simple example:
| Extracted description | Extracted amount | Interpretation |
|---|---|---|
| Service A | 120 | Detail row |
| Service B | 80 | Detail row |
| Page subtotal | 200 | Summary of the two detail rows |
| Service C | 50 | Detail row |
| Grand total | 250 | Summary of all detail rows |
The intended detail total is 250, from 120 + 80 + 50. Summing every displayed numeric row gives 700, because both summaries are counted again. The figures are synthetic and supplied only to illustrate the validation problem.
I would keep summaries available as control values while excluding them from the detail table used for calculation. Deleting them before recording the expected total discards a useful check.
Inspect the errors that a clean layout can hide
| Potential issue | What to inspect | Why appearance is not enough |
|---|---|---|
| Repeated header | Rows containing column labels inside the table | It may look like an ordinary text record |
| Wrapped description | Adjacent rows belonging to one item | A continuation line can become a false record |
| Identifier converted to a number | Leading zeros and long codes | Formatting cannot recover lost source characters |
| Decimal interpretation | Source punctuation and imported numeric value | A plausible-looking number can have the wrong magnitude |
| Negative value | Minus sign or accounting parentheses | A lost sign changes the meaning |
| Page boundary | Last record on one page and first on the next | The break may split one logical row |
For wrapped rows, Microsoft discusses cleanup operations such as filling values down or grouping related rows. I would apply those only after identifying a reliable rule. Filling every blank cell can spread a value into records where it does not belong.
Keep original extracted columns until the cleaned result passes inspection. A helper column showing the proposed interpretation makes disagreements easier to investigate than overwriting the source values immediately.
Validate before the table feeds another process
My acceptance checks would include the number of detail rows, the total where a meaningful control total exists, and a selection of records from different pages. Include an awkward record with a wrapped description or unusual identifier rather than checking only the cleanest first row.
Compare the source and imported value for any high-consequence field. In a payment schedule, that could be account identifiers and amounts; in an inventory, product codes and quantities. The appropriate review depends on how the spreadsheet will be used.
If the PDF layout changes each month, do not assume last month’s query remains correct. A refreshed table can load successfully while selecting a different detected table or interpreting a new page layout badly.
I would keep a small import note: source filename, relevant pages, cleanup rules, control totals, and review date. The next person can then repeat the check without reverse-engineering the workbook.
Check the file type as well as the numbers
An .xlsx file is an Excel workbook format used on both Windows and macOS. The final x does not mean that the file was created on a Mac. A converter that produces CSV has given you a different kind of output: a text table, not a workbook with multiple sheets and its original formatting.
Before choosing an output, decide whether you need separate worksheets for separate source tables or one continuous table for analysis. Open the downloaded result and inspect the sheet names and row boundaries. A successful download tells you that a file was created; it does not prove that repeated headers, totals or page breaks became the right rows.
FAQ
Can Excel import a PDF without an online converter?
Supported installations can use Power Query’s PDF connector. Check the available connector in your Excel product.
Why is the imported table empty or unusable?
Inspect the PDF’s text and structure, the selected table, and any need for text recognition. A visually readable page does not guarantee extractable table data.
Why does the total become too large?
Look for subtotal and grand-total rows counted alongside detail records. The worked example shows how double-counting can happen.
Should I load every detected table?
Only if the task requires them. I would identify the target table and pages first, then verify coverage.
Can I reuse the same query next month?
Often the workflow is reusable, but check the refreshed result against the new source. Successful refresh is not evidence that the layout remained compatible.
Google · Your sourcesQuantslant higher in your Google - one clickAdd to preferred sources →Sources
-
Microsoft: workbook formats on Mac and Windows - accessed November 16, 2026.
-
Microsoft Learn: Power Query PDF connector - read November 16, 2026.
- Microsoft Support: import data with Power Query - read November 16, 2026.
- Microsoft Support: leading zeros and large numbers - read November 16, 2026.
More on this from QUANTSLANT
ai tools·Turn a Microsoft Teams meeting transcript into a usable decision recordFind and download a Teams transcript, check names and decisions, and prepare a useful meeting record without treating automated text as approval.