How to move a Word table to Excel without the mess

Copying a table from Word and pasting it into Excel looks like it should just work. For a small, simple table, it often does. For anything bigger, you usually spend the next half hour fixing it.

What goes wrong with copy and paste

• Cells with several lines become several rows. A cell that says “Main Street 4” on one line and “Berlin” on the next turns into two rows, and every column below it shifts.
• Numbers stay text. Values like 1,250.00 or $3,400 often arrive as text, so SUM returns 0 and sorting puts 1,000 before 200.
• Leading zeros disappear. An item code 007 becomes the number 7. Long ID numbers turn into 1.23E+15.
• Dates get changed. 03/04 can become 3 April or 4 March, depending on your computer’s settings.
• Merged cells and headings get lost, and every table in the document has to be copied one at a time.

How to convert Word tables to Excel

1. Open Word to Excel and choose your .docx file. Every table in the document is found and previewed, with the text just above it so you can tell them apart.
2. Untick any tables you do not need.
3. Choose a layout: Sheet per table puts each table on its own sheet, named after the heading or caption above it. One sheet stacks them all with a blank row between.
4. Leave Turn numbers into real numbers on, and check how numbers are written in your document: 1,234.50 or 1.234,50.
5. Click Convert to download an .xlsx file you can open in Excel, Google Sheets or LibreOffice.

What you get

• Real numbers. 1,234.50, 12%, (250) and $5,000 become numbers you can add up, with a matching format, so $5,000 still shows a dollar sign.
• Codes kept as text. Values like 007, part numbers and long ID numbers are left exactly as they were, so nothing is lost or rounded.
• Multi-line cells stay in one cell, with their line breaks.
• Merged cells stay merged, and header rows stay bold and are frozen at the top while you scroll.

Two things it does not do

• Dates stay as text. A Word document does not say which date format was meant, so the tool does not guess. Use Excel’s Data → Text to Columns on the column to turn them into real dates in the format you know is right.
• Text outside tables is not copied. A spreadsheet needs rows and columns, so only tables are converted.

Older .doc files need to be saved as .docx in Word first.

When copy and paste is fine

For a small table with plain text and no merged cells, copy and paste works well. To keep codes like 007, select the target column in Excel first and set its format to Text before you paste.

Going the other way

Need to put an Excel table into a Word report? Excel to Word turns each sheet into a formatted Word table and keeps your number formats.

Both tools run in your browser, so documents with salaries, prices or customer data are never uploaded.