Hi everyone! Welcome back to Smart Study Blog.
In today’s post, I’ll show you how to convert PDFs to Excel spreadsheets using Microsoft Excel. This can be very helpful when you need to extract data from PDF reports or tables. Let’s get started!
What You’ll Need:
- A PDF file you want to convert.
- Microsoft Excel installed on your computer.
Steps to Convert PDF to Excel
- Open the PDF File: For this example, I’ll use my bank statement downloaded from Online Banking. It has five pages—the first page with headings and the rest with transaction details.
- Open Microsoft Excel: Once Excel is open, create a new blank workbook.
- Import PDF Data:
- Click on the Data tab at the top of Excel.
- Select Get Data > From File > From PDF.
- A file explorer window will pop up. Find your PDF file, select it, and click Import.
- Analyze PDF:
- Excel will start analyzing your PDF and show the available tables and data.
- In the Navigator window, you’ll see a preview of the tables and data found in your PDF.
- Select Data:
- Select the Multiple Items checkbox.
- Choose the tables or pages you want to import.
- Click on Transform Data to bring them into the Power Query Editor.
- Edit Data in Power Query Editor:
- You will see the table with a predefined header row for each table. We need to remove it.
- On the right side of Query Settings, below Applied Steps, remove Changed Type and Promoted Headers.
- You will see new rows inserted with “Column 1,” “Column 2,” etc. Repeat this for each sheet under Applied Steps.
- Combine Sheets:
- Select the first table.
- In the Power Query Editor, go to the Home tab and click on Append Queries > Append Queries as New.
- Under Append, select Three or more tables.
- Add all the tables to Append, from Available Table to Tables to Append.
- Click the Add button one by one or select multiple tables and then click Add.
- Then, click OK.
- Load Data to Excel:
- You will see a preview of Append1, which combines multiple tables into one sheet.
- Click on Close & Load to load the combined data back into Excel as a new sheet.
- Your PDF data is now in one Excel sheet!
- Clean Up Data:
- To remove the top row, select table design and uncheck the header row.
- Your PDF data will now appear in your Excel workbook. You can now edit, analyze, or manipulate the data as needed. Depending on your PDF, you might need to do some cleanup or formatting. Use Excel’s tools to adjust columns, remove unwanted rows, or format the data to your liking.
- Save Your Work:
- Go to File > Save As.
- Choose a location, give your file a name, and save it as an Excel Workbook.
Conclusion
And that’s it! You’ve successfully converted a PDF to an Excel spreadsheet using Microsoft Excel. I hope you found this tutorial helpful. If you did, please give it a thumbs up and subscribe to Smart Study Blog for more tips and tutorials. If you have any questions, leave them in the comments below, and I’ll be sure to get back to you. Thanks for reading and see you next time!
