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
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.
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.
Do check what you do! On top of human errors (99.9999%), there may be bug hidden (0.0001%).
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!