In this tutorial learn how to,
- Extract tabular data from one PDF to Excel
- Combine and extract tables from multiple PDFS to Excel
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.
13 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”
Hi Purna..
Again a great tip.. Its a great way to convert Fractional Time..
By the way.. Excel has two great and rarely used formula..
=DOLLARFR(7.8,60) and =DOLLARDE(7.48,60)
basically US Account person uses those to convert some currency denomination.. and we can use it to convert Year(i.e 3.11 Year = 3 year 11 month) and Week(6.5 week = 6 week 5 days), in the same manner...
This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12
Any ideas why?
@Jason
7.8 in Excel talk means 7.8 days
=7.8*24
=187.2 Hrs
=187 Hrs 12 Mins
If you follow Chandoo's instructions you will see that he divides the 7.8 by 24 to get it to a fraction of a day
Simple, assuming the fractional time is in cell A1,
Use below steps to convert it to hours & minutes:
1. In the target cell, write =A1/24
2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
3. Select Custom from “Number” tab and enter the code [h]:mm
4. Done!
Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'
good one
So how do I go the other way and get hours and minutes to fractional time?
If you have 7.5 in cell A1,
- Use int(A1) to get the hours.
- Use mod(A1,1)*60 to get minutes.
If you have 7:30 (formatted as time) in A1
- Use hours(a1) to get hours
- Use minutes(a1) to get minutes.
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Thanks guys, these are the tips I'm looking for.
...dividing the number of minutes elapsed by the percent change is my task - "int" is the key this time
It doesnt work for greater than 24 hours
It returns 1:30 for 25.5 hours. It should have returned 25:30
Ideally I would right function as
=QUOTIENT(A1,1)&":"&MOD(A1,1)*60
Sorry, replied to wrong comment....
----
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Clever use of MOD here to extract the decimal part of a number. Divide a number containing a decimal by 1 and return the remainder. Humm. Very clever.
Thanks very much, extremely useful !