RANDBETWEEN Function in Excel

What this does

RANDBETWEEN returns a random integer between two bounds, both included. Like every random function in Excel it is volatile: it recalculates on every edit anywhere in the workbook, so the numbers change constantly. That is the single most important thing to know about it, because a "random sample" that reshuffles every time someone types is not a sample. The standard fix is to generate the values, then copy and Paste Special as Values to freeze them. Both bounds must be integers, and bottom must not exceed top or the result is #NUM!. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. For “randbetween function in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Generating a test dataset of scores: =RANDBETWEEN(1, 100) filled down 200 rows, then frozen with Paste Special → Values. Picking a random row from a 500-row table: =INDEX(A2:A500, RANDBETWEEN(1, 499)). Simulating a die roll: =RANDBETWEEN(1, 6). RANDBETWEEN is the fastest way to build realistic test data — provided you freeze it, which is the step most people skip. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. If you take one thing from this page on “randbetween function in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

Common mistakes

• Leaving the formulas live and treating the result as a fixed sample — every keystroke in the workbook reshuffles them.
• A bottom greater than top, which returns #NUM!.
• Expecting no repeats; RANDBETWEEN draws independently, so duplicates are normal. For a unique shuffle, sort by RAND instead.

Frequently asked questions