RandBetweenText Formula in Excel: Random Text from a List

The RandBetweenText formula in Excel does for text what RANDBETWEEN does for numbers: it returns a random item from a list you choose, such as a random employee, product or country. Excel has no such function built in, so PK wrote it as a small VBA user-defined function (UDF). In this tutorial you learn how to use it to generate a dummy data set in seconds, how to make it smarter with named ranges and INDIRECT, and how the VBA code works line by line. At the end you also get formula-only alternatives with INDEX, CHOOSE and RANDARRAY.

⬇️  Download the RandBetweenText Practice File
ZIP (16 KB) with a macro-enabled .xlsb workbook · enable macros to run the UDF · Excel 2010 and later

Watch the Video Tutorial

PK builds a random dummy table with RandBetweenText, switches to named ranges and INDIRECT so one formula fills every column, and then writes the UDF from scratch in a new module.

What You Will Build

• A custom RandBetweenText function that returns a random value from any range.
• A dummy data table with random Employee, Gender, Product, Location and Sales columns.
• One copyable formula that reads the right list from the column header with INDIRECT.
• Formula-only alternatives for workbooks where macros are not allowed.

Part 1: Use RandBetweenText to Create Dummy Data

Step 1: Set up the lists

PK’s practice file has a List sheet with one column per field: Employee in A2:A11 (PK, Charlie, Thomas, Olivia, Lily, Lucy, Alax, Rosie, Emma, Leo), Product in B2:B4 (Mobile, Laptop, Tablet), Location in C2:C5 (India, USA, UK, Germany) and Gender in D2:D3 (Male, Female). On the Data sheet, row 1 holds the headers Employee, Gender, Product, Location and Sales.

Step 2: Random numbers for Sales

Numbers need no custom code. In E2 enter =RANDBETWEEN(100,500) and fill it down; every cell gets a whole number between 100 and 500.

Step 3: Random text with RandBetweenText

In A2 type =RandBetweenText(, switch to the List sheet, select A2:A11, press F4 to lock the range and close the bracket:

=RandBetweenText(List!$A$2:$A$11)

Do the same for Gender, Product and Location with their own lists, then fill all four columns down. Press F9 (or Formulas → Calculate Now) and every value changes, exactly like RANDBETWEEN.

Part 2: Named Ranges and INDIRECT

Step 4: Name each list

Select A2:A11 on the List sheet, go to Formulas → Name Manager → New and name it Employee. Now the formula is easier to read:

=RandBetweenText(Employee)

Create Product, Location and Gender the same way. In the video PK creates all four at once with the Create Names button of his free PK’s Utility Tool add-in; Excel’s own Formulas → Create from Selection (top row) does the same job. The practice file uses Employee =List!$A$2:$A$11, Product =List!$B$2:$B$4, Location =List!$C$2:$C$5 and Gender =List!$D$2:$D$3.

Step 5: One formula for every column

Because each name matches a header on the Data sheet, INDIRECT can turn the header text into the range:

=RandBetweenText(INDIRECT(A$1))

The dollar sign before 1 locks the header row while the column letter moves. Enter it in A2 and copy it across A2:D39: column A reads Employee, column B reads Gender and so on, so each column draws from its own list.

Part 3: The RandBetweenText VBA Code

Step 6: Insert a module and paste the function

Press Alt+F11, choose Insert → Module and paste this code. It is the exact code from the practice file:

Option Explicit

Function RandBetweenText(Input_Value As Range) As String

Application.Volatile

Dim rng As Range
Dim Random_Values As String

For Each rng In Input_Value
    If Random_Values = "" Then
        Random_Values = rng.Value
    Else
        Random_Values = Random_Values & "," & rng.Value
    End If
Next

Dim arr As Variant
arr = Split(Random_Values, ",")

Dim n As Integer
n = UBound(arr)

RandBetweenText = arr(Application.WorksheetFunction.RandBetween(0, n))

End Function

Save the workbook as .xlsm or .xlsb, otherwise the code is removed when you save.

How the code works

• Function … As String: a Function (not a Sub) can be used in a cell like any Excel formula. It takes one range and returns one text value.
• The For Each loop visits every cell of the input range and builds one comma-separated string. The first cell starts the string; every later cell is added after a comma, giving for example Mobile,Laptop,Tablet.
• Split cuts that string at each comma and returns an array. Array positions start at 0, so the three products sit at 0, 1 and 2.
• UBound(arr) is the last position, here 2. UBound + 1 would be the count, but the code needs the last position.
• RandBetween(0, n) picks a random position from 0 to 2, and the function returns the item stored there.
• Application.Volatile makes the function recalculate whenever the sheet recalculates. Without it the result would only change when the input range changes.

In the video PK types the same logic from scratch under the name Random_Text, and tests it with =Random_Text(Employee). The function name is up to you. To learn more about Function procedures, see the user-defined functions chapter of the VBA course.

Modern Excel Alternatives (No VBA)

These are additions, not from the video. They work without macros, which matters when you share the file.

INDEX with RANDBETWEEN (any version)

=INDEX(Employee,RANDBETWEEN(1,ROWS(Employee)))

ROWS counts the items, RANDBETWEEN picks a position from 1 to that count, and INDEX returns the item. With the header trick from Step 5 it becomes =INDEX(INDIRECT(A$1),RANDBETWEEN(1,ROWS(INDIRECT(A$1)))).

CHOOSE for a short fixed list

=CHOOSE(RANDBETWEEN(1,2),"Male","Female")

Handy for two or three options typed straight into the formula, with no list on the sheet.

RANDARRAY (Microsoft 365 and Excel 2021)

=INDEX(Employee,RANDARRAY(38,1,1,ROWS(Employee),TRUE))

RANDARRAY returns 38 random whole numbers between 1 and the list size, and INDEX turns them into 38 random names in one spilling formula, enough to fill A2:A39.

Common Errors and Fixes

Additions from general Excel and VBA practice.

#NAME? instead of a value

Macros are disabled or the code is in another workbook. Enable content for the file, and keep the function in a standard module of the same workbook (or in an add-in).

A value is cut in two

The function joins values with commas and splits on commas, so an item like New Delhi, India becomes two items. Remove commas from the list or use the INDEX alternative.

Sometimes the result is empty

The selected range contains blank cells, which become empty items. Select only the filled cells or use a named range that ends at the last item.

Tips

• Random formulas change on every recalculation. When your dummy data is ready, copy it and use Paste Special → Values to freeze it.
• Weight the result by repeating an item in the list: a list with Laptop twice returns Laptop more often.
• Keep all lists on one sheet, named after the headers, so the INDIRECT formula works for any new column.
• Microsoft documents the RANDARRAY function with more examples.

Want It Ready-Made?

If you need dummy data because you are building a report, you can skip the build entirely: the Excel templates collection on NextGenTemplates.com has ready dashboards and trackers with sample data included. To master RANDBETWEEN, INDEX, INDIRECT, RANDARRAY and 100 other formulas step by step, join the course Excel + AI Mastery: 100 Essential Formulas and Real-World Projects.

Related Tutorials

• User Defined Functions in Excel VBA
• Excel INDEX Function: Syntax and Examples
• Dependent Drop-Down List in Excel with INDIRECT and Named Ranges
• PK’s Utility Tool: Free Excel Productivity Add-in
• Excel VBA Course: From Macro Recording to ADODB

Frequently Asked Questions

Is RandBetweenText a built-in Excel function?

No. It is a user-defined function written in VBA, so it only works in a workbook that contains the code, saved as .xlsm or .xlsb with macros enabled.

Why do the values change every time I edit the sheet?

The function uses Application.Volatile, so like RANDBETWEEN it recalculates whenever Excel recalculates. Paste the results as values to keep them fixed.

How can I pick random text without VBA?

Combine INDEX with RANDBETWEEN and ROWS, or use RANDARRAY in Microsoft 365 and Excel 2021. The Modern Excel Alternatives section shows each formula.

Can RandBetweenText use a named range?

Yes. Pass the name directly, or use INDIRECT with a header cell that has the same text as the name, so one formula can be copied across several columns.

Why does a list item with a comma break the result?

The code joins the values with commas and then splits them on commas, so any comma inside a value creates an extra item. Remove the commas or switch to the INDEX formula.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

The RandBetweenText formula in Excel fills the gap next to RANDBETWEEN: a few lines of VBA turn any list into a random text picker, and with named ranges and INDIRECT one formula builds a whole dummy data table. Download the practice file to try it, and reach for the INDEX or RANDARRAY versions whenever the workbook has to stay macro-free.