# Miscellaneous conditional formatting techniques

**Table of contents**

## 1. How to change cell formatting using a Drop Down list

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column B contains an Excel Table with numerical values.

The Drop down list in cell D3 lets you choose between five different types of cell formatting, they are:

- No formatting (General)
- % (percentage)
- Numbers formatted as time
- Highlight numbers below average
- Highlight numbers above average

You can, of course, pick whatever cell formatting you want and how many or few as you want. I chose those for demonstrational purposes.

The animated image above shows me selecting different formattings and column B instantly changes based on what I selected. This technique can be useful if you are building a Dashboard.

Here is how I did it.

### Create an Excel Table

This step is optional, however, I highly recommend it if you know you will be adding more values later on.

An Excel Table applies Conditional Formatting to new values automatically, this makes it really useful because there is no need to extend CF formatting or change CF formulas when new values are added.

- Select all cells in your data set.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Table" button, a dialog box appears.

- Press with left mouse button on OK button.

You data set has now a different cell formatting applied, this is done every time data is converted to an Excel Table. You can change this if you like.

- Press with left mouse button on any cell in your Excel Table.
- Go to tab "Table Design" on the ribbon.
- Here you have plenty of Table Styles to choose from.

### Create a Drop Down list

A Drop Down list lets you control what the user enters in a worksheet, press with left mouse button on the black arrow next to the cell to expand the list.

The list shows valid values the user can select, simply press with left mouse button on a value with the mouse or use up/down arrow keys.

- Select cell D3.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data Validation" button on the ribbon and a dialog box appears.
- Go to tab "Settings" on the dialog box. See image below.

- Select List
- Type:Â No formatting, %, Time, Above Average, Below Average
- Press with left mouse button on OK button to close the dialog box.

### Apply Conditional formatting

Conditional Formatting allows you to format a cell or cell range based on a condition, in this case, the condition is given in cell D3 where we have a Drop Down list located.

We need to create five different CF formulas, each one applying different cell formatting to a column in the Excel Table.

For example, the image above shows that the selected value in the Drop Down list is "Above Average", the corresponding Conditional Formatting formula is activated and highlights values in Excel Table if a number is above the average of all numbers in the "Values" column.

Follow these steps to apply Conditional Formatting to column Values in the Excel Table.

- Select all values in column "Values".
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the "Conditional Formatting" button and a menu appears.
- Press with left mouse button on "New Rule...".

- Copy this formula:

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

and paste to "Format values where this is true:" - Press with left mouse button on "Format..." button and pick a color. Cells that meet the requirement (return TRUE) in the above formula will be highlighted with this color.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button once again to close the Conditional Formatting dialog box.
- Repeat step 1 to 8 with the remaining cell formattings.

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

## 2. Highlight cells based on coordinates

The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22Â based on row and column values in column B and C.

ConditionalÂ Formatting formula applied to cell range F3:Y22:

The first coordinate in cell range B3:C3 is column 1 and row 4, the conditional formatting formula highlights cell F6 because it is column 1 determined by the value in F2 and row 4 based on value in E6.

### Explaining Conditional Formatting formula in cell F3

The COUNTIFS function counts the number of records in B3:C13Â that match both the column (F$2) and row ($E3) value.

F$2 is 1 and $E3 is 1, no record in B3:C13 matches so cell F3 is not highlighted.

F$2 is locked to row 2 and $E3 is locked to column E so the cell references changes to F$2 and $E4 in next cell below. The dollar sign $ determines if a cell reference is absolute (locked) or relative.

### How to apply conditional formatting formula to a cell range

- Select cell range F3:Y22.
- Go to tab "Home" on the ribbon.
- Press with mouse on the "Conditional Formatting" button.
- Press with mouse on "New Rule..."

- Type the formula and then press with left mouse button on "Format..." button
- Press with mouse on tab "Fill"

- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on OK button

### Get Excel *.xlsx file

Highlight cells based on coordinates.xlsx

## 3. Highlight every other row

Here is how to highlight every other row using conditional formatting.

**Conditional formatting formula:**

**Alternative CF formula:**

This formula colors every second row if any cell in the row is populated.Â If you know you will add more records later to the list, select and conditional format a range larger than the list. Empty rows wonÂ´t be formatted.

**How to apply the conditional formatting formula in excel 2007:**

- Select the range, example cell range A:C
- Press with left mouse button on "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Press with left mouse button on "Format values where this formula is true" window.
- Type =ISEVEN(ROW())*OR($B3:$D3<>"")
- Press with left mouse button on Format button
- Press with left mouse button on "Fill" tab
- Select a color
- Press with left mouse button on OK!
- Press with left mouse button on OK!

### Explaining the contional formatting formula in cell A2

=ISEVEN(ROW())*OR($A2:$C2<>"")

**Step 1 - Check if row number is even**

=**ISEVEN(ROW())***OR($A2:$C2<>"")

ISEVEN(ROW())

becomes

ISEVEN(2)

returns TRUE in cell A2.

**Step 2 - Check if Â any of the cells in row are not empty**

=ISEVEN(ROW())***OR($A2:$C2<>"")**

OR($A2:$C2<>"")

becomes

OR({"Federer", " Roger ", 25}<>"")

becomes

OR({TRUE, TRUE, TRUE})

and returns TRUE

**Step 3 - Multiply functions (AND logic)**

Both functions need to return TRUE in order to return TRUE

=ISEVEN(ROW())*OR($A2:$C2<>"")

becomes

TRUE*TRUE

and returns TRUE. Row 2 is conditional formatted.

### Get excel sample file for this tutorial

color-every-second-row-using-dynamic-conditional-formatting.xls

(Excel 97-2003 Workbook *.xls)

## 4. Highlight opposite numbers

Pamela asks:

Ex. 1 with -1, 5000 with -5000, 75 with -75, etc.

Once I find those pairs, WITHOUT REPEATING

Ex. 75,-75,75, just the first TWO should get marked, and leave the last 75 alone.

Conditional formatting formula applied to cell range B3:B17:

### Explaining CF formula in cell B3

COUNTIF($B$3:B3, B3)=1

The first COUNTIF function counts how many values there are in the first argument that match the second argument.

It returns TRUE if it is equal to 1, meaning this is the first instance of the value. In other words, this makes sure that duplicates are not highlighted.

The first argument is also a growing cell reference, the picture below shows what it returns in column D:

COUNTIF($B$3:B3, B3*-1)

The second COUNTIF function counts how many values there are in the first argument matching B3 but with a different sign, starting from the top.

This verifiesÂ that there is a number withÂ a different sign above the current value.

The first argument has a growing cell reference meaning it expands as you copy the formula to cells below. In this case, I am using it as a Conditional Formatting formula, however, it behaves the same.

The picture below shows what it returns in columnÂ C:

MATCH(B3*-1,$B$3:$B$17,0)

The MATCH function looks for the number but with a different sign, this verifies it really exists a pair in the first place.

The MATCH function returns a #N/A error if a number is not found

COUNT(MATCH(B3*-1,$B$3:$B$17,0))

The COUNT function then converts error to a 0 (zero). The picture below shows what it returns in columnÂ E:

Then the formula adds the numbers in the two last arrays, like thisÂ COUNTIF($B$3:B3, B3*-1)+COUNT(MATCH(B3*-1, $B$3:$B$17, 0)

and lastly multiplies withÂ COUNTIF($B$3:B3, B3)=1.

### Get Excel *.xlsxÂ file

Matching opposite numbers.xlsx

### Recommended reading

### Built-in conditional formatting

Data Bars Color scales Icons### Highlight cells rule

Highlight cells containing stringHighlight a date occuring

Conditional Formatting Basics

Highlight unique/duplicates

### Top bottom rules

Highlight top 10 valuesHighlight top 10 % values

Highlight above average values

### Basic CF formulas

Working with Conditional Formatting formulasFind numbers in close proximity to a given number

Highlight empty cells

Highlight text values

### Search using CF

Highlight records – multiple criteria [OR logic]Highlight records [AND logic]

Highlight records containing text strings (AND Logic)

Highlight lookup values

### Unique distinct

How to highlight unique distinct valuesHighlight unique values and unique distinct values in a cell range

Highlight unique values in a filtered Excel table

Highlight unique distinct records

### Duplicates

How to highlight duplicate valuesHighlight duplicates in two columns

Highlight duplicate values in a cell range

Highlight smallest duplicate number

Highlight more than once taken course in any given day

Highlight duplicates with same date, week or month

Highlight duplicate records

Highlight duplicate columns

Highlight duplicates in a filtered Excel Table

### Compare

Highlight missing values between to columnsCompare two columns and highlight values in common

Compare two lists of data: Highlight common records

Compare tables: Highlight records not in both tables

How to highlight differences and common values in lists

Compare two columns and highlight differences

### Min max

Highlight smallest duplicate numberHow to highlight MAX and MIN value based on month

Highlight closest number

### Dates

Advanced Date Highlighting Techniques in ExcelHow to highlight MAX and MIN value based on month

Highlight odd/even months

Highlight overlapping date ranges using conditional formatting

Highlight records based on overlapping date ranges and a condition

Highlight date ranges overlapping selected record [VBA]

How to highlight weekends [Conditional Formatting]

How to highlight dates based on day of week

Highlight current date

### Misc

Highlight every other rowDynamic formatting

Miscellaneous conditional formatting techniques

Highlight cells based on ranges

Highlight opposite numbers

Highlight cells based on coordinates

### Excel categories

### 9 Responses to “Miscellaneous conditional formatting techniques”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This is helpful, thanks, but I've been looking for the formatting to take place AS I fill new cells.

So my scenario is: A clear-formatted excel sheet, and ONCE you insert data into each row, the color of the row changes, e.g. row 1 becomes light blue, row 2 becomes dark blue, BUT row 3 is still clear-formatted because I haven't inserted data in it yet.

Do you know how I can do that?

*Thanks!!!*

@Sawsan,

Select all the cells (starting from upper left corner of the range so the the upper left cell ends up the active cell) that you think you will ever need to put your alternating row colors in (I would refrain from selecting entire columns), then follow the blog's instructions to get to the Conditional Formatting dialog box and then use this formula...

=AND(ISEVEN(ROW()),COUNTIF($A1:$C1,"*")>0)

Change the range as indicated, but note that your formula should only specify the first row's cells range even though you will have many more rows selected.

@Sawsan,

I guess I missed your request for a second color. That will require a second condition for the second color... use the identical formula I gave you in my previous message for this condition, but change the ISEVEN function to ISODD.

hi,

i'm interested to know on the conditional formatting.

how do i set the condition to evaluate a dynamic range?

my example is this:

Rule: =MAX($G$5:$G$20)=$G5

Applied to: =$AG$5:$AG$20

based on the value in G5:G20, the rule will determine the max value and highlight it on AG5:AG20

If i add new rows of data to G21 onwards, the rules does not "extend" to the newly added rows.

david,

I got this working in excel 2007:

Rule: =MAX(OFFSET($G$5;0;0;COUNTA($G5:$G1000))=$G5

Applied to: =OFFSET($AG$5;0;0;COUNTA($G5:$G1000)

thanks oscar,

i was using a dynamic named range as the rule in conditional formatting.

will try ur solutions ;)

thanks!!!

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

Brilliant nonetheless.

At least not in the version I opened 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.