Microsoft has introduced a feature in Microsoft Excel 365 that makes it easy to import data from PDFs on your computer or directly from the web. The feature has its quirks but will work for many applications. In this article, I will show you how to check if the feature is available in your version of Excel and import data from a PDF on the web.
Step 1: Check if the Feature is Available
The PDF Connection feature is only officially available on Microsoft Excel 365 version 2007 or greater, first released in July 2020. For more information on releases, see Microsoft’s documentation. To check if you have that version or greater, open Excel and go to File > Account. Beside About Excel you will see the version.
If you have at least version 2007, you are good to go. If not, update if possible. If no update is possible, you will need to use another method to get your PDF data into Excel.
Step 2: Import the PDF
To import a PDF from your computers disk, go to the Data tab > Get Data > From File > From PDF. To import a PDF directly from the web, go to the Data tab > Get Data > From Other Sources > From Web. Excel will detect it is dealing with a PDF automatically if using the From Web option.
Enter the URL or browse for the PDF and click OK / Import to open the Navigator. The Navigator will show you various tables Excel was able to detect in the PDF and the raw page data. The page date will include everything but is often not a very clean format with lots of blank cells and rows/columns that don’t really make sense. The table data tends to be more suited to importing into Excel, but data can be missed in tables. Tables don’t always generate the same in very similar PDFs which is one gripe I have with the software. Take a look through the tables and pages the Navigator offers you and choose the best for your case.
If you’re satisfied with the way the navigator displays the table, click Load. Otherwise, click Transform Data and make any necessary changes in the query editor. For more information on using the Query Editor, see the video above
That’s it! Feel free to post any comments or questions below.