This is an extension of my previous post – Freeze a random result with mouse trick.
In my experience using Excel, from Excel 2000 to 2010, and recently 365 (I’d never used Excel 2013 though), I have experienced the same strange behavior when I tried to convert a random number to value by using copy and paste value – the random numbers got re-calculated before the paste action. It happened to me in different PC (work, school, or home). Therefore I thought it is a common problem until David N left his comments to my post.
It aroused my curiosity. I tried again with four different ways to paste (random) values to see if I get different results. Interestingly, it did. See below:
A) Using shortcut Ctrl+Alt+V to open the paste special dialog, then select Value. It works! 🙂
B) Using the mouse trick discussed in previous post. It works! 🙂
C) Using the “Paste”, “Values” under Home Tab. It doesn’t work. 😦
D) Using Right-Click, Paste Value. It doesn’t work. 😦
Tip: Using Clipboard also works! 🙂
Honestly I have no clues why it happened. If you know, please leave your comments.
Anyway, it reminds me one thing that I always say to my audience when I conduct in-house Excel training:
Always check your result! No matter how confident you are!
The mystery indeed continues because I can use all four of those same methods (and have always been able to in Excel 97, 2000, 2003, 2007, and now 2013), and Excel never recalculates the copied values before they are pasted. And every related hit on page one of a Google search for “excel recalculates random numbers before they are pasted” says that what I see is the normal/expected behavior.
Well… i would say… that’s Excel 😅
Hey, seems I am not alone…
I see that the person who started that thread used this wording “..and paste to another cell…the value of the original cell changes.” In other words, it doesn’t sound like they were trying to paste back over the original cell, in which case Excel would certainly follow normal behavior and recalculate that “original cell” as trigger by the paste/change of “another cell.”
I’ve just come across an interesting video
This video is not about using Randbetween… but it shows the strange paste behavior at 4:30
Have a look when you have time.
That is indeed strange. I’ve tried this with two of my colleagues at work as well as on my home computer (to be sure it wasn’t something unique to our work installation). And the behavior I see remains consistent in Excel NOT recalculating before it pastes. So this video seems to validate that neither of us is alone in how we are seeing Excel operate in this scenario. Unfortunately, it also does not tell us why we’re seeing that difference.
This is the interesting part of Excel… 😅
Have a nice weekend!
Disable ‘Live Preview’ (general options) and C) and D) will work as well 🙂
Thank you so much Frank! You solved the myths! 🙂
Having said that, I suggest NOT to turn “Live Preview” off as it is such a good feature!