Perhaps you have tried to use Excel to generate a random number for a lucky draw.
That is easy. For example, if you want to generate a random number, you can use the function RANDBETWEEN.
=RANDBETWEEN(1,1000)
This will give you a random number between 1 and 1000. However, RANDBETWEEN re-calculates whenever there is update to the worksheet. In order words, it keeps giving you a new value when you work on the same worksheet. That is the nature of “Random” but you don’t want this for a lucky draw. As you want to “freeze” the draw result.
On top of your mind may be Copy and Paste Value…. BUT it doesn’t work 😦
Re-calculation occurs before the value is pasted…
No worry! There is a mouse trick to perform the Paste Value action without triggering a re-calculation. Watch this:
What is the trick?
- Select the range of cells
- Move the cursor to a border of the selected range
- When the arrow-cross appears, Right-Click and HOLD
- Drag it to somewhere else and then back
- Release the Right-Click
- Select “Copy Here as Values only”
- DONE
In short, drag and drop with Right-Click, followed by selecting “Copy Here as Values only”.
As simple as this. 🙂
You may watch it in my YouTube Channel:
Enjoy! 🙂
wmfexcel 
I’m not sure what your Excel and/or file are doing, but I’ve tried to recreate the behavior of seeing a recalculate before the paste can occur. I’ve done it ten consecutive times using both the keyboard (my preferred method) and the mouse, and the pasted values never differ from what was copied. I’ve also hosted a weekly Excel User Group for the past 3+ years, and I’ve used this RANDBETWEEN approach hundreds of times. Never once have I seen the pasted values be different from what was copied.
LikeLike
Hi David, I believe you are using Excel 2016 or later version… that recalculation problem is fixed “Partially”… but not completely. That’s something strange.
Try this…
Copy the range of cells with Randbetween
Right click to open the contextual menu
Hover the paste value icon (but don’t click)
Wait a bit
Then Hover to the normal Paste icon
By now… you will see the results recalculated
Since that, the recalculation problem occurs no matter what method you do
This is what I experienced.
Let me know if you find the same.
LikeLike
No, I’m still using the 2013 version. I tried the steps you suggested, and it didn’t matter. I can do everything with the mouse, hovering over paste values, then paste, and even back and forth with 30-60-90 second delays, and it still pastes the same values that were copied. Or if I’m using the keyboard, then I can copy and wait for those same extended periods before pasting values, and it still works.
And I never saw what you describe in the 2007 or 2003 versions either. Before becoming the primary host of the weekly group I mentioned, I was its co-host for many years. So I’ve been routinely using the RAND and RANDBETWEEN trick with paste values for more than a decade across three versions of Excel and have never seen it, not even once. It almost seems like something from a setting, add-in, or VBA event procedure on your computer that is triggering this non-standard behavior.
LikeLike
Thanks for the details, David.
This is super interesting. In my case, it is exactly opposite. I jumped to Excel 2016 from 2010… no experience on Excel 2013. When I first used Excel 2016, I thought the problem has been fixed as i can paste the value resulted from Randbetween without using this mouse trick… until one day i found it happened again. With some trial and errors, i found the way to trigger that strange behavior. Therefore i keep using this mouse trick if i want exactly the random values generated.
I have been experienced this “strange” behavior of recalculation for years when i used Excel 2010 or before. Not a single case i could keep my random values.
Not sure what makes the difference… anyone else have experienced this? Pls leave your comments
Arrr… just to confirm, you have not turned off auto calculation, right?
LikeLike
No, I never operate in Manual calculation mode if I can help it. Is it possible you have a “personal” workbook or add-in that calls one of these Event Procedures and might contain code that is triggering an unexpected recalculation?
Workbook_SheetBeforeRightClick
Workbook_SheetSelectionChange
Workbook_SheetChange
Workbook_SheetCalculate
Worksheet_BeforeRightClick
Worksheet_SelectionChange
Worksheet_Change
Worksheet_Calculate
LikeLike
No I don’t have these Event Procedures
LikeLike