h1

Word: Import an Excel table

June 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:

  1. Copy the Excel data (just the cells you want to include in the table).
  2. In Word, paste the copied data (Ctrl+V).
  3. 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:

  1. In Word, select the table you just inserted.
  2. Open the Find/Replace window (Ctrl+H).
  3. In the Find what field, type ^l (that’s a lower case L for Larry).
  4. In the Replace with field, type ^p (the p must be lower case).
  5. 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]

6 comments

  1. Thanks for your tutorial.


  2. [...] Copy and paste the Excel table directly into Word — see http://cybertext.wordpress.com/2009/06/05/word-2003-import-an-excel-2003-table/ [...]


  3. I can not tell you how extremely helpful this article was. It was clearly explained and so easy to follow. Thank you.


  4. […] insert it as a Word table that you can modify later in Word; instructions are here: http://cybertext.wordpress.com/2009/06/05/word-2003-import-an-excel-2003-table/ […]


  5. After Years and years I have finally cracked this with Office 2007:
    Select the cells in Excel.
    Select the cells in Word.
    Paste Special / Formatted Text (RTF) <<<Not html
    Word table formatting will be preserved (There are some advanced word COPY options that may affect the result too).



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 348 other followers

%d bloggers like this: