Paste special extended options disappear

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
I was trying to copy an Excel table to Word preserving all of the formatting, such as highlight colors, borders, etc. I usually do this by pasting the table as a picture, but this one is too long to fit on a page, so I wanted to have it be an actual Word table so it could span multiple pages.

I was about to paste it as text and then apply the formatting manually when I discovered quite by accident what I am calling the "paste special extended options". After selecting the table, I right-clicked in the selection and noticed an option labeled "Paste Special" with a little ">" on the right. Hovering over that ">", I got a dropdown with 3 sections. The third section, labeled "Other Paste Options", has several icons. Hovering over the first, I see "Formatting (R)". Curious, I selected it. I then went to Word and pasted it in (C+v).

I nearly jumped out of my seat. The table appeared exactly like it was in Excel. It was liker Christmas!

Sadly, the next time I tried that same procedure, the Paste Special option does not have the little ">" so I cannot replicate the magic.

I did a little checking. several sites said I needed to check the "Show paste options button when content is pasted". But I check and that option was already checked.

Can anyone tell me what I need to do to get this amazing magic ">" back?

Thanks
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you are talking about the Paste Special options in Excel, then you actually pasted the formatting in Excel when you clicked the button. The presence of those options is determined by what you had copied before you right clicked in the table. A simple Ctrl+C of a table in Excel followed by Ctrl+V in Word should present you with a formatted table anyway, unless it's merging with an existing table in Word.
 
Solution

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Also, if the table is too large and is truncated in Word, you can right click the table in Word and resized the table to fit the page.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
If you are talking about the Paste Special options in Excel, then you actually pasted the formatting in Excel when you clicked the button. The presence of those options is determined by what you had copied before you right clicked in the table. A simple Ctrl+C of a table in Excel followed by Ctrl+V in Word should present you with a formatted table anyway, unless it's merging with an existing table in Word.
At first I didn't think you understood my question. Then I tried it and it works just as you describe. Is this new in Office 2019? In my old copy of Office 2007, I thought I always had to use paste special (Ctrl+Alt+v) and then select one of those options. But none of them did what just Ctrl+v does now. I either got a graphic object or text without most of the formatting.

Thanks

The "extended" options under the ">" are the ones in this screen clip:
Mr Excel 20210128 Paste special extended options.jpg

I discovered that the ">" only appears if I already did a copy (Ctrl+c) before right-clicking. But your solution is simpler.

It also avoids another little Excel oddity. If I select this table, copy it (Ctrl+c), right-click and select the option indicated by the arrow above, it messes up the headers.

These are the headers before the paste special:

Mr Excel 20210128 Table before paste special.jpg


and these are after:


Mr Excel 20210128 Table after paste special.jpg


Apparently merged cells are too complicated for it to handle, so it just trashes them. Fortunately, undo (Ctrl+z) fixes the error. But your solution avoids the problem entirely.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
Also, if the table is too large and is truncated in Word, you can right click the table in Word and resized the table to fit the page.
If you are referring to a table that has been pasted as a graphic object, then, yes, I am aware that it can be resized. But it is no longer a table and not appropriate for my project. These tables can be 100 rows or more. Shrinking that down to one page would make it so tiny it cannot be read. In addition, I want it to span as many pages as it takes and I want to mark the headers as such so that they get repeated on every page.

If you are referring to a table that is still a table, as with RoryA's solution, then I do not know how to resize the entire table to fit the page width. Note, I don't want to resize to fit the page length -- just the width.

Thanks
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is this new in Office 2019?
No, it’s in 2016, possibly 2010 too (I don’t recall). Office 2007 was, to my mind, one of the worst ever, and tables were still very new, so it wouldn’t surprise me if it didn’t work there.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,404
Messages
5,624,557
Members
416,034
Latest member
Shiv kumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top