Using RANDBETWEEN Function to Generate Random Numbers in Excel

Last Modified: June 16, 2024 - 5 min read

Hannah Recker
randbetween excel

Introduction To RANDBETWEEN Function

The RANDBETWEEN function in Excel is used to generate a random integer (whole number) between a lower and higher limit number.

You can use it to generate random numbers which you can use for simulations, sampling, or creating random data sets.

It’s a fairly straightforward function, so let’s dive into how to use it.

Understanding RANDBETWEEN Function

The syntax for the RANDBETWEEN  function is: =RANDBETWEEN(bottom, top)

Where, bottom and top are integer values that define the lower limit and upper limit, respectively, between which you want to generate the random number.

Both these arguments have to be whole numbers too, including negative numbers.

Let’s look at some practical examples of how this function works.

Practical Use Cases Of RANDBETWEEN

Here are some practical use cases of the RANDBETWEEN function in Excel:

Example #1: Generating A Random Number Between Any Two Values

All you have to do to generate a random number between any two numbers (in this case, 0 and 99) is to use this formula: =RANDBETWEEN(0,99)

randbetween to generate random number between any two numbers

Cells E3, E4, and E5 contain the same formula, but the function has generated a random number between the defined lower and upper values.

Example #2: Generating Random Decimal Numbers

Even though the RANDBETWEEN function only returns whole numbers, you can force it to return a random decimal number. You can achieve this by multiplying the lower and upper values by 10, 100, 1000, etc. and dividing by the same number for a random number that has one, two, three decimal places.

If you use the formula: =RANDBETWEEN(11*100,49*100)/100

randbetween decimal number

The function will return a random number that has two decimal places.

Example #3:  Generating Random Text Strings

Since Excel uses the numbers 1-255 to represent characters, you can force the RANDBETWEEN function to return a string of random characters.

To do this, use the formula: =CHAR(RANDBETWEEN(1,255))&CHAR(RANDBETWEEN(1,255))&CHAR(RANDBETWEEN(1,255))

randbetween to define which random characters with arguments

This returns the three random characters.

You can restrict/define which random characters to generate from by defining the arguments for RANDBETWEEN from the ANSI character table.

For example, this formula will restrict the random characters generated to mostly upper and lower case letters: =CHAR(RANDBETWEEN(65,122))&CHAR(RANDBETWEEN(65,122))&CHAR(RANDBETWEEN(65,122))

randbetween example 3 character text string

When using the RANDBETWEEN function the manner demonstrated in these examples, there are some things you should be mindful of.

Managing RANDBETWEEN Results

Unlike other functions that provide a static result, RANDBETWEEN generates a new random number every time the worksheet is recalculated or the cell is edited. By refreshing the random number with each calculation, RANDBETWEEN ensures that the data remains dynamic and responsive to changes in the worksheet.

However, this refresh behavior can also be a source of frustration if you need to maintain a consistent set of random numbers for a specific purpose. A simple solution to this problem is to use the Paste Special feature in Excel.

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 314,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Using Paste Special To Fix Values Post-Randomization

Using Paste Special allows you to “lock in” the randomized values, preventing them from changing with subsequent calculations or formula updates.

Here’s how to implement this:

Step #1: Generate the initial random values using the RANDBETWEEN function.

paste special to lock in randomized values in excel

Step #2: Select all the cells containing the randomized values, right click, and choose “Copy”.

paste values only to lock in randbetween values

Step #3: Right-click again (in the same cell or a different one) and select “Paste Special”, and then choose the “Values Only” option.

paste values only in excel

You will notice that as soon as you’ve pasted the values, the values in the cells with the RANDBETWEEN function change to a new set of random values.

Video Tutorial

Check out the tutorial below for a complete video walkthrough!

Handling Errors When Using RANDBETWEEN in Excel

Despite being simple to use, you could still run into situations where you get an error when using the RANDBETWEEN function.

FormulaErrorWhy It Occurred
=RANDBETWEN(1,10)#NAME?The spelling of RANDBETWEEN is wrong
=RANDBETWEEN(a,z)#NAME?The arguments are letters, instead of numbers
=RANDBETWEEN(10,1)#NUM!The lower argument is greater than upper

Resolve the issues by tackling the reason why the error occurred.

Now that you understand how the RANDBETWEEN function works, how to use it, and handle common errors, let’s wrap it up.

Using The Excel RANDBETWEEN Function

The Excel RANDBETWEEN function can be used to generate a random whole number. And with a few clever modifications, it can generate decimal numbers or even a string of characters. We’ve also shown you how to resolve the issue of the RANDBETWEEN function recalculating values with every change or refresh.

Functions like this are relatively easy to use when handling small, static data sets. But what if you’re handling large amounts of dynamic data and want to make sure all your calculations are on point?

Say hello to Coefficient, the leading spreadsheet automation tool which can help you connect all your data sources, automate workflows, and share live insights within Excel. Get started today.

Sync Live Data into Excel

Connect Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies