Word: Import an Excel tableJune 5, 2009
A client wanted to know the best method of getting Excel tabular data into a Word table, so I spent an hour or so testing the various methods available from the normal Copy, Save As and Print options in Excel and the Insert and Paste options in Word. I did not test any third-party specialist programs.
I won’t go through every method I tried as some were so bad they’re not worth talking about (how does a single paragraph with all the cell contents in it sound?? Or one line for every piece of cell data with NO formatting?).
The testing I did was on a reasonably simple table in a single worksheet, not on something with linked worksheets, formulas etc. so the method I recommend here may not be the best one if you have a complex Excel spreadsheet.
I also didn’t test capturing the Excel data as an image as the authors needed to be able to change it once it was in Word.
Bottom line: The methods recommended by Microsoft in their Help topic “Copy Excel data and charts to Word or PowerPoint” are the best, particularly the option to paste and format to match the destination table style.
That said, you may still have a little bit of work to do once the table is in Word, but it shouldn’t be too onerous.
Here are the steps I used to get the data from Excel into Word:
- Copy the Excel data (just the cells you want to include in the table).
- In Word, paste the copied data (Ctrl+V).
- When you see the little ‘paste options’ icon (bottom right of the pasted table) for what to do with the copied data etc., click it and select Match Destination Table Style (Word 2003 and 2007) or Keep Source Formatting (Word 2010).
Once the table is in Word, you’ll may still have to do some of these:
- Apply any special formatting to the header and the rows (such as borders, shading, text styles)
- Set the table header row to repeat
- Autofit the table to the window
- Adjust the column widths.
If the author has added ‘pseudo’ bullets and line breaks to the Excel data, you may have a bit more to do.
You can’t add paragraph marks to an Excel cell — you use a workaround [Alt+Enter] to insert a soft line break. If the author has used these, you may need to replace the soft line breaks with real paragraph marks:
- In Word, select the table you just inserted.
- Open the Find/Replace window (Ctrl+H).
- In the Find what field, type ^l (that’s a lower case L for Larry).
- In the Replace with field, type ^p (the p must be lower case).
- Click Replace All.
This looks for the soft line breaks (^l) and replaces them with paragraph marks (^p). The caret (^) is important, as is lower case for the letters in the find/replace strings. These steps will clean up the soft line breaks and make them proper paragraphs.
Now for the ‘pseudo’ bullets…
You can’t add bullets in Excel — instead you fudge around it by inserting a symbol and choosing something that looks like a bullet, typically a small filled-in disc symbol. Unfortunately, there’s no real option for these except to find them with your eyes and manually delete them. Selecting the pseudo bullet symbol (e.g. a filled disc) and copying it into Word’s find/replace changes it into a lower case O (for orange), so that’s not a solution as you don’t want to replace the letter ‘o’ everywhere it appears in the table! The only time you might be able to use find/replace would be if the author had consistently added two spaces after the ‘bullet’ in Excel, then you could look for “o<space><space>” and replace it with nothing, or if the table only had numbers not letters.
[Updated July 2013 with details for Word 2007 and 2010]