## Drop down list changes cell formatting

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

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

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 […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

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 […]

How to use absolute and relative references

What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

### 3 Responses to “Drop down list changes cell formatting”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.