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.
Method #1: Convert PDF to Excel Using the Get Data Feature in Excel
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:
- Open a blank worksheet in Microsoft Excel.
- Click the Data tab.
- Click the Get Data option.
- Hover the cursor over the From File option and click the From PDF option on the sub-menu that appears.
- Navigate to the folder where the PDF file is stored, select the file and click the Import button.
The data from the PDF file appears in a preview mode in the Navigator window in Excel.
- At the bottom of the Navigator window, open the Load drop-down and choose the Load To option.
- Select the appropriate options on the Import Data dialog box that appears and click OK. In this case, we have selected the Table and Existing worksheet options.
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.
Also read: VBA to Print Range of Cells to PDF
Method #2: Convert PDF to Excel Using Microsoft Word
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:
- Open Microsoft Word and click the File tab to open the Backstage window.
- On the Backstage window, click Open on the left sidebar and click the Browse option on the right.
- Navigate to where the PDF file is saved, select it and click the Open button.
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.
- Click OK on the message box that appears.
The data is converted to an editable Word document:
Notice that the imported data does not look exactly like the original PDF file.
- Press Crtl + A to select the imported data and press Ctrl + C to copy it.
- Open a new Excel worksheet and press Ctrl + V to paste the data.
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.
Method #3: Convert PDF to Excel Using Free Online Tools
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:
- Open your browser and navigate to the online Adobe Acrobat converter.
- Drag the PDF file you want to convert and drop it on the tool’s interface or click the Select a file button and navigate to the folder where the file is stored.
The tool converts the file:
- Once the process of conversion is complete, click the Download button to download the converted 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 recommend that you avoid using the free online tools if the PDF file contains sensitive or confidential information. The online tools do not guarantee the privacy of your data.
- The free online tools set a limit to the file size you can upload so if your file is too big you cannot use the tools.
Method #4: Convert PDF to Excel Using Copy and Paste
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.
- Open the PDF file in Adobe Acrobat Reader.
- Press Ctrl + A to select the data.
- Press Ctrl + C to copy the data to the Windows clipboard.
- Open a blank Excel worksheet and press Ctrl + V to paste the data.
The data is pasted into one column:
Changing the Vertical Data into Columns
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:
- Create some numeric horizontal and vertical headers as shown below:
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.
- Select cell F2 and type in the following formula:
=OFFSET($A$1,$E2+F$1-2,0)
- Click the Enter button on the formula bar to enter the formula.
- Drag the fill handle to copy the formula across to the next six columns and down to the next eight rows:
Explanation of the formula
=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.
Method #5: Convert PDF to Excel Using Google Docs
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:
- Login into your Google account and navigate to Google Drive:
- Click the New button in the top left corner of the drive:
- Click the File upload option on the menu that pops up:
- Navigate to where the PDF file is stored, select the file and click Open.
- Right-click the uploaded file and choose Open with Google Docs on the shortcut menu that appears.
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
Method #6: Convert PDF to Excel Using Programming Language Such as Python
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:
- Import the needed Module.
- Read the PDF file called “Tablet Sales.”
- Converts the PDF into a CSV file. The CSV file can then be imported into Excel using the Get Data feature:
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:
