In Excel for Microsoft 365, use =RANDBETWEEN(1,100) to generate a random whole number from 1 through 100, including both limits. For a random decimal, use =RAND()*(B1-A1)+A1 when A1 contains the lower limit and B1 the upper limit; this returns a value at least as large as A1 but less than B1.
Choose the right random-number formula
The best formula depends on what you mean by a number between two values. Excel’s RANDBETWEEN function returns an integer, while RAND returns a decimal. Both recalculate, so their results can change as the workbook updates.
- Whole numbers with inclusive limits: use
RANDBETWEEN(bottom,top). For example, a range of 1 to 100 can produce 1 or 100 as well as any integer between them. - Decimals in a range: scale the result of
RAND()to the width of the range, then add the lower limit. The upper limit is normally excluded. - Decimals in fixed steps: generate a random integer at a smaller scale and divide it. This gives control over increments such as tenths or hundredths.
These functions create pseudorandom values for ordinary spreadsheet tasks such as sample data, simulations, and classroom exercises. They are not intended for security-sensitive uses such as passwords, encryption, or cryptographic keys.
Generate an inclusive random integer
RANDBETWEEN takes a bottom and a top value and includes both endpoints. With the lower limit in A1 and the upper limit in B1, enter this in C1:
Assume A1 contains 10 and B1 contains 25. In C1, enter =RANDBETWEEN(A1,B1). The result is one integer from 10 through 25; the exact result varies whenever Excel recalculates.
You can also put the limits directly in the formula. With the formula entered in C1, =RANDBETWEEN(10,25) returns an integer from 10 through 25. This is useful for a quick one-off number, while references to A1 and B1 make it easier to change the range without editing the formula.
To fill a column with independent random integers, enter the formula in the first cell, then use the fill handle or copy and paste it down. Each row recalculates separately. If the lower limit is greater than the upper limit, Excel returns the #NUM! error, so check that the limits are in the intended order.
Generate a random decimal between two limits
RAND returns a decimal greater than or equal to 0 and less than 1. To fit that value into a range, multiply it by the distance between the limits and add the lower limit. With the lower bound in A1 and upper bound in B1, enter this in C1:
Assume A1 is 5 and B1 is 20. In C1, enter =RAND()*(B1-A1)+A1. The result is at least 5 and less than 20. For instance, if RAND produces 0.4, the calculation is 0.4 × (20 − 5) + 5, or 11. The value is not restricted to a particular number of decimal places.
The order of the calculation matters: multiplying RAND by the range width sets the span, and adding the lower bound shifts the span into place. A common mistake is to use =RAND()*(B1-A1) alone. That produces values starting near zero, not values starting at A1.
If you want the decimal output displayed with two decimal places, format the cell using Home > Number, or use a formula that rounds it. With A1 set to 5 and B1 set to 20, enter this in C1:
=ROUND(RAND()*(B1-A1)+A1,2) displays a result rounded to two decimal places. For a strictly bounded result, choose limits that match the rounding precision; rounding can otherwise move a value slightly beyond a limit that has more precision than the displayed result.
Set a fixed decimal increment
When you need values in steps rather than arbitrary decimals, convert the desired range into integers, generate one of those integers, and divide by the scale. For values from 5.00 through 20.00 in increments of 0.01, enter this formula in C1:
Assume the required range is 5.00 to 20.00, with both endpoints allowed. In C1, enter =RANDBETWEEN(500,2000)/100. The result is a number from 5.00 to 20.00 in hundredths. The arithmetic works because 500 divided by 100 is 5 and 2000 divided by 100 is 20.
For increments of 0.1 across the same limits, use integer endpoints 50 and 200, then divide by 10. This method is often more predictable than rounding a continuous decimal: it gives a defined set of possible values and can include the upper endpoint. Make sure both bounds convert cleanly to integers at the chosen scale.
Generate a random number from cells or a list
Putting the bounds in cells makes a worksheet easier to reuse. Label A1 Lower limit and B1 Upper limit, enter the values beneath those labels, and refer to the cells in the formula. You can change the inputs later without rewriting the formula. If the limits come from user entry, consider adding Data Validation so users supply numbers in the intended order.
You can also use a random position to select a value from a list. Assume the items are in A2:A11 and enter the following in B2:
=INDEX(A2:A11,RANDBETWEEN(1,ROWS(A2:A11))) returns one item from the ten cells in A2:A11. RANDBETWEEN chooses a row position from 1 to the number of entries, and INDEX returns the item at that position. If the same item appears more than once in the list, it has more chances of being selected.
For a shuffled list in Microsoft 365 or Excel 2021, assume the values are in A2:A11 and enter this in B2:
=SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))) returns the list sorted by randomly generated keys and spills the reordered items into cells below B2. This is useful when you want a random order rather than a single selection. The formula recalculates, so the order may change. Make sure the cells below B2 are empty to avoid a spill error.
Understand recalculation and keep a result
RANDBETWEEN and RAND are volatile functions: Excel can recalculate them when a calculation occurs, including after edits elsewhere in the workbook. A random value that changes later is not necessarily a formula error; it is expected behavior. Pressing F9 on Windows recalculates the workbook and can produce new values. On a Mac, the equivalent may be Fn+F9, depending on the keyboard and system settings.
To preserve a particular draw, copy the result cells and replace the formulas with their current values. In Excel for Windows, use Home > Paste > Paste Values. This keeps the numbers in place while removing the random formulas that would otherwise recalculate. On Excel for Mac, use the Paste Special values option; its exact menu presentation can vary with the ribbon and app version.
Calculation mode also affects when results update. In Excel for Windows, check Formulas > Calculation Options > Automatic. If the workbook is set to Manual, formulas may not update until you trigger a calculation. Before distributing a workbook that relies on random formulas, decide whether readers should see live-changing results or a fixed sample.
Duplicates, fairness, and common problems
Generating several random integers does not guarantee that they will all be different. Each RANDBETWEEN call is a separate draw, and the same result can occur more than once. For example, ten draws from 1 to 10 might contain a duplicate and omit another number. A wider range makes collisions less likely but does not prevent them.
If you need a shuffled set of distinct existing entries, use SORTBY with RANDARRAY as shown above. If you need unique random integers from a numeric range, the requirement is different: you need to sample without replacement, not simply repeat RANDBETWEEN until the results look distinct. Repeated draws can be inefficient and require duplicate checking. For a controlled worksheet, create the complete set of eligible numbers once and shuffle that set.
Uniform does not mean that a short sample will look evenly distributed. In a small batch, repeated values or apparent clusters are normal. RANDBETWEEN gives each integer in its specified range an equal chance for each draw, but equal chances do not promise equal counts in a limited number of draws.
#NUM!: check that the lower limit is not greater than the upper limit and that both inputs are valid numbers.- Unexpected decimals: use RANDBETWEEN for integers, or use a scaled integer formula when you need a fixed increment.
- Results keep changing: replace formulas with values after generating the sample.
- Repeated values: duplicates are allowed unless you build a distinct list or shuffle unique source values.
Frequently Asked Questions
Does RANDBETWEEN include the minimum and maximum?
Yes. RANDBETWEEN includes both its bottom and top arguments. A formula with limits 1 and 10 can return either 1 or 10.
How do I generate a random number with decimals in Excel?
Use RAND scaled to the range. With the lower limit in A1 and upper limit in B1, enter =RAND()*(B1-A1)+A1. It returns a decimal from the lower limit up to, but not including, the upper limit.
How can I stop random numbers from changing?
Copy the cells containing the random formulas, then use Home > Paste > Paste Values on Windows. The current results remain, but the formulas no longer recalculate.
Can Excel generate random numbers without duplicates?
Not with repeated independent RANDBETWEEN formulas alone. To randomize a unique list, sort it by random keys with SORTBY and RANDARRAY in Microsoft 365 or Excel 2021, or use another sampling-without-replacement method appropriate to your workbook.
Why does my random-number formula show #NUM!?
For RANDBETWEEN, the bottom value must not be greater than the top value. Check the limits and confirm that the referenced cells contain valid numeric values.
Related
- How to Find and Clear a Circular Reference in Excel
- How to Enter an Absolute Cell Reference in Excel
- How to Use Absolute Cell References in Excel on Dell
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Schedule an Email in Outlook: Steps and TipsTo schedule an email in Outlook, write the message, open the menu beside Send, choose Schedule send, and…
- How to Add a Dropdown in Google Sheets: Step-by-StepTo put a dropdown in Google Sheets, select the cell or range, choose Data > Data validation >…
- Track Changes in Word: How to Turn It Off and FinishTo get out of Track Changes in Word, open the Review tab and turn off Track Changes. This…
- How to Add Reference Footnotes in Word, Step by StepTo add a reference footnote in Word, place the cursor after the passage you are citing, choose References…
- Excel Not Responding to Keyboard? Causes and FixesIf Excel is not responding to your keyboard, first check whether the cell, worksheet, or a dialog box…
- How to Unblock a Sender in Outlook Email on Any DeviceTo unblock a sender in Outlook, remove their email address or domain from your blocked senders list. In…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.