Documents such as bank statements may be sent to us in PDF format. We may need to convert these documents into Excel.
This enables us to use the powerful Excel features to extract and compute the data we need for different uses, such as filing tax returns.
We may want to do the PDF to Excel conversion without using dedicated software to save on costs.
This tutorial shows six techniques for converting PDF to Excel without software.
Table of Contents
In the past, it was impossible to extract data from PDF using Excel’s built-in features, but now we can use the Get & Transform options.
For the purpose of this tutorial, let’s consider the following PDF document called Tablet Sales that we got from a sales agent.
We can use the Get Data feature in Excel to extract data from PDF and get it into Excel.
Below are the steps to use the Get Data option to get PDF data in Excel:
The data from the PDF file appears in a preview mode in the Navigator window in Excel.
The PDF document is converted to Excel, but the column headers have been replaced with the generic Column1, Column2, …headers:
Note: Imported data may lose its original layout, format, and other details, and you will need to add them manually.
Microsoft Word has the built-in capability to convert a PDF to an editable Word file.
We can use this Microsoft Word built-in capability to convert a PDF to an editable Word document, copy the converted data, and paste it into Excel.
To show how this method works, let’s consider the following PDF document called Tablet Sales that we got from a sales agent.
We can import the PDF data into Microsoft Word and copy and paste the data into Excel.
We use the below steps to convert the PDF file to an editable Word document and then get the converted data into Excel:
An informational message box appears, informing you that the process of converting the PDF to an editable Word document may take a while. It also informs you that the resulting Word document might not look exactly like the original PDF.
The data is converted to an editable Word document:
Notice that the imported data does not look exactly like the original PDF file.
Notice that the pasted data has lost some formatting and you will need to format the data so that it appears the way you want.
There are many free online tools on the Internet that we can use to convert a PDF to Excel.
An example of a free online tool that we can use to convert a PDF to Excel is Adobe Acrobat.
To demonstrate how this method works, let’s look at the following PDF document, Tablet Sales, that we received from a sales agent.
We want to convert this PDF file to Excel using the online Adobe Acrobat tool.
We use the steps below to convert our PDF file to Excel using Adobe Acrobat:
The tool converts the file:
When you open the Excel file, it appears in protected view with a warning that files from the internet may contain viruses. Since we trust the source of the file, we click the Enable Editing button to come out of the protected view.
Notes:
We can copy-and-paste to copy the data from a PDF file and paste it into Excel.
Note: You cannot copy content from a PDF file if the author of the file has applied security settings that forbid copying.
For the purpose of this tutorial, let’s look at the following PDF document, Tablet Sales, from a sales agent.
We want to copy the data from the PDF and paste it into an Excel file.
We use the below steps to copy data from the PDF and paste it into Excel.
get in excel" width="724" height="324" />
The data is pasted into one column:
When we copied the data from the PDF file and pasted it into Excel, it was pasted into a single column.
This is not what we expected. We can use Excel features such as Transpose to change the data into columns.
But there is a much simpler way that we can use to change the vertical data into columns.
Notice that in the imported data, each record consists of seven consecutive cells in a single column: Tablet, Jan, Feb, March, April, May, and June.
The goal is to convert this data so that each record appears in seven columns.
We use the below steps to convert the vertical data into columns:
Notice that column E contains numbers that correspond to the first row of each data item, in this case, the tablet.
These numbers can be generated by entering the first two numbers, selecting the numbers, and dragging down the fill handle.
The horizontal row of headers consists of consecutive integers, beginning with 1.
In this case, each record has seven data cells, so the horizontal header contains the numbers 1 to 7.
=OFFSET($A$1,$E2+F$1-2,0)
The formula uses the OFFSET function which returns a reference to a range that is a given number of rows and columns from a given reference.
The formula uses an absolute reference for cell A1 so that it does not change when the formula is copied to other cells.
Google Docs has a built-in capability to convert a PDF file to an editable document. We can use this capability to convert a PDF file into an editable document, copy the data, and paste it into Excel.
To show how this method works, we will use the following PDF document called Tablet Sales from a sales agent.
We will open the PDF file using Google Docs and then copy and paste the data into Excel.
We use the below steps to convert the PDF file into an editable document, copy the data and paste it into Excel:
The file becomes editable in Google Docs. You can make any necessary changes to the data and then copy and paste it into Excel.
The pasted data may need some formatting to become more visually appealing.
While this technique works in most cases, sometimes you may find yourself in a situation where the PDF file just doesn’t open in the Google Docs
If you are familiar with the Python programming language, you can use it to convert PDF files to Excel.
Python is considered one of the best programming languages to use if you want to convert PDF to Excel.
You can use the following code snippet to do the conversion:
import tabula df = tabula.read_pdf("Tablet Sales.pdf ", pages='all')[0] tabula.convert_into("Tablet Sales.pdf ", "Tablet Sales.csv", output_format="csv", pages='all') print(df)This code does three things:
In this tutorial, I showed you six techniques for converting PDF to Excel for free without using any software.
In most cases, you do not need to venture out of Excel and can use the Get & Transform option to import the data from a PDF file into Excel.
You can also try converting your PDF file into an MS Word or a Google Docs document and then copy that data into Excel. If nothing else works, you can also try the free PDF to Excel Converter available on the Adobe Acrobat website.
We hope you found the tutorial helpful.
Other Excel articles you may also like:
I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.
