Extracting data from a PDF file and placing it into an Excel spreadsheet used to be one of the most frustrating tasks in office administration. Historically, it involved tedious manual retyping or messy copy-pasting that destroyed table alignments. However, modern versions of Microsoft Excel have integrated powerful connectors that can recognize tables within PDF documents and convert them into clean, structured data in seconds.

The most efficient way to achieve this is using the built-in "Get Data" feature located under the Data tab. This method utilizes Power Query, a sophisticated engine that allows you to preview, filter, and clean your data before it ever touches your spreadsheet cells.

The Best Method to Import PDF Tables into Excel Using Power Query

If you are using Microsoft 365, Excel 2021, or Excel 2019, you have access to a native PDF connector. This is the professional standard for data extraction because it treats the PDF not just as a document, but as a database.

Step 1: Locating the PDF Connector

Start by opening a new or existing Excel workbook. Navigate to the Data tab on the top ribbon. On the far left, look for the Get Data button. Click it, then hover over From File, and finally select From PDF.

A file browser will appear. Locate the PDF document containing the data you need and click Import.

Step 2: Using the Navigator Window

Once you click import, Excel doesn't just dump the file into the sheet. Instead, it opens a Navigator window. This is a critical bridge where Excel shows you every individual table and page it has detected within the PDF.

On the left pane of the Navigator, you will see folders representing the document. If your PDF has multiple tables across different pages, they will be listed as "Table 001," "Table 002," etc. Clicking on a table will show you a live preview on the right.

In our testing with complex financial statements, we found that selecting the "Table" option is usually superior to selecting the "Page" option. The Table recognition algorithm is specifically tuned to identify headers and row structures, whereas the Page option simply grabs all text it finds on that physical page, which often results in more cleaning work later.

Step 3: Transform Data vs. Load

At the bottom of the Navigator window, you have two primary choices: Load and Transform Data.

If the preview looks perfect, click Load to bring the data directly into your worksheet. However, PDFs are rarely perfect. Tables might have merged cells that confuse the importer, or there might be unwanted headers and footers from the document. In these cases, clicking Transform Data is essential.

Step 4: Cleaning Data in the Power Query Editor

When you select Transform Data, the Power Query Editor opens in a new window. This is where the real magic happens. Here, you can perform several high-value actions:

  • Remove Top Rows: If the PDF has a logo or title at the top that shifted into your data, you can strip those rows out.
  • Use First Row as Headers: Often, Excel might not realize the first row is a header. One click here fixes that alignment.
  • Split Columns: If the PDF had two columns very close together and Excel merged them, you can split them by a delimiter (like a space or a dash).
  • Fill Down: If you have a category name in one cell and empty cells below it that should have the same name, the "Fill Down" feature automates this instantly.

Once your data looks pristine in the editor, click Close & Load in the top left corner. Your data will now appear in your Excel sheet as a formatted table.

Why the Get Data From PDF Option Might Be Missing

A common point of frustration for many users is clicking the Data tab only to find that the "From PDF" option is nowhere to be found. There are three primary reasons for this.

Version Compatibility

The PDF connector is a relatively modern addition. It is available in the subscription-based Microsoft 365 and the standalone versions of Excel 2016 (ProPlus), 2019, and 2021. If you are using Excel 2013 or older, this feature simply does not exist. You will need to upgrade or use one of the intermediary methods described later in this article.

Windows vs. macOS Limitations

In our experience troubleshooting office workflows, we frequently encounter Mac users who cannot find this feature. As of current releases, the "From PDF" connector is a Windows-exclusive feature for Excel. While the Mac version of Excel has received significant Power Query updates recently, the direct PDF connector remains missing. Mac users should look toward the Word-intermediary method or online converters.

Subscription Type

Even within Office 365, some "Home" or "Student" editions may have delayed feature rollouts compared to the "Enterprise" or "ProPlus" versions. If you are on Windows and have a modern version but still lack the button, ensure your Office installation is fully updated by going to File > Account > Update Options.

How to Insert a PDF into Excel as a Viewable Object

Sometimes, you don't actually need to edit the numbers inside the PDF; you just need the document to be accessible within the Excel file for reference. This is common for attaching a signed contract to a budget or an invoice to a reimbursement form.

Embedding the File

  1. Go to the Insert tab.
  2. On the right side of the ribbon, find the Text group and click Object.
  3. In the dialog box, go to the Create from File tab.
  4. Click Browse and select your PDF.
  5. Check the box that says Display as icon if you want to keep the spreadsheet clean. If you leave it unchecked, Excel will display the first page of the PDF as a large image.
  6. Click OK.

Anchoring the PDF to a Cell

A common complaint with inserting objects is that they "float" around the screen. When you add or delete rows, the PDF icon stays in the same spot, eventually overlapping with other data.

To fix this, right-click the PDF icon and select Format Object. Navigate to the Properties tab and select Move and size with cells. This anchors the document to the specific cell underneath it, ensuring it stays in context regardless of how you filter or sort your data.

Alternative Methods for Older Versions of Excel

If your version of Excel lacks the "Get Data" feature, you don't have to resort to manual typing. You can use other software as a bridge to get the data into a format Excel understands.

The Microsoft Word Bridge

Microsoft Word has a surprisingly robust PDF conversion engine.

  1. Right-click your PDF file in your computer's file explorer.
  2. Select Open With > Word.
  3. Word will notify you that it is converting the PDF to an editable document. Click OK.
  4. Once the document opens, the tables should be editable. Simply highlight the table, copy it (Ctrl + C), and paste it into Excel (Ctrl + V).

This method often preserves formatting better than a direct copy-paste from a PDF reader because Word does the heavy lifting of interpreting the document's layout first.

The Google Sheets Intermediary

Google Sheets is a free tool that handles PDF imports through its integration with Google Drive.

  1. Upload your PDF to Google Drive.
  2. Right-click the file in Drive and select Open with > Google Docs.
  3. Google Docs will perform OCR (Optical Character Recognition) on the file.
  4. Copy the resulting table from Docs and paste it into Google Sheets.
  5. Finally, go to File > Download > Microsoft Excel (.xlsx).

This is a particularly effective strategy for Mac users or those on older PCs who need a free way to digitize PDF data.

Handling Scanned PDFs with OCR

There is a major difference between a "native" PDF (generated by a computer) and a "scanned" PDF (a photo of a paper document). Standard connectors like Power Query work by reading the underlying code of the PDF. If the PDF is just a giant image, the connector will see a blank page or a single large image with no text.

To import data from a scan, you need OCR (Optical Character Recognition) software.

Using Adobe Acrobat Pro

If you have access to Adobe Acrobat, you can use the Export PDF tool. Choose Spreadsheet > Microsoft Excel Workbook. Adobe’s OCR is world-class and can often straighten crooked lines and recognize text that Excel’s basic connector might miss.

Using Dedicated Online Converters

Tools like Smallpdf or ILovePDF offer specialized conversion engines. In our testing, these tools are often faster for one-off conversions where the data structure is highly complex (like multi-line cells). However, be cautious with sensitive financial data when using free online tools; always ensure the site uses SSL encryption and has a clear data deletion policy.

Comparison Table: Which Method Should You Use?

Method Best For Version Required Editable Data?
Power Query Frequent, structured data imports Excel 365 / 2019+ (Win) Yes (Highly structured)
Insert Object References and attachments All versions No (View only)
Word Conversion Simple tables, one-off tasks Office 2013+ Yes
OCR Tools Scanned images, receipts Third-party software Yes
Google Sheets Mac users, older versions Any web browser Yes

Professional Tips for Cleaning PDF Data in Excel

Once you have successfully imported your data, you might notice some artifacts from the PDF format. Here are three professional tips to ensure your data is ready for analysis.

Tip 1: Remove Non-Breaking Spaces

PDFs often use a specific type of space character (non-breaking space) to keep text together. Excel sometimes fails to recognize these as normal spaces, which can break your formulas. To fix this, use the Find and Replace tool (Ctrl + H). In the "Find what" box, hold the Alt key and type 0160 on your numpad. Leave the "Replace with" box empty or type a normal space, then click Replace All.

Tip 2: Handling Dates and Currency

When data comes from a PDF, Excel might import dates or prices as "Text." You can tell if this happened if the numbers are left-aligned in the cell. To fix this, select the column, go to the Data tab, click Text to Columns, and immediately click Finish. This forces Excel to re-evaluate the data types in each cell, usually converting text-based numbers back into true numbers.

Tip 3: Fix Merged Cell Chaos

PDF tables frequently use merged cells for titles. When imported, these can create "Null" values in subsequent rows. In the Power Query editor, use the Fill Down feature. This takes the value from the top cell and copies it into all empty cells below until it hits a new value. This is a lifesaver for creating pivot-table-ready data from structured reports.

Troubleshooting Common PDF Import Issues

The "Table Not Found" Error

If Power Query tells you it cannot find any tables in the document, it is likely because the PDF was "flattened" or is a scan. Try the Word-conversion method. If Word also fails to see a table, the document is an image, and you must use OCR.

Data is All in One Column

If you used the copy-paste method and all your data ended up in Column A, use the Text to Columns feature. Choose Delimited, and try selecting Space or Tab as the delimiter. This will spread the data across multiple columns based on the gaps between words.

Performance Issues with Large PDFs

If you try to import a 500-page PDF, Excel may freeze. In the Navigator window, instead of selecting the entire document, try selecting only the specific tables or pages you need. If you need the whole file, it is often better to split the PDF into smaller chunks using a PDF splitter tool before importing into Excel.

Summary

Importing a PDF into Excel has evolved from a manual nightmare into a streamlined process. For most users on modern Windows systems, the Data > Get Data > From PDF workflow is the gold standard, offering unmatched control through the Power Query Editor. For those on Mac or older systems, using Microsoft Word or Google Sheets as a bridge provides a reliable, free alternative.

When choosing a method, always ask if you need to view the data or manipulate it. If it's for viewing, use "Insert Object." If it's for analysis, use the Power Query connector. By mastering these tools, you can save hours of data entry and ensure that your spreadsheets remain accurate and professional.

Frequently Asked Questions

Can I import a PDF into Excel for free?

Yes, if you have a modern version of Excel, the feature is built-in. If not, you can use Google Sheets or the web version of Word to convert the file for free.

How do I import a PDF into Excel on a Mac?

Currently, Mac Excel does not have a direct "From PDF" button. You should open the PDF in Microsoft Word first, then copy the resulting table into Excel, or use an online PDF-to-Excel converter.

Why does my PDF look like a mess when I paste it into Excel?

PDF files are designed for consistent display, not data structure. A simple copy-paste often loses the "coordinates" of where text is supposed to be. Using the Power Query connector or Word conversion helps the computer "reconstruct" the table logic before it reaches Excel.

Does Excel have OCR for PDFs?

Excel’s native PDF connector has a limited ability to recognize text, but it is not a full OCR engine. For heavily stylized or handwritten scans, you will need a dedicated OCR tool like Adobe Acrobat or a specialized online service.

Is it safe to use online PDF to Excel converters?

For public documents, it is safe. For sensitive company data or personal financial information, it is safer to use Excel’s built-in offline tools or Adobe Acrobat to ensure your data stays on your local machine.