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%).

EDIT:

Ensure you continue to read comments below. Richard Rashleigh suggested a solution (to turn off filter on the same sheet outside the table)  that many people find it work!  Thanks Richard for your suggestion!

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

40 Responses to Bug with copy and paste in Excel Table

  1. David Vogel says:

    Just add a blank row to the top of your data source, select the blank row and your data, paste values into the Table. Double check top and bottom rows of data pasted into your data.

    Like

  2. Joan Congine says:

    Worked for me! Thanks so much!

    Like

  3. Vio says:

    Hi All, I checked for filters and didn`t work…

    Found another option that worked in my case:
    Select the column from the top, black arrow appears, select entire column and copy, then go with the mouse on the first row (next row under the header) and paste as value

    Hope works for you too!
    KR

    Like

  4. PAULO MITRI says:

    Thks SO much, worked !

    Like

  5. mike says:

    My Hero, thank you! Removing an unused useless external filter has resolved months of frustration and errors!

    Like

  6. 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.

    Liked by 1 person

  7. 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

  8. 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

  9. 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

  10. PmC says:

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

    Like

  11. 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

  12. 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

  13. 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

  14. 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

  15. מיכאל אבידן - בנטויזן 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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s