The Portable Document Format (PDF) continues to be a popular method for sharing and publishing information online. This is understandable, as it ensures that the original format is preserved. Financial statements and reports, for instance, contain all sorts of interesting data and are often published in this format. Fortunately, you can convert these files to a format more appropriate for working with numbers, like Microsoft Excel.
PDF files are created in different ways, which affects the complexity of extracting data from them. Generally speaking, there are two main types of PDF files: those created from a text document (text-based) and those created from images (image-based), usually scanned documents. If you have the latter, converting it will require the use of Optical Character Recognition (OCR) technology. However, if your PDF file is text-based, importing the data to an Excel file is simpler than you think.
In this guide, you will learn 4 different ways to import PDF data to Microsoft Excel for free. First, you will learn to use Power Query to select and import data from a PDF to Excel. Second, you will learn to copy & paste from PDF to Excel if you're dealing with small or simply structured tables. Third, you will learn how to use the ‘Data from Picture’ feature to import your PDF data to an Excel workbook. Fourth, you will learn about three PDF to Excel online converters.
How to Convert PDF to Excel with Power Query?
Power Query is a powerful and versatile tool that can be used to query data, import it, transform it, and easily automate these processes. This can make it a little intimidating at first, but you don’t need to know every feature to make your life easier. You can simplify tasks like importing data from a PDF file with a few simple steps.
Unfortunately, the ‘From PDF’ feature is not available in all versions of Power Query, so if you’re using Excel on Mac, you may have to skip to the other methods.
- 1. In Excel, go to Data > Get Data > From File and select ‘From PDF’.
- 2. Once you have selected the file, click ‘Import’.
- 3. You’ll see a window with a list of the contents on the left. You can select one or more tables.
- 4. If you don’t need to make any changes, you can click ‘Load’ to import the table to a new sheet in your workbook, or ‘Load to’ to specify how and where to load it. If you need to make changes, click ‘Transform’.
- 5. You can promote the column headers by clicking ‘Use First Row as Headers’.
- 6. If you need to modify a column header, simply double-click on it.
- 7. Finally, click ‘Load’ or ‘Load to’ to send the table to your worksheet.
- 8. That’s it. The table has been imported perfectly.
How to Convert PDF to Excel with Copy & Paste?
Unless you’re dealing with very simple tables, the copy & paste method can be tedious and time-consuming, as you would likely need to move data that has been assigned to the wrong column or row and fix other formatting issues. However, if you have a simple table, it’s a relatively straightforward process.
- 1. Open the PDF file, select the table you want, and copy it.
- 2. Open your Excel file and right-click on the cell where you want to paste the table. Right-click to paste or use the shortcut: Ctrl/Cmd + v.
- 3. As you can see, the table has been pasted correctly. If your table has a more complex structure, you will probably have to edit it manually.
How to Convert PDF to Excel with Data from Picture Feature?
Using Excel’s ‘Data from Picture’ feature, you can extract table data from images, including image-based PDF files. If the image is clear and not taken at an angle or otherwise distorted, the process is very accurate. However, even if the image isn’t perfect, it’s easier to check the result and make a few corrections than to reproduce the entire table manually.
- 1. Take a screenshot of the PDF that only includes the table you want to copy to Excel.
- 2. In Excel, go to ‘Data’ and select ‘Picture from File’ or ‘Picture from Clipboard’.
- 3. Select the screenshot of the table and click ‘Open’.
- 4. The ‘Data from Picture’ sidebar will open and start extracting data. Once it’s done, you will see a preview. You can review any cells that Excel isn’t sure about by clicking on ‘Review’.
- 5. You can quickly switch between items and accept Excel’s suggestion or edit it. In this case, I simply have to accept the suggestions.
- 6. When you click ‘Insert Data’, you will be asked to confirm and be informed of your responsibility to validate the accuracy of the data.
- 7. That’s it. The table was copied from the PDF to Excel.
Online PDF to Excel Converters
In addition to the methods shown above, there are many PDF to Excel converters available from 3rd parties. Some of these are Excel add-ons, but most are websites where you can upload a PDF file, and it will be converted to the required format.
Below, you have three online converters that you can use free of charge, though there is a limit to how many files you can convert in a given period of time.
PDF Converter by Apryse
This online converter is very easy to use, and you don’t even need to supply an email address. Simply upload or drag and drop the file to convert. After a few seconds or minutes, depending on the size of the file, you will be able to download the converted file. Click here to try it.
As soon as the file is converted, you will see a ‘Download’ button.
Like the previous converter, Online2PDF doesn’t require you to sign up. You can simply drop your file in the designated area and specify the settings you want. Click ‘Convert’ to start the conversion, and the file will download automatically when it’s ready.
Free PDF Converter
This online converter can also be used free of charge. However, the site enforces a waiting period between conversions if you’re not a member. Additionally, it sometimes fails to convert larger files or ones that combine tables and text. Click here to try it.
There are many ways to import data from PDF to Excel files without using additional software. If the PDF file is text-based, you can use Power Query to extract the tables you want to import. In fact, you can transform and manipulate them any way you want. The copy & paste method can be used to copy tables from PDF to Excel, but depending on the complexity of the table, the checking and formatting required could be imposing.
Excel’s ‘Data from Picture’ feature can take a little longer to process, but it can extract data from both text-based and image-based PDF files. Finally, if you prefer to convert using 3rd party software, you know about three PDF to Excel online converters.
To learn more about converting to and from other file types, check out our guides on: