Peril-sensitive formatting in Excel

Well, given the name of the site I had to do this one sooner rather than later!

Some of you may remember the peril sensitive sunglasses worn by Zaphod Beeblebrox in the Hitchhiker’s guide to the galaxy. Excel has its own version of this useful accessory which it refers to, rather strangely, as ‘Conditional formatting’.

The point of peril sensitive sunglasses was to protect the wearer from danger by turning completely opaque when any threat was imminent. This concept is highly relevant to spreadsheets which often communicate financial or other information that may be upsetting to the user. In this example we are listing the weekly turnover figures for 7 shops, and also including a ‘peril’ level. Results below the peril level will trigger alarm and dismay, so we will use peril sensitive formatting to hide them. Select the cells containing the values – B4 to B11 and then choose Format, Conditional Formatting. Choose ‘Cell Value Is’, ‘less than’ and then refer to our peril level cell, B1. Then click on the Format button and set the ‘Patterns’ to a black background, and the Font to a black font colour:

Peril-sensitive formatting in Excel - black font colour

Once the ‘Pattern’ and the font have been changed to black the screen should look as follows:Peril-sensitive formatting in Excel

If we click the ‘OK’ button, and then click outside our selected area, we can see the effect of the peril sensitive formatting:

Peril-sensitive formatting in Excel - result

The formatting is dynamic, so if we enter new values, or change the peril level, the formatting will change accordingly:

Peril-sensitive formatting in Excel - dynamic

You can set up to three different formats on a cell or range of cells (press the ‘add’ button on the conditional formatting screen to add a second or third format). So you could, for example, use a ‘traffic lights’ system to set the background colour of all cells with values below a ‘minimum’ level to red, those with values above a ‘maximum’ value to green and the ones in between to amber. In Excel 2007 conditional formatting has been greatly enhanced to enable the use of a much wider range of formats and icons to highlight certain values.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s