Import data into Excel from PDF by using Excel’s Get Data from PDF feature with Power Query for text-based PDFs, OCR tools for scanned files, or PDF converters like Adobe Acrobat when direct import is unavailable.
Moving structured data from a PDF into an editable Excel worksheet is a common hurdle for users working with documents or spreadsheets. The process often fails due to formatting loss, uneditable cells, or version-specific limitations in Excel. By the end of this guide, you will be able to choose the right method for your PDF type and Excel version, import the data cleanly, and troubleshoot common issues like merged cells or misaligned columns.
Yes, you can import data from PDF directly into Excel. Modern versions of Excel (2019 and Microsoft 365) include a built-in feature called Get Data from PDF, which uses Power Query to extract tables from text-based PDFs into editable worksheets. For scanned PDFs or older Excel versions, alternative methods like OCR tools, Adobe Acrobat, or manual copy-paste are required.
Import PDF data into Excel with the right method
The first step is identifying the type of PDF you are working with. Text-based PDFs, which contain selectable and searchable text, are the easiest to import. These files often preserve table structures, making them compatible with Excel’s Get Data from PDF feature. Scanned PDFs, on the other hand, are image-based and require Optical Character Recognition (OCR) to convert the visual data into editable text before importing.
The complexity of the PDF layout also matters. Simple tables with clear borders and consistent rows and columns import smoothly, while multi-column layouts, merged cells, or floating text boxes often require preprocessing or manual cleanup. For recurring tasks, built-in tools like Power Query offer repeatable workflows, whereas one-off conversions might benefit from online converters.
A common failure mode in PDF-to-Excel workflows is assuming all PDFs behave the same during import. Always verify whether your PDF is text-based or scanned, as this determines whether you need OCR preprocessing.
<mui:table-metadata title="PDF-to-Excel Method Selection Guide" />
PDF-to-Excel Method Selection Guide
| If | Then |
|---|---|
| The PDF contains structured text-based tables with consistent rows and columns | Use Excel Power Query (Get Data from PDF) for direct, repeatable import with minimal cleanup |
| The PDF is a scanned image or contains image-based tables without selectable text | Use an OCR-based converter such as Adobe Acrobat Pro or dedicated OCR software, then import the resulting file into Excel |
| The PDF contains only a small amount of simple data (fewer than 20 cells or a short list) | Use manual copy-paste or retype the data to avoid setup time exceeding the data value |
Import PDF data using Excel Get Data and Power Query
For users with Excel 2019 or Microsoft 365, the Get Data from PDF feature is the most efficient way to import structured tables. This method leverages Power Query, Excel’s data connection engine, to recognize and extract tables from text-based PDFs while preserving rows, columns, and basic formatting.
Prepare the PDF before extraction
Before importing, review the PDF to ensure it meets the criteria for a smooth extraction. Check that tables have clear borders and consistent column widths, as these visual cues help Power Query accurately detect table boundaries. Remove unnecessary pages or non-tabular content, such as headers, footers, or annotations, which can confuse the extraction process. Confirm that rows and columns are uniformly aligned—irregular spacing or merged cells may require manual adjustments later.
Complete the Excel import workflow
- Open Excel and navigate to the Data tab on the ribbon.
- Click Get Data, then select From File, followed by From PDF. This opens the Import Data from PDF dialog box.
- Browse to the PDF file and click Import. Excel will analyze the file and display a list of detected tables in the Navigator pane.
- Select the tables you want to import by checking the boxes next to their names. Use the preview window to verify that the data is correctly recognized.
- Click Load to import the selected tables into new worksheets. Each table will appear in its own sheet, ready for further editing.
To refresh the data later, right-click the query in the Queries & Connections pane and select Refresh. This updates the worksheet with any changes made to the source PDF.
<mui:table-metadata title="Repeatable PDF Import Workflow" />
- Document the extraction steps (connector path, selected tables, Power Query transformations) during the first import.
- Save the query and workbook as a template for future imports of the same document structure.
- Replace the source PDF file in the query source path and refresh the data instead of repeating copy-paste.
- Run the post-import verification checklist on the refreshed output before analysis.
Import selected PDF tables or pages only
Not all PDFs require a full-file conversion. If your document contains multiple tables or sections, you can selectively import only the data you need. This is particularly useful for large PDFs where extracting everything would be inefficient or unnecessary.
In Excel 2019 or Microsoft 365, use the Navigator pane during the Get Data from PDF process to preview and select individual tables. Each table is listed with a checkbox, allowing you to import only the relevant ones. For scanned PDFs, use Adobe Acrobat Pro to export specific pages or tables as CSV or XLSX files before importing into Excel.
If the PDF contains non-contiguous tables or partial pages, consider using a PDF editor to isolate the relevant sections before extraction. Alternatively, import the entire file and delete unwanted data in Excel after the fact. For recurring tasks, save the Power Query settings as a template to streamline future imports of the same tables.
Fix PDF imports with broken layouts and formatting
Even with the best tools, PDF imports can go wrong. Common issues include merged cells splitting into extra rows, multi-column layouts being read incorrectly, or text boxes appearing in the wrong cells. These problems often stem from inconsistent PDF formatting or limitations in table recognition algorithms.
For merged cells, pre-split them in a PDF editor before extraction, or plan to manually merge and realign the data in Excel afterward. Multi-column layouts, such as newspaper-style tables, may require OCR tools with zone selection to correctly interpret the structure. Floating text boxes or annotations outside the main table are often ignored or misplaced; remove them before extraction or manually relocate the data in Excel.
<mui:table-metadata title="Clean vs. Problematic PDF Layout Features" />
Clean vs. Problematic PDF Layout Features
| Layout feature | Import behavior | Recommended action |
|---|---|---|
| Clear table borders and consistent column widths | High probability of clean import with correct row and column mapping | Proceed with Power Query or direct extraction |
| Merged cells or spanning headers | May split into extra rows or misalign columns during extraction | Pre-split merged cells in a PDF editor or plan cleanup in Excel after import |
| Floating text boxes, sidebars, or annotations outside the main table | Often ignored by extraction tools or placed in wrong cells | Remove floating elements before extraction or manually relocate data after import |
| Multi-column page layouts (e.g., newspaper-style) | Data may be read left-to-right across columns instead of down each column | Use OCR tools with zone selection or manually reconstruct the correct table order in Excel |
Choose between Power Query and PDF converters
Deciding between built-in Excel tools and third-party converters depends on your PDF type, Excel version, and workflow needs. Power Query is ideal for text-based PDFs with clear table structures, offering high accuracy and preserving formatting. However, it requires Excel 2019 or Microsoft 365 and does not support scanned PDFs without OCR preprocessing.
Dedicated desktop PDF converters, such as Adobe Acrobat Pro, handle scanned PDFs by applying OCR to extract editable text. These tools are accurate but may require manual correction for misread characters or complex layouts. Online PDF-to-Excel converters are convenient for one-off tasks but come with privacy risks for sensitive data and may strip formatting or impose file size limits.
<mui:table-metadata title="Power Query vs. PDF Converters Comparison" />
Power Query vs. PDF Converters Comparison
| Method | Best for | Accuracy | Limitations |
|---|---|---|---|
| Excel Power Query (Get Data from PDF) | Text-based PDFs with clear table structure | High for structured tables; preserves formatting | Requires Excel 2019 or Microsoft 365; does not process image-based PDFs without OCR preprocessing |
| Dedicated desktop PDF converters | Scanned or image-based PDFs requiring OCR | Moderate to high depending on OCR engine quality | Requires installation; may need manual correction of misread characters |
| Online PDF-to-Excel converters | One-off conversions with no software installation | Moderate; accuracy varies by service | Privacy risk for sensitive data; file size limits; may strip formatting |
| Manual copy and paste | Small PDFs with fewer than 20 cells or simple lists | Low; high risk of transcription errors | No automation; formatting is lost; impractical for recurring tasks |
Convert scanned PDFs and use older Excel versions
Scanned PDFs require OCR to convert image-based content into editable text. Adobe Acrobat Pro includes built-in OCR capabilities, allowing you to export scanned tables directly to Excel. Alternatively, use dedicated OCR software like ABBYY FineReader or online tools to convert the PDF into a text-based format before importing into Excel.
For users with Excel 2016, 2013, or earlier, the Get Data from PDF feature is unavailable. In these cases, use Adobe Acrobat to export the PDF to Word or CSV, then copy the data into Excel. For large or complex tables, this method may introduce formatting errors, so verify the output carefully. Excel 2007 and earlier versions have even fewer options; stick to CSV exports or manual entry for these releases.
Alternative methods without PDF connector
If your Excel version lacks the PDF connector, Adobe Acrobat’s export tools are a reliable alternative. Open the PDF in Acrobat, select Export PDF, and choose Spreadsheet (XLSX) as the output format. This method works for both text-based and scanned PDFs, provided OCR is applied to the latter. For simple tables, copying and pasting directly from the PDF into Excel may suffice, though this often loses formatting and requires manual adjustments.
Mac users face similar version-based limitations. Excel for Microsoft 365 on Mac includes the Get Data from PDF option under the Data tab, though the dialog layout differs slightly from the Windows version. Excel 2019 and earlier releases for Mac do not include this connector, so Mac users on those versions should follow the same Adobe Acrobat export or manual copy-paste approach described above for Excel 2016 and 2013 on Windows.
CSV format is another useful intermediate step. Many PDF tools, including Acrobat, allow exporting to CSV, which can then be imported into Excel using the Text to Columns feature to reconstruct the table structure.
Handle older Excel compatibility issues
Excel 2007 and earlier versions do not support the XLSX format natively, so save imported data as XLS or CSV for compatibility. If using CSV, ensure that special characters and delimiters are handled correctly during import. For recurring tasks, document the steps required to clean and format the data after each import, as older versions lack many of the automation features available in modern Excel.
<mui:table-metadata title="Excel Version and PDF Type Compatibility" />
Excel Version and PDF Type Compatibility
| Excel version | Available option | PDF type | Recommended approach |
|---|---|---|---|
| Excel 2019 / Microsoft 365 | Built-in Get Data from PDF connector | Text-based PDFs | Use Data > Get Data > From File > From PDF; select tables in the Navigator pane |
| Excel 2016 / 2013 | No native PDF connector | Text-based PDFs | Export to Word or CSV using Adobe Acrobat, then copy into Excel or use Text to Columns |
| Excel 2007 or earlier | No PDF connector; limited format support | Text-based PDFs | Use Adobe Acrobat export to XLSX or CSV; avoid direct paste for large tables |
| Excel 2019 / Microsoft 365 | Power Query with third-party OCR add-ins | Scanned PDFs | Run OCR in Adobe Acrobat Pro or dedicated OCR software, save as CSV/XLSX, then import |
| Excel 2016 / 2013 / 2007 | Third-party OCR tools or online converters | Scanned PDFs | Use Adobe Acrobat Pro or trusted OCR converters; verify output against source images |
Clean and verify imported Excel spreadsheet data
After importing, always verify the data to ensure accuracy and usability. Start by checking that column headers align with the original PDF and occupy a single row. Numeric values should be stored as numbers, not text—check for right-alignment and formula compatibility. Dates should be recognized as date values and display in the correct locale format.
Next, confirm that all imported cells are editable and not locked or embedded as images. Scan for duplicate rows or blank lines introduced during extraction, and remove them as needed. For merged cells from the PDF, ensure they have been split into individual cells with the correct data. If formulas were present in the original PDF, note that they will not transfer as executable formulas; you will need to recreate them manually in Excel.
<mui:table-metadata title="Post-Import Data Verification Checklist" />
- Confirm that column headers align with the original PDF and occupy a single row.
- Verify numeric values are stored as numbers, not text, by checking right-alignment and formula compatibility.
- Check that dates are recognized as date values and display in the correct locale format.
- Ensure imported cells are editable and not locked or embedded as images.
- Scan for duplicate rows or blank lines introduced during extraction.
- Validate that merged cells from the PDF have been split into individual cells with correct data.
Automate recurring PDF extraction tasks by saving Power Query templates or using macros to repeat the import and cleanup steps. For example, if you regularly import monthly reports with the same structure, save the query and refresh it each month instead of rebuilding the import from scratch.
Open the PDF in a compatible tool, identify whether it is text-based or scanned, and select the appropriate import method based on your Excel version and the document’s layout.
FAQ
Can I import data from PDF to Excel?
Yes, you can import data from PDF to Excel using the built-in Get Data from PDF feature in Excel 2019 and Microsoft 365 for text-based PDFs. Scanned PDFs require OCR tools before importing, while older Excel versions may need Adobe Acrobat exports, CSV files, or manual copy-paste methods.
How do I import a PDF into Excel without losing formatting?
To preserve formatting, use Excel Power Query’s Get Data from PDF option with structured text-based PDFs and select the correct tables in the Navigator pane. Clear table borders and consistent columns improve results, while merged cells or complex layouts may require cleanup after import.
Why is Get Data from PDF not showing in Excel?
Get Data from PDF may not appear because your Excel version does not include the PDF connector. Excel 2019 and Microsoft 365 support this feature, while older versions require alternatives such as Adobe Acrobat exports, CSV files, or other conversion methods.
What are the best free tools to convert PDF to Excel?
Free PDF-to-Excel options include online converters and OCR-based tools for cases where Excel cannot directly extract the data. Online converters are convenient for one-off tasks, but accuracy varies and they may have file size limits or formatting differences compared with desktop tools.