How to Import Data from PDF to Excel

Getting your data out of a PDF and into an Excel spreadsheet often feels like an impossible task. This article gives you a step-by-step guide to several different methods, ranging from a powerful built-in Excel feature to simple workarounds for those tricky files.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Is Getting Data from a PDF so Hard?

PDFs are designed to be digital documents that look the same everywhere, whether you're viewing them on a Mac, PC, or a phone. They preserve fonts, images, and layout, making them perfect for sharing invoices, reports, and presentations. But that strength is also a major weakness when it comes to data.

A PDF doesn’t think in terms of rows, columns, and cells like a spreadsheet does. It just sees text and lines on a page. When you try to move that data, things can get messy. Columns can get smooshed together, numbers can be mistaken for text, and formatting goes out the window. Fortunately, Excel has gotten much smarter about handling this.

Method 1: The Best Way Using Excel's Get & Transform Data (Power Query)

For anyone using a modern version of Excel (Excel 2016 and later, or Microsoft 365), this is hands-down the best and most reliable method. It uses a powerful tool called Power Query that's built right into the "Data" tab.

Step 1: Navigate to the Data Tab

Open a new or existing Excel workbook. In the top ribbon, click on the Data tab. On the far left, you'll see a section called "Get & Transform Data."

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Select the PDF Data Source

Within the "Get & Transform Data" section, click on Get Data. A dropdown menu will appear. Follow this path: From File > From PDF.

Step 3: Choose Your PDF File

A file browser window will pop up. Navigate to the location of your PDF file, select it, and click Import. Excel will now connect to the file and start analyzing its structure.

Step 4: Use the Navigator to Select Your Data

After a few moments, the Navigator window will appear. This is where you get to preview the data Excel found in your PDF. On the left side, you'll see a list of all the tables and pages that Excel identified.

• Table Icons: Items with a blue-and-white table icon represent structured tables that Excel automatically detected. These are usually your best bet.
• Page Icons: Items with a page icon represent entire pages from the PDF. This option can be useful if your data isn't in a clear table format, but it often requires more cleanup.

Click on each item in the list to see a preview on the right. Select the check box next to the table or page that contains the data you need. You can select multiple tables if your PDF has them.

Step 5: Transform or Load the Data

At the bottom of the Navigator window, you have two main choices:

• Load: This is the simplest option. It will immediately load the data from your PDF into a new worksheet in your Excel file as a formatted table. If the preview looks clean and ready to go, this is a great choice.
• Transform Data: This is the more powerful option and is recommended if your data needs any cleanup. Clicking this opens the Power Query Editor, a specialized tool for preparing data before it even hits your spreadsheet.

A Quick Peek at Power Query

The Power Query Editor is a game-changer. Inside this editor, you can perform common cleanup tasks without having to write any formulas:

• Remove unwanted columns or rows.
• Promote the first row to become the table headers.
• Change data types (e.g., make sure a column of numbers isn't being read as text).
• Split columns that have been merged together.

Every change you make is recorded as an "Applied Step" on the right sidebar. Once you are happy with how the data looks, click Close & Load in the top-left corner of the Power Query Editor. Your cleaned-up data will be loaded into your Excel sheet.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Method 2: The Easiest Way - Copy and Paste

Sometimes, the simplest approach is all you need. If your PDF contains clean, simple, selectable text, a quick copy and paste might do the trick. This works best when the PDF was created digitally (not from a scanner) and has basic table layouts.

1. Open the PDF: Use any PDF reader, like Adobe Acrobat Reader or even your web browser.
2. Select the Data: Click and drag your cursor to highlight all the data you want to import. Be careful to select just the table itself, avoiding page headers or footers.
3. Copy the Data: Press Ctrl+C (or Cmd+C on Mac) to copy the highlighted data to your clipboard.
4. Paste into Excel: Open Excel, click into a single cell, and press Ctrl+V (or Cmd+V on Mac) to paste.

Cleaning Up Pasted Data with "Text to Columns"

More often than not, pasting directly will result in all your data being crunched into a single column. This is frustrating but easily fixed using Excel's "Text to Columns" feature.

1. Select the single column that contains all your pasted data.
2. Go to the Data tab in Excel's ribbon.
3. Click Text to Columns. A small wizard will pop up.
4. Choose Delimited if your data is separated by a consistent character like a space, comma, or tab. Choose Fixed Width if your data is aligned in columns that you can manually separate with lines. Delimited is usually the better option here.
5. Follow the next steps in the wizard to specify your separator (spaces are common for PDF copies) and format the data for each new column.
6. Click Finish, and Excel will split the single column into multiple columns.

Method 3: Go Through a Word Document First

This surprising workaround often preserves formatting better than a direct copy-paste from a PDF. Modern versions of Microsoft Word are incredibly good at converting PDFs into editable Word documents.

1. Open the PDF in Word: Right-click your PDF file, choose "Open with," and select Microsoft Word. Alternatively, open Word first, go to File > Open, and select your PDF.
2. Convert the File: Word will show you a message saying it's going to convert your PDF to an editable Word document. Click OK. This might take a minute for larger files.
3. Copy the Table from Word: Once the file is open in Word, the data you need will likely be in a Word table. It's now much easier to interact with. Highlight the entire table and copy it (Ctrl+C).
4. Paste into Excel: Go back to your Excel spreadsheet and paste the table (Ctrl+V). Because Word has already done the heavy lifting of structuring the data, it should paste into Excel's rows and columns much more cleanly.

Method 4: Using Free Online Converters

If you don't have a modern version of Excel or Word, plenty of free online tools can convert a PDF to an Excel file for you. Websites like Smallpdf, iLovePDF, and Adobe's free converter all work in a similar way.

1. Navigate to your chosen converter website in your browser.
2. Upload your PDF file.
3. Select the "PDF to Excel" conversion option.
4. Start the conversion and wait for it to process.
5. Download the resulting .xlsx file.

A Word of Caution:

🚨 A Word of Caution: Do not use this method for documents containing sensitive, personal, or confidential information. When you upload a file to a free online service, you temporarily lose control over your data. For any private information, stick to the offline methods discussed above.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

What About Scanned PDFs and Images? (OCR)

What if your PDF is a scan of a paper document? In this case, there's no "real" text to copy - your computer just sees one big picture. If you can't click and highlight the text, you're dealing with a scanned document.

To handle this, you need a tool with Optical Character Recognition (OCR) technology. OCR scans the image for patterns that look like letters and numbers and converts them back into actual text.

Some of the options mentioned already include OCR:

• Full paid versions of Adobe Acrobat Pro have a powerful built-in OCR feature.
• Many online converters will automatically perform OCR when they detect a scanned file, though quality can vary.
• Excel's native Get Data from PDF tool has some limited ability to interpret these files, but specialized software is better.

If you're dealing with a scanned PDF, your first step will be to run it through an OCR-enabled tool to make the text selectable before trying any of the methods above.

Final Thoughts

Turning a rigid PDF into a flexible Excel spreadsheet is a common but manageable challenge. For most people, Excel's built-in "Get Data from PDF" feature is the most robust option, while a simple copy-paste or a detour through Microsoft Word can solve the problem in a pinch. The key is to choose the method that best matches your PDF and the tools you have available.

Solving tedious data tasks manually, like extracting tables from a PDF, is just one small piece of the data-wrangling puzzle. At Graphed we automate the entire process for your key data sources. Instead of exporting CSVs or wrestling PDFs from Shopify, Google Analytics, or Salesforce, you can connect your accounts in seconds and use simple natural language to build real-time dashboards and reports. This allows you to spend your time acting on insights, not just fighting to uncover them.