How to Transfer Data from Word to Excel Automatically

Excel can’t watch a Word document and pull in changes on its own, but you can get close. For a single table, copy it from Word and paste it into Excel, which takes seconds. To update Excel automatically whenever the Word data changes, save the Word data as a text file and import it with Data > From Text/CSV. After that, one click on Refresh All (or opening the workbook) pulls in the latest version. For Word forms, Word can save just the form answers as comma-separated text, ready for Excel. Here’s each method, from simplest to most automated.

Method 1: Copy and paste a table (fastest)

1. In Word, click inside the table, then click the move handle (the four-way arrow at the top left) to select the whole table.
2. Press Ctrl + C.
3. In Excel, click the top-left cell where the table should go and press Ctrl + V.
4. Use the Paste Options button to choose Keep Source Formatting or Match Destination Formatting.

Each Word cell lands in its own Excel cell. If the text isn’t in a table but uses tabs between values, paste it the same way. Excel splits it into columns at each tab. For other layouts, see how to convert a Word document to Excel.

Method 2: A refreshable import (automatic updates)

Use this when the same Word document is updated regularly and Excel should always show the latest data.

In Word: save the data as text

1. Put the data in one table, or separate values with tabs or commas.
2. Go to File > Save As, choose Plain Text (*.txt), and save it in a folder that won’t move, for example in OneDrive.
3. When Word shows the File Conversion box, keep the default encoding and click OK. Table cells are saved separated by tabs.

In Excel: import and set it to refresh

1. Go to Data > From Text/CSV and choose the .txt file.
2. Check the preview. Set Delimiter to Tab (or Comma), then click Load.
3. Go to Data > Queries & Connections, right-click the query and choose Properties.
4. Tick Refresh data when opening the file. Optionally, tick Refresh every and set a number of minutes.

Now, whenever someone updates the Word document and saves the text version again, Excel picks up the changes when the workbook opens or when you click Data > Refresh All. The import is built with Power Query, so you can also clean it up (remove blank rows, set data types) in the query editor. See how to find and enable Power Query in Excel.

Method 3: Word forms to Excel

If you collect information with a Word form, such as surveys or applications, Word can save just the answers:

1. Open a completed form in Word.
2. Go to File > Options > Advanced, and under Preserve fidelity when sharing this document tick Save form data as delimited text file.
3. Save the document. Word saves a .txt file containing only the field values, separated by commas.
4. Open or import the .txt file in Excel. Repeat for each form, and add the rows to one sheet.

This option is designed for older-style form fields. Forms built with newer content controls may not export this way. If yours don’t, use Method 4 or collect responses with Microsoft Forms, which exports straight to Excel.

Method 4: Many documents (Power Query or a macro)

• Power Query from a folder: If each Word document is saved as a text file in the same folder, use Data > Get Data > From File > From Folder, then Combine & Transform. Every file in the folder becomes rows in one table, and new files are included on the next refresh.
• VBA macro: A macro in Excel can open each Word document, read its tables or content controls, and write the values to rows. This needs some programming, but it’s the most automatic option for large batches. Test it on copies of your files first.

Going the other way

To show Excel data in Word that updates automatically, use Paste Special > Paste link. See how to link Excel to Word and how to copy a graph from Excel to Word. To place a whole Word file inside a workbook, see how to embed a Word document in Excel.

Troubleshooting

Everything lands in one column

The values aren’t separated by tabs. Use Data > Text to Columns in Excel and pick the right separator.

Numbers or dates come in as text

In the import preview or Power Query, set the column type to Whole Number, Decimal or Date.

The refresh doesn’t pick up changes

Make sure the text file was saved again after the Word document changed, and that it’s still in the same location.

Summary

1. For a quick copy: select the table in Word, then paste it into Excel.
2. For automatic updates: save the Word data as text, import it with From Text/CSV, and turn on refresh.
3. For forms: save form data as delimited text.
4. For many files: use Power Query From Folder or a macro.

Matt Jacobs

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.