Do you ever need to generate a column of sample text in Excel? If you need to test a spreadsheet, database or application you’re creating, there’s a good chance you’ll need to do this.
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.
Need live, online training for Excel or other applications? Check the calendar for availability and sign up here.