Generate Random Numbers in Excel: RAND and Macros

Generating random numbers in Excel can be useful for a number of applications, from statistical sampling to computer simulation.  Even gambling.   Sometimes an unpredictable result is exactly what you need.  In Excel, there are several ways to generate random numbers depending on your requirements and skill level.  The first thing we’ll look at is the RAND function and some of its variations.  Once we have that down, we’ll write a simple macro to generate random numbers, too.  Ultimately, macros will give you the most comprehensive power, and you can learn all about their amazing capabilities in this macro and VBA tutorial.

The most common random number generator (RNG) in Excel is the RAND function.  There are two ways to enter RAND.  Because it’s such a short function, typing it manually is arguably the quickest, and a general rule of thumb is to learn the long-hand version first, thereby making you worthy of the shortcut.  The RAND syntax is: =rand().  See?  It’s practically a shortcut already.  Note: in the newer versions of Excel, as you begin to type, you will be provided with a list of relevant functions to choose from:

You can also use the Formula Builder to insert RAND.  Even if you decide to enter RAND manually, the Formula Builder is an invaluable tool to easily and reliably attach longer, more difficult formulas.  To locate the Formula Builder, click on the Formulas tab on your Ribbon bar (the Ribbon bar is the green (Excel 2011) bar with a Home tab on its extreme left side):

Find and click on the Formula Builder button that appears below.  This will bring up a dialogue box:

Type in “rand” in the search bar and hit enter.  Several options should appear, but the ones in which we are most interested are RAND and RANDBETWEEN.  For now, double-click RAND.  It will be entered into the cell you preselected.  Tab over and a number between 0 and 1 will be generated in the cell; this is what RAND does, every time.  You can drag the fill handle to the range that you want to contain this formula:

For generating numbers between 0 and numbers greater or less than 1, modify the RAND syntax in the following way: =rand()*x, in which “x” is a number of your choosing.  To generate numbers between 0 and 50, your syntax should look like this: =rand()*50.

For generating random numbers in absolutely any range of your choosing, you will use the RANDBETWEEN function.  This can be found in your formula builder, or you can quickly enter it manually: =randbetween(x,y), in which “x” is the bottom number and “y” the top number.  You’re probably getting the picture by now, but if you want to generate numbers between 35 and 75, your function should look like this: =randbetween(35,75).  And again, simply drag the fill handle over your desired range.

Now, let’s look at something a little more advanced.  First, we have to locate the macro builder.

• Click Excel on the title bar
• Go to Preferences
• Under Sharing and Privacy, click Ribbon
• There will be a list: Tab or Group Titles.  Scroll down until you see Developer and select it.

Your Ribbon bar will now display a Developer tab, and this is where you will find Macros (second from the left):

Click on Macros to launch the Macro dialogue box.  Name your macro GenerateRandom and click Create:

This is the code you will write (or copy/paste) into the field:

This one is pretty self-explanatory.  Once we run the macro, we can expect random numbers between 0 and 1 to generate in rows 1-100 in column A.

So.  To run the macro, exit the Macro dialogue box.  Now all you have to do is click Macros again, select GenerateRandom from your list, and hit Run:

Every time you run the macro, the numbers will re-generate.  You can see how useful this would be if you wanted to generate, say, ten-thousand random numbers.  You can also see how, with a few small modifications, anyone can tailor a simple piece of code to accomplish a variety of tasks.  People have written millions of macros and proudly display them online for public use.  Have some fun Googling cool Excel macros and see what you discover.  Often you can just cut and paste someone else’s hard work and instantly own an awesome, complex piece of code.  Learning to write your own macros is time well spent, but you’re likely to need more than a Google search.

Macros have you stressed?  Become a Zen Master of data (yes, they actually exist) and bridge the gap between “data” and “insight.”