Bug with copy and paste in Excel Table

No one says Excel is free of bug.  I have encountered one recently.

Here’s the situation:

In a big Excel Table, with 20k+ rows and 40+ columns (not really big actually), there are quite many formulas.

What I did:

Select a (continuous) range of cells with formula, Copy and Paste Value

What happened:

The top row of my selection is gone; all the cells below go up by one row.  ==> My data shift up by one row

See below animated screenshot:

Bug on Table

I thought that only happened to the cells with formula.  So I did the same thing to a simple range with no formula.  Same thing happened.

What I did:

I copied a single cell and pasted it to a small range.

What happened:

The contents of the selected ranges shift up. See below animated screenshot:

Bug with Copy and Paste in Table 1

I wondered if it was because of the huge amount of formula.  I converted all formula into value by using a VBA statement: selection = selection.value and then repeat the above steps of copy and paste.  Interestingly, it behaved normally.

Bug with Copy and Paste in Table 2

Also, if I deleted rows of record, e.g. kept only 100 rows of record (and the formula), the bug disappeared.

Note: There is no macro.  Run on Excel 2010 and Windows 7.

I have no idea what I did I to “awake” the bug… @_@

Any idea? Please share your experience in comments.

Moral

Do check what you do!  On top of human errors (99.9999%), there may be bug hidden (0.0001%).

Advertisements
This entry was posted in General and tagged . Bookmark the permalink.

25 Responses to Bug with copy and paste in Excel Table

  1. Richard Rashleigh says:

    I have found the same thing happening. It was due to a filter being inadvertently set in a weird part of the spreadsheet (off the table) – which I was quite unaware of. The thing was driving me mad just like the others here.
    The solution in my case was found by putting the cursor in a cell outside the table and then navigating to the [Filter] Tab on the menu bar. If a filter is on somewhere in the non-table part of the spreadsheet this will be set and highlighted. – de-selecting will turn off the errant filter and everything starts behaving again.
    The explanation seems to be that Excel ring-fences areas formatted as a table from other parts of the spreadsheet and if there is a filter set outside of the table – it affects the functioning of the table section – certainly with regard to the copy and paste.

    Like

    • MF says:

      Thanks for sharing your case and the Solution. Nevertheless that is not my case that has only one table… ๐Ÿค”

      Like

    • Neil says:

      Worked for me! Thanks!!! HUGE pain and nowhere else was there a solution. just disabled filter outside table

      Like

    • Ram says:

      Thanks a lot … It worked for me …

      Like

    • Victoria says:

      Thank you!!!! I read your solution, and said to myself “no way, I didn’t set a filter up outside my worksheet, that won’t work for me.” Amazingly, it DID work. This will save me from endlessly having to manually enter data into my table b/c copy/paste was working so erratically. Thank you!!!

      Like

  2. venkata seshasai says:

    Hi all I FOUND THE ANSWER…..PLEASE INTHE WHOLE OF THE EXCEL SHEET IF THERE ARE MORE THAN ONE ROWS HAVING FILTERS. REMOVE THESE FILTERS AND PUT THE FILTER ONLY IN THE TABLE WHERE YOU ARE WORKING
    SAVE THE FILE ONES AND THEN START THE COPY PASTE AGAIN.

    Like

  3. Santos says:

    I’ve tried the tip ยซSelection = selection.valueยป in the immediate window. It worked the 1st time, not ever more ๐Ÿ˜ฆ To have to do this everytime we need to paste some cells, is not practical. MS have to solve the ยซbugยป.

    Like

  4. Admilson Marques says:

    I was having the same issue, but I figured out the problem source was a manual filter I’ve added to a column out of the table range in the same sheet, once I removed the filter from this column I was able to copy/paste again inside the table.

    Like

  5. PmC says:

    Same problem. Selected entire worksheet column and pasted values solved for me.

    Like

  6. Sarel says:

    HI Guys
    Try switching off the totals row of your table if you have one.
    I ran into the same issue just now using Excel 2016 on Windows 10.
    Copied an entire column in a Table, and “Paste as Values” back into the same place (to replace formulas with flat values). I noticed that the entire column have been “moved up” by 1 cell – the top cell value disappeared, and was replaced by the value of the 2nd cell. The bottom cell in the range was blank. I tested this repeatedly and got the same result EVERYTIME. I noticed that my table had a TOTALS row switched on. I switched the TOTALS row OFF, and then the copy and paste as values worked correctly. . . . .

    Like

    • MF says:

      Hi Sarel,
      Thanks for sharing! In my case, the totals row is off…

      Like

    • Pedro Ferreira says:

      I didn’t had any totals row. Other than some additional columns that have formulas, everything is pure source data.

      M$ should really fix this! It is a terrible bug to have on a commercial application!

      Like

  7. Moshe says:

    I have this problem as well.
    It started last month. I do the same analysis each month. I copy data from a previous table, but it pastes other data from that table.
    But what is weird is that it only happens to one of my five tables. All other tables copy / paste fine.

    I am at wits end because it is making me mad beyond mad

    Like

  8. Pedro Ferreira says:

    I have this exact same behaviour with copy and pasting rows on Excel tables.
    My table has headers

    Like

    • MF says:

      Good to know i am not alone!

      Like

      • Pedro Ferreira says:

        I just had to manually edit about 500 cells due to this bug.

        The only thing that seems to work is to convert the tableback to a normal range, edit it, and then convert it back to table.

        The problem with this approach is that all the formulas that have been built using the table are edited automatically by excel, and not always work as expected.

        Like

        • MF says:

          You may try the following:
          Select the range
          Go to VBE (ALT + F11)
          In the immediate window
          Selection = selection.value

          Like

        • Bill Nixon says:

          I had the same problem. The table has about 2900 rows of data. Converting back to a normal range worked.

          Like

  9. Pyrrhic says:

    This week got a Windows10 update with an uninvited change to Excel16.

    ‘Table1’ of 4000+ rows/25 columns.
    1. manually applied Ctrl+C/Ctrl+P, Ctrl+X/Ctrl+P not working.
    2. Unable to change Table-Styles.

    Convert ‘Table1’ to a range and save/reopen
    1. Ctrl+C/Ctrl+P, Ctrl+X/Ctrl+P working.
    2. Able to change Table-Styles.

    Convert range back to ‘Table1’ and save/reopen
    1. Ctrl+C/Ctrl+P, Ctrl+X/Ctrl+P not working.
    2. Unable to change Table-Styles.

    ?

    Like

    • MF says:

      I can understand your frustration, Pyrrhic. Thanks for sharing.

      Like

      • Pyrrhic says:

        Hi

        Two mths on and my puzzle unresolved – and compounded by (1) ‘reapplied’ filters (Ctrl-Alt-L) corrupting data sort and (2) conditional formatting being WYSI(not)WYG!

        Have reapplied (sic) old rule for large Excel databases – i.e. do not use excel for ‘large’ databases!

        Happy now.

        PyRRRRRRRRRRRRRRRRRhic

        Like

  10. ืžื™ื›ืืœ ืื‘ื™ื“ืŸ - ื‘ื ื˜ื•ื™ื–ืŸ says:

    Hi,

    1) Is it possible to get a copy of the “huge” Workbook in order to check its behavior under various “Excel” versions ?

    2) I order to copy > paste > as values โ€“ one doesn’t need VBA. Just select the whole range ” right(!) click on one of the 4 borders > while holding the mouse button > drag it one column to the right DO NOT release the mouse button drag it, immediately, back to its original place and choose ‘copy here as values’.

    Thanks in advance and have a nice weekend,

    Micky

    Like

    • MF says:

      Hi Micky,
      I will try to upload the workbook later… but not sure if it can be done.
      On the other hand, I tried basically all the “Copy and Paste” methods. All produced the same result… ๐Ÿ˜ฆ
      Cheers,
      MF

      Like

Comments, suggestions, corrections are welcome.

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