1

When copying more than 250 cells and pasting via the excel clipboard, the formatting (like fill colour) doesn't carry over, but on the first ever use of the clipboard, formatting was copy-pasted correctly for over 250 cells.

Steps to reproduce:

  • Select and copy more than 250 cells
  • Open the clipboard using this button clipboard button on excel
  • Paste by clicking on the item shown in the clipboard list

On the first use, the formatting will be copied correctly, however, any subsequent copying of over 250 cells will only keep the value, and partial formatting. Restarting the application or the computer doesn't fix this, but I've reproduced the behaviour on a second machine whose excel clipboard was never opened.

Originally, I used this to copy cells with conditional formatting (but only the values and formatting, without the conditions); another trick to copy the formatting without the conditions was to copy it to a word file, and then back to excel, but this method seems to work less when dealing with large amounts of data.

Improve this question
6
  • 1
    Seems like Excel has a bug with reusing the clipboard (bug report from 2016). Try to clear the clipboard before doing the second copy-paste.
    – harrymc
    Commented Feb 6 at 19:00
  • Nice information, great to know that it doesn't work beyond 249 cells.
    – Mayukh Bhattacharya
    Commented Feb 6 at 19:02
  • @harrymc Sir, does this behavior of clipboard follows with using VBA as well?
    – Mayukh Bhattacharya
    Commented Feb 6 at 19:08
  • 1
    @MayukhBhattacharya: I don't know. VBA is ancient, but I would assume that it uses the Excel mechanisms and not its own.
    – harrymc
    Commented Feb 6 at 19:13
  • 1
    @harrymc Thank you for the bug report, it seems I'm not alone to have had this issue. I've tried to clear the clipboard using echo.|clip, but it didn't seem to fix the issue. I believe excel uses another method to store the data in the clipboard once the latter has been opened at least once, because the first copy (251+ cells with correct formatting) can be pasted several times, even after another selection has been copied. @MayukhBhattacharya it's not very important but, it works for 250, and not for 251
    – Daniel Cho
    Commented Feb 6 at 19:26

1 Answer 1

Reset to default
1

Seems like it's an old bug that was reported in 2016, a fix was promised, but we're still waiting (forever probably).

Bug report Excel: Office Clipboard loses formatting when pasting more than 249 cells says :

I have an Excel sheet with more then 250 cells (text and yellow background). When I select all the cells and paste it the first time through Office clipboard (not CTRL+V) ->Start>Clipboard and select the first clipboard entry. All works well ->The text and formatting (yellow Background) is OK.

When I copy all the cells a second time and paste it through office clipboard, the formatting (yellow Background) ist lost.

When I select only 249 cells the formatting is everytime OK.

The answer says :

We currently do not have estimated time on a fix but, the issue will be taken care in the future updates.

If the promised future update has not yet arrived, I wouldn't wait for it.

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .