Category list

Formatting Conditionally

You probably are asking yourself, "What is formatting conditionally?" Formatting conditionally lets you apply special formatting settings that take effect when the contents of a cell meet specified conditions. For instance, if the values fall below a specific number, you can show those values in bold pink, and if the values are greater than a specific number, you can display those values in bold blue. Excel's Conditional Formatting command helps you easily format your values based on specific conditions.

In the upcoming To Do exercise, you step through the process of setting up conditional formatting for values on the Summary sheet in the Sales workbook.

To Do: Format Values Conditionally

Select the cells that contain the values you want to format conditionally; in this case, select cells B4:B8.
Choose Format, Conditional Formatting. The Conditional Formatting dialog box pops open , as shown in Figure 9.4. You should see boxes for setting up Condition 1 and the Format button for specifying the format for the values.

Figure 9.4. The Conditional Formatting dialog box.
In the Condition 1 area, leave the Cell Value Is option. In the next box, choose Less Than, and type 500 in the last box.
Click the Format button. The Format Cells dialog box appears.
In the Font Style list, choose Bold, and select the pink color patch in the Color palette.
Click OK.
Click the Add button.
In the Condition 2 area, leave the Cell Value Is option. In the next box, choose Greater Than, and type 500 in the last box.
Click the Format button. The Format Cells dialog box appears.
In the Font Style list, choose Bold, and select the blue color patch in the Color palette.
Click OK to close the Format Cells dialog box. Click OK again to confirm your conditional formatting choices. Click any cell outside of the selected range. Excel displays numbers less than 500 in bold pink and numbers greater than 500 in bold blue.

Hiding Zeros

Worksheets are often cluttered with zeros as a result of calculations or information that hasn't been entered. Formulas frequently display a zero when referenced cells are blank. These zeros can make a worksheet confusing.

If you enter the sum formula in row 10 on the Detail sheet in the Sales workbook, the formulas produce unwanted values of zero (see Figure 9.5). This worksheet shows several columns where data has not been entered. Therefore, the cells with the formulas that total the empty columns produce zeros. In this case, you might want to suppress the zeros.

Figure 9.5. Formulas that produce unwanted values of zero.

graphics/09fig05.jpg

There are a couple of ways to hide zeros in a worksheet:

Use the Tools, Options command to hide all values of zero in the worksheet. In the Options dialog box, click the View tab (see Figure 9.6). In the Window Options section, click the Zero Values check box to remove the check mark, which hides all zeros on the worksheet.

Figure 9.6. Zero Values option in the Options dialog box.
Create a custom number format in the Format Cells dialog box to hide zeros in a range of cells.

The To Do exercise coming up walks you through hiding zeros in a range of cells on the worksheet. Use the Summary Sheet of the Sales workbook to see how it works.

To Do: Hide Zeros in a Range of Cells

Select the range that contains the zeros you want to hide; in this case, select cells B10:D10.
Choose Format, Cells. The Format Cells dialog box appears.
Click the Number tab.
In the Category list, click Custom.
In the Type text box, the General category appears, which is the type you want. Click after the l in General and type a semicolon ( ; ) followed by General and another semicolon ( ; ) See Figure 9.7.

Figure 9.7. Format Cells dialog box, hiding zeros.
Click OK.
Click any cell to deselect the range. The zeros are hidden in the selected range.