The Portable Document Format, or “PDF”, has been a highly popular standard for electronic sharing since 1993. It is no wonder, then, that the need to convert information from PDF to Excel comes up. Powerful tools are destined to collaborate.
The nature of the PDF file format, however, makes this problematic. PDF files are graphic, visual documents. They are not collections of letters and numbers easily converted to other programs that deal more elegantly with text and data.
In Excel 365, you will use the Get & Transform tools to convert PDF data into a worksheet.
Pros: Lasting queries and connections from your source so that, if you wish, you can update your workbook data simply by updating the original file. Since PDFs are not dynamic, this means saving a new file with updated information in the same place with the same name.
Cons: Excel doesn’t do a great job of interpreting PDF content. Plan to spend time cleaning up data formatting or stitching together tables that get split.
Import a Single Table from a PDF File
1. Open the workbook where you want the data to live, then click the Data tab.
2. Click the Get Data dropdown menu in the Get & Transform group.
3. Hover over From File and select PDF from the flyout menu. [A]
4. Browse to and select the PDF that you want to convert, then click Import.
5. The Navigator window opens. Excel parses the file and prepares a list of tables and pages in the Display Options pane. Select any item to see a preview of the table.
6. When the table you want is selected, click Load [B] to create a new tab with the data imported as a table, or open the dropdown and select Load To [C].
- Load To opens the Import Data dialog. Select how you want the data to appear in the workbook – as a plain table or a PivotTable for example. Then, select where you want the data imported. We want to import to a plain table [D] into an Existing worksheet [E] at a specific cell address [F].
- Click OK.
The data will be added to your workbook at the location you specified.
Import Multiple Tables from a PDF File
If there are multiple tables that you want imported within the same PDF document, begin by following the same steps as above from the Get Data command on the Data tab.
1. In the Navigator window, put a check in the Select multiple items checkbox [G], and then a checkmark by each item you want to include.
2. Select Load To to open the Import Data dialog window.
3. Select the Table radio button and click OK. You cannot place multiple tables at a location using the Existing worksheet option, it will be greyed out. When you click OK, a new tab will be created for each table you imported.
Import and Place Multiple PDF Tables into Excel Worksheets from the Queries & Connections Pane
Each time you import data using the Get Data command on the Data tab, Excel creates a connection back to the original file. If your PDF is complicated and Excel splits the information up into multiple tables – a common problem when tables cross page borders – then you may be need to stitch your tables back together into a single worksheet. One easy, though tedious, way to do this is to place each table, one at a time, like a puzzle.
Here is an example:
1. Begin as you did above from the Get Data command on the Data tab. Select your tables, then choose the Load To option.
2. In the Import Data dialog, leave on the Only Create Connection radio button [H] and click OK. Each table or page you selected appears in the Queries & Connections pane but no data is loaded into a worksheet, yet.
3. Hover over an item in the pane to see a preview of the table. You will probably select the top-leftmost table to begin. In the preview pane, click the More button [I] and select Load To [J] from the menu.
4. Click the Table radio button [D] in the Import Data dialog, then select Existing worksheet [E] and the A1 [F] cell. Click OK.
5. Excel imports that table, then you are ready to stitch in the next one. Select the first cell in the first empty row under your imported data, then find the next table in the Queries & Connections pane, most likely the table that begins on page 2 of your PDF but it will depend upon how Excel broke up the pages.
6. Repeat 3-4 steps above. In the Import Data dialog, the selected cell should be pre-filled in the field under Existing worksheet. If not, type or select the cell and click OK.
Repeat for each table you need to stitch, either to the bottom or to the right of your worksheet. You will also likely need to do some cleanup to remove repeated row headings and so on. This is not an elegant solution to complex PDF conversions, but simple in terms of steps.
