How to Bulk Combine PDF Files to Excel Without Losing Formatting

You can get table data from several PDFs into one Excel sheet by copying and pasting the data or manually typing it into the spreadsheet. However, these methods are not ideal. They do not keep the original formatting, and errors can occur in transcription.

The Power Query merge tool is the best way to bulk combine PDFs into Excel files. This automatic process preserves the original formatting and takes less time. Follow these steps to use Power Query.

1. Put the PDFs in a Folder

Move all the PDF files into one folder. This step allows you to bring all the PDFs into Excel at the same time.

2. Open the Excel Workbook

Open a new Excel worksheet or an existing spreadsheet where you want the combined table to generate.

3. Select the PDF Folder in Excel

Click on the “Data” tab, then “Get Data.” In the drop-down menu, hover over “From File,” then choose “From Folder.” In the File Explorer dialog, choose the folder with your PDFs and click “Open.”

4. Transform the Data

Once the PDFs are selected, Power Query previews the metadata for each PDF in the folder. Click “Transform Data,” and the metadata appears in the Power Query Editor window.

5. Extract the PDF Content

The previous steps pull the PDFs’ metadata instead of the file contents. Extract the PDF data by clicking the “Expand Binaries” button — the double-down arrows in the top-right area of the “Content” column.

The Combine Files dialog box shows the tables and pages from all PDFs. Excel automatically selects the first file in the PDF folder as the sample file. Excel uses the sample file as the template for the table that generates in the spreadsheet.

Preview the table and page from the sample file, then click “OK” to combine all the PDF data.

6. Append the Results

As Power Query appends the results, it applies the routine from the sample file to all of the PDFs in the folder. After going through the PDFs, all the data appears combined in the spreadsheet. The columns in the spreadsheet should match your PDF.

7. Send the Data to the Workbook

In the “Home” tab, click “Close & Load” in the “Close” group to send the data to a table in your Excel worksheet.

Learn More About Our Managed IT Services

Agio offers managed IT services to provide a frictionless IT experience for your team. We monitor your network around the clock and support your users while navigating any issues. For more information, contact us online.