To import a PDF to Excel, go to Data, Get Data, From File, From PDF, choose the file, pick a table in the Navigator and select Load or Transform Data. Excel reads the tables it can find and loads them as a query you can refresh. This works only for PDFs that contain text.
Which Excel versions can import PDF files?
You can import a PDF only if your copy of Excel includes Power Query, Excel's built-in data import and shaping tool, and the From PDF connector within it. Check your own menu: if Data, Get Data, From File lists From PDF, you have it. If it is missing, update Excel or use another route.
How to check in ten seconds
Open Excel, select the Data tab and look at the Get Data button on the left of the ribbon. Open it, point to From File and read the list. You are looking for From PDF next to From Workbook, From Text/CSV and From XML. Menus can differ between versions and between Windows, Mac and Excel for the web, so match the wording on your screen, not this post.
Microsoft's Power Query PDF connector page lists Excel among the products that support the connector, with the connector in general availability. It also notes that some capabilities may be present in one product but not others, because of deployment schedules and host-specific features. In plain terms, do not assume that a menu you saw in a tutorial exists in your version.
If the option is missing
You have three options. Update Excel through your Microsoft 365 account or your IT department. Use a different route, such as the PDF reader export covered in the post on saving a PDF as Excel, or an online converter. Or, on a Mac, read whether your version offers the import at all, in the guide on whether Excel for Mac imports PDF files.
What the import cannot do
The import reads tables from PDFs that contain real text. It is not an OCR tool. A scanned page is a picture, and Excel will list nothing useful for it. If you are not sure which kind of file you have, the quickest test is to try selecting a line of text in the PDF with your cursor.
Get Data from PDF: the import steps
The import takes a handful of clicks. You open Get Data, point it at the PDF, choose from the tables Excel finds, and load them. The numbered list below is the full click path, with the choice to make at each step. Menu names may vary slightly by version.
The click path
- Open the Data tab in a blank workbook. A blank workbook keeps the result easy to find.
- Choose Get Data, then From File, then From PDF. The button may read Get Data or From Text/CSV and other icons, depending on the width of your window.
- Select your PDF in the file window and choose Import. A PDF with a password will not open here until you remove the password.
- Wait for the Navigator. This window lists what Excel found in the file. Large files take longer, and Excel may show a loading message first.
- Click an entry in the list to see a preview on the right. Check that columns and rows look like your table. The next section covers which entry to pick.
- Choose Load to put the table straight into a new sheet, or Transform Data to open the Power Query editor and shape it first.
- In the editor, use Close and Load on the Home tab when you are finished. The table lands on a sheet as an Excel table.
- Save the workbook. The query is saved with it, ready to refresh.
What the connector does behind the scenes
When you pick a file, Excel runs a function called Pdf.Tables. Microsoft documents its options, including a start page, an end page, a switch for combining similar tables on consecutive pages (on by default), and a switch for how border lines are treated. You do not need to touch these for a first import. They matter when a file is large or a table is split oddly, and you can see them in the formula bar of the editor.
Check the result straight away
Before you shape anything, look at three things. Are the column headings in row one or in the data? Do amounts show as numbers, aligned to the right? Is the last row of the table the last row of the PDF? If the answers are yes, you are most of the way done. If not, the cleanup guide shows what needs transforming after a PDF lands in Excel.
Picking tables and pages in the PDF import Navigator
The Navigator is the window that lists what Excel found in the PDF, and your job is to choose which entries to load. Entries are of two kinds: tables, which Excel detected as rows and columns, and pages, which hold everything found on a whole page. Tables are usually what you want.
Tables versus pages
Tables are listed with names that include a table number and the page they came from, and pages are listed with a page number. Think of a table entry as Excel saying "I think this block is a table", and a page entry as "here is everything on this page, split into columns as best I can". If table detection missed your table, the page entry is a fallback, but you will need to clean it.
How to choose
Click each entry once and read the preview. Look for:
- Headings that match your PDF's column titles.
- The right number of columns, not one column holding everything.
- A full table, not a fragment of it.
- No page furniture, such as a page number or a title mixed into the first row.
If the file is long, the Navigator may list many entries. Excel's connector can combine similar tables on consecutive pages into one entry on its own, which is why you sometimes see one long table instead of one per page. If you see a single entry that spans several pages, try that one first.
Loading more than one entry
Tick the Select multiple items box at the top of the list to load several tables at once. Each becomes its own query. Do this when the PDF holds separate tables, such as a summary and a transaction list, and you want both. Do not do it to stack pages. For that, use the append step in the next section.
Appending PDF import tables across pages
Appending stacks tables from several queries into one long table. You need it when the Navigator lists a separate table for each page and Excel did not combine them. The Append Queries command in the Power Query editor adds the rows of one query under the rows of another.
Steps to stack page tables
- In the Navigator, tick Select multiple items and tick each page table. Then choose Transform Data.
- In the editor, check that every table has the same number of columns, in the same order, with the same type of content. Rename columns so the names match across queries.
- On the Home tab, choose Append Queries, then Append Queries as New. A new query is cleaner than changing an original.
- Pick Three or more tables if you have more than two, add each table from the list, and choose OK.
- Remove repeated heading rows that came from each page. A filter on a column, removing rows that equal the heading text, usually does it.
- Choose Close and Load.
An example, with made-up numbers
Suppose a three-page file produces tables of 38, 40 and 22 data rows, and each page repeats the heading row. Appended, you get 100 rows plus three heading rows. After you filter out the repeated headings you have 100 data rows. If your count differs from the PDF, a page was missed or a heading was kept.
Keep the columns aligned
Appending depends on matching column names. If page two arrives with "Description" and page three with "Column2", the append will place them side by side instead of stacking them. Rename before you append. Watch for a page that is split into one more or one fewer column than the rest. If the pages have already landed on separate sheets, the guide to combining one sheet per page into a single sheet stacks them with formulas instead.
Why does the Excel PDF import skip rows?
Excel's PDF import can skip rows because table detection works from positions on the page, and it can miss a row that sits oddly. The usual causes are a table split over a page break, wrapped text, rows with different spacing, and tables with no ruled lines. The data is in the file, but the detection did not include it.
Common causes and what to try
- Rows at a page break. The last row on a page, or the first on the next, may fall outside the detected table. Compare row counts page by page.
- Wrapped descriptions. Text that runs onto a second line may be read as a separate row or as part of the row above. Microsoft's connector page notes that multi-line rows are not always identified correctly and suggests cleanup with steps like Table.FillDown or Table.Group.
- Sparse or merged cells. A heading that spans columns can leave blanks that shift the columns.
- Tables without lines. The connector has an option about whether border lines are enforced as cell boundaries. Try changing it only if you know your table has real ruled lines.
- A page that fell outside the page range. Check the start and end page options if you set any.
Find the gap with a total
You do not need to compare every row. Add the amount column and compare it with the total on the PDF. A difference means a row is missing, and its size is often the amount of the missing row, which you can then search for in the PDF. On a bank statement, check that the opening balance plus credits minus debits equals the closing balance. FastBankConvert, which is getting ready to open, runs that check for statements and says so before you download if the balances do not add up.
When to stop fixing
If a file keeps dropping rows after two or three attempts, the layout is working against the connector. Try the page entry instead of the table entry, or switch tools. The guide to opening a PDF in Excel and the page on converting a PDF to an Excel spreadsheet you can sort and sum cover other starting points.
Refreshing a PDF import when next month's file arrives
Yes, you can reuse the same query. A Power Query import stores the steps, not just the data, so next month's PDF can flow through the same cleanup if its layout matches. You point the query at the new file and refresh. If the layout has changed, the steps may fail and need adjusting.
Two ways to switch the file
The first way is to replace the old PDF on disk with the new one under the same name and folder, then use Data, Refresh All. The second is to change the source inside the query: open the query, select the Source step and edit the file path, or use the data source settings to change the file location. Either way, check the result, because a refresh will not tell you that a page is missing.
What can break a refresh
A refresh fails or returns wrong data when:
- The page count changes and the query names a fixed table, such as the third one in the list.
- Column headings change, because later steps refer to names.
- The new PDF is a scan or has different table lines.
- The file is moved or renamed.
To make a refresh more reliable, avoid steps that depend on a table's position in the list. After each refresh, run the same two checks: compare the last row with the PDF and compare the column total with the printed one.
Many files, one folder
If you handle a PDF every month, Microsoft's connector page points to the Folder connector for importing many PDF files at once. That is a different setup from a single-file import, and it is worth trying only after the single-file query works.
For a bank statement, the point of Excel's import is the repeatable query. If you want the same result without building one, FastBankConvert is aimed at statement PDFs, and you can get started to be told when it opens.