How to generate a list of sample text using Excel

Bob FlisserAuthor
CERTIFIED EXPERT
Bob Flisser has authored many courses and books about Microsoft, Adobe and Apple products, and has been a computer trainer since the 1980s.
Published:
Updated:
Edited by: Andrew Leniart
Do you ever need to generate a column of sample text? Do it in Excel pretty quickly.
Here's a technique I just used to create sample data for a cool tutorial I'm working on. Let's say you want a hundred cells down a column to contain one of five words or phrases and you want the list randomized. Here's how to do it by nesting the Randbetween function inside the Choose function.

Randbetween generates random numbers between a lower and upper range. For example:
=RANDBETWEEN(5,15)
...returns any integer from 5 through 15.

The Choose function has two types of arguments: an index number and a list. The function returns the list item that corresponds to the index number. For example:
=CHOOSE(2, "Apples", "Bananas", "Pears", "Oranges", "Peaches")
...returns "Bananas" because it's the 2nd item in the list. Because these are text items, they need to be in quotation marks.

If we want to generate 100 cells that has the entries in the above list randomized, we use Randbetween to generate the index number.

Enter this formula in the first cell:

=CHOOSE(RANDBETWEEN(1,5), "Apples", "Bananas", "Pears", "Oranges", "Peaches")

Then AutoFill down 99 more rows.

Keep in mind this list will keep changing since the entries are based on a random number that changes every time something happens on the workbook. If you want to convert the formulas into actual text:
1. Copy the column to the clipboard.
2. On the Home tab of the Ribbon, click the down arrow on the Paste button and choose Paste Values.
1
142 Views
Bob FlisserAuthor
CERTIFIED EXPERT
Bob Flisser has authored many courses and books about Microsoft, Adobe and Apple products, and has been a computer trainer since the 1980s.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community