Excel Tip: Conditional Formatting

The purpose of conditional formatting is to automatically change the format of any cell based on the value in the cell, or other cells. Read full text for more info.

Conditional formatting

Purpose

The purpose of conditional formatting is to automatically change the format of any cell based on the value in the cell, or other cells.

Useful When

Conditional formatting is very useful during error flagging, and as a way to quickly let the user know the underlying data. For example, if assets do not equal liabilities, you would use bold, red format for the word “ERROR” to draw the user’s attention. Another example is when you want to draw attention to variances that are outside of a certain range, like all budget to actual variances greater than 10%.

Detail

Conditional formatting uses similar logic to the IF function discussed above. In essence, the logic is: “if a certain condition is true, then apply this formatting, otherwise use that formatting.” For example, you might use: “If the budget to actual variance is greater than 10%, format as red text, otherwise, use black text.”

Exercise

To demonstrate conditional formatting, we will use two examples.

Error flagging exercise

First, we want to build upon the IF function error checking, and change the formatting if an error exists. So, our logic will be: “If assets=liabilities then use black text, otherwise use red text.” This way, the user will easily see if the balance sheet is out of balance.

To set up the conditional formatting, simply select the cell that you want conditionally formatted, as shown in Figure 34.

Figure 34

Next, choose Conditional Formatting from the Format menu, as shown in Figure 35.

Figure 35

On the conditional formatting dialog box as shown in Figure 36, we will set up the logic: if the cell value is “ERROR” then format the cell with red text and bold.

Figure 36

So, set the Condition 1 to read, Cell Value Is equal to ERROR, and then use the Format button to specify red text and bold, as shown in Figure 37.

Figure 37

Now, if the balance sheet is not in balance, the user will be notified with a big red error, as shown in Figure 38.

Figure 38

Using the technique presented above, you should be able to reduce or eliminate clerical errors on spreadsheets. You can probably already imagine specific uses for this technique in your own workbooks.

Highlight variances

Conditional formatting is also very useful for highlighting certain conditions that exist in your spreadsheet. For example, highlight every department who had a variance to budget of more than 10%. By highlighting the “bad” departments, the reader of your report can more quickly understand the report. In the “unformatted” report shown in Figure 39, there is no conditional formatting and the reader must read line by line and identify the departments that are out of the acceptable variance range of plus or minus 10%.

Figure 39

However, with conditional formatting, the reader of the report can immediately identify the offending departments. Simply highlight the cells you would like to format, D5:D9, and choose Conditional Formatting from the Format menu. Set up the Conditional Format dialog box as shown in Figure 40.

Figure 40

The background in the dialog box is red. Simply use the Format buttons to apply white text, bold, with a red pattern background. Click OK to apply the formatting. Now, the report will contain formatting as shown in Figure 41.

Figure 41

The reader of this report can quickly identify HR and Marketing as departments that have a large Budget to Actual variance.