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]")))

Download excel *.xlsx file

Use a drop down list to change conditional formatting.xlsx