Generating Numeric Testing Data

Written by Allen Wyatt (last updated August 12, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


Tom needs to generate some numeric testing data that consists of random numbers that are greater than or equal to 100.01 and less than or equal to 99,999.99. His dataset should consist of at least 10,000 numbers, and they need to be sorted in ascending order. Tom knows he can do this using a bunch of individual steps and helper columns, but he wonders if there is a way to generate the numbers using just a single formula.

The traditional way of doing this involves using helper columns or multiple steps. All you would need to do is to place this formula into a cell:


Copy it down for 10,000 cells and you'll have your random numbers, to two decimal places. You can then copy all the values and use paste special to convert them to values. Finally, you can sort the values into ascending order.

If you are using the newest versions of Excel, you could remove one of the steps (the need to sort) by using this formula:


Provided there is nothing in the 10,000 cells below where you insert this formula, you'll end up with a sorted list of values in the desired range. If you want to limit the values to two decimal places, you could modify the formula in this manner:


Finally, you could ensure that no values are repeated (something that is not possible by any of the formulas so far presented) by expanding the formula just a bit:


Be aware, though, that if you use the UNIQUE function, the list of values will be reduced by the number of duplicate values in the original list. Thus, you may end up with a list of only 9,994 or 9,998 values instead of 10,000. For that reason, you may want to increase the 10000 parameter in the formula to a slightly larger value.

Regardless of the formula you choose to use, you'll quickly find out that the results are volatile, meaning that they update every time you do something else in your worksheet. You'll want to copy the values and use paste special to make the values static.

If you have to generate lists of values regularly, you may be interested in a macro to do the work for you. The following macro adds a worksheet and then, in column A, places 10,000 random values using the same RANDBETWEEN formula introduced earlier. It then sorts the values in column A so that you end up with a sorted list.

Sub RandNums()
    Dim Rownum As Integer

    For Rownum = 1 To 10000
        Cells(Rownum, 1) = Application.WorksheetFunction.RandBetween(10001, 9999999)/100
    Next Rownum

    With ActiveSheet.Sort
        .SortFields.Add Key:=Range("A1:A10000"), SortOn:=xlSortOnValues, _
          Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:A10000")
    End With
End Sub


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 (4273) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

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. ...


Adjusting Column Width from the Keyboard

It's easy to adjust the width of table columns using the mouse, but what if you don't want to use the mouse? Adjusting ...

Discover More

Separating Evens and Odds

If you have a series of values in a column, you might have a need to separate the values into even values and odd values. ...

Discover More

Selecting Objects

Windows uses a graphical user interface that requires the manipulation of objects that appear on the screen. ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

First Value Less Than or Equal to 100

If you need to evaluate a row of values to meet specific criteria, then you'll appreciate the discussion in this tip. It ...

Discover More

Number of Terms in a Formula

Formulas are made up of operands that separate a series of terms acted upon by the operands. You may want to know, for ...

Discover More

Throwing Out the Lowest Score

Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 six more than 3?

2023-08-17 14:56:07

J. Woolley

My previous comment below discussed Excel 365's TAKE function, which is NOT included in Excel 2021 or earlier. The following function in My Excel Toolbox provides capability similar to the TAKE function as well as Excel 365's DROP and EXPAND functions:
=Resize(RangeArray, [Rows], [Cols], [PadVal])
where RangeArray is a contiguous cell range or array constant or result of an array function. Rows is the number of rows to pick from the start, or ABS(Rows) from the end if < 0; default is 0 for all rows. Cols is the number of columns to pick from the start, or ABS(Cols) from the end if < 0; default is 0 for all columns. If ABS(Rows) or ABS(Cols) expands RangeArray, then added rows/columns are optionally set to PadVal.
Here is the formula from my previous comment using Resize instead of TAKE:

2023-08-16 02:19:50

Michael van der Riet

This works fine as long as you realise that this generates pseudo-random sets and is unsuitable for heavy-duty apps. Best practice is to download true randoms. There are several pages that offer this, usually at no charge up to about a hundred thousand numbers. To reduce the hassle factor when I was calculating the odds on card games, I downloaded one large set that I could use many times, and used a pseudo-random to determine the offset into the set.

2023-08-13 11:52:30

J. Woolley

The Tip says, "...if you use the UNIQUE function, the list of values will be reduced by the number of duplicate values in the original list." This can be resolved by generating a larger list (like 10,123), then using Excel 365's TAKE function:
Note: The TAKE function is NOT included in Excel 2021 or earlier.

This Site

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

Newest Tips

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.