How to Convert a PDF to Excel With Power Query

To convert a PDF to Excel without retyping it, use Power Query's PDF connector: Data > Get Data > From File > From PDF. It finds the tables in the PDF, lets you clean them, and loads them into a sheet you can refresh later. This guide walks through the steps, the clean-up most PDF tables need, and what to do if your Excel doesn't have the connector or your PDF is a scan.

Which versions of Excel can convert a PDF?#

The PDF connector is part of Power Query in Excel for Microsoft 365 on Windows. Version support changes over time, so the quickest test is to look: go to Data > Get Data > From File. If From PDF is in the list, you can follow this guide.

If it isn't there, as in Excel 2019 and earlier, you still have options:

The last option is often the best. A PDF is made for printing, and the original file always has cleaner data. If you get a CSV, see how to import a CSV into Excel.

How to convert a PDF to Excel step by step#

The PDF connector reads every table it can find and shows them in a Navigator window. Here's the full process:

1. Open a new or existing workbook and go to Data > Get Data > From File > From PDF.
2. Select the PDF and click Import.
3. In the Navigator, click each item on the left to preview it. Items named Table001, Table002 and so on are tables Excel detected. Items named Page001 are whole pages.
4. Pick the table you want. If the PDF has several, tick Select multiple items.
5. Click Transform Data to clean it first (recommended), or Load to drop it straight into a sheet.
6. In the Power Query Editor, make your fixes (see the next section), then click Home > Close & Load.

Excel creates a new sheet with the data as a formatted table. Start with the Table items: Page items include page numbers, headers and footers that you'd have to remove by hand. If Power Query is new to you, the Power Query beginner's guide explains the editor.

How to clean the table in Power Query#

PDF tables almost always need a little tidying, and Power Query records each fix so it repeats on refresh. These are the common steps, all in the Power Query Editor:

• Headers in the first row. Click Home > Use First Row as Headers.
• Header rows repeated on every page. Click the filter arrow on a column and untick the header text, such as "Amount".
• Blank or total rows. Use Home > Remove Rows > Remove Blank Rows, or filter out the word "Total".
• Extra spaces. Select the text columns and choose Transform > Format > Trim.
• Numbers stored as text. Click the type icon at the left of the column header and choose Decimal Number or Whole Number. Do the same for dates.
• Columns split or merged wrongly. Use Split Column or Merge Columns on the Home and Transform tabs.

Set the data type after removing headers and totals. Otherwise, the header text in a number column gives errors when the type changes.

How to fix numbers stored as text with formulas#

If you loaded the data without cleaning it, or copied it from a PDF reader, numbers often arrive as text. Signs of this: the numbers sit on the left of the cell, a green triangle appears, or =SUM(B2:B6) returns 0. SUM skips text, even text that looks like a number.

Say the amounts in B2:B6 look like " 1,250.00", "980.50 " and so on, with stray spaces. Fix one cell with VALUE and TRIM:

=VALUE(TRIM(B2))

The TRIM function guide shows how TRIM removes the extra spaces, and the VALUE function guide shows how VALUE turns the cleaned text into a number. The result is 1250, a real number.

To total the whole column in one go without a helper column:

=SUMPRODUCT(VALUE(TRIM(B2:B6)))

This returns 5,991.50. SUMPRODUCT handles the range one cell at a time and adds the results.

TRIM removes ordinary spaces only. PDFs sometimes contain non-breaking spaces (character 160), which TRIM leaves behind. Remove them first with SUBSTITUTE:

=VALUE(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))

The cleaning text lesson practises TRIM and SUBSTITUTE on messy data, and the text and value lesson practises VALUE.

What to do without the PDF connector#

Copying and pasting still works when your Excel has no From PDF option, but it needs more clean-up. Open the PDF, select the table, copy it, and paste into Excel.

Often each row lands in a single cell, like "INV-201 1,250.00 Paid". Split it with Data > Text to Columns: choose Delimited, tick Space (or whatever separates the values), and finish. The guide to Text to Columns covers the wizard step by step. Its Advanced button can also handle thousands separators and trailing minus signs, which PDFs often use for negative numbers.

Word can help too: Word can open many PDFs and turn them into editable documents. Tables sometimes come through as real Word tables, which paste into Excel cleanly.

Common problems after converting a PDF#

Most conversion problems show up later, when sorting or totalling. Check these before you use the data:

1. Totals too low. Some numbers are still text. Use =COUNT(B2:B100) and =COUNTA(B2:B100): if the counts differ, some cells aren't numbers.
2. Sorting looks wrong. Text numbers sort as text, so "1,005" can come before "415". The guide to fixing sort problems covers this and other causes.
3. Rows split across pages. A row that breaks across a page can become two rows. Look at the last and first row of each page.
4. Dates in the wrong order. A date like 03/04/2026 may be read as March or April depending on your settings. Set the type with Using Locale in Power Query if needed.
5. Scanned pages missing. If a page is a scanned image, Power Query finds nothing on it.

Always compare one or two totals with the PDF before you rely on the converted data.

Refresh it next month#

The real payoff of Power Query is reuse. If the same report arrives as a PDF every month with the same layout, save the new file under the same name and location, then click Data > Refresh All. Every cleaning step runs again by itself. To point at a differently named file, use Data > Get Data > Data Source Settings and change the source. The comparison of Power Query vs formulas explains when a query beats a formula clean-up.

Practise it#

Converting is quick. Cleaning is where the work is. Try the exercise on this page, then practise cleaning messy text in a live spreadsheet and practise turning text into numbers. Both lessons check your formula instantly.