How to convert a Word table to Excel

Struggling with complicated Word tables? While Microsoft Word is great for documents, it is suitable for managing data. In contrast, Excel is a powerhouse tool designed for data management. So, converting Word to Excel unlocks advanced features like formulas, charts, and pivot tables for deeper data analysis and collaboration.

Now, the DOCX to XLSX conversion process is straightforward for digital documents. You can copy-paste the table from the doc to the spreadsheet manually, upload the document to online tools, or use dedicated converters. But for scanned documents, you need OCR (Optical Character Recognition) technology. Scanned files are images and not editable. OCR extracts the information into a machine-readable format that can be easily exported to Excel.

In this article, we’ll explore different ways to convert Word tables to Excel, from simple copy-pasting techniques and Excel’s built-in features to powerful AI-OCR tools.

Let’s explore some of the best ways to get your Word table to an Excel spreadsheet.

Method	Best for	Limitations
AI-powered OCR tools	– Complex or numerous documents needing automation – Extracting specific data fields – Integration with other tools and platforms	– May require some initial setup and training of the AI model for specific document types and layouts
Copy and paste	– Small tables – Simple formatting	– Can lead to formatting issues with complex tables or merged cells
Save as text and import	– Preserving basic text formats and delimiters	– Requires manual data cleaning for documents with a lot of non-tabular content
PDF and Get Data	– Importing multiple tables with original formatting	– May not accurately recognize complex layouts or merged cells
Online conversion tools	– Quick conversions without software installation	– May not perfectly handle complex tables or preserve all formatting – Privacy concerns for sensitive data
VBA Scripts and Macros	– Efficiently converting multiple tables	– Requires coding knowledge – May not handle complex table structures or formatting perfectly
Python Libraries	– Handling large volumes of documents or complex table structures	– Requires programming knowledge and setup – May need updates for changes in document structure or format

1. Manual copy-paste

Manual copy-paste is the most straightforward method to transfer your tables to a spreadsheet. While it may be time-consuming for large docs, this method works for small one-pagers. Microsoft Excel offers a few paste options to give you some control over what gets pasted.

Follow these steps to copy a table from Word to Excel:

1. Open the Word doc and locate the table you want to copy.
2. Click at the beginning of the table, hold down your mouse button, and drag to select the entire table.
3. Right-click inside the area and choose ‘Copy’.
4. Open a new Excel worksheet and place your cursor in the cell where you want to paste.
5. Right-click and choose from the available paste options:

• Match Destination Formatting: This option pastes the content of the Word table and matches the formatting of the destination Excel worksheet.
• Keep Source Formatting: This option pastes the content of the Word table and retains the original formatting from the Word document.

6. Alternatively, right-click and choose ‘Paste Special’ from the context menu for more options.
   1. In the Paste Special, you can paste the table as a Microsoft Word Document Object, Picture, HTML, Unicode Text, Text, or Hyperlink.
   2. Select the ‘Paste Link ‘ if you want to maintain a link to the original Word table. This ensures that any changes to the original Word table will be reflected in the Excel worksheet.
7. Click ‘OK’ to paste the table into Excel.
8. Adjust the column widths and row heights as needed.

To copy a table from Word to Excel while preserving the formatting, use the ‘Keep Source Formatting’ option. Additionally, the ‘Paste Special’ option allows you to paste the table as an object, picture, or text and create a link to the original doc.

If you’re using Microsoft Office 365 or 2019, there’s another convenient method to copy Word tables into Excel:

1. Open the Word doc and select the entire table you want to convert.
2. Click the ‘Layout’ tab under the ‘Table Tools’ section.
3. Click on ‘Convert to Text’ in the ‘Data’ group.
4. Choose the desired separator (e.g., commas, tabs, or others) and click ‘OK’.
5. Select the converted text and copy it.
6. Open a new Excel worksheet and select the row or cell where you want to paste it.
7. Go to the Paste and click ‘Use Text Import Wizard’.
8. The Text Import Wizard will open — select ‘Delimited‘ and click ‘Next’.
9. Go with the delimiter used in your text file (comma, tab, space, etc.) and click ‘Next’.
10. Specify the data format for each column or use the default ‘General’ format.
11. Click ‘Finish’, and the copied data will appear in the Excel worksheet, maintaining the original tabular structure.

Both these approaches might not work well when dealing with scanned documents. Scanned documents are essentially image files, making it difficult for Excel to accurately recognize and extract the tabular data. You’ll have to manually it into Excel cells, which nobody wants to do.

The copy-paste method is inefficient for bulk processing. Imagine individually copying and pasting each table in a longish doc — it’s simply not practical.

2. Save the Word document as a .txt file

Saving the Word document containing the table as a .txt file and importing it into Excel is an easy and convenient way to get your data to a spreadsheet.

It allows you to choose the column data format and delimiters, so basic formatting like number, date, and text formats can be retained.

Here’s how to do it:

1. Open the Word doc and locate the table you want to convert. Select the whole table.
2. Click on the layout tab, then click ‘Convert to Text’. In the dialog box that pops up, select the delimiter you want and click ‘OK’.
3. Save the document as a .txt file by clicking on ‘File’, then ‘Save As’, and choose ‘Plain Text (.txt)’ from the drop-down menu.
4. Open Excel, click ‘File’, then ‘Open’, and locate the .txt file you saved. An import wizard will pop up.
5. Follow the Text Import Wizard’s instructions: select ‘Delimited’, click ‘Next’, choose your delimiter, and click ‘Next’ again. You can also set the data format for each column.
6. Click ‘Finish’ to complete the import process. You should now see your table in Excel.

As we discussed in the previous method, the delimiter helps divide the data into separate cells, rows, and columns. 

While this method is straightforward, you’ll have to spend considerable time manually cleaning the data in documents with a lot of text and images besides the table. Also, lengthy tables, merged cells, and complex layouts may not be accurately represented when converted into the .txt format.

3. Save the document as a PDF and use Excel’s ‘Get Data’ feature

Excel’s built-in function ‘Get Data’ allows users to import data from various sources, including PDF files. So, save your doc as a PDF and use the same function to import it to the spreadsheet. 

Here’s how:

1. Open your Word document and go to ‘File’ > ‘Save As’ > ‘PDF’.
2. Open Excel, navigate to the ‘Data’ tab, and click ‘Get Data’.
3. Choose ‘From File’ > ‘From PDF’ in the drop-down menu.
4. Locate the PDF file you just saved, then click ‘Import’.
5. Excel will then display a Navigator window — select the tables you want to import and click ‘Load’.

This method is especially useful when you have multiple tables in your doc, as the ‘Get Data’ function can identify and import all of them at once. It also keeps the original formatting intact.

However, this method might not work well with complex layouts or merged cells, as the function may not accurately recognize such formatting.

Power Query is a powerful data transformation and preparation tool built into Excel. It allows you to connect, combine, and refine data from various sources, including PDFs, XML, CSV, TXT, and JSON. With Power Query, you can easily extract tables from PDFs and load them into Excel for further analysis, cleaning, and transformation.

Note: You can also extract tables from text files using the ‘Get Data’ feature.

Manual methods and simple tools might not be enough when you want to simultaneously process complex tables or hundreds of documents.

Along with table extraction, you may need tools to identify, classify, and extract data at scale. This is where AI-powered automated tools come in.

If you’re grappling with complex document automation, Nanonets is what you need. It seamlessly extracts data from even the most complicated tables.

Combining OCR and AI, Nanonets can effectively read, understand, and extract information from your documents. So, be it a bank statement, PO, customer order, or invoice — with different layouts, languages, currencies, line items, and column names — Nanonets can handle it all.

Here is how you can use Nanonets:

1. Upload your Word file(s) to the platform
2. Select the ‘Table OCR’ model from the main dashboard
3. Upload your file or connect your data source
4. The AI model will automatically extract the data from the tables in the file(s)
5. Review and validate the extracted data for accuracy
6. Download the data in your preferred format, such as XLSX or CSV
7. Export the extracted data to your desired software or tool

What’s more, every time you make a manual correction, the AI learns from that and improves its performance, producing more accurate results over time.

If you’re a finance professional, accountant, or data analyst, getting the data you want in Excel is only half the battle. Often, you must process the data further, analyze it, share it with colleagues, collaborate, seek approval, or export it to other software tools. Nanonets enables you to extract the data and automate these subsequent tasks seamlessly.

Sometimes, you may only need to extract specific data from a doc. For example, you might only need to extract customer names and email addresses from a sales report. Nanonets allows you to extract the required fields, saving time and effort.

In addition to table extraction, Nanonets offers a range of features, such as document classification, key-value pair extraction, and more. It supports various file formats, including Word, PDF, JPEG, and PNG. You can also train custom AI models for your specific document types and layouts.

Nanonets offers powerful data import and export integrations, meaning you can conveniently move data between your preferred tools and platforms. It integrates with popular tools such as:

It also has a Zapier integration, enabling you to connect with thousands of other apps and services, automating many of the tasks in your workflow.

Nanonets’ powerful AI engine and seamless integrations make it a comprehensive tool for document processing, especially when dealing with complex or large quantities of data.

With automated validation, built-in task assignment, and multi-stage approval workflows, you can ensure your data is accurately captured, processed, and approved. You can reduce manual errors, save time, maintain transparency, and improve compliance.

When it comes to data security and privacy policy, Nanonets stands out with its comprehensive approach. The platform utilizes advanced encryption to safeguard your data throughout the upload, processing, and storage phases. It’s worth noting that Nanonets is fully compliant with GDPR and CCPA regulations, ensuring the utmost security and confidentiality of your sensitive information.

Online conversion tools are a great option if you’re looking for a speedy and hassle-free way to transfer tables from Word to Excel. They can automatically convert the document into a spreadsheet in seconds.

There are several converters available online, such as:

These tools are straightforward to use. Generally, you need to:

1. Go to the website of your chosen online converter
2. Upload your Word document containing the table you want to convert
3. Select the output format as CSV, XLSX, or XLS
4. Click on the ‘Convert’ or ‘Start conversion’ button
5. Download the converted Excel file once the process is complete

These converter tools are convenient and easy to use, making them ideal for quick, one-off conversions. However, you need to watch out for a few things when using these tools. The process might take longer, especially for large files. They may not always perfectly preserve the formatting of complex tables and might have limitations on the file size you can upload.

Additionally, the converter interface may be full of annoying pop-up ads. If you click on them, you might download malware or viruses. Also, be careful when uploading sensitive data to third-party websites because it could risk your file security and privacy. You’d be better off checking their privacy policy before using such tools.

6. Automate data export with VBA scripts

For users with programming experience, VBA (Visual Basic for Applications) scripts and macros can be used to automate the export of Word tables to Excel. By creating a script or macro in Word or Excel, you can loop through all the tables in a document and extract them into a Microsoft Excel workbook.

Here’s a simple example of a VBA script that you can use:

Macros, which are recorded sequences of actions, can also automate the process of copying and pasting tables from Docs to Worksheets. However, macros are less flexible than VBA scripts and may not handle complex table structures or formatting as effectively.

While VBA scripts and macros can efficiently convert multiple tables, they require some coding knowledge, and complex layouts or formatting may not always transfer perfectly to Excel. Furthermore, there may be compatibility issues and a steeper learning curve compared to other methods.

7. Automate the conversion process using Python libraries

For users comfortable with Python programming, libraries like Python docx and Pandas can be used to automate the process of extracting tables from Word documents and adding them to Excel. This method is handy when dealing with documents containing multiple tables that need to be processed in bulk.

With some expertise, you can extend it to handle more complex scenarios, such as preserving formatting, handling merged rows and columns, or processing specific tables based on certain criteria.

Using Python libraries offers a high degree of flexibility and control over the conversion process. However, it does require programming knowledge and may involve additional steps to set up the necessary environment and dependencies. You must dedicate time to learning the needed libraries and developing the script. Additionally, any changes to the document structure or format might require corresponding updates to the script.

While Python libraries are great when dealing with large volumes of documents or complex table structures, they do require a lot of trial and error. You’ll have to spend a considerable amount of time and effort creating a script that can handle all edge cases.

Wrapping up

Whether you’re a legal professional dealing with contracts and agreements, an accountant managing multiple invoices, or a doctoral student compiling research data, transferring tables from Word to Excel can be a frustrating step in the workflow. However, this process can be significantly simplified with the correct methods and tools.

Whether you choose to use AI-powered OCR tools, online converters, or manual methods like copying and pasting, the goal is to streamline your workflow and make the most of your time and resources. Hopefully, this quick guide has been a good starting point.

---

FAQs