How To Export A Word Table To Excel

If you accumulate lots of data in Word tables, or you use lots of Word tables to list data records, you may at some point want to export all this data into Excel. This article describes several ways to do this depending on how much data you want to export.

If you try to open a Word file in Excel and you'll be faced with an error message such as "file format is not valid" as Excel only recognises data formats such as txt or csv (comma separated values) files which allow storing data without any extra formatting. So if you want Excel to recognise your Word file you need to change the file type to a text file when saving. However if you save a Word file containing a table as a text file, you will loose the table formatting, and Excel will only import the table data as one huge list with each data item on a separate line. Before looking at ways to save a Word table in a format Excel will recognise, we'll first look at copy and paste.

A surprisingly effective way to export a table from Word to Excel is to copy and paste the table from Word to Excel. So in Word you first select the table, choose copy, switch to Excel and then choose paste. Excel will create a correctly structured table and applying border formatting round all the data cells. If you don't want the border formatting in Excel you can choose paste special and paste the data only. Copy and paste works well if you only want to convert one table. However if the Word data is split over two or more tables then the following two methods may also be useful. These are converting table to text, and mail merging.

Firstly, you can convert your table to columns of text within Word using the table to text feature, and then save the file as a text file. When you do this Word will add tab characters between the data so you'll still retain the table like structure but without the Word table formatting. If you then save the Word file as a text file the data will remain in columns, making it easy for Excel to import. You can use table to text to convert more than one table of data.

To use text to table to create the conversion in Word, first select the table, then choose table, convert, table to text. In the convert table to text panel choose which character to use to separate the columns, for example tab, then click OK to finish. If the table contains many columns then a space character may be a better choice in order to keep the total table width under control. The data will now show in tabular layout but without the table formatting. Then save the Word file as a text file.

In Excel, choose open file, and choose all file types. Then navigate to the saved text file and double click it to load it. Excel's recognises the txt file format and the import file wizard will appear. In the import wizard choose the correct data separator character such as tab or space you chose earlier, and you'll find that Excel then correctly previews the columns of data. If the preview does not correctly show the data columns, try going back a step in the wizard and change the separator character, for example from a tab to a space. Once the data previews correctly you can complete the wizard and the data will be imported in Excel in the original columns layout.

Secondly, you can create columns of data from the Word table by using Word's mail merge feature. Using this you can create a new document containing columns of data from the original Word table. You can also use mail merge to convert more than one table of data into a final list but you'll need to run mail merge again for each table, changing the data source each time.

To use mail merge to create the conversion in Word, suppose you have a Word table saved as table1. To use mail merge to convert your data, open a new Word file then start "Mail Merge". Choose the data source as list1 and the new document as the current newly opened document. Start the mail merge process but rather than choosing the traditional letter or envelope option, choose to merge to a directory, which will create a list of data in columns corresponding to the original table layout. Choose the "Insert Merge Field" buttons to add the table headings separated by spaces across the top of your merge document, and press the enter key at the end of the line to place the cursor in a new line. It's important to press enter key at the end of the headings in order that each line of data starts on a new line.

Then click the "Merge to New Document" button, and Word will create a columnised list from the original table, which you can then save as a text file before importing into Excel.

So you can export a table from Word to Excel using copy and paste, text to columns or mail merge to a directory. All these methods retain the data structure. Hopefully this article will give you a brief insight into how to import Word tables into Excel. To find out much more about these and many other Word and Excel features, you might consider attending a training course and boost your skills further.