Summary

A supplier sends the price list as a PDF, the bank sends the statement as a PDF, and you need the numbers in columns you can total. Excel has a PDF importer built in — Data > Get Data > From File > From PDF — which reads the tables out of the file on your own computer, with no converter website and no retyping. This article covers that path, what to do when the table arrives split or mangled, the copy-and-paste fallback for a small table, and the one kind of PDF none of it works on.

In this guide

• 1. Check the PDF Holds Real Text, Not a Picture
• 2. Import the PDF with Data > Get Data > From File > From PDF
• 3. Pick the Right Table in the Navigator
• 4. Clean Up a Table That Imported Badly
• 5. Join a Table That Split Across Pages
• 6. Copy and Paste a Small Table Instead

Before You Start

• Excel for Windows, desktop version, Excel 2016 or later. The From PDF connector is not in the source list for Excel for Mac, and Excel for the web offers PDF only for files held in SharePoint.
• The PDF file saved somewhere you can browse to. Download it out of the email first rather than opening it from the preview pane.
• A PDF that contains real text rather than a scan. Step 1 shows you how to tell in five seconds.
• No admin rights required, unless Excel prompts to install a missing component — the Troubleshooting table has the wording for that ticket.

Instructions

1. Check the PDF Holds Real Text, Not a Picture

This is the five-second test that decides whether any of the rest applies to your file.

1. Open the PDF — double-click it, or right-click it and choose Open with > Microsoft Edge.
2. Drag your cursor across a line of text in the document.
3. Read the result. Individual words highlighting one at a time means the file holds real text and everything below will work. A blue box covering the whole page, or nothing highlighting at all, means the page is an image.
4. Confirm it with a search: press Ctrl + F, type a word you can plainly see on the page, and press Enter. 0/0 or No results for a word that is right there is the signature of a scan.

If it is a scan, stop here. Excel cannot find tables in a photograph, and the Navigator in the next step will come back empty. Run the file through text recognition first — How to Copy Text from a Scanned PDF or Picture covers the tools that do it — then come back and import the result.

Note: A PDF produced by a system — an accounting package, a bank, a web report — almost always contains real text. A PDF that came off an office scanner or arrived as a photo of a printout almost never does.

2. Import the PDF with Data > Get Data > From File > From PDF

Most people have never noticed this menu item, and it is the whole article.

1. Open Excel and create a blank workbook, or open the workbook the data belongs in.
2. Open the Data tab on the ribbon.
3. In the Get & Transform Data group at the far left, click Get Data.
4. Point to From File, then click From PDF in the submenu that opens.
5. Browse to your PDF, select it, and click Open.
6. Wait while Excel reads the file. A large document takes a minute or two — the window stays blank until it finishes, which is normal.

The Navigator window opens with a list down the left side and a preview pane filling the rest.

Note: If From PDF is missing from the From File submenu, check the version scope in Before You Start. If Excel shows This connector requires one or more additional components to be installed before it can be used, see the Troubleshooting table.

3. Pick the Right Table in the Navigator

The list on the left is Excel's reading of your document, and the names tell you what each item is.

1. Read the item names in the left pane. Items named Table001 (Page 1), Table002 (Page 2) and so on are tables Excel detected. Items named Page001, Page002 are whole pages captured as one block, including the headings and paragraphs around the table.
2. Click an item. The right pane fills with a preview grid so you can check you have the correct one before importing anything.
3. Work down the list until the preview matches the table you want. A table that spans several pages usually appears as one Table00n entry per page — that case is step 5.
4. To bring in more than one item, check Select multiple items above the list, then tick each item you want. The preview switches to a summary of what you selected.
5. Choose how it lands:
   • Load — drops the table onto a new worksheet as an Excel table. Use this when the preview already looks right.
   • Load > Load To — opens the Import Data dialog so you can choose the worksheet and starting cell, or create a PivotTable straight away.
   • Transform Data — opens the Power Query Editor so you can clean the table before it lands. Use this when the preview looks rough.
6. Click your choice. With Load, the Navigator closes and the data appears on a new sheet, with the Queries & Connections pane listing the query on the right.

Verification: Click a cell that should hold a number and look at the status bar at the bottom of the Excel window. A Sum and Average appear when Excel is holding a number. Nothing but Count means the value came in as text — step 4 fixes that.

4. Clean Up a Table That Imported Badly

PDF tables carry their layout, not their structure, so headings land in the wrong place and columns arrive as text more often than not.

The editor you land in is Power Query, and the generic cleanup — promoting headers, dropping junk rows, setting column types, splitting a column — works the same for any source: How to Use Power Query in Excel covers it properly, including how to rerun the whole cleanup on next month's file. Two problems are specific to PDFs, and both are fixed in that editor:

1. Click any cell in the imported table, click the Query tab that appears on the ribbon, and click Edit. The Power Query Editor opens. (If you have not loaded it yet, click Transform Data in the Navigator instead.)
2. If a currency column refuses to become a number, the values still carry a $ or a thousands separator that came across as part of the text. Right-click the heading, choose Replace Values, type the character in Value To Find, leave Replace With empty, click OK, then set the column type.
3. If rows look merged or shifted by one, the PDF drew a value once across several printed rows and only the first row received it. Select the affected column and click Transform > Fill > Down to carry each value into the blank cells beneath it. Fill Down writes into empty cells only, nothing already there is overwritten, and deleting the step from Applied Steps puts the blanks straight back.
4. Click Home > Close & Load. The editor closes and the cleaned table lands in the workbook.

5. Join a Table That Split Across Pages

A table running over four pages arrives as four separate items, each with its own copy of the header row.

1. In the Navigator, check Select multiple items.
2. Tick every item belonging to the table — Table001 (Page 1), Table002 (Page 2), and so on. Click each one first to confirm the preview shows the same columns.
3. Click Transform Data. The Power Query Editor opens with one query per item, listed in the Queries pane down the left.
4. Click the first query in that list.
5. Click Home, then click Append Queries in the Combine group, then click Append Queries as New.
6. In the Append dialog, select Three or more tables. Click each query in Available table(s) and click Add until they all appear in Tables to append, in page order.
7. Click OK. A new query named Append1 appears in the Queries pane, holding every row from every page.
8. Click Home > Use First Row as Headers to promote the header row from page 1.
9. Remove the repeated headers from the later pages: click the dropdown arrow on the first column heading, clear the checkbox beside the heading text where it appears as a data value, and click OK.
10. Click Home > Close & Load. One continuous table lands in the workbook.

Note: If page 2 onward lost the header row and came in as Column1, Column2 while page 1 has real names, promote headers on the page-1 query before appending. Mismatched column names make Power Query stack the pages side by side instead of end to end, which shows as a table twice as wide as it should be.

6. Copy and Paste a Small Table Instead

For a dozen rows, opening Power Query costs more than it saves.

1. Open the PDF in Microsoft Edge or your PDF reader.
2. Drag across the table to select it, then press Ctrl + C.
3. Switch to Excel and click the cell where the table should start.
4. Right-click that cell and, under Paste Options in the menu, click Keep Text Only — the clipboard icon marked A. This strips the PDF's fonts and boxes, which otherwise arrive as unusable formatting.
5. Look at what landed. Each row of the PDF becomes one row in Excel, with all its values crammed into a single column.
6. Select that column by clicking its letter.
7. Open the Data tab and click Text to Columns in the Data Tools group.
8. In the Convert Text to Columns Wizard, select Delimited and click Next.
9. Check the separator the data actually uses — Space for most PDF tables, Tab for some, or Other with the character typed into the box. Watch the Data preview panel at the bottom: vertical lines appear where the split will happen.
10. Click Finish. The values spread across separate columns.

Splitting on spaces breaks any value that contains a space — New York becomes two columns, and a name column throws the rest of the row out of alignment. When that happens, undo with Ctrl + Z and use the From PDF import in step 2 instead. How to Split Text into Columns in Excel covers the wizard's other options, including fixed-width splitting.

Troubleshooting

WARNING

Free PDF-to-Excel websites ask you to upload the file to someone else's server. An invoice, a bank statement, a payroll report, or a customer list handed to an unknown third party is a data protection problem regardless of how good the conversion is. Everything in this article runs on your own computer, and the file never leaves it.

Symptom / Error	Potential Cause	Solution
From PDF is missing from the From File submenu	Excel for Mac, Excel for the web, or Excel 2013 and earlier	The connector is in Excel for Windows, 2016 and later. On a Mac or the web, use the copy-and-paste route in step 6.
This connector requires one or more additional components to be installed before it can be used	.NET Framework 4.5 or higher is missing	Installing it needs admin rights. Ask IT to install .NET Framework 4.5 or later on your machine and include this message and the Excel version from File > Account.
The Navigator opens with an empty list	The PDF is a scan, or has no detectable tables	Run the text test in step 1. If it is a scan, use How to Copy Text from a Scanned PDF or Picture first.
Numbers imported as text and will not add up	Currency symbols or thousands separators in the values	In the Power Query Editor, use Replace Values to strip the character, then set the column type (step 4).
The table came in as one column per page instead of end to end	Column names differ between the appended pages	Promote headers on each page's query before appending, so the column names match (step 5).
Excel hangs for several minutes on a large PDF	Every page is being scanned for tables	Expected on documents of a hundred pages or more. Leave it running; if it has to be repeated often, split the PDF and import the pages you need.
Rows are merged or shifted by one	The PDF used merged cells or wrapped text across lines	In the Power Query Editor, select the affected column and click Transform > Fill > Down to carry values into the blank cells below them (step 4).

Related Articles

• How to Use Power Query in Excel
• How to Copy Text from a Scanned PDF or Picture
• How to Convert a PDF to an Editable Word Document
• How to Open a CSV File in Excel Without Breaking It

Did this solve your problem?