How to Copy Data from PDF to Excel
Pulling data from a PDF into Excel can feel like you're trying to get ketchup out of a glass bottle - frustrating, messy, and you’re never quite sure what you'll get. PDFs are designed to lock down a document's look and feel, which makes them perfect for sharing final reports but terrible for analyzing the data within them. This guide will walk you through several practical methods, from simple copy-pasting to powerful built-in Excel tools, to help you extract your data cleanly and efficiently.
We'll cover the best approaches for different types of PDFs, from crisp, digitally-created tables to tricky scanned documents.
Method 1: The Classic Copy and Paste (And How to Fix It)
Let's start with the most intuitive method. Sometimes, a straightforward copy and paste is all you need, especially for simple, well-structured tables. However, more often than not, the results are a single, jumbled column of text in Excel.
How to Copy and Paste Correctly
Open your PDF file. Use your cursor to highlight the data table you want to copy.
Right-click and select "Copy," or use the keyboard shortcut (Ctrl+C on Windows, Cmd+C on Mac).
Open a blank spreadsheet in Excel.
Select a single cell where you want the data to start.
Right-click and choose one of the "Paste" options. "Keep Text Only" often works best to avoid bringing in strange formatting. Alternatively, use Ctrl+V (or Cmd+V).
Why It Fails (And a Quick Fix)
You’ll often find that all your data lands in Column A, with each piece of information jumbled together. Don't panic - this is where Excel’s “Text to Columns” feature comes in handy.
Imagine your pasted data in Column A looks like this:
That’s unworkable. Here’s how to clean it up:
Select the entire column containing the messy data. Click on the column header (e.g., "A") to highlight it all.
Go to Text to Columns. Navigate to the ‘Data’ tab on Excel's ribbon and click the ‘Text to Columns’ button.
Choose ‘Delimited’ or ‘Fixed width’. A wizard will pop up with two options.
Delimited: This is the best choice if your data is separated by a specific character, like a comma, tab, or space. In our example above, multiple spaces separate each field.
Fixed width: Choose this option if your data is aligned in neat columns, and you can visually draw a line between each one.
Set your delimiters. If you chose ‘Delimited’, the next step will ask you what the separator is. For our example, you would check "Space." The data preview at the bottom will show you what the separated columns will look like.
Click ‘Finish’. Excel will automatically split the data from the single column into multiple, organized columns.
The copy-paste method is a quick first attempt, but for anything more than a tiny table, it usually creates more cleanup work than it's worth. For more reliable results, let's move to a much more powerful technique.
Method 2: Use Excel’s “Get Data From PDF” Feature (The Game Changer)
If you're using a modern version of Excel (Microsoft 365, Excel 2021, or Excel 2016), you have a secret weapon: Power Query. This built-in tool includes a dedicated connector that can directly import and clean data from PDF files. It’s hands-down the best method for most situations.
Step-by-Step Guide to Importing from a PDF
Navigate to the Data Tab. Open a new Excel workbook and click on the 'Data' tab in the ribbon.
Open the 'Get Data' Menu. On the far left, click 'Get Data' -> 'From File' -> 'From PDF'.
Select Your PDF File. An ‘Import Data’ window will appear. Browse to your PDF file, select it, and click 'Import'.
Use the Navigator to Choose Your Data. After a moment, a 'Navigator' pane will open. This is where Excel shows you all the individual tables and pages it has autodetected within your PDF.
Tip: Click on each table or page name in the list on the left to see a preview of the data on the right. This helps you find the exact table you need.
Load or Transform Your Data. At the bottom of the navigator window, you have two key options:
A) Load: Click "Load" if the data preview looks perfect - clean, well-structured, and ready to go. Excel will immediately drop the data into a new worksheet as a formatted table.
B) Transform Data: Click "Transform Data" if the preview looks a bit messy. This is the more powerful choice, as it opens the Power Query Editor, where you can clean up the data before it even hits your spreadsheet.
A Quick Look at the Power Query Editor
The Power Query Editor is an incredibly robust tool for data cleaning. You don't need to be a data wizard to use its basic features. Inside the editor, you can easily perform common cleanup tasks:
Remove Unnecessary Rows: Get rid of header or footer rows that aren't part of the actual data.
Split Columns: If a column contains multiple pieces of information (like "City, State"), you can split it into two separate columns.
Change Data Types: Ensure numbers are treated as numbers (not text) and dates are formatted correctly.
Filter Data: Remove rows you don't need based on certain criteria.
Once you’re finished with your adjustments in Power Query, click “Close & Load” in the top-left corner. Excel will then load your perfectly cleaned data into your worksheet. Even better, your query is now saved. If the PDF is updated, you can simply go to the ‘Data’ tab and click ‘Refresh All’ to pull in the new data with all your cleaning steps automatically applied.
Method 3: Use Microsoft Word as a Middleman
This is a surprisingly effective trick that works well when a direct Excel import fails or if you're using an older version of Excel that doesn't have the 'Get Data from PDF' feature. Microsoft Word has excellent capabilities for converting PDFs into editable formats, preserving tables along the way.
Open the PDF with Word. Don't open Word first. Locate your PDF file, right-click on it, and select 'Open with' -> 'Microsoft Word'.
Confirm the Conversion. Word will show a polite message explaining that it is about to convert your PDF into an editable Word document. Click ‘OK’. This process might take a few moments for larger files.
Copy the Table from Word. Once the file is open, scroll to the table you need. It should now be an editable Word table. Drag your cursor to select the entire table, and then copy it (Ctrl+C or Cmd+C).
Paste it into Excel. Switch to your Excel workbook, select a cell, and paste (Ctrl+V or Cmd+V). The table structure usually transfers over much more cleanly than a direct copy from a PDF reader because Word has already done the heavy lifting of converting and structuring the data.
Method 4: Online PDF to Excel Converters
Dozens of websites offer free PDF-to-Excel conversion. These can be convenient when you’re not on your primary computer or don't have access to Excel. To use them, you typically upload your PDF, wait for the tool to process it, and then download the resulting Excel file.
Popular options include Adobe's own free converter, Smallpdf, and iLovePDF.
A Word of Caution: Security and Privacy
While convenient, online converters come with a significant catch: you are uploading your data to a third-party server. You have no real control over what happens to it. Therefore, absolutely avoid using these services for any document containing sensitive information, such as financial statements, customer lists, medical records, or proprietary business data.
For non-sensitive data, these tools can be a quick fix, but always double-check the final Excel file for conversion errors.
Method 5: Handling Scanned PDFs with OCR
What if your PDF isn't a digital document but a scan of a paper one? In this case, your PDF is essentially an image of a table, not a table itself. None of the above methods will work because there is no underlying data to copy - just pixels. The technology to solve this is called Optical Character Recognition (OCR).
OCR software scans the image, recognizes the shapes as letters and numbers, and converts them back into actual text and data. Here’s how you can access it:
Adobe Acrobat Pro: If you have the paid version of Adobe Acrobat, it has a world-class OCR feature built-in. You can open the scanned PDF and export it directly to an Excel spreadsheet, and Acrobat will perform the OCR automatically.
Dedicated OCR Converters: Many of the online tools mentioned in Method 4 also include OCR capabilities, sometimes as a premium feature. Again, the same privacy concerns apply.
Excel's 'Get Data from Picture' (Mobile App): A neat and often overlooked feature in the Excel mobile app lets you take a picture of a printed table, and it will use OCR to convert it into editable Excel data right on your phone. From there, you can save and open it on your desktop.
Keep in mind that OCR quality depends heavily on the scan's clarity. Blurry text or complex layouts can lead to errors, so you'll always want to proofread the final data carefully.
Final Thoughts
Getting your data out of locked PDF files and into an actionable format doesn't have to be a productivity killer. By starting with Excel's powerful "Get Data From PDF" function, resorting to the handy Microsoft Word trick for stubborn files, or using OCR for scans, you have a full toolkit to tackle almost any scenario and end the frustrating cycle of manual re-typing.
Ultimately, this recurring and time-consuming task highlights a bigger problem: relying on static, manual reports instead of live, accessible data. That's a challenge we are passionate about solving. We built Graphed to help teams move beyond this cycle by connecting directly to marketing and sales data sources (like Google Analytics, Shopify, Facebook Ads, and Salesforce) to generate real-time, interactive dashboards instantly. Instead of spending your Mondays wrestling data from last week's PDFs, you can ask a question in plain English and get an answer with live data in seconds.