Freeze a random result with mouse trick

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 😦

Excel Tip - PasteValuewithMouse

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: 

Excel Tip - PasteValuewithMouse1

What is the trick?

  1. Select the range of cells
  2. Move the cursor to a border of the selected range
  3. When the arrow-cross appears, Right-Click and HOLD
  4. Drag it to somewhere else and then back
  5. Release the Right-Click
  6. Select “Copy Here as Values only”
  7. 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! 🙂

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

6 Responses to Freeze a random result with mouse trick

  1. David N says:

    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.

    Like

    • MF says:

      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.

      Like

      • David N says:

        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.

        Like

        • MF says:

          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?

          Like

          • David N says:

            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

            Like

          • MF says:

            No I don’t have these Event Procedures

            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

This site uses Akismet to reduce spam. Learn how your comment data is processed.