Conditional Formatting- Simple

Conditional Formatting allows you to change the format of a cell based on its, or another cells, contents. From Excel 2007 this has been enhanced to include the ability to create icons within a cell.

To follow along go to 5.1

To find the CONDITIONAL FORMATTING buttons you go to HOME/ STYLES/ CONDITIONAL FORMATTING.

Conditional Formatting Simple

In order to make it work you need to highlight a group of cells that you would like to conditionally format.

You can then choose one of the simple HIGHLIGHT CELLS RULES to apply. For example if you want a format to be applied when a cell is Greater Than a particular number you can choose the first option, and similarly with the rest.

You will also notice that you can apply formatting to the TOP/ and BOTTOMS of data.

Conditional Formatting Simple

All these options allow you to easily set up some standard conditional formats. However, sometimes you may want to make some changes. In that case you should highlight your cells with the Conditional Formats and click on the MANAGE RULES button.

Conditional Formatting Simple

This box allows you to change any aspect of the rules and is self-explanatory. If you click EDIT RULES the following will appear where you can now change the various details about each rule.

Conditional Formatting Simple

It is useful to note that some of the options for conditional formatting include

  • Above or Below averages
  • Unique or duplicate items
  • Linking to another formula or cells

Some useful tips relating to conditional formatting

If you edit a rule you will sometimes see the following screen (this is for conditional formatting using icons).

Conditional Formatting Simple

Note that you can specify how Excel will decide whether it should show a green, yellow or red traffic light. The important issue is the Type options.

By default the option is Percent. In this case it implies that items >= 67% OF THE RANGE will be green. So if the cells highlighted were 10, 12, 16 and 20 then items over 16.7 would be green (67% of 10 being the difference between the highest number of 20 and the lowest of 10).

Alternatively you could change the type option to be a number. In this case you would need to change the value to the ACTUAL number that will determine a green traffic light. If we want the green traffic light only if the number is above 17.5, we would change the Type to Number and the Value to 17.5.

Also note the bottom right options. You can reverse your icon order if required. You can also hide the underlying numbers and show only the icon.

Back to: Excel Dashboards and Management Reporting > Conditional Formatting for Dashboards