## Drop down list changes cell formatting

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

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

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

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

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.