Excel Conditional Formatting

Conditional formatting allows us to easily identify data based on certain criteria.  This is ideally suited for the high level reports where a visual indication is necessary.

Conditional formatting can also be effective for highlighting erroneous data or for seeking specific values.

The Conditional Formatting option can be found on the “Home” ribbon.

conditional formatting - 1

In this example I am going to highlight all rows that have “Europe” for the continent.

We are presented with 8 options on the drop down menu.

Highlight cells rule

As the name suggests this highlights cells that satisfy certain criteria.

The Criteria available are: –

Greater than; Less than; between; Equal to; Text that contains; A date occurring; Duplicate values.

Top / Bottom rules

Allows you to quickly identify data compared to other values within the same data set.

The Criteria available are: –

Top 10 items; Top 10%; bottom 10 items; Bottom 10%; above average; bottom average

Data bars

Data bars are a visual representation of the size of the number in the cell compared to other values in the series.

The expanded menu allows you to select the colour of the data bars.

Colour scales

Colour scales are effectively the same as data bars however as opposed to bars being the visual representation is through the use of various colour gradients.

Icon sets

Icon sets use a small graphic to classify the data based on the criteria set.

conditional formatting - 2

By selecting “equal to” and entering “Europe” I have instructed the cells to be highlighted if this criteria is matched.  All that is left is to select the formatting of the cell in the second box.

conditional formatting - 3


Example of cell rules

conditional formatting - 5

Example of data bars

conditional formatting - 6