A table across many PDF pages, into 1 clean Excel sheet

Convert the whole table

1. Drop the PDF.
2. Sheets: “All on one”. The default, “One per page”, gives a tab per page, which is the wrong shape for 1 long table.
3. Pages: if the table runs from page 12 to page 30 of a longer report, type 12-30. Only those pages are read.
4. Convert, then open the .xlsx.

If you also want to keep a PDF of just the table pages, to send or archive, Extract PDF pages makes one; for the conversion itself the Pages box is enough.

What the converter does at each page break

It reads every page on its own and puts the results one after another. It keeps everything that is printed: the heading row that repeats on each page, the footer, the page number. That is deliberate, because a converter that guesses what to throw away sometimes throws away data. The cost is a few rows of cleanup per page.

We converted an invented 4-page price list with 100 articles: article number, description, unit and price, the headings repeated at the top of every page, “Continued on next page” and “Page 1 of 4” at the bottom, and 1 description that runs off the bottom of page 2 and finishes at the top of page 3. The result card said “Put 112 rows into a spreadsheet, with 100 numbers as numbers you can add up”. The seam between pages 2 and 3 looked like this:

The 100 prices arrived as numbers and the 100 article numbers kept their zeros, as text. On a 48-page version with 1,920 rows, the sheet held 48 heading rows, 48 footer rows and 47 empty rows around the data, every one of them in the predictable places shown above.

Clean it up in 3 moves

1. Mend the broken rows first. Look at the first row after each repeated heading. If it holds only a scrap of text in column A, like row 60, it is the rest of the row before the page break. Join it by hand: click B56, press F2, type a space and “of 25, green”, Enter. Then delete row 60. On most tables this happens on a few pages at most; on a statement with long descriptions it can happen on every page.

2. Mark the real data rows. In the first free column (E here), type Keep in E3, next to the first heading, then in E4 =ISNUMBER(D4) and fill it down to the last row. It shows TRUE where the price column holds a number, which is exactly the data rows, and FALSE for headings, footers, empty rows and title lines.

3. Filter out the rest. Click A3, the first heading, then Data, Filter. In column E’s filter, untick TRUE so only the FALSE rows show. Select the visible rows below row 3, right-click, Delete Row. Clear the filter: the data rows remain under 1 heading. Delete the title rows above it if you do not need them, and column E.

If your table has no amount column, use a column that is always filled in data rows and never in headings, such as the article number with =LEN(A4)=6.

Check the result

1. =COUNT(D:D) counts the numbers in the price column. Our list printed “100 articles” at the end; the count was 100.
2. =SUM(D:D) must match the table’s printed total if it has one. Our 100 prices summed to 2065, the total of the source data.
3. Scroll to each former seam once and read the row above and below. Rows broken across pages are the only place where the converter can put text in a row of its own.

Do the count before and after cleanup. If cleanup deleted a data row by mistake, the count drops and tells you.

When the table sits among pages of prose

A 60-page report with the table on pages 41 to 48 converts fine as a whole, but you then delete 40 pages of paragraphs. Find the table’s first and last page in your PDF viewer and type them into Pages instead. In our test, converting pages 2 to 3 of the price list gave 55 rows and 50 prices: exactly those 2 pages, nothing else.

The honest part

Rows that break across a page are the weak spot: the converter cannot know that a scrap of text at the top of page 3 belongs to the last row of page 2, so it gives it a row of its own, and you join it. Check every seam once; on a table with short, 1-line rows there is often nothing to fix.

The converter also does not decide which rows are headings or footers for you, so the 3 cleanup moves above are yours to do. Columns are worked out from where the text sits; if a page puts text in the wrong columns, see PDF to Excel wrong columns. A scanned table has no text to read: run OCR PDF first. And if the source of the table offers it as a spreadsheet or CSV, take that; it has no page breaks to mend. For bank statements in particular, the month-by-month routine is in Bank statement to Excel.