Consider a dataset of 10 residents. Their ID, home type, region, and the number of family members are in a PDF. We’ll extract the PDF to an Excel file.
Method 1 – Use Power Query to Extract Data from PDF
Steps:
- In the Data tab, select Get Data and choose From File.
- Select the From PDF option.
- A box named Import Data will appear (you may get a “Connecting” notification box).
- Choose the PDF file from which you want to extract the data and click Import. We chose a PDF file called Data.
- A new dialog box called Navigator will appear.
- Select the Table001 (Page 1) option to import the table into your worksheet.
- You will see a preview of the dataset of that page in the Navigator dialog box.
- Click on Load.
- A new sheet will open, entitled Table001 (Page 1), and the data will be extracted into the Excel file as a table.
- You will get the data in the Excel sheet.
Read More: How to Extract Data from Multiple PDF Files to Excel
Method 2 – Using the Copy Paste Features
Steps:
- Open the PDF.
- Press Ctrl + A.
- Press Ctrl + C to copy the dataset.
- Open Microsoft Excel and choose the Blank workbook option to open a blank spreadsheet.
- Select any cell in that worksheet.
- In the Home tab, select Paste and choose Paste Destination Formatting.
- You can also press Ctrl + V to paste the dataset into the worksheet.
- Write down a suitable title for your dataset and choose formatting from the Font, Alignment, and Style available in the ribbon to make your dataset look similar to the PDF.
- You will get the dataset in your Excel workbook.
Read More: Convert PDF to Table in Excel
Method 3 – Using Microsoft Word
Steps:
- Select the dataset in the PDF file.
- Press Ctrl + C.
- Launch Microsoft Word and select the Blank document option.
- Right-click and choose Keep source formatting (K) under Paste.
- If you can’t see the whole dataset, click on the move pointer icon on the top-left corner of the table (it will also help you to select the entire table) and choose a suitable alignment.
- Click the move pointer icon and press Ctrl + C to copy the table.
- Open Excel.
- In the worksheet, select any cell and press Ctrl + V to paste the dataset.
- Format the dataset however you like it.
Read More: How to Convert PDF to Excel without Software
Method 4 – Extract Data by Using the Adobe Acrobat Conversion Tool
Steps:
- Open the PDF file in Adobe Acrobat.
- Select the Export PDF option from the Tools option on the right panel.
- If the tools option doesn’t display on the right side of your window, it will be on the right side of the Home tab.
- Choose Spreadsheet and select Microsoft Excel Workbook.
- Click the Export button.
- A new window named Save As will appear.
- Check on the option Open file after export.
- Choose your desired location to save the Excel file. We chose the Desktop to save the file.
- Another dialog box will appear.
- Write down a suitable name for your Excel file and click Save. We wrote Data as our file name.
- A small progress bar will be displayed in Adobe Acrobat to show the progress rate of your file exporting.
- Microsoft Excel will open automatically. Apply your desired formatting to the resulting dataset.
- You will get your data in an Excel spreadsheet.
Read More: How to Export PDF Comments into an Excel Spreadsheet
Download the Practice Workbook
Related Articles
- How to Convert PDF to Excel without Losing Formatting
- Link PDF Form to Excel Database
- How to Extract Data from PDF to Excel Using VBA
<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
The copy and paste option works perfectly in Excel 2010, but it does not work in Excel 2013.
Why would that be?
Hello Andre Van Niekerk
Thanks for sharing your problem. It is unusual for copying and pasting to stop working entirely in Excel 2013 if it worked perfectly in Excel 2010. Both versions rely on the same core Windows clipboard functionality.
The PDF file copied from might have a complex layout or structure that Excel 2013 has difficulty handling during the copy-and-paste process. Also, if you’re trying to paste into merged cells in Excel 2013, it might cause problems. Try un-merging the cells before pasting.
If you are still having trouble with the issue, we recommend you use the other approaches mentioned. Let us know if they lead you to the same result; good luck.
Regards
ExcelDemy