Formula Friday- Excel Tip- Generate Random Numbers With Excel.


Do you need to generate random numbers for a project? Excel can help. This guide will show you how to use the RAND and RANDBETWEEN functions to create random numbers in Excel. You can also learn some tricks for formatting and using these numbers in your projects. Let’s get started!

What Are Random Numbers?

Before we begin, you might ask what are random numbers?. Random numbers are numbers that occur in random order. They are not predetermined and have no pattern. The best way to generate random numbers is through the use of a random number generator, which can be found in Excel. When generating random numbers, it is important to ensure that the quality of the randomness is high, as this can have a significant impact on the results of any analysis or research.

Why Generate Random Numbers?

There are many reasons why you might need to generate random numbers. Perhaps you’re conducting a statistical analysis and need a set of random data points to work with. Or maybe you’re developing a computer simulation that requires random inputs. I tend to generate random numbers is to populate and test Excel worksheets as I develop them.  They are great for giving my formulas and functionality a great workout. Whatever the reason, Excel has a built-in functions that can help.

Options For Generating Random Numbers.

There are two built in Excel function to generate are RAND and RANDBETWEEN. The RAND function in Excel is used to generate random numbers. The RAND function generates a random number between 0 and 1, while the RANDBETWEEN function lets you specify a range of numbers. To use either function, simply enter the =RAND() or =RANDBETWEEN() function into a cell. The numbers generated by the RAND function are pseudo-random, which means that they are not truly random but are generated using a formula. However, for most purposes, the pseudo-random numbers generated by Excel will suffice. Read on to go inot more details about each of the functions.

RAND Function

So, the RAND function in Excel is used to generate random numbers between 0 and 1. Follow the instructions below. It is VERY simple.

  • Select the cell to insert a random number
  • Type =RAND() and hit enter
  • RAND takes no extra arguments
  • You now have a random number between 1 and 0
random numbers in Excel with RAND

To generate more numbers drag the formula down or across rows and columns.

RANDBETWEEN Function

This formula will allow you to specify the range of numbers Excel has to pick from.  The syntax for the formula is

=RANDBETWEEN(BOTTOM,TOP)

Bottom- the LOWEST number the function is to use

Top – the HIGHEST number the function can use

So, let’s set up our test area as below.  We want to generate random numbers between 10,000 and 100,000 (let’s say this number represents for the number of sales leads for each of our employees in our data set.  There are a few ways to get the RANDBETWEEN function.  You can either

  • Select the cell you want to start the random numbers, in this example I have chosen C2.
  • Hit the = sign and start typing RAND, you should see the RANDBETWEEN function appear
  • Type the Bottom number, hit comma then type the top number then close brackets
random numbers in Excel

Alternatively you can

  • Click on the Formulas Tab of the Ribbon Menu
  • Choose Math & Trig
  • Choose RANDBETWEEN and bring up the dialog box

All we need to do now is

  • Hit the Bottom line in the dialog box and there and enter 10,000
  • Hit the Top line in the dialog box and enter 100,000
  • Click ok and a random number will be generated ( just hit F9 and another one will be generated)- go ahead and test it

We now will want to generate a whole column of figures, so just drag the fill handle at the bottom right of cell C2 right down the column – in this example it is down to cell C15.

excel random numbers

A random list of numbers is now generated.  Job done.  If you want to prevent the numbers being regenerated then just select the column of cells and paste back using Paste Special.  This cements the number in place, so it will not change for you.

 More Excel Tips

Excel Macro

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts