Convert PDF to Excel

PDF files contain useful information such as bank statements, invoices, product lists, and business reports. However, working with this information in Excel can be difficult if the data is available only in PDF format. Entering everything manually takes time and may lead to typing errors. It converts a PDF to Excel and makes the data easier to edit, organize, sort, filter, and calculate. It provides a built-in PDF import feature in supported versions, while Microsoft Word and copy-paste methods are useful alternatives.

This article explains how to convert PDF to Excel using different methods, clean the converted data, and handle scanned PDF files.

Method 1: Convert PDF to Excel Using the Built-in Feature

Excel's From PDF feature is a convenient way to import tables directly from a PDF file. It detects tables and allows you to preview the data before loading it into a worksheet.

Availability: The From PDF connector is available in supported Microsoft 365 versions of Excel for Windows. If the option is missing, check your Excel version and available updates. Availability on Mac depends on the version and build.

Steps to Import a PDF into Excel

1. Open Microsoft Excel and create a blank workbook.
2. Select the Data tab from the ribbon.
3. Click Get Data > From File > From PDF.
4. Browse your computer, select the PDF file, and click Import.
5. Wait for the Navigator window to display the tables and pages detected in the PDF.
6. Select a table, such as Table001 (Page 1), to preview its contents.
7. Click Load to insert the data into Excel, or select Transform Data to clean the data before importing it.

Example

Suppose a PDF contains the following sales table.

After importing the PDF, Excel can display the information in a structured table with separate columns for Product, Quantity, and Price.

Output

Explanation

Excel uses Power Query to detect and import tables from the PDF. The Navigator window lets you preview the detected tables before loading them. If the PDF contains several pages, you may see multiple tables or page entries.

Power Query can also recognize data types, such as numbers, dates, and text. However, you should verify the imported values because the detected structure and data types may not always be correct.

If the source PDF is updated later, you can refresh the imported data using the available refresh options, provided the source file remains accessible at the expected location.

Clean the Data Before Importing

If the imported table contains extra headings, blank rows, or incorrect column names, click Transform Data instead of Load. Power Query provides options to clean the data before sending it to Excel.

After cleaning the table, select Close & Load to insert it into the worksheet.

You can also learn more about Excel features and formulas in Excel Interview Questions.

Method 2: Convert PDF to Excel Using Microsoft Word

Microsoft Word is another useful option when Excel's PDF import feature is unavailable. Word can open many text-based PDF files and convert their contents into an editable document.

This method works well for simple tables, although complex layouts may not retain their original formatting.

Steps to Convert PDF Using Word

1. Open Microsoft Word.
2. Select File > Open and choose the PDF file.
3. Word displays a message explaining that it will convert the PDF into an editable document. Click OK to continue.
4. Wait for the document to open and check whether the table structure has been preserved.
5. Select the table using the table selection control, usually found near its top-left corner.
6. Press Ctrl + C to copy the table.
7. Open Excel, select cell A1, and press Ctrl + V to paste the data.

Explanation

Word converts the PDF content into an editable document, allowing you to copy the table into Excel. The result depends on the PDF layout. Simple tables usually convert more cleanly than documents with multiple columns, merged cells, or complicated formatting.

After pasting, check the column headings, numbers, and row alignment. If the data appears in the wrong columns, you may need to clean it using Excel's data tools.

Method 3: Convert PDF to Excel Using Copy and Paste

Copy and paste is a simple method for transferring a small table from a PDF into Excel. It is useful when the PDF contains selectable text and you do not need to import a large amount of data.

Steps to Copy Data from PDF to Excel

1. Open the PDF in a PDF reader or browser.
2. Select the table text using your mouse.
3. Press Ctrl + C to copy the selected text.
4. Open Excel and select cell A1.
5. Press Ctrl + V to paste the data.
6. If the data appears in a single column, use Data > Text to Columns to separate it into multiple columns.

Example

Suppose the copied text looks like this after pasting into cell A1:

The information is present, but Excel has not separated the product names, quantities, and prices into different columns.

Steps to Separate the Data

1. Select the cells containing the pasted text.
2. Open the Data tab and click Text to Columns.
3. Select Delimited and click Next.
4. Choose the delimiter that separates the values. For this example, select Space.
5. Click Finish to split the text into separate columns.

Output

Explanation

Text to Columns separates text according to a selected delimiter, such as a comma, tab, or space. In this example, spaces separate the product names, quantities, and prices.

However, using Space as the delimiter may cause problems when a value contains spaces. For example, the product name "Mobile Phone" could be split into two columns. In such cases, use a more suitable delimiter or choose the PDF import method to preserve the table structure.

How to Fix Common Problems After Converting PDF to Excel

Sometimes, the converted data does not work correctly because numbers are stored as text, extra spaces remain in the cells, or the columns are not arranged properly. Excel provides several functions and tools to fix these issues.

1. Convert Numbers Stored as Text

When a number is imported from a PDF, Excel may treat it as text rather than a numeric value. Such values may not work correctly in calculations or the SUM() function.

Suppose cell B2 contains the text value 1,250. You can use the following formula to remove the comma and convert the result into a number:

=VALUE(SUBSTITUTE(B2,",",""))

Output:

1250

Explanation: 

The SUBSTITUTE() function removes the comma from the text, and VALUE() converts the remaining text into a numeric value. The formula assumes that the comma is a thousands separator. Check your regional number format before using it on other data.

2. Remove Extra Spaces

Data copied from a PDF may contain unnecessary spaces before or after words. These spaces can cause problems when sorting, matching, or comparing values. We use the following formula if cell A2 contains text with extra spaces:

=TRIM(A2)

The TRIM() function removes leading and trailing spaces and reduces repeated spaces between words to a single space in ordinary text.

You can also explore SQL String Functions to understand how text-cleaning functions are used in databases.

3. Fix Data Split into the Wrong Columns

Sometimes, a PDF table does not retain its original structure after copying or importing. A product name may be separated from its quantity, or several values may appear in a single cell. We can use the following Excel features to correct the layout:

Choose the method that matches the problem. Always review the output to ensure that each value is in the correct column.

How to Convert a Scanned PDF to Excel

A scanned PDF contains images of pages rather than directly selectable text. Excel cannot reliably extract the table from such a file through ordinary text copying.

To convert a scanned PDF, use Optical Character Recognition (OCR). OCR is a technology that recognizes letters, words, and numbers in an image and converts them into machine-readable text.

Steps for Scanned PDFs

1. Open the scanned PDF in an OCR-enabled application or tool.
2. Run the text recognition process to extract the text and table data.
3. Export the recognized information to Excel or copy it into a worksheet.
4. Review the extracted values and correct any recognition errors.
5. Check important numbers, dates, account details, and totals against the original PDF.

Some supported versions of Excel also provide a Data from Picture feature, which can recognize tabular data from an image. Availability depends on the Excel version and platform.

OCR can confuse similar-looking characters, such as 0 and O or 1 and I. Therefore, verify the converted information carefully, especially when working with financial records.

Privacy tip: Avoid uploading confidential documents, such as bank statements, invoices containing sensitive information, or salary records, to unknown online conversion websites. Prefer trusted applications and offline methods when possible.

Important Points About Converting PDF to Excel

• Check whether the PDF contains selectable text or scanned images before choosing a conversion method.
• Excel's From PDF feature is a convenient option for importing tables in supported versions of Microsoft 365 for Windows.
• Microsoft Word and copy-paste are useful alternatives for simple PDF tables.
• Use Text to Columns, Power Query, and Excel functions to clean the imported data.
• Use OCR to extract information from scanned PDFs, then verify the results.
• Check numbers, dates, column alignment, and totals before using the converted data for calculations or reports.

Converting PDF to Excel makes information easier to organize and analyze. The best method depends on the PDF format, the complexity of its tables, and the Excel features available on your computer.