Generate random data

Excel can generate random data. It is easiest to generate random numbers. The random numbers are going to be generated with a specific number of characters. Then alphabetical strings with a specific number of characters are going to be generated. Finally alphanumeric strings with a specific number of characters will be generated. In all cases, the user is going to set how many characters are returned.

Here is an example of nine character alphanumeric output generated in a 10x10 grid.

Random numeric data

Excel has the RANDBETWEEN function. A bottom number and a top number are specified. Excel will generate a random whole number between the bottom and top number.

This formula will generate an integer between and including 1 and 9.

=RANDBETWEEN(1,9)

The user is able to enter the number of digits to return, between 1 and 9. That number is entered into a predetermined cell and is used in the formula to determine the bottom and top numbers.

To determine the bottom number use the CHOOSE function, that returns a value based on position. The position will be the user entered value, a number between 1 and 9.

=CHOOSE($C$8,1,10,100,1000,10000,100000,1000000,10000000,100000000)

If the user value is set to 6, the sixth value of 100000 will be returned.

The top number is determined the same way with a slightly different formula.

=CHOOSE($C$8,9,99,999,9999,99999,999999,9999999,99999999,999999999)

If the user value is set to 6, the sixth value of 999999 will be returned.

To get a random number between the bottom and the top number, use a formula like this.

=RANDBETWEEN(100000,999999)

or combining all the formulas together.

=RANDBETWEEN(CHOOSE($C$8,1,10,100,1000,10000,100000,1000000,10000000,100000000),CHOOSE($C$8,9,99,999,9999,99999,999999,9999999,99999999,999999999))

The RANDBETWEEN function is volatile. This means that every time the workbook recalculates, the formula result will change. The workbook recalculates every time a value is changed.

Generate random data

The final step is to make the formula spill based on user parameters. This means the formula only has to be entered once. The formula is inserted into a LAMBDA function, which is called by a MAKEARRAY. The dimensions of the MAKEARRAY are determined by user input.

"=MAKEARRAY($C$20,$C$21,LAMBDA(Row,Column,RANDBETWEEN(CHOOSE($C$8,1,10,100,1000,10000,100000,1000000,10000000,100000000),CHOOSE($C$8,9,99,999,9999,99999,999999,9999999,99999999,999999999))))"

Random alphabetical data

It is slightly more difficult to create alphabetical strings in Excel. Excel has the RANDBETWEEN function. It can be used to generate random numbers between 65 and 90. These are the ASCII code page numbers for the capital letters A to Z. The number can be converted to a letter by using the CHAR function.

This formula will generate a number between and including 65 and 90.

=RANDBETWEEN(65,90)

This can be converted to a letter using CHAR.

=CHAR(RANDBETWEEN(65,90))

This returns a single letter.

To return an alphabetical string where the user has defined the string length, the formula needs to be enclosed in a LAMDA function. Then use BYROW and SEQUENCE to call the LAMBDA. The resulting array is joined into a single string using TEXTJOIN.

=TEXTJOIN("",TRUE,BYROW(SEQUENCE($C$8),LAMBDA(Single_Alpha,CHAR(RANDBETWEEN(65,90)))))

If the $C$8 cell holds a value of 9, the function will return an alphabetical string, similar to this string “TYHWPGBXQ”.

The RANDBETWEEN function is volatile. This means that every time the workbook recalculates, the formula result will change. The workbook recalculates every time a value is changed.

Generate random data

The final step is to make the formula spill based on user parameters. The formula is inserted into another LAMBDA function, which is called by a MAKEARRAY. The dimensions of the MAKEARRAY are determined by user input.

=MAKEARRAY($C$30,$C$31,LAMBDA(Row,Column,TEXTJOIN("",TRUE,BYROW(SEQUENCE($C$8),LAMBDA(Single_Alpha,CHAR(RANDBETWEEN(65,90)))))))

Random alphanumeric data

Finally, creating random alphanumeric strings. The principles used are the same as those used to produce the alphabetical data. The chance of a number being generated versus a letter is going to be given equal weighting (50/50).

The first additional formula is RANDBETWEEN to return numbers between 48 and 57. These are the ASCII code page numbers for numbers from 0 to 9. The number can be converted to a [0-9] number by using the CHAR function.

Depending on how the data is being used, it might be a good idea to only produce numbers between 1 and 9. This is to avoid visual confusion between the letter O and the number 0.

Then the formula needs to choose which RANDBETWEEN to use, the one that produces letters or numbers. This is done by using another RANDBETWEEN that returns a zero or a one.

=RANDBETWEEN(0,1)

The result is used to make the choice with an IF statement. If a zero is returned, produce a letter otherwise a number.

=CHAR(IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(65,90),RANDBETWEEN(48,57)))

To call the function multiple times, it is put into a LAMBDA. Then BYROW and SEQUENCE are used to call the LAMBDA. The resulting array is joined into a single string using TEXTJOIN.

=TEXTJOIN("",TRUE,BYROW(SEQUENCE($C$8),LAMBDA(Single_Alpha_Num,CHAR(IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(65,90),RANDBETWEEN(48,57))))))

Generate random data

=MAKEARRAY($C$33,$C$34,LAMBDA(Row,Column,TEXTJOIN("",TRUE,BYROW(SEQUENCE($C$8),LAMBDA(Single_Alpha_Num,CHAR(IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(65,90),RANDBETWEEN(48,57))))))))