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.
To get this option enabled,
- Select Data >> Get Data >> From File >> From PDF.
- After that, the Import Data window will appear. Browse for the PDF file, select it and click on Import.
- 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.
- After that, select the cell where your table will start from the Import Data dialog box and click OK.
Thus, you can find your desired table in Excel from a PDF file.
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.
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.
You can see that the PDF file now contains two 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…
The tables will appear in individual sheets. Here you can see the table with Grades.
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.
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.
- 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…
- 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.
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.
- After that, select Close & Load to load the merged table to a new sheet.
The merged table now appears with default formatting.
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 Copy from PDF to Excel Table (2 Suitable Ways)
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.
- 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.
Read More: How to Extract Data from PDF to Excel (4 Suitable Ways)
- How to Link PDF Form to Excel Database (with Easy Steps)
- Convert PDF to Excel without Software (3 Easy Methods)
- How to Export Data from Fillable PDF to Excel (with Quick Steps)
- How to Export PDF Comments into an Excel Spreadsheet (3 Quick Tricks)
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.
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.
The formula uses INDEX and ROW functions to insert the data in B4, B7, B10,,,, cells. We get the ID column from this formula.
The following formula will return the values in cells B5, B8, B11,,,,. This actually returns the Name column.
The formula below will give us the Grade column.
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.
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.
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 (2 Easy Ways)
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 Pro has 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.
In the end, we can conclude that you will learn some easy and effective tricks to convert PDF to 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.
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.
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.
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.
Afia Aziz Kona