This page explains the basic principles of conditional formatting, and then shows a few specific examples that I have regularly used. The page is structured as follows
Basic Principles
Row Striping
Column Striping
Identifying Top 3 In a Range Of Values
Hiding Errors
Due Dates
Traffic Lights
A Simple Calendar
Restrictions
In Summary
Download
Basic Principles
Conditional formatting is a facility accessed from the Format menu on the main Worksheet menu bar. When this option is selected, you are presented with the folowing dialog box:
Figure 1.
This allows the selection of a condition criteria, the default shown is the 'between' criteria but there is also 'not between', 'equal to', 'not equal to', 'greater than', 'less than', 'greater than or equal to', or 'less than or equal to'. After the criteria has been selected, appropriate value box(es) are shown to enable the input of the value to be tested. For instance, the 'between' option allows 2 values to be input, for obvious reasons, whereas the 'Equal to' option simply has one.
After setting the condition and the value, it is then necessary to set the format. Whilst it is possible to set font characteristics, borders, and cell patterns, in this discussion, I am only setting cell colours. I will leave it to you to experiment with the others, they are all accessed from the 'Format' button, as separate tabs.
As an example, consider highlighting any values greater than 100. The basic data is shown here in Figure 2.
Figure 2.
To implement the solution, select the data to be tested, enter conditional formatting as described above, select the 'greater than' condition from the list, and put 100 in the value box. Click the 'Format' button, select the 'Patterns' tab, and choose a colour to format the cell with. Exit conditional formatting and the result should be similar to Figure 3. below.
Figure 3.
Note that cells D6 and E10 are not highlighted, as we tested for greater than 100.
Row Striping
The previous example shows a simple use of conditional formatting, but it can be much more powerful than this. This power comes when using formulae within the condition criteria. An example is a worksheet with colour striping (just like the old computer printout paper that was common many years ago). This is useful to be able to read a very busy spreadsheet, separating rows for legibility.
Using the data from the previous example, we will add green stripes to every other row.
As before, select the rows to be tested, and enter conditional formatting. In the condition dropdown box to the left, the one that says 'Cell Value Is', select the other value, 'Formula Is'. This then presents a single text box for entering the criteria, as shown in Figure 4. below.
Figure 4.
To get row striping, add a formula of =MOD(ROW(),2)=0, click the 'Format' button, select the 'Patterns' tab, and choose a cell colour, such as 'Light Green'. Exit conditional formatting, and if all is well, your spreadsheet should look like Figure 5. below.
Figure 5.
This formula works by comparing the modulus of the row number and 2 against zero. If this test succeeds, the format is applied, if not, nothing happens.
Note that a formula of =MOD(ROW(),2)=1 would colour all of the odd numbered rows.
It is also possible to stripe every 3rd row, or every 4th row, etc. This is achieved by changing the value of the divisor argument to the 'MOD' function. So
=MOD(ROW(),3)=0 will stripe every 3rd row
=MOD(ROW(),4)=0 will stripe every 4th row
etc.
Similarly, it is simple to change which row the striping starts at. In this instance, the value that the 'MOD' fuction is being compared against is changed. The value tested against can be any value bewteen 0 and the divisor minus one, as these are the only values that the 'MOD' function will return. Examples are:
=MOD(ROW(),2)=1 will stripe every 2nd row, starting at row 1
=MOD(ROW(),3)=1 will stripe every 3rd row, starting at row 1
=MOD(ROW(),3)=2 will stripe every 3rd row, starting at row 2
=MOD(ROW(),4)=1 will stripe every 4th row, starting at row 1
=MOD(ROW(),4)=2 will stripe every 4th row, starting at row 2
=MOD(ROW(),4)=3 will stripe every 4th row, starting at row 3
etc.
The general formula is =MOD(ROW(),m+1)=MOD(n,m+1)
where m is the number of non-coloured (blank) rows between coloured rows, and n is the start row.
Column Striping
Column striping is just as easy as row striping, the 'COLUMN()' function is used within the 'MOD' function, rather than 'ROW()'. For example:
=MOD(COLUMN(),2)=1 will stripe every other row, starting at row 2.
The effect is as shown below in Figure 6.
Figure 6.
Identifying Top 3 In a Range Of Values
Another good use of conditional formatting is to pick out certain items on a spreadsheet amongst a large number of items, so as to highlight those that of most importance. This is especially useful when the data is being updated, thus the key items may not be in any fixed place on the spreadsheet.
Using the same worksheet as before, select the data cells, enter conditional formatting, select the 'Formula Is' condition, and input the following formula
=A1=LARGE($A$1:$H$18,1)
and set the format as required (in this example, I set the format to a red background, with bold white text).
To add further conditions for the second and third largest, click the 'Add >>' button, use a condition of 'Formula Is' with formulae of
=A1=LARGE($A$1:$H$18,2)
=A1=LARGE($A$1:$H$18,3)
and set the formats to the same as the top value. The conditional formatting dialog should then look like Figure 7. below.
Figure 7.
If this is all done successfully, the end result should be as in Figure 8.
Figure 8.
If wanted, each of the conditions could be formatted differently, to identify largest, second and third largest separately.
It would also have been possible to amalgate all 3 conditions into a single condition, using a formula of
=OR(A1=LARGE($A$1:$H$18,1),A1=LARGE($A$1:$H$18,2),A1=LARGE($A$1:$H$18,3))
What it cannot be is a single conditioon with a formula of
=A1=LARGE($A$1:$H$18,{1,2,3})
as conditional formatting does not support the use of array constants in the criteria.
Hiding Errors
Conditional formatting can even be used to hide errors, such as #DIV/0, divid by 0, or #NA, match not found.>/p>
Fig. 9 shows an example, with two columns where the errorsre hidden, two where the same erreors are not hidden.
Figure 9.
The errors are hidden by formatting the cell's font colour to white, which means they will not show against the cell background (of course if the cell colour is changed, the font colour should be changed to that colour.
To create this highlighting, follow these steps:
- Step 1.
Select all of the target rows in the 'State %ge' column, staring in row 2 (this example will use column G).
- Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=ISERROR(G2)
Click the Format button
Select the Font Tab
Select White from the Color dropdown
OK
At this point, the conditional formatting dialog should look like this
Figure 10.
OK the dialog, and it is all done.
If specific errors need to be hidden, this can also be done as follows:
- #N/A, #REF - test against the error function, =ISNA(G2) or =ISREF(G2)
- all error types - test that error directly, such as =COUNTIF(G2,"#N/A") or =COUNTIF(G2,"#NAME?"). Note that it is necessary to get the error text correct in this way.
Whilst it may not be a good idea to just mask the errors, it might be better to fix the data, but it can be done, as shown.
Due Dates
Conditional formatting is an ideal vehicle for highlighting due dates, showing items that are overdue, those shortly due, and those due later.
Fig. 11 shows the resultant highlights.
Figure 11.
In this example, we have a list of invoices, and the invoice due date. Highlighting is as follows:
- red - items whose due date has passed
- amber - items due within the next 2 weeks
- green - items due within the next 3 months (or 12 weeks)
All other items are left without highlights.
To create this highlighting, follow these steps:
- Step 1.
Select all of the target rows in the 'Due Date' column, staring in row 2.
- Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=B2
Click the Format button
Select the Pattern Tab
Select red from the palette
OK
- Step 3.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=B2<=TODAY+2*7
Click the Format button
Select the Pattern Tab
Select orange from the palette
OK
- Step 4.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=B2<=TODAY()+12*7
Click the Format button
Select the Font Tab
Select the Pattern Tab
Select green from the palette
OK
At this point, the conditional formatting dialog should look like this
Figure 12.
OK the dialog, and it is all done.
If it is necessary to test a particular date, use a formula such as
=B2
which tests for a date less than 1st October 2007
OR
=B2
which tests for a date less than 3 months from today (3 months here, not 12 weeks).
Traffic Lights
Another useful technique that is used in management reports is to highlight the data using the 'traffic lights' technique, on target items are in green, items slightly slipping are shown in yellow, and big slippages are shown in red.
Figure 13.
The picture shows a number of national projects with a budget, cost to date, and estimated final cost. Thye will be flagged as follows:
- green - estimated cost is less than or equal to budget
- yellow - estimated cost.is less than or equal to budget + 25%
- red - estimated cost is greater than budget + 25%
As well as showing the lights colours, the background is shaded to provide emphasis.
The conditional formatting will be formula driven, and to avoid showing a light on a blank line kit tests for no budget, as well as the spend status.
It would be very simple to do the highlighting by shading a cell, maybe the status cell or one of the amount cells, such as the budget. Whilst this works, it would be better to show as a traffic light shows it, a coloured circle. This is achieved by using the Winding font.
To create this traffic lights, follow these steps:
- Step 1.
Select the first data row in the 'Status' colum
Set the font of this cell to 'Winding' Enter a lower-case letter l in the cell
Centre the text in this cell
Copy this cell down to the appropriate number of rows.
- Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2<=B2)
Click the Format button
Select the Font Tab
Select bright green from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
- Step 3.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2<=B2*1.25)
Click the Format button
Select the Font Tab
Select yellow from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
- Step 4.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(B2<>"",D2>B2*1.25)
Click the Format button
Select the Font Tab
Select red from the Color dropdown
Select the Pattern Tab
Select dark green from the palette
OK
At this point, the conditional formatting dialog should look like this
Figure 14.
OK the dialog, and it is all done.
A Simple Calendar
The final example of conditional formatting is a simple, one-page calendar. This calendar shows a month per column, and highlights weekdays, weekends, and today. The year is shown in a cell, changing the year will update the calendar automatically, all as a result of conditional formatting. Because the calendar is shown in a 12x31 grid, and not every month has 31 days, the inmvalid days are whitened-out, again using conditional formating. The finished result looks like Figure 11. below
Figure 15.
The picture shows buttons at the foot of the calendar that allow scrolling through the years, selecting today, and showing the selected date. This is achieved with VBA and is not described here, but the example workbook does have these functions.
To create this calendar, follow these steps:
- Step 1.
Open a new workbook
Select B2:M32
Put a box border around the selection
Add vertical border lines
Shade all cells with a light-green background.
- Step 2.
Put a year number in A1
Add a named range to A1, menu Insert>Name>Define with a name of 'year'
Put day numbers, 1-31, in A2:A32
In B1 insert the formula
=DATE(year,COLUMN()-1,1)
format as 'mmm' and copy across to M1
- Step 3.
In B2, put formula
=TEXT(DATE(year,MONTH(B$1),$A2),"ddd")
and centre the cell
Copy the formula to B2:M32
- Step 4.
Select B2:M32
Menu Format>Conditional Formatting
Change Condition 1 to 'Formula Is', add a formula of
=DATE(year,MONTH(B$1),$A2)=TODAY()
and format as red background
Add condition, change Condition 2 to 'Formula Is' , add a formula of
=MONTH(DATE(year,MONTH(B$1),$A2))<>MONTH(B$1)
and format as white text and a white pattern
Add condition, change Condition 3 to 'Formula Is', add a formula of
=OR(WEEKDAY(DATE(year,MONTH(B$1),$A2),1)=1,WEEKDAY(DATE(year,MONTH(B$1),$A2),1)=7)
format as pale blue background
- Step 5.
A bit of presentation, colour the headings etc.
Restrictions
Although conditional formatting is a very useful, very powerful facility, it does have some restrictions. It is not possible to use unions, intersections or array constants in the criteria. And possibly the severest restriction is that there are only 3 conditions allowed. Although this can be effectively increased to 4 by virtue of the default, that is anything that does not match any of the defined criteria, this is still severely limited. I would have expected Excel to have increased this by now, 10 would be much more useful, but it is still three, and so if you want more, you need to revert to VBA.
In Summmary
There it is, a few examples of conditional formatting that I hope demonstrate the power of this facility. It is all about presentation and useability, rather than adding to the spreadsheet calculation functionality, but that is a very important aspect of any spreadsheet and should not be overlooked.
It's usefulness will, as always, depend upon the creativity of you the user, and the appropriateness of its use. For example, whilst the calendar may in itself be of little value, it does show the level of complexity conditional formatting can be taken to.
As I come across more good uses of conditional formatting, I will post them on the website, and include them in the download. And, of course I would welcome suggestions/examples from others.
No comments:
Post a Comment