We need to extract data or convert different types of files from the various formats to Excel for calculations and data manipulation reasons. PDF is one of the most used document formats used worldwide and an important source of data. How we convert PDF files to Excel at the same time without losing the parent formatting is discussed here with detailed instructions.
How to Convert PDF to Excel without Losing Formatting: 2 Easy Ways
For the demonstration purpose, we are going to convert the below PDF file to an Excel worksheet to keep the major formatting intact. In the PDF we have the Date of Purchase, Region, Product, and Quantity as the table header.
1. Utilizing Power Query to Convert PDF to Excel without Losing Formatting
Power Query is a data preparation or processing engine. Here we will extract data and then process it in another window in Excel. Then we will get the output and load the result in the Excel worksheet altogether.
Steps
- At first, open an empty Excel worksheet, and then click on Get Data from Data tab.
- After clicking the Get Data icon, go to From File to From PDF as shown in the image.
- After clicking the From Folder, a new Browse window will open, from that window go to your file location and select the file on your computer which you want to convert to Excel format click on Open after this.
- Then you will see that all of the tables inside the PDF file are now loaded to a new window.
- If you see closely, the title and main table show separately as tables in the preview window.
- Select Page001 to select the whole first page of the article, it will include the whole dataset, and then click on Load and then Load to.
- The power query window will close and a new window named Import Data will spawn, in that window select the Existing Worksheet option and also select the location of the loaded data in the orange box, here it is $A$4. Click OK after this.
- After this, you will notice the data table is now loaded into the specified location as a table in the worksheet.
- Now select the table and go to Table Design, from there select Convert to Range from the Tools group to convert the table back to the range.
- Now you notice that the PDF file now loaded into the Excel worksheet
- Although it will need some modifications like color adjustment, cell width adjustment because of different cell widths in Excel, the basic data or text will be the same in Excel
- The below image is showing the PDF file data in an Excel worksheet after some minor formatting.
Read More: How to Convert PDF to Excel without Software
2. Using Adobe Acrobat Conversion Tool
Adobe Acrobat is the complete PDF product that can create, edit, and convert PDF files to other formats. Conversion of PDF to Excel is also possible through this product seamlessly.
Steps
- We are going to use the following PDF file to demonstrate how we can export PDFs into the Excel worksheet.
- Now open Adobe Acrobat Reader and from the Homepage, click on Tools.
- After clicking tools you will be taken to a new options menu. From that menu, select Export PDF and click the below Open drop-down menu.
- After clicking the Open drop-down menu, click on Open from the dropdown menu.
- Next, a new menu will appear, from that menu first, you need to choose in what type format of the file you want to convert your PDF file. Select Spreadsheet and from the right side and select spreadsheet type, which is Microsoft Excel Workbook.
- Click on the Export button below the window.
- After that Adobe Acrobat will open a file browse window from where you need to select the PDF file that you want to convert to Excel. Head over to the location of the file, and select the file. After that, click Open.
- After clicking Open, you will notice the file is now in the adobe reader and now you have to select the destination folder where you want to save the final converted Excel sheet.
- If you already have converted one of the PDF files before, then the previous location will be shown below the Save in Recent Folder.
- Tick on the Open file after export box if you want to open the file right away after the conversion.
- Select the destination folder by clicking the Choose a Different folder, if you are the first time doing it. Or you want to save the file in a different location than the previous location.
- After clicking Choose a Different folder, go to your file location, and click Save.
- Now you are seeing your PDF file now exported or converted to an Excel worksheet.
Read More: How to Convert PDF to Excel Table
Download Practice Workbook
Download this practice workbook and the PDF below.
Conclusion
To sum it up, the question “how to convert PDF to Excel without losing format” is answered here in 2 different ways. The most useful method is to use a power query to get data from PDF files and process them in the Power Query window. And formatting them appropriately afterward. . There are other methods which are using Adobe acrobat reader and exporting PDF files to Excel worksheets.
For this problem, a workbook is available to download where you can practice these methods.
Related Articles
- How to Extract Data from PDF to Excel
- How to Extract Data from Multiple PDF Files to ExcelÂ
- How to Export Data from Fillable PDF to ExcelÂ
- How to Link PDF Form to Excel DatabaseÂ
- How to Export PDF Comments into an Excel SpreadsheetÂ
- How to Extract Specific Data from PDF to Excel Using VBA
<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
