If you have a PDF document such as a bank statement or financial report, you may want to pull this information into Microsoft Excel. With a built-in feature, you can easily import and automatically convert a PDF to Excel.
This convenient feature can save you from hunting down a PDF file converter. Not only that, but it allows you to pick and choose the data from the file you want to import. The entire process takes only a minute.
Note: Since the time this feature was implemented, August 2020, it has only been available to Office 365 subscribers.
Connect a PDF File to Excel
To get started, select the sheet you want to work with in Excel and go to the Data tab. Click the Get Data drop-down arrow on the left side of the ribbon. Move your cursor to From File and pick “From PDF.”
Locate your file in the browse window, select it, and click “Import.”
Next, you’ll see the Navigator pane. On the left are the tables and pages in your PDF file. You can search for one at the top or simply select an element and see a preview on the right side.
When you see the item you want to import, click “Load” at the bottom of the window.
Once your data is imported from the PDF, you should see it in your sheet along with the Queries & Connections sidebar. This handy spot lets you adjust the connected data if you wish, and we’ll explain these options below.
Adjust the Load Settings
If you prefer to load the data in a specific format such as a pivot table or to a new worksheet, select the Load drop-down arrow and pick “Load to” instead.
Then choose your options in the Import Data window and click “OK.”
Transform the Data with Power Query
Another option for importing data from your PDF is to transform it using the Power Query Editor. In the Navigator window where you select the element to import, click “Transform Data” at the bottom instead of “Load.”
This will open the Power Query window where you can do things like choosing or adding more columns, transposing rows and columns, managing parameters, and formatting the data.
Adjust the Connected Data
If you want to adjust the data you imported, you can do so by opening the data from the Queries & Connections sidebar. You can then take actions such as editing it using the Power Query Editor, duplicating, merging, adding a reference, or deleting the connected data.
As mentioned, you’ll see the Queries & Connections sidebar open when you import your PDF file. If you happen to close it, you can reopen it by going to the Data tab and clicking “Queries & Connections” in the ribbon.
Place your cursor over the connected data and you’ll see a window appear. At the bottom, you have actions for View in Worksheet, Edit, and Delete. If you click the three dots, you’ll see options like Duplicate, Reference, and Merge.
You can also manipulate the data within your sheet as you normally would. If you’re using a table, you can filter and sort. You can also select a different style, update the font, and apply additional formatting.
For those times when the data you need to work with or analyze comes in the form of a PDF, remember that you can pull that PDF file right into Microsoft Excel.