When it comes to storing, analyzing and visualizing data, Microsoft Excel is the preferred choice. But, when the source of the data you need to analyze is contained within a PDF file, your first step is going to be getting it into Excel.
To stay entirely within the Excel user environment, use Get Data to Convert PDF tables into Excel cells. Advanced users can also take advantage of Power Query to build effective import rules that will not only convert your data but keep it up to date if the PDF document is changed.
Learn more about How to Convert a PDF to Excel Using Get Data Here
Excel’s built-in tools, while powerful, can also be cumbersome or not-especially good at some kinds of conversions. Here are several other ways, then, to convert PDF data into Excel data using different methods and external tools to help you.
Copy/Paste Using Word
It might seem too obvious, but sometimes good old-fashioned copy/paste is the right answer in the right circumstances. If you only have a few cells of data in a small PDF table, then carefully copying and pasting each cell could be the fastest method just because it is simple.
Copying individual cells, however, is not fun or efficient when there are many rows and columns. When you’d rather copy an entire table at once, first open your PDF in Word, then copy the whole table. Adding this one extra step can open up copy/paste as a viable option and keeps things very simple.
Use this method when your data is in a table with consistent rows and columns that don’t extend beyond one Word page width. You’ll be able to easily copy/paste long tables, but wide tables will be more difficult to work with. Complex table structures, such as merged cells and wrapped text will also be difficult to copy/paste.
Figure 1 Copy/Paste Directly from PDF file
Figure 2 Copy/Paste from Word
Export PDFs to Excel Using Adobe Acrobat
If you have access to the pro versions of Adobe Acrobat software, (not Acrobat Reader which is free but limited), then you can take advantage of the tools provided by the creator of PDF itself. Open the PDF in Acrobat and then use the Export To option in the File menu.
Choose Spreadsheet, then select Microsoft Excel Workbook. Browse to the folder where the new workbook will go, then click Save. The newly exported file will open in Excel for you to review and clean up.
Original PDF Viewed in Adobe Acrobat:
Data after being exported to Excel:
Abobe Acrobat is a nice solution as you can edit content in the original PDF before you export, and it does a decent job of converting even complicated tables. Even then, always assume there will be some clean up required. If you already have access to this somewhat expensive software, take advantage!
If you don’t have an Adobe Acrobat license, you can use Adobe’s free web-based converter for one-file-at-a-time conversions. You will not be able to edit your pdf files, but the results are very similar to starting from the software, and Adobe does not ask for personal information to perform the conversion. Open the link in your browser, then upload your PDF file using Select files or drag and drop. Select to Microsoft Excel, then click Convert to Xlsx. Wait for the progress bar to complete, then click the Download button on the Your file is ready page.
There are many one-file-at-a-time web-based conversion pages out there. Many are affiliated with 3rd party paid services that offer free individual conversions to introduce you to their software. Just be aware of features that may not be available in these freebies – OCR (Optical Character Recognition) is sometimes excluded for example. Be wary of those that require you to create an account or submit an email address to get results unless you don’t mind ending up on marketing lists for the company. Never submit a document with sensitive or confidential information through a one-off web-based service.
Convert PDFs to Excel Using 3rd Party Software
And if PDF conversions are a critical and frequent task of your business, you may want to consider a dedicated conversion service or software. Here are a few things to consider when choosing a 3rd party tool to ensure that your Excel results will be useful and affordable.
- Bulk conversion – Services often base their plans on how many files you plan to convert, and/or on file sizes. Make sure that the number of conversions you need is included level of service you plan to purchase. If you go with desktop software, make sure it includes batch processing tools and can handle large files on your equipment.
- OCR – If you are mostly working from scanned source documents, be sure that your package includes OCR. It should be a default service, but some do carve that out as a feature for separate fees.
- Security – If you work with documents that contain sensitive information, then be sure to vet any service you use for security, especially if documents are stored on their cloud or up-loaded to their servers. For very sensitive information, consider desktop software instead of an online service so that documents are only processed in-house.
- Additional features – Make sure the service or software includes the features you need to support your workflow. Do you need to edit the PDF before conversions? Will you only convert certain tables or sections of documents? Do you need it to connect with cloud services such as Dropbox or Google Drive? Jot down your workflow and then make sure that any service you engage in can complete every step.
- Results – Each converter can vary in how it processes a PDF and produces an Excel result. Run your most common examples through each service you are considering and evaluate based on how well they handle your
Example: Results starting from the same scanned PDF document using two different converter services. Note how some text is clumped into one cell in one, more usefully placed into individual cells in the other. Also, one preserved image information from the pdf and the other discarded it.
If you regularly need to convert PDFs into Excel workbooks for your business, then start with Excel’s built-in Power Query tools for importing PDF data. If that does not meet your needs, then try some of the above methods. When your workflow is as efficient as possible, you can spend less time converting and more time using Excel’s powerful analysis tools.