If you have a workbook containing data that originates in Word, you can keep the data current with the help of Excel’s Import External Data wizard. Here’s a look at how to put this handy feature to work.

Add data to an Excel worksheet from a Word table

If you need to analyze data that exists only in a Word table, you can use Excel’s Import External Data feature to update your worksheet whenever the data within the Word table changes. For example, at the end of the month you receive an e-mail with an updated listing of all YTD invoices in a Word document (Figure A).


Figure A

You would like to use Excel’s tools for analyzing the data, but you don’t want to spend time entering the new data each month. Follow these steps to have Excel update your worksheet for you:

1. Open the copy of the Word document you received as an e-mail attachment.
2. Select the table.
3. Go to Insert | Table and click on Convert Table To Text. In Excel 2007, click the Layout Tab under Table Tools and click Convert To Text in the Data group.
4. Click OK.
5. Go to File | Save As and save a plain text copy of the file as Import_table.txt.
6. Open a blank workbook in Excel.
7. Go to Data | Import External Data and click Import Data. In Excel 2007, go to Data | Get External Data | From Text (Figure B).

1. Navigate to and select Import_table.txt.
2. Click the Import button.
3. Click Next button twice (Figure C).

1. Click Finish.
2. Click OK.
3. Save the workbook (Figure D) as Import_from_Word.

1. Open the document containing the updated table.
2. Go to Insert | Table and click on Convert Table To Text.
3. Go to File | Save As and save a plain text copy of the file as Import_table2.txt.
4. Close Word.
5. Open the Import_from_Word workbook. In Excel 2007, click the Options button and then select the Enable button and click OK.
6. Go to Data | Refresh Data. In Excel 2007, click the Data tab and then click Refresh (Figure F).

1. Navigate to and select the Import2_table.txt document.
2. Click the Import button.

Page last edited: May 13, 2009