• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel RAND and RANDBETWEEN Functions

You are here: Home / Excel Formulas / Excel RAND and RANDBETWEEN Functions
Excel RAND and RANDBETWEEN Functions
April 6, 2011 by Mynda Treacy

Excel’s RAND and RANDBETWEEN functions aren’t very well known, but they can be quite handy.

RAND Function

RAND is a simple function that returns a random number between 0 and 1.

It is entered

=RAND()

That’s it…simple.

RANDBETWEEN Function

RANDBETWEEN is slightly more complicated.

=RANDBETWEEN(bottom,top)

Where ‘bottom’ is the lowest number you want returned and ‘top’ is the highest.

For example, to return a number from  1 to 100 you would enter:

=RANDBETWEEN(1,100)

If you prefer whole numbers then RANDBETWEEN is the best option.

Pressing F9 on your keyboard will calculate new random numbers, as will entering a new formula anywhere else in the workbook or any formatting changes like inserting columns/rows etc.

If you don’t want it to update just turn off automatic calculations:

Go to the Windows Button > Excel Options > Formulas section – see image below

turn off auto calc

Note: if you get an error when you use the RAND function in Excel you will need to install the Analysis ToolPack add-in first.

USES for RANDBETWEEN

RANDBETWEEN on its own has some limitations, for example you could end up with duplicate random numbers, which is no good if you want to randomly number a list.

RANDBETWEEN and CHOOSE Example

Let’s say we have to select someone to work each Saturday…you’re not going to be the most liked boss, but if you can blame it on Excel’s Random Selection it might reduce some of the flack!

Below is a list of 7 employees and we have to choose one to work each Saturday.

turn off auto calc

Our formula is:

turn off auto calc

Use RAND with Microsoft Word!

Microsoft Word also had a RAND function which is handy when you’re setting up templates and you want to insert some text to experiment with layouts.

You enter it the same way you enter it in Excel. That is, simply type =RAND() into your document and press enter. Voila, you will be presented with 3 paragraphs of text.

If you want more or less than 3 paragraphs simply enter the number of paragraphs inside the brackets like this:

=RAND(5) This will give you 5 paragraphs of text.
Excel RAND and RANDBETWEEN Functions

More Math & Trig Posts

Excel CEILING and FLOOR Functions

Excel CEILING and FLOOR Functions allow you to round values up or down to the nearest value divisible by a specified number. e.g ending in 95 cents.

How to Round Numbers in Excel Using 3 Rounding Functions

Use ROUND, ROUNDUP and ROUNDDOWN functions to round your numbers in Excel. Lots of examples and a sample workbook to download

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: math & trig
Previous Post:How to Calculate Interest on Savings in ExcelHow to Calculate Interest on Savings in Excel
Next Post:Microsoft Excel Shapes & SmartArtMicrosoft Excel Shapes & SmartArt

Reader Interactions

Comments

  1. David N

    June 24, 2017 at 2:05 am

    I know this is an old post, but it can still come up as an internet search result; so I wanted to comment. As others have noted, the combination of RANDBETWEEN and CHOOSE is not a valid way to guarantee a lack of repetition. This makes sense because the very definition of random is that repetition (even in consecutive calls) is possible. So each time the example function is executed, it simply gets a random number between 1 and 7 and then “chooses” that person.

    Those commenters and others who would like a range-based approach should use INDEX instead, recognizing that this too allows for repetition.
    =INDEX(D4:D10,RANDBETWEEN(1,7))

    Achieving random numbering or sorting without repetition is more complicated with one numbering method for the given example being as follows.

    Step 1: Enter this array formula in cell E4 and execute with Ctrl+Shift+Enter
    {=SMALL(IF(COUNTIF(E$3:E3,ROW(INDIRECT(“1:7”)))=0,ROW(INDIRECT(“1:7″)),””),INT(RAND()*(7-ROWS(E$4:E4)+1)+1))}

    Step 2: Copy the formula to the remaining cells (down to E10)

    If needed, the formula could be made more dynamic by using COUNTA with INDEX or OFFSET (maybe even with one or more Defined Names) and replacing the two hard-coded instances of INDIRECT(“1:7”) and the one instance of a 7.

    Reply
    • Mynda Treacy

      June 24, 2017 at 1:58 pm

      Nice tips. Thanks for sharing, David.

      Reply
  2. Leon

    June 11, 2016 at 12:22 pm

    Hi,

    Greeting from Malaysia.
    Currently I’m study to do a simple random name picker which need to done in excel.
    Below is my conditions:
    1. sample size, n=500;
    2. The previous selected name (a.k.a. cells) need to be omitted so that the name will not be repeated again.

    I had done it by using =INDIRECT(CONCATENATE(“A”, RANDBETWEEN(1,500)))
    [I put the name list in cell A],

    I’m also tried to use the CHOOSE function that you wrote in this blog. Well, it can be randomly selected name. However, some name will still be repeated again because I had no wrote any function to fulfill the condition 2.

    Can you help me in this instance?
    Thanks!

    Reply
    • Catalin Bombea

      June 11, 2016 at 11:16 pm

      Hi Leon,
      You have to prepare a sample file, there are a few thinks that can be clarified by a sample file. Where is the previous selected name? In the rows above from the same column or is it in the same cell, where you are making another selection?
      You can send it by mail, or create a new ticket on Help Desk.
      Catalin

      Reply
  3. William Petit

    May 18, 2016 at 11:39 pm

    rand in Word you can also specify the number of lines and paragraphs if you enter both values in the brackets – =rand(5,3)
    Which is 5 paragraphs of 3 lines…
    Great for teaching or testing printers!

    Reply
    • Mynda Treacy

      May 19, 2016 at 9:47 am

      Sure is, William. Thanks for sharing.

      Mynda

      Reply
  4. Sarah

    December 14, 2015 at 10:03 pm

    I do not understand. I wanted random from C1:C500. I put =Choose(Randbetween(62500000000, 1), C1:C500). It does not work. why not?

    Reply
    • Catalin Bombea

      December 15, 2015 at 9:24 am

      Hi Sarah,
      If you want that, you should use:
      =INDIRECT(“C”&RANDBETWEEN(1,500))
      Catalin

      Reply
  5. Afsar Mohiuddin Qhadri Syed

    March 19, 2014 at 5:13 pm

    How to use the formula for =countifs( for the below file in the below format
    1-Mar-14 Credit 100 2000 CHENNAI
    2-Mar-14 Debit 200 1000 ANDHRA PRADESH
    3-Mar-14 Credit 100 2000 BANGALORE
    4-Mar-14 Debit 200 1000 HYDERABAD
    5-Mar-14 Credit 100 2000 CHENNAI
    6-Mar-14 Debit 200 1000 ANDHRA PRADESH
    7-Mar-14 Credit 100 2000 BANGALORE
    8-Mar-14 Debit 200 1000 HYDERABAD
    9-Mar-14 Credit 100 2000 CHENNAI
    10-Mar-14 Debit 200 1000 ANDHRA PRADESH
    11-Mar-14 Credit 100 2000 BANGALORE
    12-Mar-14 Debit 200 1000 HYDERABAD
    13-Mar-14 Credit 100 2000 CHENNAI
    14-Mar-14 Debit 200 1000 ANDHRA PRADESH
    15-Mar-14 Credit 100 2000 BANGALORE
    16-Mar-14 Debit 200 1000 HYDERABAD
    17-Mar-14 Credit 100 2000 CHENNAI
    18-Mar-14 Debit 200 1000 ANDHRA PRADESH
    19-Mar-14 Credit 3300 4400 ANDHRA PRADESH

    I need the above details in the given below format.

    Region Sum of Debit-Count of Debit2-Sum of Credit-Count of Credit2
    ANDHRA PRADESH 7100 20 23400 20
    BANGALORE 1900 19 38000 19
    CHENNAI 1900 19 38000 19
    HYDERABAD 3800 19 19000 19
    Grand Total 14700 77 118400 77
    Request to help me how to use this type of formula.
    =COUNTIFS($B$2:$B$78,”Credit”,$E$2:$E$78,$A81)

    Afsar …

    Reply
    • Catalin Bombea

      March 19, 2014 at 11:25 pm

      Hi Afsar,
      The formula you presented will count the rows where both criterias are true in the same time; the second criteria range refers to Region, (E2:E78), but the criteria points to a cell in column A: A81, which is a date, so it won’t work. Instead of A81, change that to “ANDHRA PRADESH”, or point to a cell that has the region name in it.
      For adding the values, use =SUMIFS($D$1:$D$19,$E$1:$E$19,$E22,$B$1:$B$19,”Credit”), in E22 type the region name: ANDHRA PRADESH or any other region, or type directly in formula: =SUMIFS($D$1:$D$19,$E$1:$E$19,”ANDHRA PRADESH”,$B$1:$B$19,”Credit”)
      If you need more assistance, please upload a sample workbook with your data structure, on Help Desk.
      Catalin

      Reply
  6. Lana Gokey

    March 14, 2014 at 1:07 am

    I am trying to use the randbetween function to take a simple random sample of a data value set of 150 ages varying from 17 to 66. Note there are some ages in the set that are not represented. How do I get a sample of say 20 random ages. I have tried =choose(randbetween(2,151), a2, a3, a4, etc but this is not working.

    Thanks

    Reply
    • Catalin Bombea

      March 15, 2014 at 2:28 am

      Hi Lana,
      Use =RANDBETWEEN(17,66) to get a value between those ages. If you want 20 random ages in that interval, you have to copy that formula in 20 cells, the function is not returning 20 values in 1 cell. Note that in those 20 cells, there may be duplicates, and all the rewsults will change every time excel recalculates the sheet. You can see that by pressing F9 to force a recalculation.
      If you need more help, you can use the Help Desk: https://www.myonlinetraininghub.com/help-desk
      Catalin

      Reply
  7. Yuri

    December 15, 2012 at 11:43 pm

    Hi for CHOOSE and RANDBETWEEN, could you tell me what’s wrong with the 2nd formula, below? Thanks!
    =CHOOSE(RANDBETWEEN(1,3), J36,J37,J38)
    =CHOOSE(RANDBETWEEN(1,3),$J$36:$J$38)

    Reply
    • Mynda Treacy

      December 16, 2012 at 8:13 pm

      Hi Yuri,

      The CHOOSE function requires you to enter the values separately, not as a range of cells. This is why your first formula works and your second doesn’t.

      Kind regards,

      Mynda.

      Reply
  8. Ovl

    September 23, 2012 at 6:56 am

    Is there a way to print these tips without having to print the extra side panels?

    Reply
    • Philip Treacy

      September 24, 2012 at 10:34 pm

      Hi Ovl,

      you can use Print Friendly to do this

      Regards

      Phil

      Reply
      • Mike Gallagher

        February 20, 2015 at 2:55 am

        The Print/PDF box does not show on my screen. I am using IE-11.0.16

        Peace,
        Mike G.

        Reply
        • Philip Treacy

          February 20, 2015 at 10:16 pm

          Hi Mike,

          Sorry, we removed that link as it was causing the page to load very slowly. But you can visit Print Friendly to get a printer friendly version of the page if that’s what you want?

          Regards

          Phil

          Reply
  9. Chuck

    September 21, 2012 at 4:54 am

    I tried the choose(randbetween) function, but it is still repeating. How can I fix that?

    Reply
    • Mynda Treacy

      September 21, 2012 at 8:20 am

      Hi Chuck,

      Can you please show me your formula so I can understand why it’s not working as it should.

      Thanks,

      Mynda.

      Reply
  10. sadafkazmi

    June 21, 2012 at 9:24 pm

    Dear sir,

    thanx for the nice post. plz let me know how can we use this function in
    MS Office 2003.

    Regards,
    S.SadafKazmi

    Reply
    • Mynda Treacy

      June 22, 2012 at 12:19 pm

      Hi,

      It works the same in Excel 2003.

      Kind regards,

      Mynda.

      Reply
  11. mikeross

    April 25, 2011 at 7:27 am

    Thanks for an idea, you sparked at thought from a angle I hadn’t given thoguht to yet. Now lets see if I can do something with it.

    Reply
    • Mynda

      April 25, 2011 at 2:09 pm

      @mikeross. Great. I’d love to hear what you use RAND and RANDBETWEEN for your work.

      Mynda.

      Reply

Trackbacks

  1. Jitter in Excel Scatter Charts • My Online Training Hub says:
    March 23, 2020 at 2:20 pm

    […] start, use the RAND function to generate a random number. You'll get something greater than or equal to 0 and less than […]

    Reply
  2. Excel Factor 15 The Lazy Lookup • My Online Training Hub says:
    September 14, 2014 at 10:53 pm

    […] the figures used in this example are fictional (I created them using the RANDBETWEEN function); I would never accept a reduction in my clothing budget year on […]

    Reply
  3. Excel Designed Minecraft Cake says:
    September 9, 2014 at 1:17 pm

    […] 3. I also didn’t want to have to ‘think’ about creating a random pattern. So, I used the RANDBETWEEN function to generate the pattern with numbers. There were 3 shades of brown and 3 shades of green. Browns […]

    Reply
  4. Merge Excel Worksheets with VBA says:
    June 17, 2014 at 2:01 pm

    […] : I used Excel’s RANDBETWEEN function to generate the sales figures. I wasn’t going to type out those numbers by hand, and if you need to generate random numbers […]

    Reply
  5. Microsoft Excel Standard Deviation Functions says:
    January 5, 2012 at 5:26 pm

    […] These are hypothetical scores generated using the RAND function, for hypothetical book club […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x