I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word.

What happens is when I do a special paste, I am only allowed 2 linking options, Link & keep source formating, or Link & merge formatting. Both gives poor formatting results in which the content of the cell (a text returned from the formula through lookup) adds a line before the result, and a line after the result. This is unacceptable for my purposes as I am trying to minimize vertical white space, while locking the horizontal dimensions of the cell. Any attempt to remove those lines will delete the entire linked object. Pasting as text does not have this problem, as well as typing something into the word table cell, but I lose the automatic update/linking ability.

Making a new object for link gives an even worse result, where it is now pasting the entire spreadsheet into the single cell, and undoing all the formatting in the table.

Help would be appreciated, as this is very frustrating for something that should be very simple.

EDIT: I fixed part of my problem, it turns out I overlooked the Paste Link option in the Paste Special menu. I can now paste the unformatted text into the table cell and not have it add extra formatting from Excel, just like I want it (via Paste Link, and select Unformatted Unicode Text). However, this introduces a new problem where if the links are updated, if the new text of the table cell contains a space, only the first few words are in the correct format that I had specify for the table cell, while the extra words are using the default formatting, instead of the table cell formatting.

To replicate this, Create a table cell in word, assign any formatting other than your current default (such as changing font size to size 7, add bold and italics), then link a cell from excel via special paste by going paste options -> paste link -> unformatted unicode text. What you will see is that the link keeps default formatting and places it infront of the table cell formatting. If you highlight the entire cell again and change the formatting to what is wanted, the current version of the link is now in the proper format. However, if you update the link by changing the text in excel and it contains spaces (or basically more words), the first words will be in the correct formatting, while the last few words will be in the default formatting. The table cell will not be in consistent formatting after update. All done in Word and Excel 2013.

  • 2
    Could you share an example docx and xlsx with us? – nixda Sep 16 '13 at 5:15
  • AFAIK the approach that best preserves what you actually see in the Excel cell while maintaining a link is (a) in Excel, select the cell and use the "Copy as Picture" option on the Copy dropdown, then (b) in WOrd, use Paste->Paste Special->Paste Link, and select the only available format (Windows Metafile). (But I haven't checked in 2013). – user181946 Sep 16 '13 at 10:00
  • (To qualify my previous comment, I don't think that's actually any different from the normal Copy/paste special, paste link with Windows Metafile Format)) – user181946 Sep 16 '13 at 10:59
  • @nixda While I cant share you the file itself as it contains personal information, I did find a partial way around the problem, and have just one small thing left. Please see my edit in the opening post for new updates, as well as how to replicate the problem. – Fysloc Sep 17 '13 at 5:03
  • Why don't you answer your own question – Firee Jan 9 '14 at 9:38

In case anyone else runs into this, I was able to synthesize a solution from the updates and other comments above. When you paste the data from Excel into Word, choose Paste > Paste Special..., then choose the Paste Link radio button, and Unformatted Unicode Text. Looks like this in Office 2016:

Office 2016 Paste Special dialog

If your content actually has some formatting, one of the other options might be better. In my case I just wanted the plain text content, which I was then able to style in Word to match its context.

  • Oh man, I never ever would have gotten that one. Thanks! – bigjosh Mar 18 at 8:20

Instead of using unicode format, paste the link using RTF and select "Match destination formatting and link to Excel" for the paste options. It should automatically update correctly. If not, right click on the link and go down to "Linked Worksheet Objects", select "Links" from the dropdown and make sure "Preserve formatting after update" is checked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.