How to Import Data From a PDF to Microsoft Excel

If you own documents in PDF, like an account statement from a bank or financial document, then you may need to import this data in Microsoft Excel. With an integrated tool, you are able to add and then convert a PDF file to Excel.

This feature is convenient and can spare you from having to search for the PDF file converter. It’s not just that, it lets you pick and select the information from the document you would like to convert. The entire process is only about a time of about a minute.

Note: Since the time the feature was introduced in August of 2020, it’s only been accessible only to Office 365 subscribers.

Connect a PDF File to Excel

To begin, choose the worksheet you’d like to work on within Excel and then go to the Data tab. Select to open the Get Data drop-down arrow on the left-hand edge of the ribbon. Place your cursor over From File and select „From PDF.“

Find your file in the browser window, choose it, and then click „Import.“

Then, you’ll be able to see next, you’ll see the Navigator pane. Below are the pages and tables within the PDF document. You can search for them on the top, or select an element and view an example on the right.

If you find the item you wish to import Click „Load“ at the bottom of the screen.

When your data is transferred from the PDF then you’ll see it on your spreadsheet as well as your Queries & Connections sidebar. This useful area lets you modify the data connected if you want as we’ll discuss the options further down.

Adjust the Load Settings

If you would prefer to save the data in a certain format like the pivot table or an entirely new worksheet, click the drop-down arrow for loading and choose „Load to“ instead.

Choose your options from the Import Data window, and then select „OK.“

Transform the Data using Power to transform the data using Power

Another option to import information in your document is to convert it with Power Query Editor. Power Query Editor. Within the Navigator window, where you choose the element you want to import then click „Transform Data“ at the lower right hand corner in place of „Load.“

Then, you will be able to allow you to open The Power Query window where you can make choices like selecting to add more columns as well as transposing rows and columns as well as managing parameters or formatting data.

Adjust the Connected Data

If you’d like to modify the information you have downloaded then you can do it by opening the file from within the Queries & Connections sidebar. Then, you can take action like editing it using Power Query Editor, editing it using Power Query Editor, duplicating or merging it and adding references or even deleting the data.

As stated, you’ll see that the Queries & Connections sidebar open after you’ve imported pdf files. If you shut it down, you are able to open it by clicking on the Data tab, and clicking „Queries & Connections“ in the ribbon.

Click on the data that is connected and a window will open. The bottom of the window can select View in the Worksheet, Edit and deletion. When you click on 3 dots on the right, you’ll find options for Duplicate, Reference and Merge.

You are also able to alter the data in your spreadsheet just like you normally do. If you’re using tables that you filter, you are able to sort. You can also choose another style, alter the font, and then apply additional formatting.