How to Convert a PDF to an Excel Table - 4 Methods

Method 1 – Using the Get Data Feature to Convert PDF Data into an Excel Table

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

In Import Data, select the PDF file and click Import.

In Navigator, select Table001 (Page 1).
Click Load and select Load To…

The table will be displayed in a new sheet.

Select the cell where your table will start in the Import Data dialog box.
Click OK.

Advantage:

This procedure is dynamic: the PDF file was updated.

Replace the destination file with the updated PDF file.
Right-click the table and select Refresh.

The table will be updated.

Extracting Multiple Tables from PDF to Excel

The PDF file contains two tables.

Open the PDF file in the Navigator window.
Check Select multiple items.
Check the tables and click Load or Load To…

The tables will be displayed in individual sheets.

Merging Multiple Excel Tables from a PDF file

Open the Power Query Editor.
Place the cursor on any of the Queries & Connections to open a preview window.
Select Edit.

Select the first table and click the drop down icon marked 2 above.
Select Merge Queries…

Select Table002 in the drop down list marked 2 above.
Select the similar columns in Merge.

A column is added to the current table.

Click the drop down icon in the heading of the new column.
Check Attendance Percentage. Rename the column heading.

Name the Query. Here, ‘Performance Overview‘. The Query Settings are at the right side of the Power Query Editor window.

Select Close & Load.

The merged table is displayed with default formatting.

Resize and reposition the table. You can also change the number format.

Note: If data in the similar columns does not match cell positions, use the Fuzzy Lookup Algorithm.

Method 2 – Converting a PDF to an Excel Table from Online (Free)

Open the window: Convert PDF to Excel online for free.

Click select a file.
Select the PDF file and click Open.

The PDF file will be converted to an Excel workbook. Download it.

Method 3 – Using the Copy and Paste Feature and a Formula to Convert a PDF File to an Excel Table

Copy the table from the PDF file.
Paste it in the Excel sheet.

Data is displayed in a single column.

Data is pasted in sequential order.

Use the formula.
Press Enter and drag down the Fill Handle.

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

The INDEX and the ROW functions enter data in B4, B7, B10,,,,.

Enter the following formula to enter values in B5, B8, B11,,,,.

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

Enter the formula below to get data in the Grade column.

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

Select the data range and press Ctrl + C.
Right-click the first cell in the table and select Paste Values.

Formulas will be removed and only values will remain.

To convert the data range:

Select the data range.
Press Ctrl + T to convert it into a table.
Check ‘My table has headers’ and click OK.

Method 4 – Two Step Copy and Paste (PDF to Word to Excel)

Copy the table from the PDF file to a Word Document.
Copy the table from the Word Document to an Excel workbook.

The table from the PDF file is pasted in a tabular form.

Download Workbook

Convert PDF to Table.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

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

2 Comments

Yos Dec 9, 2022 at 9:25 AM

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
- Reply
  Afia Dec 12, 2022 at 11:31 AM
  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.
  
  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

How to Convert a PDF to an Excel Table – 4 Methods