Author: Oscar Cronquist Article last updated on October 02, 2018

You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list.

Here is how I did it.

### Create a drop down list

1. Select cell D3
2. Go to tab "Data"
3. Click "Data Validation" button
4. Go to tab "Settings"
5. Select List
6. Type: No formatting, %, Time, Above Average, Below Average
7. Click Ok!

### Conditional formatting

Here are the formulas and formatting:

% formula

=\$D\$3="%"

The formula checks if the value in cell D3 is %. If true the following formatting is applied:

Time formula

=\$D\$3="Time"

The formula checks if the value in cell D3 is Time. If true the following formatting is applied:

Above Average

=(\$D\$3="Above Average")*(INDIRECT("Table1[@Values]")>AVERAGE(INDIRECT("Table1[Values]")))

In this formula, cell D3 is "Above Average" AND checks if each value is above the average of the table values. If TRUE the cell is highlighted.

Below Average

=(\$D\$3="Below Average")*(INDIRECT("Table1[@Values]")<AVERAGE(INDIRECT("Table1[Values]")))