Today, we’re going to learn how to Converting a PDF Document into Excel Spreadsheet. Are you ready to discover the simple steps and commands we need to follow? Let’s dive into this exciting adventure in the world of digital data!
Converting a PDF Document into an Excel Spreadsheet
I’ll guide you through the process of converting a text-based PDF into an Excel spreadsheet using Power Query in Excel. However, please note that this method may not work for PDFs containing images or complex formatting.
Step 1: Install Power Query
If you are using Excel 2010 or 2013, you need to download and install the Power Query add-in. For Excel 2016 and later versions, Power Query is already built-in.
Now first we need to locate our PDF file which we want to convert into an Excel Spreadsheet.
You can see, that the above image shows my PDF file location on my computer.
Now it is time to open this file and check the data, double-click on it to open.
See in PDF we can not change or edit our data.
But in Excel, we can change our data or edit or even modify our data.
Now our next step is how to send this image or this file into Excel to convert this PDF data into an Excel Spreadsheet. (Converting a PDF Document into Excel Spreadsheet)
Step 2: – Import Data
Open your Excel application and navigate to the “Data” tab on the Ribbon area.
Click on “Get Data” or “Get & Transform Data” (depending on your Excel version).
Select “From File” and choose “PDF”.
Once you click on it, then you need to locate your PDF file on your computer. (Converting a PDF Document into Excel Spreadsheet)
3. – Choose PDF File
Locate and select your PDF file in the File Explorer window.
Click “Import” or “Open” to proceed.
4.- PDF Import Wizard
The PDF import wizard will appear. If your PDF is primarily text-based, you’ll see a data preview.
On the left side, you can file your data tab and on the right side, you can see the preview of your data.
There are two options appear at the bottom.
Load
If you want to load your data as it is appearing and without making any changes, then click on the “Load” button. This will load your PDF data into Excel as it appears. (Converting a PDF Document into Excel Spreadsheet)
Transform Data
The imported data will appear in a new worksheet. Use the Power Query Editor to clean and transform the data:
You can make the below changes on your dataset.
Remove unnecessary columns.
Rename columns for clarity.
Filter rows if needed.
Split or merge columns if required.
Correct data types.
Once you click on Transform Data, your data will open in the Power Query window to make changes.
In this window, you can edit any of your datasets. like, Delete, Rename, Merge, or Split, such kind of modification you can make in this window. (Converting a PDF Document into Excel Spreadsheet)
5.- Load Data into Excel
Once you make the necessary changes to your data in the Power Query Editor window, then you can load your data into Excel.
Go to the “Home” tab in the power query editor window, then click on the drop-down of “close & load” and use “Close & Load”. (Converting a PDF Document into Excel Spreadsheet)
Once you have done this, your PDF data will be imported into Excel format.
But if you use the “Close & Load To” option, you can set the location for your data and where you want to import.
See the below image, of how your PDF data looks in Excel.
See that your PDF data is converted into Excel Spreadsheet.
This is a short article about how we can import our PDF data into Excel.
Furthermore, details for PDF to Excel are provided on the Microsoft Office website.
I hope you find this article useful, please feel free to put your comments or suggestions in the comment box below.
Other Useful Article
How to Compare Two Excel Workbooks and Highlight Differences in Excel VBA
How to Extract Data Between Two Date Ranges in Excel VBA
Combine Data from Multiple Worksheets into a Single in Excel VBA
Excel VBA Macro: – Apply Filter and Copy, Paste Data Into Another Worksheet
How to Copy Data From a Closed Workbook in Excel VBA
How to Convert Excel Files to CSV Format File
How to Create Search Box in Excel (No VBA Code Required)
How to Filter the Data as You Type in Excel VBA
VBA Macro to Dynamically Copy and Paste from One Sheet to Another