How to Convert PDF to Excel Table (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you some easy and effective procedures on how to convert PDF data to an Excel table. Sometimes, we may work on reports which are documented as PDF files and analyze the data in the reports. If we want to analyze the data in Excel, we need to extract those data in the Excel workbook.
However, there are some techniques to convert the tables in PDF files to Excel tables. There are some paid tools you may find online to convert PDF files to Excel, but the methods in this article will cost you nothing.

Here is an overview that shows how you can copy a table from a PDF file to an Excel workbook.

Here, we copied a table from PDF to Word document then copied the table to an Excel Workbook.


How to Convert PDF to Excel Table? (4 Easy Ways)

Some of the basic tips on converting a PDF document to Excel table will be discussed in the following sections. Please go through them and follow any of the methods according to your convenience.


1. Using Get Data Feature to Convert PDF Data to Excel Table

The Get Data feature has various options to extract data from different sources. Here, we will select the option for the PDF files.

Opening Get Data Feature for the PDF File

To get this option enabled,

  • Select Data >> Get Data >> From File >> From PDF.

Importing the PDF Data

  • After that, the Import Data window will appear. Browse for the PDF file, select it and click on Import.

Selecting Table Option and Worksheet for New Table

  • Now, Excel will initiate the Navigator Select Table001 (Page 1) as Excel detects this as a table while getting data from the PDF file. You can see a preview of this table in the window.
  • Next, click on the drop down icon beside the Load button and then select Load To… Clicking on Load will open the table randomly in a new sheet.

Setting up Table Position in the Sheet

  • After that, select the cell where your table will start from the Import Data dialog box and click OK.

Table Loaded from the PDF File

Thus, you can find your desired table in Excel from a PDF file.

Advantage:

The advantage of the procedure is that this is dynamic. Say we updated the PDF file with a new entry. Let’s see if you can update the table in the Excel sheet in an instant.

PDF File with Updated Data

Please follow the video below to see the process of updating the Excel table with easy steps.

The following steps were shown in the video.

  • First, we replaced the destination file with the updated PDF file. The destination file is the one that Excel used to extract data.
  • Next, right-click on the table and select Refresh.

You will see the updated table after this command.

Now, we will discuss some topics regarding this method. The PDF file may contain several tables or similar datasets. I’ll show some procedures if we need to work with this type of PDF files.


Extracting Multiple Tables from PDF to Excel

If the PDF file contains multiple tables, we need to take a little bit more steps to extract them in the Excel sheets.

PDF File with Two Tables

You can see that the PDF file now contains two tables.

Selecting Options to Load Tables

  • Open the Navigator window with the PDF file with multiple tables first.
  • After that, check the option Select multiple items. This command will enable you select multiple tables from the PDF
  • Check the tables and click on Load or Load To…

Table with Grades Loaded to Excel

The tables will appear in individual sheets. Here you can see the table with Grades.

Table with Attendance Percentage Loaded to Excel

And this is the table with the Attendance Percentage.


Merging Multiple Excel Tables from PDF

Here, I’ll show you an important feature which we may need to use frequently when we have similar data in the PDF file. As our tables contain similar data, we can merge them to see the overall performances.

Opening Queries & Connections

Open the Power Query Editor window to edit the Queries & Connections. Place the cursor on any of the Queries & Connections and it will temporarily open a preview window. Select Edit from that window.

Opening Merge Query Window

  • Now, follow the above image for merging the tables. Select the first table and click on the drop down icon marked as 2.
  • Next, select Merge Queries…

Selecting Options for Merging Queries

  • After that, select any of the similar columns from the tables that appear on the Merge The second table (Table002) needs to be selected from the drop down list marked as 2 in the image.

This command will create an additional column to the current table.

Follow the video below to understand the next actions of the procedure.

The following steps were covered in the video.

  • Click on the drop down icon of the heading of the added column. It will show you the table headings of Table002.
  • Check Attendance Percentage as we want to merge this in the new table. Rename the column heading for convenience.

Naming the Merged Query

Now, you can give a name to your Query if you are want. Here I named it ‘Performance Overview‘. You can find the Query Settings at the right side of the Power Query Editor window.

Loading Merged Table

  • After that, select Close & Load to load the merged table to a new sheet.

Merged Table Loaded

The merged table now appears with default formatting.

Repositioning the Table

Resize and reposition this table in the sheet. Also change the number format if necessary.

Following the instructions of this section, you will learn not only converting a PDF file data to Excel table, but also extracting and merging multiple tables when necessary.

Note: If the data in a similar column does not match by cell positions, you need to apply Fuzzy Lookup Algorithm in that case.

Read More: How to Extract Data from Multiple PDF Files to Excel


2. Converting PDF to Excel Table from Online (Free)

If you want to avoid the procedures in Excel, you can use an online tool of Adobe Acrobat for free to convert the PDF file to Excel table. To use this tool, Open the window Convert PDF to Excel online for free.

Applying Online Converter Tool of Adobe

  • In the tab, click on select a file.
  • Browse for the PDF file and click on Open.

After that, the PDF file will be converted to an Excel workbook. Download it and do the necessary formatting.

Converted Excel Table from PDF with Modifications

Read More: How to Extract Data from PDF to Excel


3. Applying Copy & Paste Feature and Formula to Convert PDF File to Excel Table

We can also directly copy and paste the data from a PDF file and paste it to the Excel sheet. But this process leaves some problems. Follow the steps below.

The following steps were taken in the video.

  • First, copy the table from the PDF file.
  • Next, paste it in the Excel sheet.

The table data will appear in a single column. We need to extract the data in a tabular form.

Inserting ID Column from Copied Data

If you observe, you can see that the data have been pasted in a systematic way. The data of the first column appeared in the rows 4, 7, 10 etc. And the data of other columns also have their own sequential positions. For this reason, we generated a formula to arrange the data like in the PDF table.

Write the formula, press Enter and copy down the formula using the Fill Handle icon to AutoFill the lower cells with the corresponding data.

=INDEX(B:B,(ROW()-ROW($C$4))*3+4)

The formula uses INDEX and ROW functions to insert the data in B4, B7, B10,,,, cells. We get the ID column from this formula.

Inserting Name Column

The following formula will return the values in cells B5, B8, B11,,,,. This actually returns the Name column.

=INDEX(B:B,(ROW()-ROW($C$4))*3+5)

Inserting Grade Column

The formula below will give us the Grade column.

=INDEX(B:B,(ROW()-ROW($C$4))*3+6)

Now, we need this formula output as values. Otherwise using this data as a table may cause some error. Follow the video to see how we can convert these formula results to values.

We did the following tasks in the video.

  • Select the data range and press Ctrl + C to copy it.
  • Right click on the cell where the table will start and select Paste Values from the context menu.

The formulas will be removed and the values will prevail.

Creating Table from the Extracted Data

To convert this data range,

  • First, select the data range again.
  • Press Ctrl + T to convert this range to a table. Make sure you select ‘My table has headers’ and click OK.

Converting Extracted Data to Table

After that, you will get your table.

Thus you can convert PDF files to Excel table.

Read More: How to Convert PDF to Excel without Losing Formatting


4. Two Step Copy & Paste (PDF to Word to Excel)

Another easy way to convert PDF files’ data to Excel table is to copy the data to Excel workbook from the PDF file in two steps. Please follow the video below.

In the video, we simply copied the table from the PDF file to a Word Document. Then copied the table from the Word Document to Excel workbook. The table from the PDF file is pasted in a tabular form. The advantage of using this method is that we don’t need additional formulas to separate the data in tabular form. Here, the Word Document plays an intermediate role to keep the formatting of the table.


Things to Remember

While converting PDF to Excel table, you should keep in mind some important matters.

  • After extracting data from PDF to Excel workbook, check the data carefully as it may leave some unwanted blanks or wrong fonts.
  • Using free online converter tool may be inefficient in some cases. Find the best converter tool and use it.
  • Adobe Acrobat Prohas it’s own feature to directly convert a PDF table to Excel, which will be time-saving. So if you can afford it, use it for the best outcome.

Download Workbook


Conclusion

In the end, we can conclude that you will learn some easy and effective tricks to convert PDFto Excel table. Each method has it’s own advantages and disadvantages. If you have any suggestions or ideas or questions regarding this article, please share them in the comment section. Your feedbacks will help me to enrich my upcoming articles.


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.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

2 Comments
  1. Assalamu’alaikum Ukhti Afia..

    i need to know, how to import *.pdf file to *.txt and then ended up in excel form using vba code?

    i’d love to learn something new, yes i noobie.
    please
    thank you

    • Dear Yos,
      Walaikum’assalam. Thank you so much for your comment. I will try my best to give you a proper solution.
      Firstly, I will show you how you can import a .pdf file to a .txt file, and after that, I will show you the VBA code to open the text file in Excel.

      Let me show you how you can import a .pdf file to a .txt file.

      ● First of all, upload your PDF file to your Google Drive.
      ● Then, from Google Drive >> right-click on the uploaded PDF file.
      ● Then, click on Open With from the Context Menu >> select Google Docs.
      This will open the PDF as a Google Doc file.

      ● After that, go to the File tab of Google Docs >> click on Download.
      ● This will bring out several Download options >> select Plain Text (.txt)
      This will make the Google Docs as a Text file.
      ● Next, open your Text file and see the outcome.

      Next, I will show you how you can open the Text file in Excel using VBA.
      In the beginning, carefully notice the location of the Text file, and also carefully note the name of the file.
      Here, we have marked our text file name.

      This is because we have to implement the file name and location in the VBA code properly.

      null

      Now, it is time to open a VBA Module.
      ● To open a VBA module, open your Excel file >> go to the Developer tab >> select Visual Basic.
      You can also press the ALT+F11 keys.
      This will open a VBA Editor window.
      ● Furthermore, from the Insert tab >> select Module.
      ● Moreover, in the Module, type the following code.

      Sub ConvertToNewWorkbook()
          Dim iBook As Workbook
          Dim iTexts As Workbook
          Dim iSheet As Worksheet
          Set iBook = ThisWorkbook
          Set iSheet = iBook.Sheets(1)
          Set iTexts = Workbooks.Open("C:\ExcelDemy\Student List.txt")
          iBook.Sheets(1).Cells.Copy iSheet.Cells
          iBook.Close SaveChanges:=True
      End Sub

      Here, change the location and name of the text file according to your file.
      ● Afterward, Save the code >> Run the code.
      Therefore, you will see the Excel file will have all the texas.
      I hope this was helpful. Please let us know if you have any additional queries.

      Regard,
      Afia Aziz Kona

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo