How to generate random text values

How can we generate random values that aren’t numeric?

In this worksheet, I have a list of 4 colors in the range B7:B10.

In column E, I’ll enter a formula to create a random list of these colors.

To start off, I’ll enter just the INDEX function.

Min is 1 and max is 4, since we have four colors in the list.

Finally, the integers argument needs to be set to 1 or TRUE, because we want whole numbers.

When I enter the formula, we get 20 random colors based on our list.

This list is dynamic.

So, if I change a color, we’ll see that change in the output.

Now let’s improve things a bit.

First, I’ll convert the colors to an Excel Table, and name the table “colors”.

Then I’ll update the formula to use the table reference.

This will make it easier to add new colors.

I also need to change the max argument inside RANDARRAY.

Instead of hardcoding the number 4, we want the actual count of colors.

To get this, I’ll use theCOUNTA functionto count values in the table.