Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Generating Random Testing Data.

# Generating Random Testing Data

by Allen Wyatt
(last updated March 4, 2019)

When you develop worksheets that will be used by other people, you should test those worksheets to make sure that they work as you expect. This is particularly true of worksheets that contain complex formulas or will be used for critical purposes. The concept of testing a worksheet means that you will need to generate some sort of data to use in testing the worksheet.

Entire books have been written on putting together testing suites for software. How rigorous you are in compiling test data depends, in large part, on the needs of your audience and the nature of your worksheet. Unfortunately, there is no quick cure-all that will automatically figure out what you need and generate the data for you. There are tools in Excel, however, that you can use toward this end.

First, if you need to generate random numeric values, you can use the RAND or RANDBETWEEN worksheet functions. The difference is that RAND generates a value between 0 and 1, and RANDBETWEEN generates integer values between any bounds you set.

Random data may not be appropriate for your testing needs, however. This is particularly true when you are testing boundaries of your formulas. For instance, testing with large values, small values, or a combination of large and small values. Likewise, you may want to test for inappropriate values, such as using text as input rather than numbers (or vice versa). There are a whole contingent of conditions you need to think through, and then pick the type of data that is right for your needs.

If you prefer, you can use macros to generate testing data. The following macro fills a selected range of cells with a random numeric value, between whatever boundaries (minimum and maximum) that you set.

```Sub RandNums()
Dim MyRange As Range
Dim lMin As Long, lMax As Long
Dim dRand As Double

' If selection is not Excel Range
If TypeName(Selection) <> "Range" Then Exit Sub

Set MyRange = Selection

' Get Min and Max value
lMin = CLng(InputBox("Minimum?"))
lMax = CLng(InputBox("Maximum?"))

Randomize
Application.ScreenUpdating = False

For Each c In MyRange.Cells
' Calculate random value, where
' Value >= Min And Value <= Max
dRand = Rnd * (lMax - lMin) + lMin

' Use the following line only if the random
' value should be an integer
' dRand = Int(dRand)

c.Value = dRand
Next c

Application.ScreenUpdating = True
End Sub
```

To use the macro, just select a range of cells that you want to contain random numeric values, and then run the macro. If you must use integer values in the cells, then you can "uncomment" the noted line within the macro.

If you want to fill a range of cells with random dates, then a slight modification to the RandNums macro results in the following.

```Sub RandDates()
Dim MyRange As Range
Dim dtMin As Date, dtMax As Date
Dim dtRand As Date

' If selection is not Excel Range
If TypeName(Selection) <> "Range" Then Exit Sub

Set MyRange = Selection

' Get Min and Max value
' From: 1/1/1990 (put your start Date)
dtMin = #1/1/1990#
' To: Today
dtMax = Date

Randomize
Application.ScreenUpdating = False

For Each c In MyRange.Cells
' Calculate random value, where
' Value >= Min And Value <= Max
dtRand = Rnd * (dtMax - dtMin) + dtMin
dtRand = Int(dtRand)

c.Value = dtRand
' Change format for cell, below, as desired
c.NumberFormat = "m/d/yyyy"
Next c

Application.ScreenUpdating = True
End Sub
```

Again, just select a range and then run the macro. You can modify the initial values set to the dtMin and dtMax variables in order to specify the boundaries for the dates desired. You can also, if desired, change the formatting applied to the cells after the random date is stored within the cells.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2077) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Generating Random Testing Data.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Word allows menus to be displayed in two modes. The default mode, which displays menu options dynamically, drives some ...

Discover More

Controlling Endnote Placement

Endnotes are often used in technical and scholarly documents. You can control exactly where the endnotes appear in your ...

Discover More

Ribbon Acting Strangely

The ribbon is the place where Word stores all the tools you need to work with your documents. What happens when the ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

Getting Context-Sensitive Help

Need help on a particular topic, dialog box, or command? You can use Excel's powerful help system to get just the ...

Discover More

Clearing Large Clipboard Entries

Need to clear out a large amount of information saved on the Clipboard? All you need to do is to replace it with a small ...

Discover More

Want to cancel whatever menu command you started to use in Excel? The normal method is by pressing Esc, but this is not ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 1?

2018-11-03 18:24:59

Michael Bennett

How about once a column of randomly generated numbers is found, then a second column of randomly generated numbers are found that each (line by line) are equal or greater than amount in first column.

For instance

A1 670, B1 909
A2 59, B2 121

etc.

Or the opposite, randomly generated numbers in Column B equal to or less than randomly generated numbers in Column A?

2018-11-02 06:42:58

sheela

hi ,

i have added randbetween function for generating random characters but it is calculating only when some operation is performed in excel like if i copy paste some thing then that random numbers are getting changed or i should close and open the excel.

As i want to pass random data for my automation script ,this is not helping me in generating random generation of characters without manual intervention.

Formula which i have used:

=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(97,122)) & CHAR(RANDBETWEEN(97,122)) & CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122)) & CHAR(RANDBETWEEN(97,122)) & CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))

please give me an idea to resolve it

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.