Assume that you want to highlight Saturdays and Sundays as weekends in a column containing dates. Use the WEEKDAY() function in Excel to return the weekday for a date value (see Chapter 7, for more information) where a value of 1 corresponds to Sunday, and 7 corresponds to Saturday. This can be used to set a light green background for weekends by using the conditional formatting options.

Assume that the range B3:B33 contains the dates for one month. Select this date range, and then follow these steps:

For Excel 2007 and Excel 2010:

For Excel 2003:

Using a combination of conditional formats and Excel functions is a useful way to visually highlight information. Figure 5-7 shows the completed dialog box and the result for the date list.

Figure 5-7. Using the WEEKDAY() function for conditional formatting in Excel 2003.

Another possibility is to combine the two conditions with an OR function if the same formatting is to be used in both instances. With the logical function OR() (see Chapter 9), you need only one condition:

=OR(WEEKDAY(B3)=1,WEEKDAY(B3)=7)

This is particularly useful in Excel 2003, in which the number of conditions is limited.

Alternatively, the TEXT() function could be used to determine the day rather than WEEKDAY():

=OR(TEXT(B3,"ddd")="Sun",TEXT(B3,"ddd")="Sat")

In this formula, the “ddd” formatting command returns the first three letters of the weekday.

Highlighting Weekends and Holidays

Color-highlighting weekends in calendar overviews is useful, but what about other days, such as national and cultural holidays? It also might be useful to automatically highlight days such as payment dates, deliveries, maintenance, and monthly or quarterly reports.

These dates can be added to a list to be considered for highlighting. In our example, besides processing weekends, two more requirements have to be met: holidays and other free days. This means that four conditions have to be checked:

• Saturdays

• Sundays

• Holidays

• Other free days

Start by setting up the Free Days list. Enter the information into the spreadsheet and name the list.

1. Select the range G27:G31 and click in the Name Box.

2. Enter the name FreeDays.

3. Press the Enter key.

Figure 5-8 on the next page shows the result. The highlighted range now has the name FreeDays.

Figure 5-8. The list with the free days has the name FreeDays.

To check whether a date is included in the Holidays range, you need to use the MATCH() function. This function has the following syntax:

=MATCH(Lookup_value, Lookup_array, Match_type)

The lookup_value is each cell of the date list. The lookup_array is the FreeDays range. Match_type specifies how Excel is to match the matrix values with the lookup_value. This example uses the type 0 for an exact match. Data in the lookup_array does not need to be sorted.

The MATCH() function returns a number for the position of the value found in the lookup_array. If the date is not contained on the FreeDays list, the error value #N/A is returned.

Finally, the ISNUMBER() function is used to determine whether a match has been found. If the MATCH function returns a numeric value, the date is included on the FreeDays list.

=ISNUMBER(MATCH(B3,FreeDays,0))

Add this condition to the conditional formatting already set to highlight weekend days. Assign the free days a red background color and a bold white font color in contrast

Figure 5-9. In addition to the weekends, the free days are highlighted.

Note that the order of the conditional formatting conditions is important. The formatting will be applied sequentially, so if the Free Days condition is checked first, the holiday format will be applied rather than the weekend format for a holiday that falls on a weekend. Reverse the conditions to give preference to the weekend formatting. In Excel 2007 and Excel 2010, with the Conditional Formatting Rules Manager, you can move rules up and down in order. In Excel 2003, each condition will need to be reset.

The example of the calendar overviews demonstrates some of the facilities available for highlighting information.

Sometimes it may be useful to highlight cells with identical content. Several functions are available to compare the content of cells, and conditional formatting can be used to highlight the results.

When Everything Is the Same

To format the cells in the range B3:B7 as light blue if all of the cells contain the same content (see Figure 5-10), perform the following steps:

1. Select the range B3:B7, select Conditional Formatting, and then select Use Formula.

2. For the condition, enter the formula

   =COUNTIF($B$3:$B$7,$B$3)=COUNTA($B$3:$B$7)

3. Click the Format button and select the light blue color format.

   The cells are formatted only if all of the cells contain the same content.

Figure 5-10. All of the cells need to have the same content for the format to be applied.

When Only One Matches

Let’s take a look at a different scenario: All cells in the range B10:B14 have to be formatted if the range B11:B14 includes a cell that matches cell B10. Perform the following steps:

1. Select the range B11:B14, and then select Conditional Formatting and Use Formula.

2. Enter the following formula for the condition:

   =COUNTIF($B$10:$B$14,$B$10)>1

3. Click the Format button and select the format you want.

4. Confirm the format and the condition by clicking OK.

All cells are formatted if the range B11:B14 contains a cell with the same content as cell B10 (see Figure 5-11).

Figure 5-11. Only one cell has to match B10.

When Two or More Are the Same

The following example also compares the cells in a range. It formats those cells in the selected range that have identical contents. Perform the following steps:

1. Select the range B17:B21, and then select Conditional Formatting and Use Formula.

2. Enter the following formula for the condition:

   =COUNTIF($B$17:$B$21,B17)>1

3. Click the Format button and select the format you want.

4. Confirm the format and the condition by clicking OK.

Cells are only formatted if they match the content in cell B17. The COUNTIF function counts the number of matches and if it is greater than 1, formatting is applied (see Figure 5-12).

Figure 5-12. The duplicates are highlighted.

Sample Files

Use the Comparison worksheet in the Chapter05_CF.xls or Chapter05_CF.xlsx sample file. The sample files are found in the Chapter05 folder. For more information about the sample files, see the section titled Using the Sample Files.

Sometimes you might have two versions of a table, and you want to compare them to see if there are any differences. Conditional formatting can help.

Assume that you have two versions of a worksheet containing accident statistics—Accident1 and Accident2. On the Accident2 worksheet, you want to highlight the cells that have different content from the matching cell or cells on Accident1.

This task should be simple, because you need to compare only the cell content. But there is a problem you need to consider. Try these steps:

You get an error message. Conditional formatting cannot handle external references to other worksheets or workbooks. The solution is to use a table function to calculate these references.

To use an external sheet reference for conditional formatting, do the following:

Now the cells in which Accident2 differs from Accident1 are visible (see Figure 5-13). The functionality of the INDIRECT(), CELL(), and COLUMN() functions is explained in Chapter 11.

Note that the reference to the worksheet is entered as a string, which means that the formula is not automatically updated if the name of the worksheet changes. In this case, remember to modify the formula for conditional formatting.

Figure 5-13. The differences between the tables are highlighted with conditional formatting.

This solution also works with tables in different workbooks, but both workbooks must be open. You can use the following formula to apply conditional formatting to reference an external workbook:

=B4<>INDIRECT(ADDRESS(ROW(B4),COLUMN(B4),TRUE,TRUE,"[Accident.xlsx]Accident 1"))

Note that the workbook extension in Excel 2003 is .xls.

If you print a list, it might be better to highlight every other row with a color rather than use borders (see Figure 5-14). But for large lists, it would be very tedious to format every other line manually, and a single sorting process would undo your work. Conditional formatting can help:

Figure 5-14. The rows with uneven numbers are highlighted in gray.

The functionality of the MOD() function is described in Chapter 16.

Even if the list is dynamic, the current formatting will always be applied to the same range. The formatting can be extended dynamically by combining two tests with the AND() function, as follows:

=AND(MOD(ROW(),2)=1,$A2<>"")

With this formula, conditional formatting also tests the first column of the list. If this column contains a value and the line number meets the condition, a reference line is displayed. To use this formula, select a larger range before you enter the condition. The formatting takes place only if this range is filled with data.

Emphasizing the top elements should not be a problem with conditional formatting. Assume that you measured the temperatures of different places for a month. You now want to emphasize the three warmest and the three coldest temperatures (see Figure 5-15).

Figure 5-15. The warmest days are shown in red, and the coldest days are shown in blue.

To perform this task, do the following:

The INDIRECT() function creates the range names from the titles, which allows you to create conditional formats for all of the columns in one step. Chapter 12, describes the static MIN(), MAX(), SMALL(), and LARGE() functions in more detail.

Importing data from a text file can cause problems if the information contains blank characters, particularly if the cell content ends with a space, because this is not immediately obvious. Although you can search for spaces with the Find And Replace dialog box, it might not be possible to replace all spaces, because those in the center of a text string may be perfectly valid. The TRIM() function can be used to remove leading and trailing blanks, but a case-by-case review may be more useful. Conditional formatting can be used to highlight cells that contain blanks.

You can use conditional formatting for this purpose by applying the formulas shown in these examples:

Often you might need to choose a value from a table at the intersection of a certain row and a certain column. In this example, we want to make sure that the installment amount and the number of partial payments can quickly be found (see Figure 5-16). On a piece of paper, you can use a ruler and pencil; on the screen, conditional formatting can help.

The PMT() table function calculates the installment amount in cell C9 with the following formula:

=PMT($C$5/12,C$8,-$B9)

The combination of absolute (interest) and relative references (payment periods and cash value) allows you to copy the formula for the range C9:J28. Chapter 15, describes the math functions for finances, especially PMT(), in more detail.

Figure 5-16. The colored leader lines highlight the selection in the installment matrix.

Using data validation, you can easily handle the input of the amount in cell C3 and the input of the duration in cell C4. For the input of the amount, use the named range Amount (this name stands for the range B9:B28), and for the input of the duration, use the named range Months (this name stands for the range C8:J8). The section titled Functions for Validation contains more examples of data validation.

Highlighting the Result Cell and the Leader Line

Displaying the leader line is a bit more difficult. Several conditions have to be checked, because all of the cells in the row and all of the cells in the column should be formatted. You configure these conditions with the OR() function. Both conditions have restrictions, because each time, the formatting should only be applied up to a certain column or row. In this case, two conditions are valid and linked with the AND() function.

To highlight the result cell and insert leader lines into the data range, do the following:

1. Select the date range C9:J28 and select Conditional Formatting.

2. Select Formula and enter the formula

   =C9=$G$4

3. Select the format for the result cell, and click the Add button (or, in Excel 2007 and Excel 2010, the New Rule button).

4. Select Formula and enter the formula

   =OR(AND($B9=$C$3,C$8<=$C$4),AND(C$8=$C$4,$B9<=$C$3))

5. Select the format for the selected area, and confirm the format by clicking OK.

The order of the conditions is important. If the cell also corresponds to the result in the header, it should be highlighted as the result cell. Because this cell also meets condition 2, the formatting would never be displayed if the condition was the second condition.

Figure 5-17 shows the formulas for the conditions in the Conditional Formatting dialog box in Excel 2003.

Figure 5-17. The first condition is for the result cell, and the second condition is for the leader lines.

Sample Files

Use the Credit worksheet in the Chapter05_CF.xls or Chapter05_CF.xlsx sample file. The sample files are found in the Chapter05 folder. For more information about the sample files, see the section titled Using the Sample Files.

Often large lists need to be sorted and separated. For example, a list containing information about several companies should be separated so that you can recognize associated groups quickly (see Figure 5-18). It is easy to do this manually, but can you do this dynamically, too?

Figure 5-18. Between each company, a separator line is inserted.

With conditional formatting, this task can be done dynamically:

This formula checks whether the value in the line that follows is the same as the value in the current line. If it is different, a separator line is inserted between the groups.

Suppose you have a list to which you have applied subtotals. Conditional formatting can be used to highlight the total lines (see Figure 5-19).

Figure 5-19. The partial results are formatted in bold with a gray background and a border.

Perform the following steps to configure this format:

In Excel 2007 and Excel 2010, the Conditional Formatting Rules Manager allows you to handle and manage conditional formats in an intuitive and a comfortable way. This section provides some hints for using conditional formats in Excel 2003 and previous versions.

Changing and Deleting Conditional Formats

To change a conditional format, select the cells you want to modify. Then open the Conditional Formatting dialog box and modify the setting for the individual conditions. Confirm the format by clicking OK.

If you want to delete one or more conditions, select the corresponding cells and open the Conditional Formatting dialog box. Click the Delete button to open the dialog box shown in Figure 5-20. This dialog box contains three check boxes that you can select to indicate the condition you want to delete. Click the OK button to delete the conditions according to your settings.

Figure 5-20. The dialog box for deleting conditions.

Using an Icon to Apply Conditional Formatting

You access the Conditional Format command in Excel 2003 from the menu options. To add this command as an icon to your toolbars, follow these steps. Steps 1–5 correspond to the number labels in Figure 5-22.

1. On the Tools menu, choose Customize. Click the Commands tab and select the Format category. On the right side of the list, you should see the Conditional Formatting command without an icon.

2. Drag the Conditional Formatting option onto one of the existing toolbars.

3. Right-click the new button to open the shortcut menu. Give the new button a new label, if required.

4. Click the Default Style menu option.

5. Click Change Button Image, and choose a picture.

6. Press the Enter key to confirm the changes.

7. Click Close in the Customize dialog box.

Figure 5-22. The new icon button gets a label.

Now you can click the new icon to quickly open the dialog box for conditional formatting.

There are several reasons why a conditional format might not display correctly: