Extract data from PDF to Excel – Step by Step Tutorial

get data from pdf to excel

In this tutorial learn how to,

  • Extract tabular data from one PDF to Excel
  • Combine and extract tables from multiple PDFS to Excel
We will be using Excel 365 & Power Query to do this. If you have different version of Excel (2016, 2013 or older), read the FAQ section at the end for another way to do this.

How to extract PDF table to Excel

Optional:  If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Step 1: Go to Data ribbon & click on Get Data > File > PDF

From data ribbon, use the PDF option  and point to the location on your computer (or web address).

data from PDF option - power query get data excel

Step 2: Select the table(s) you want in the navigator screen

Power Query will open up a navaigator screen. Just specify the table(s) you want. Refer to below illustration to know more about the navigator screen.

navigator screen for pdf - power query

💡 Bonus tip: Use the composite table if you want to get a data table in your PDF that spans multiple pages. This is excellent for bank or credit card statements.

Step 3: Load or Transform data

If the preview in navigator looks satisfactory, just load it. Otherwise, click on “Transform data” to open query editor to make any final adjustments.

Combine & Extract data from multiple PDFs

Step 0: Place all your PDFs in a folder

Step 1: Folder connection

Instead of PDF option, use the Folder option in the Get Data.

from folder option - get data - power query - excel


Step 2: Choose “Combine” in file listing screen

Power Query will show you a screen with a list of all files it found in the folder. Choose any of the combine options here to combine the data from all files to one table.

File listing screen - Power Query - Folder connection option

Step 3: Select the table you want from Transfer Sample Screen

Now, you will see another navigator like screen. Just select the table you want in here. Power Query will go to each file in the folder, get the same table and combine them.

Step 4: Load or Edit the query

And enjoy.

Practice PDF Credit Card Statements

If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Video – Convert PDF to Excel

Still not sure how to extract data tables from PDF to Excel? Watch this short video and get it. See it below or on my YouTube channel.

PDF to Excel – FAQs

I don’t have PDF option in my Excel. What do I do?

You can use free Power BI Desktop to do the same. (Download Power BI for free here)

Once you have Power BI, open it, go to Get Data > PDF and follow the same steps as above tutorial.

Instead of loading the data, copy the entire table from Query Editor and paste it to Excel. See below illustration.

copy entire table - power query in Power BI

I have new files, how do I refresh?

Just place the files in the same folder.

Go to Excel and right click on the extracted table and select “Refresh”. Excel will update the details.

I want to exclude certain files in the folder when combining…

Open the query editor and go to the query that is responsible for your combining PDF process. Go to source step. This will show all the files in the folder. 

Include a filter condition here. Power Query will warn about inserting a step. Proceed and you will be able to exclude files based on conditions.


  • Process files that have file name starting with certain letters
  • Files created after certain date
  • Having specific extension.

Remember: Power Query is case sensitive.  

I want to pre-process or clean-up data before loading it into Excel

Open the query editor and add any necessary data transformation steps at the end. 


  • Removing all foreign currency transactions from credit card statements
  • Cleaning up account codes
  • Rearranging columns in the PDF data table

For more on what you can do with Power Query, check out this tutorial.

Other questions…

Post a comment and I will try to help you.

More examples on Power Query…

  • Introduction & 4 full examples of Power Query
  • Combine files with Power Query
  • Extract common values between two tables
  • 5 Power Query tips for accounting & finance people

The post Extract data from PDF to Excel – Step by Step Tutorial appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: http://feedproxy.google.com/~r/PointyHairedDilbert/~3/ODEpnjbJiws/

Leave a Reply

Close Menu