Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

A new Excel 2007 conditional formatting trick

by
16th Jul 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst discovers a simple, but previously overlooked enhancement to conditional formatting options in the latest version of Microsoft's spreadsheet program.

I was researching some of the perhaps less obvious aspects of the enhancements in Excel 2007 and came across something that was all too obvious, but that I had previously missed, about Excel 2007's dramatically improved conditional formatting feature.

For anyone that has so far escaped conditional formatting, it sort of does what is says – it changes the formatting of a cell or block of cells depending on whether the cell or cells meet a condition. The feature did exist in previous versions of the program, but was limited to a total of three conditions, and the use of Excel's basic cell formatting capabilities. Excel Zone has looked at it in the past including in the oddly titled: What would Zaphod Beeblebrox do with Excel? and the rather more straightforward: Excel 2007: Get ahead with conditional formatting which covered the new 2007 features.

Where Excel 2003 and before was limited to 'ordinary' formatting, Excel 2007 introduced formats specific to conditional formatting, in particular data bars and icon sets. Data bars display a coloured bar the length of which relates to the value in the cell, whereas icon sets display a different icon from a set of three to five different icons in a set, again depending on the value. At their most basic, both simply involve selecting a block of cells, choosing the conditional formatting option from the Styles section of the Excel 2007 Home ribbon tab, and then clicking on your preferred option:

As you can see, it is possible (regrettably!) to include more than one conditional formatting option for the same block of cells.

We can delve a little further into conditional formatting by clicking on the More Rules… option. This allows more control over the use of colour scales, data bars and icon sets. It also reveals the option that I had missed: Show icon/bar only.

In this example, we have entered references to the numbers in column A in the column immediately to the right, we have then chosen our icons and set the thresholds for the different icons manually by going to the Conditional formatting, Icon Sets, More Rules... option. Note that the values could be references to cell values rather than entered numbers. Finally we have selected the 'Show Icon Only' option so as to hide the actual values in the cells. We could have done the same thing with data bars so as to show only the formatting rather than the values.

An earlier article on presenting financial data in Excel looked at the idea of creating very simple charts or graphics in a single cell as a means of displaying information simply but effectively. The use of conditional formatting data bars and icons, with the figures hidden, would be a fairly straightforward way of achieving this sort of presentation.
 

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.