Extract data from PDF to Excel – Step by Step Tutorial
- Last updated on April 14, 2021
Chandoo
Share
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).
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.
💡 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.
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.
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.
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.
Examples:
- 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.
Examples:
- 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
Share this tip with your colleagues
Get FREE Excel + Power BI Tips
Simple, fun and useful emails, once per week.
Learn & be awesome.
- 7 Comments
- Ask a question or say something...
- Tagged under consolidation, downloads, Excel Howtos, pdf, power query, videos
- Category: Learn Excel, Power Query
PrevPrevious10 Advanced IF formula tricks you must know
NextGetting Started with Power Pivot & DAX – FREE Live MasterclassNext
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.Welcome to Chandoo.org
Read my story • FREE Excel tips book
Want an AWESOME
Excel Class?
Excel School made me great at work.
5/5
– Brenda
FREE Goodies for you...
100 Excel Formulas List
From simple to complex, there is a formula for every occasion. Check out the list now.
20 Excel Templates
Calendars, invoices, trackers and much more. All free, fun and fantastic.
13 Advanced Pivot Table Skills
Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.
Get started with Power BI
Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.
Recent Articles on Chandoo.org
Hi everyone… Let me start by wishing you all a very happy holiday season and stellar start to 2023. Here is our annual holiday card. [pictured left to right: Nishanth (son), Jo (wife), Nakshu (daughter) and Chandoo (me)]Merry Christmas & Happy New Year 2023
Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
#### in Excel cell
How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]
Best of the lot
- Excel for beginners
- Advanced Excel Skills
- Excel Dashboards
- Complete guide to Pivot Tables
- Top 10 Excel Formulas
- Excel Shortcuts
- #Awesome Budget vs. Actual Chart
- 40+ VBA Examples
Related Tips
Learn Excel
Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
Learn Excel
#### in Excel cell
Learn Excel
How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
Learn Excel
FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]
Learn Excel
Filter one table if the value is in another table (Formula Trick)
Learn Excel
Which Excel Formulas should you learn first?
7 Responses to “Extract data from PDF to Excel – Step by Step Tutorial”
Jinesh Vasa says:
April 21, 2021 at 7:16 am
Dear Chandoo,
Thank you very much for this and it is very helpful.
However, all the Credit Card Statements are now password protected.
Please advise how can we have a workaround for thatReply
Sivakumar H says:
May 15, 2021 at 2:02 pm
Hello sir,
How to check two names are present in the same column ?
Thanks and RegardsReply
Ahmed Mallook says:
May 27, 2021 at 5:37 am
Hi, Thank you for the great tip. One problem, when I click on get data >> from file, I don't see the PDF source option. How can I add it?
I tried to add it from Quick Access toolbar >>> Data Tab, but again the PDF option is not listed there.
I am using Office 365Reply
PP says:
August 10, 2021 at 12:49 am
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. Your video would work except that I can't get the composite table and each of the tables I do get or the pages does not have all the info. what to do?
Reply
Jr. H says:
September 14, 2021 at 8:38 pm
Dear Chandoo,
How do we select multiple amount of tables/pages in one PDF and repeat the same for rest of the PDF;s in the same folder and then extract that data only on power query.Thank you
Reply
antonlagi says:
December 3, 2021 at 1:09 am
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. nice share
Reply
cookie clicker says:
May 17, 2022 at 4:54 am
One bank statement takes up 20 tables and four pages in this document. I need to consolidate roughly thirty different bank statements that I have. Your video would be useful if I could only get the composite table, which I can't for some reason, and each of the tables or pages that I can get is missing some information.
Can macro convert PDF to Excel?
Click the Run Macro button in the toolbar, or press F5 on your keyboard, and select PDF2Workbook . When asked for your API key, enter the API key from our API page. Select the PDF file that you want to convert. Wait for your PDF to be converted.How to convert PDF file to Excel in VBA?
To be more precise, you can use VBA to automate the 3 methods of converting PDF to Excel that I mention above. I show you how to do this in the VBA tutorial below..#1: Automation..#2: Data objects..#3: Dynamic Data Exchange (DDE)..#4: SendKeys..#5: The Command Line..How do I convert a PDF to Excel automatically?
Open a PDF file in Acrobat..Open a PDF file in Acrobat..Click on the “Export PDF” tool in the right pane..Choose “spreadsheet” as your export format, and then select “Microsoft Excel Workbook.”.Click “Export.” If your PDF documents contain scanned text, Acrobat will run text recognition automatically..How to extract data from PDF to Excel using macro?
First of all, you have to declare the necessary inputs. These include the worksheet name, the range of the cells, the location of the application through which the PDF file will be opened (Adobe Reader in this example), and the location of the PDF file. Next, we've to call the VBA Shell function to open the PDF file.