PDF to Excel keeps the page headers and footers? How to remove them

What gets carried over

A PDF report is a stack of printed pages, and every page repeats the same frame around the data. A converter can't tell that frame from the rows, so you usually get, once per page:

• the report title, company name and date range
• the column headings
• a footer such as "Printed by", the print date and "Page 3 of 9"
• sometimes a page total just above the footer

The good news: these lines are almost identical on every page, so one rule removes all of them.

If you use Get Data from PDF, pick the tables, not the pages

In Excel for Microsoft 365 on Windows, Data > Get Data > From File > From PDF opens a Navigator that lists two kinds of items: Table entries and Page entries. The Table entries usually leave out the page header and footer; the Page entries include everything printed on the page. Preview a Table entry first.

A long report often shows up as one table per page. To stack them, and to remove the repeated headings and page totals that remain, follow the Power Query cleanup steps. Those steps are saved, so next week's PDF gets the same treatment on refresh.

After converting: filter the junk out in Excel

Good for: any converter, any version of Excel.

1. Delete the header lines above the first real row, so the column headings are in row 1.
2. Click in the headings and turn on Data > Filter.
3. Open the filter on the first column. Untick Select All, then tick the values that come from the page frame: the heading text itself (such as "Invoice #"), the report title, "Page ..." lines and (Blanks).
4. Select the visible rows by their row numbers, right-click and choose Delete Row. Excel deletes only the visible rows.
5. Clear the filter.

If the junk lines don't all share one column, add a helper column that flags them. For example, in a spare column:

=OR(A2="Invoice #", ISNUMBER(SEARCH("Page ", A2)), ISNUMBER(SEARCH("Printed", A2)), COUNTA(A2:F2)=0)

Copy it down, filter the helper column to TRUE, delete those rows, then delete the helper column. Change the text in quotes to match your report.

Page totals and running totals

Page totals are the dangerous ones: they look like data rows, so they double the total when you sum or pivot. Look for a row with an amount but no invoice number or date, or the words "Page Total" or "Carried forward", and remove them with the same filter. Keep the grand total from the last page on a note so you can check the cleaned sheet against it.

Before converting: cropping and redaction

Another approach is to remove the header and footer from the PDF before you convert it:

• Crop the pages. PDF editors such as Adobe Acrobat can crop every page to just the table area. Check the result: some converters still read text outside the cropped area, because cropping hides it rather than deleting it.
• Redact the header and footer. A redaction tool removes the text itself. It's thorough, but slow to set up for every report.
• Acrobat's Remove Header & Footer only removes headers and footers that were added in Acrobat. It won't touch the ones your business software printed.

Some converters, such as Able2Extract and A-PDF To Excel, also have settings to skip headers and footers by page area. These work well for one report layout and need setting up again for the next.

If it's the same report every week

Filtering takes a few minutes. The weekly cost is that it never ends, and the summary someone asked for still has to be built afterward.

Wheelbarrow reads every page of the PDF and is set up to skip the repeated headings, footers and page totals, so they never reach your sheet. You describe the result in plain words, for example "one row per invoice, a tab per customer, a summary with totals", and see it built from your own PDF before you pay. Next week, drop in the new PDF and the finished workbook comes out in seconds, on your PC (even offline) or your phone.