Add data to an Excel worksheet from a Word table

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.

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).

Figure B

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

Figure C

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

Figure D

Now let’s say you receive an update to the Word document (Figure E).

Figure E

Update the data in the Import_from_Word file by following these steps:

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).

Figure F

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

The new data has now been added to your worksheet, as shown in Figure G.

Figure G

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.