## Drop down list changes cell formatting

*Article updated on January 14, 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

- Select cell D3
- Go to tab "Data"
- Click "Data Validation" button
- Go to tab "Settings"

- Select List
- Type: No formatting, %, Time, Above Average, Below Average
- Click Ok!

### Conditional formatting

Here are the formulas and formatting:

**% formula**

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

**Time formula**

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

**Above Average**

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**

### Download excel *.xlsx file

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]### 3 Responses to “Drop down list changes cell formatting”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Something doesn't work quite right with the above and below average conditional formattings.

Brilliant nonetheless.

At least not in the version I downloaded and ran on Excel 2010

Cape,

Something doesn't work quite right with the above and below average conditional formattings.What happens, what is wrong? I have excel 2010.