How to Convert a PDF Table into an Excel Spreadsheet Using Get Data

If a colleague or client sends you a financial report, a product inventory, or a list of customer details embedded inside a PDF file, extracting that data into Microsoft Excel can be a nightmare. If you try to simply highlight the table in the PDF, copy it, and paste it directly into an Excel worksheet, the formatting usually collapses. All the numbers and text will paste into a single, messy column, forcing you to spend hours manually typing out the data.

Fortunately, modern versions of Microsoft Excel (specifically Excel for Microsoft 365, Excel 2021, and Excel 2019) include a powerful data extraction tool built directly into the software. You do not need to buy expensive third-party PDF converter software. Excel can read the PDF, identify the tables, and import the data perfectly into individual cells.

This guide explains how to convert a PDF table into a clean Excel spreadsheet using the “Get Data” feature.

Step 1: Locate the PDF Data Tool

Instead of opening the PDF file, you will initiate the process from inside a blank Excel workbook.

1. Open Microsoft Excel and create a new Blank workbook.
2. Click on the Data tab in the ribbon at the top of the window.
3. On the far left side of the Data ribbon, click the Get Data button (in older versions, this might be labeled Get & Transform Data).
4. From the drop-down menu, hover your mouse over From File.
5. Click on From PDF.

Note: If you do not see the “From PDF” option, you are likely using an older version of Excel (like Excel 2016) that does not support this feature, or you are using Excel on a Mac, which currently lacks the full Power Query integration found on Windows.

Step 2: Select and Import the PDF

After clicking “From PDF,” a standard Windows file explorer window will open.

1. Navigate to the folder where your PDF file is saved.
2. Select the PDF file and click Import.

Excel will take a few moments to scan the entire PDF document, looking for structured data grids and tables.

Step 3: Choose the Correct Table from the Navigator

Once Excel finishes scanning the document, it will open the Navigator window. This is a crucial step.

On the left side of the Navigator window, you will see a list of everything Excel found in the PDF. Excel categorises its findings in two ways:

• Tables (Blue Grid Icon): Excel attempts to identify specific, self-contained tables on a page. It will label them as “Table 1,” “Table 2,” etc.
• Pages (White Page Icon): Excel also captures all the text on every single page of the document, labeling them as “Page 1,” “Page 2,” etc.

1. Click on the items in the left-hand list. When you click an item, a visual preview of the data will appear on the right side of the window.
2. Look for the item with the blue grid icon that contains the exact data you want to import. Usually, selecting the specific “Table” yields a much cleaner result than selecting the entire “Page.”
3. If your data spans multiple pages in the PDF (e.g., a massive list of products), check the box at the top left labeled Select multiple items and click all the relevant tables.

Step 4: Load or Transform the Data

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

Option A: Load directly
If the preview looks perfect and the columns are correctly aligned, simply click the Load button. Excel will instantly insert the data into your spreadsheet, perfectly formatted into rows and columns.

Option B: Transform Data (Power Query)
If the preview shows that Excel made a mistake (e.g., merging two columns together, including an empty row, or failing to recognise the header row), do not click Load. Instead, click Transform Data.

This will open the Power Query Editor. This advanced tool allows you to clean the data before it enters Excel. You can delete empty columns, split text, and promote the first row to be headers. Once the data looks correct in the Power Query Editor, click the Close & Load button in the top left corner to push the clean data into your spreadsheet.

By using the “Get Data” tool, you can extract thousands of rows of data from a PDF in seconds, completely eliminating the need for manual data entry.