In the example shown, the formula in C5 is: Which returns a random color from the values provided. In this case, we are using four values: Red, Blue, Green, and Pink, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer between a bottom and top value. Since we are only working with 4 values in CHOOSE, we supply 1 for the bottom number and 4 for the top number. When this formula is copied down, it will return one of the four colors. Note that RANDBETWEEN will calculate a new value whenever the worksheet is changed. Once you have values in the range, you may want to replace the formulas with values to prevent further changes.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.