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:
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:
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.
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!
wmfexcel 
I was having trouble cutting and pasting in a large table. Some parts of the table worked, later parts didn’t.
I followed the advice to switch off filtering outside the table (I’d left it switch on for one unused column) and BINGO! all is now well.
LikeLike
Glad that you find a solution here. Happy New Year
LikeLike
It works, Thank you so much…
LikeLike
Glad that you found a solution here 😀
LikeLike
This sounds like a good solution. Couldn’t try as I converted my table to range out of frustration.
will definitely try this is it happens in any other file. Each one of your comments do help a lot. Thanks all from bottom of my heart !
LikeLike
You are very welcome. Agree with you that all comments are valuable. Thanks everyone! 🙂
LikeLike
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.
LikeLike
Worked for me! Thanks so much!
LikeLike
Hi Joan, glad to know that you find a solution here.
Cheers,
LikeLike
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
LikeLike
Thks SO much, worked !
LikeLike
My Hero, thank you! Removing an unused useless external filter has resolved months of frustration and errors!
LikeLiked by 1 person
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.
LikeLiked by 1 person
Thanks for sharing your case and the Solution. Nevertheless that is not my case that has only one table… 🤔
LikeLike
Worked for me! Thanks!!! HUGE pain and nowhere else was there a solution. just disabled filter outside table
LikeLike
Great!!! Thanks a lot. It was driving me crazy also.
LikeLike
Thanks a lot … It worked for me …
LikeLike
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!!!
LikeLike
Thanks,
It’ works for me. I’m looking solutions for hours, and find this is the root cause.
LikeLike
this just save my life!! thank you!!!!!
LikeLike
Thanks worked for me! It was quite frustrating.
LikeLike
This worked out for me after struggling for 3 hours. Thank you
LikeLike
Thanks a lot ! It was a nightmare before I found your post !
LikeLike
Thank you so much for the help!!! It was exactly the issue I was facing. It was driving me crazy!!!
LikeLike
Hell yeah, solved the problem… will microsoft ever fix this bug????
anyway tks a lot
LikeLike
I think I love you! You just saved me from one of my biggest headaches ever!
LikeLike
Perfect! Especially the instruction to put the cursor outside the table and then use the [Filter] Tab on the menu bar to unfilter. I’ve been wrestling with this issue for ages! Thank you so much.
LikeLike
OMG, this finally worked!!! There was a bizarre filter outside my table & simply turning it off corrected everything…do not know how you figured it out, but bless you!!!
LikeLike
Glad you found a solution here! 😀
LikeLike
Saved me. Thanks a lot.
LikeLike
Glad you found a solution here! 😀
LikeLike
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.
LikeLike
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».
LikeLike
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.
LikeLike
Same problem. Selected entire worksheet column and pasted values solved for me.
LikeLike
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. . . . .
LikeLike
Hi Sarel,
Thanks for sharing! In my case, the totals row is off…
LikeLike
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!
LikeLike
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
LikeLike
“mad beyond mad” – totally understood. 🙂
LikeLike
I have this exact same behaviour with copy and pasting rows on Excel tables.
My table has headers
LikeLike
Good to know i am not alone!
LikeLike
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.
LikeLike
You may try the following:
Select the range
Go to VBE (ALT + F11)
In the immediate window
Selection = selection.value
LikeLike
I had the same problem. The table has about 2900 rows of data. Converting back to a normal range worked.
LikeLike
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.
?
LikeLike
I can understand your frustration, Pyrrhic. Thanks for sharing.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike