Microsoft Excel 2007 - Conditional Formatting

What Is Conditional Formatting?

Conditional formatting makes your spreadsheets respond to the data they contain and is a useful tool for visualising numerical data. You can draw attention to particular cells when their contents satisfy certain conditions, by making those cells display differently. To apply a conditional formatting rule to a cell or range of cells, select the cells and then click Home > Styles > Conditional Formatting. There are a variety of different rules you can apply and we'll just run through them here:

Highlight Cell Rules

Examples of this kind of rule are that data will be highlighted if it is greater than a certain value, falls within a certain range or contains a particular text string.

Top Bottom Rules

Examples include cells that are in the top 10, bottom 20% and data that is above or below average.

Data Bars

Displays a graph in the form of data bars that visually represent the data's relative size.

Colour Scales

Applies a background colour to a cell based on the relative size of that cell's data.

Icon Sets

Icons are displayed in a cell, which are dependent on the cell's value.

New Rule

This allows you to explicitly specify your own logic for a rule.

Clear Rules

Deletes all conditional formatting rules that had previously been applied to the selected cells.

Manage Rules

Displays the Conditional Formatting Rules Manager dialog box, which allows you to create new rules and also edit and delete existing rules.

A Simple Example

Let's use a simple example to put our conditional formatting prowess to the test. First of all let's generate some random numbers by typing =RAND() * 5000 in cell A1 and then pressing enter. That's one random number right there. Now hover over the bottom right corner of the cell until the cursor changes to a little '+' and drag that handle to the right. On releasing the mouse, that formula is applied to the selected cells and they all now contain random numbers between 0 and 5000. Now drag that handle downwards to cover more rows and generate more randowm numbers.

With those cells still selected, we can now apply some conditional formatting by clicking Home > Styles > Conditional Formatting > Color Scales. Choose a colour scheme and note that the colours at the top of the scale will represent the higher values in your cells whereas the colours at the bottom of the scale will represent the smaller values in your cells. When the formatting is applied, the background colour of each cell represents how big that cell's random number is.

If you like, press ctrl-z to undo the formatting and experiment with the other formatting rules available.