In our daily job life, it is a common task for us to extract data from a PDF file to our Excel spreadsheet. If you want to do it manually, then it will be laborious and time-consuming work. However, if you are familiar with the techniques by which we can extract data from PDF to Excel, you can do the job within the blink of an eye. In this article, we are going to demonstrate to you 4 possible ways to extract data from PDF to Excel. If you are interested in getting to know these methods, follow us.
Download Practice Workbook
Download this practice workbook and the PDF file for practice while you are reading this article.
4 Easy Methods to Extract Data from PDF to Excel
For explaining the approaches, we consider a dataset of 10 dwellers of a city. Their ID, home type, region, and the number of family members are in the dataset. The data is available in a PDF file. Our main concern is to extract the data from the PDF file into an Excel datasheet.
1. Use of Power Query to Extract Data from PDF
In this method, we will extract the data from PDF to our Excel worksheet using the Excel’s Power Query feature. The process is described below as follows:
- At first, in the Data tab, select Get Data > From Files.
- After that, select the From PDF option.
- A dialog box titled Connecting will appear, Please wait until the second dialog box will appear.
- Within a few seconds, another dialog box entitled Import Data will appear.
- After that, choose the PDF file from which you want to extract the data and click Import. In our case, we choose a PDF file called Data.
- A new dialog box called Navigator will appear.
- Then, select the Table001 (Page 1) option to import the table into your worksheet.
- As you select the option, you will see a visual display of the dataset of that page in the Navigator dialog box.
- At last, click on Load.
- You will see that a new sheet will open in the Sheet Bar, entitled Table001 (Page 1), and the data will extract into the Excel file as a table.
- Finally, you will get the data in the Excel sheet.
Thus, we can say that our method worked successfully and we are able to extract data from PDF to Excel.
2. Using Excel Copy Paste Features
In this procedure, we are going to use the basic copy and paste features of our computer to extract the from PDF to Excel. We are going to use the same dataset to demonstrate the process to you. The Steps of this process are given below step by step:
- First of all, select the entire dataset. For that, press ‘Ctrl+A’ on your keyboard. Then, press ‘Ctrl+C’ to copy the dataset.
- Now, open Microsoft Excel on your computer and choose the Blank workbook option to open a blank spreadsheet.
- A new blank sheet will open in front of you. Select any cell in that worksheet.
- Then, in the Home tab, select Paste > Paste Destination Formatting to paste the into the Excel spreadsheet.
- You can also press ‘Ctrl+V’ to paste the dataset into the worksheet.
- Write down a suitable title for your dataset and do the necessary formatting from the Font, Alignment, and Style group available in the ribbon to make your dataset look similar to the PDF. If you are not familiarized with how to modify the dataset style, you can format the dataset in various ways.
- At last, you will get the dataset in your Excel workbook.
Finally, we can say that our method worked perfectly and we are able to extract data from PDF to Excel.
3. Through Microsoft Word
In this process, we will take help from Microsoft Word to extract our data from the PDF to Excel worksheet. Our dataset is in a PDF called Data. In this process, first, we copy it into a word file and copy it to our final Excel workbook. The method is given below:
- At the beginning of this method, select the dataset in the PDF file.
- After that, press ‘Ctrl+C’ to copy the data.
- Now, launch Microsoft Word on your computer and select the Blank document option.
- Then, right-click on your mouse, and in the Paste option, choose Keep Source Formatting (K).
- If you cannot see the whole dataset, click on the Move Pointer icon on the left-above side of the table (it will also help you to select the entire table) and choose the suitable alignment.
- Now, again click the Move Pointer icon, and press ‘Ctrl+C’ on your keyboard to copy the table.
- In the worksheet, select any cell and press ‘Ctrl+V’ to paste the dataset.
- After that, write down a suitable title for your dataset and do the necessary formatting from the Font, Alignment, and Style group available in the ribbon to make your dataset look similar to the PDF. If you are not familiarized with how to modify the dataset style, you can format the dataset in various ways.
- In the end, you will get the dataset in your Excel workbook.
So, we can say that our procedure worked perfectly and we are able to extract data from PDF to Excel.
4. Extract Data by Using Adobe Acrobat Conversion Tool
If you are an Adobe Acrobat user, then you can export any of your PDF files into Excel from Adobe Acrobat’s built-in feature. The steps of this method are explained as follows:
- First, open the file in Adobe Acrobat.
- After that, select the Export PDF option from the Tools option shown on the right side of the PDF.
- If the Tools option doesn’t display on the right side of your window, you will find it on the right side of the Home tab.
- Now, choose the Spreadsheet > Microsoft Excel Workbook.
- Finally, click the Export button.
- A new window entitled Save As will appear. Check on the option Open file after export. Then, choose your desired location to save the Excel file. We choose Desktop to save the file.
- Another dialog box will appear.
- Write down a suitable name of your Excel file and click Save. In our case, we write Data as our file name.
- A small progressing bar will be displayed in Adobe Acrobat to show the progress rate of your file exporting.
- Finally, Microsoft Excel will open automatically. Do the necessary formatting from the Font, Alignment, and Style group available in the Home ribbon to make your dataset look similar to the PDF. If you are not familiarized with how to modify the dataset style, you can format the dataset in various ways.
- You will get your data in an Excel spreadsheet.
Thus, we can say that our working method does its job successfully and we are able to extract data from PDF to Excel.
That’s the end of this article. I hope that this will be helpful for you and you will be able to extract data from PDF to Excel. If you have any further queries or recommendations, please share them with us in the comments section below.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!