Software

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.

17 comments
helpmeforms
helpmeforms

the import from my form according the directions has to be converted back to text from table and then saved as text file and then imported to excel. the problem is that that the data is pulling the headers of my chart down the spreadsheet instead of accross. I'm sooooo frustrated.

masungit
masungit

can this work the other way around? WORD > EXCEL?

chiodi.frank
chiodi.frank

You need to better explain what is done within WORD, before switching over to Excel. You also need to better separate the differing functions/steps that are required to be actioned in Excel 2003/older, as opposed to Excel 2007.

Mycah Mason
Mycah Mason

My initial reaction is the same as everyone elses; copy paste does the same thing and is much simpler. However, I can't help but wonder if there is some specific thing we are all missing here. Is this something that provides a benefit over copy and paste in some specific case? Can you give an example scenario where this would be prefered over copy and paste as well as an explanation why?

heavener
heavener

I agree with everyone about the value of copying and pasting -- I do it all the time. But down near the bottom, the author just casually throws in using Excel's Data > Refresh function. IMHO, that's big. Say you bring in a table and do some formulas, If you copy-and-paste again when someone sends you the update, per the author's example, the links in the formulas might disconnect, especially with a shared workbook (don't flame me, I'm only posing a hypothetical).

greggrivera
greggrivera

how about up-dating a an excel table in a word doc from a main excel sheet automatically when the word doc opens....

trina
trina

kinda counter intuitive when c&p'ing has worked just fine

PCF
PCF

There's no more to say than...Ditto to all previous posts. :)

np.akhil
np.akhil

ya Too many steps, but for bulk data usefull

dave
dave

Wow, what a complicated way to copy and paste special ! Nice try though, better luck next time

pareshkamat
pareshkamat

TO MANY STEPS. BEST IS TO COPY AND PASTE

mattohare
mattohare

That's a cool set of steps, but it seems to be a few more than I go through to move data. I'll select and copy the table in Word, paste it into Excel, Then clear all the formats by applying the Normal cell style to it. The more steps, the easier it is to muck it up, I've found.

dhays
dhays

I go both ways, copying from Excel to Word or vice versa. For the amount of data I have, copy and paste is the best way for me, if I were to do pages of data, it might be easier to do an export/import. I was using export from an Outlook Task set to Excel, but I have given up on that using copy/paste to stay up to date.

Tante Waileka
Tante Waileka

Or I'll just drag and drop the table, that's worked since 1995 at least.

goran.igaly
goran.igaly

You even don't have to apply formats if you use "Paste Special" when pasting your data. So you can do the whole task by simply Copy + Paste Special

balbert314
balbert314

I don't think the point of this exercise was necessarily just to import a table from word to excel, that in and of itself is rather simple, but rather the author is trying in a clever way to teach people about the import text wizard and how it can import and parse a delimited text file of any size.

Editor's Picks