How to Extract Data from PDF to Excel (4 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Extract Data from PDF to Excel: 4 Easy Methods

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:

📌 Steps:

  • At first, in the Data tab, select Get Data > From Files.
  • After that, select the From PDF option.

Use of Power Query to Extract Data from PDF

  • 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.

Use of Power Query to Extract Data from PDF

  • 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.

Use of Power Query to Extract Data from PDF

  • Finally, you will get the data in the Excel sheet.

Use of Power Query to Extract Data from PDF

Thus, we can say that our method worked successfully and we are able to extract data from PDF to Excel.

Read More: How to Extract Data from Multiple PDF Files 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:

📌 Steps:

  • First of all, select the entire dataset. For that, press ‘Ctrl+A’ on your keyboard. Then, press ‘Ctrl+C’ to copy the dataset.

Using Excel Copy Paste Features to Extract Data from PDF to Excel

  • 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.

Using Excel Copy Paste Features to Extract Data from PDF to Excel

  • You can also press ‘Ctrl+V’ to paste the dataset into the worksheet.

Using Excel Copy Paste Features to Extract Data from PDF to Excel

  • 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.

How to Extract Data from PDF to Excel

Finally, we can say that our method worked perfectly and we are able to extract data from PDF to Excel.

Read More: Convert PDF to Table in 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:

📌 Steps:

  • 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.

Through Microsoft Word Extract Data from PDF to Excel

  • Then, right-click on your mouse, and in the Paste option, choose keep source formatting (K).

Through Microsoft Word Extract Data from PDF to Excel

  • 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.

Through Microsoft Word Extract Data from PDF to Excel

  • In the worksheet, select any cell and press ‘Ctrl+V’ to paste the dataset.

Through Microsoft Word Extract Data from PDF to Excel

  • 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.

How to Extract Data from PDF to Excel

So, we can say that our procedure worked perfectly and we are able to extract data from PDF to Excel.

Read More: How to Convert PDF to Excel without Software


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:

📌 Steps:

  • 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.

Extract Data from Adobe Acrobat

  • 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.

Extract Data from Adobe Acrobat

  • 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.

Extract Data from Adobe Acrobat

  • 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.

Extract Data from Adobe Acrobat

  • 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.

Read More: How to Export PDF Comments into an Excel Spreadsheet


Download Practice Workbook

Download this practice workbook and the PDF file for practice while you are reading this article.


Conclusion

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.


Related Articles

<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

2 Comments
  1. Reply
    Andre van Niekerk Mar 25, 2024 at 1:51 PM

    The copy and paste option works perfectly in Excel 2010, but it does not work in Excel 2013.
    Why would that be?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 28, 2024 at 12:59 PM

      Hello Andre Van Niekerk

      Thanks for sharing your problem. It is unusual for copying and pasting to stop working entirely in Excel 2013 if it worked perfectly in Excel 2010. Both versions rely on the same core Windows clipboard functionality.

      The PDF file copied from might have a complex layout or structure that Excel 2013 has difficulty handling during the copy-and-paste process. Also, if you’re trying to paste into merged cells in Excel 2013, it might cause problems. Try un-merging the cells before pasting.

      If you are still having trouble with the issue, we recommend you use the other approaches mentioned. Let us know if they lead you to the same result; good luck.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo