Conditional Formatting

In this chapter, I’ll show you the wonders of Excel’s Conditional Formatting feature that can help you enliven your data with color, charts, or icons. You can, of course, use all three at once, but usually, less is more when it comes to data visualization. Conditional Formatting is a live overlay for worksheet cells that dynamically applies formatting such as font color, cell color, borders, and number formatting, based upon the cell contents matching specific criteria. The built-in Conditional Formatting rules eliminate the need to manually color-code data, and you can extend this functionality in limitless ways by crafting custom rules. Along the way, I’ll point out nuances that frustrate even the most experienced spreadsheet users.

In this chapter, I’ll cover the following:

• Formatting versus Conditional Formatting
• Highlight Cell Rules
• Top and Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• Wingdings...

Technical requirements

Everything in this chapter will work the same in Excel for macOS and Excel for Windows.

At the time of writing, Microsoft has made some changes to the Conditional Formatting feature in Excel for the Web that will most likely make their way into the desktop versions of Excel. Excel for the Web has emerged as a proving ground for user interface changes that Microsoft is contemplating.

The workbook that I used in this chapter is available for download from GitHub at https://github.com/PacktPublishing/Exploring-Microsoft-Excels-Hidden-reasures/tree/main/Chapter04.

Formatting versus Conditional Formatting

In Excel, you can apply a wide variety of formatting to cells by way of the Format Cells dialog box. As you’ll see, Conditional Formatting utilizes a subset of the Format Cells dialog box, which means you can apply almost all of the same formatting with Conditional Formatting. Let’s compare the two:

1. Choose Home | Format | Format Cells, or press Ctrl + 1 ( + 1 in Excel for macOS).
2. As shown in Figure 4.1, the Format Cells dialog box has six tabs that allow you to control every aspect of formatting within a worksheet cell:

Figure 4.1 – Border tab of the Format Cells dialog box

3. Optional: click on each tab to review the available formatting.
4. Click Cancel to close the Format Cells dialog box.

Let’s now have a look at the Conditional Formatting version of the Format Cells dialog box:

1. Choose Home | Conditional Formatting | New Rule | Use a Formula to Determine...

Highlight cell rules

The Home | Conditional Formatting | Highlight Cells Rules submenu offers eight different options. I’ll discuss the first seven here and then cover the More Rules command later in the chapter in the Custom rules section. I’ll demonstrate the Greater Than and Less Than rules, and then briefly discuss the other rules, since all are applied in the same fashion. The Greater Than-Less Than-Between worksheet in the example workbook has three rules applied to cells D6:D25. Let’s first apply the Greater Than rule to the Work Area section of the worksheet.

Greater Than

As you may expect, the Greater Than rule allows you to apply formatting to cells that contain numbers or dates that are greater than a specific value. Let’s assign a color to every road that is greater than 8,000 miles:

1. Select a range of cells, such as J3:J22, on the Greater Than-Less Than-Between worksheet in this chapter’s example workbook, as shown in Figure...

Top and bottom rules

The top and bottom rules allow you to identify values based on their relative positions within a list. Four out of the six reference 10 Items or 10%, but you can enter any number or percentage that you wish. To apply any of these rules, do the following:

1. Select a range of cells that you wish to format, such as cells H3:H22, of the Top and Bottom Rules worksheet in this chapter’s example workbook.
2. Choose Home | Conditional Formatting | Top/Bottom Rules and then select a rule:
   • Top 10 Items: Formats the 10 largest values in the list unless you specify a different value
   • Top 10%: Formats 10% of the largest values in the list unless you specify a different value

Nuance

The percentage is based on the number of items on your list. If your list has 90 items and you specify Top 10%, the Conditional Formatting feature will multiply 90 by 10 to determine that the 9 largest values should be formatted.

• Bottom 10 Items: Formats the 10 smallest...

Data Bars

Data Bars are charts that appear within cells to provide a sense of proportionate scale within a column of numbers. As you’ll see, numbers can appear in the cell with the charts, or you can hide the numbers. Cells D3:D22 of the Data Bars worksheet in the example workbook for this chapter show how numbers and Data Bars can coexist in the same cell. To create this type of formatting, do the following:

1. Select a range of cells that contain numbers, such as K3:K22 of the Data Bars worksheet.
2. Choose Home | Conditional Formatting | Data Bars and select from the Gradient Fill or Solid Fill sections.

Tip

Most Conditional Formatting rules in the desktop versions of Excel offer live previews as you craft a rule, or when you hover your mouse over a particular data bar, color scale, or icon set.

3. As shown in Figure 4.10, the Data Bars are commensurate with the size of the numbers in a cell in comparison to the other numbers in the list. Thus, the largest...

Color Scales

Color Scales are an easy way to create a heat map within your data, and assign colors based on a number’s proportionate scale within a list. Here are the steps:

1. Optional: Sort the amounts in ascending or descending order.
2. Select a range of cells that contain numbers, such as cells I3:I22 in the Color Scales worksheet of this chapter’s example workbook.
3. Choose Home | Conditional Formatting | Color Scales, and then choose a Color Scale grouping to create a heat map, such as cells D3:D22 in Figure 4.12.

Figure 4.12 – Color scales Conditional Formatting

Nuance

As with data bars, you can choose More Rules to fine-tune the presentation of the formatting.

Now, let’s look at the Icon Sets rule, which offers yet another way to graphically present your data.

Icon Sets

Icon Sets, also known as Cell Icons, are in-cell graphical indicators that provide a sense of scale or value relative to other numbers in your list. As with Data Bars, you can choose to show numbers and icons in the same cell or hide the numbers and only show the icons. By default, Icon Sets group amounts based upon percentages, but you can edit the rule to use percentiles instead. Here are the default percentages that Excel uses and how they’re calculated:

• 67th percent: This calculation takes the form smallest value from the list plus .67 multiplied by (largest value minus smallest value). This is illustrated by the formula =MIN(D8:D27)+0.67*(MAX(D8:D27)-MIN(D8:D27)) in cell C2 of the Cell Icons – Percent worksheet. The MIN function returns the smallest value in a range, while the MAX function returns the largest. As shown in cell D2, this calculates as 72.03%, which means any amounts that are greater than the largest value in the list multiplied by...

Custom rules

Custom Conditional Formatting rules give you the ability to test for any condition that can be expressed as a logical test. Excel offers an array of IS functions that test for various conditions and return TRUE or FALSE. The CELL function also returns the equivalent of TRUE or FALSE. You also use the logical_test portion of what you would enter in an IF formula to create a TRUE or FALSE calculation as well. Let’s see create a rule that will mark a cell in red if a user overwrites a formula with a static value, also known as a constant in Excel.

IS functions

Examples of IS functions include ISBLANK, ISNUMBER, and ISTEXT. Each IS function has a single reference argument where you reference a cell that you wish to test, and the function returns TRUE or FALSE. Let’s create a custom Conditional Formatting rule that will make a cell turn red if the user types over a formula:

1. Select a range of cells, such as J10:L57, on the Custom Rules – Formulas...

Managing rules

Nothing about Conditional Formatting is set in stone, so you can always make changes as needed. I’ll discuss a few ways that you can manage existing rules in this section and provide even more options in the upcoming Troubleshooting conditional formatting section. In this section, I’ll show you how to change the color applied to a rule and adjust the range of cells that Conditional Formatting is applied to. I’ll also show you how to create a legend that can document the color scheme in use, and then we’ll look at how to remove rules from a range of cells all the way through an entire worksheet.

Editing existing rules

Sometimes, you may decide that you want to change a color that a Conditional Formatting rule applies. Let’s change Fill Color for the first rule that we created at the start of this chapter:

1. Activate the Greater Than-Less Than-Between worksheet, and then click on cell D3.
2. Choose Home | Conditional Formatting...

Troubleshooting Conditional Formatting

Generally, you can select a range of cells and apply Conditional Formatting to all cells at once. However, when crafting custom rules, you may need to apply Conditional Formatting to a single row of your list first, and then use the Format Painter command on the Home menu to apply the formatting to the remaining cells in your list. As noted earlier in this chapter, the positioning of the $ signs to indicate an absolute or mixed cell reference can make or break how a Conditional Formatting formula works. If you are applying more than one rule to an area of your worksheet, then choose Conditional Formatting and Manage Rules to display the list of rules being applied. In some cases, you may need to change the sequencing of the rules in the list to eliminate a conflict.

No formatting appears

This situation will generally only arise when you have created a custom Conditional Formatting rule. One exception that comes to mind is the Duplicate...

Summary

In this chapter, you learned how you can use Conditional Formatting to identify data in worksheet cells based upon criteria that you specify. Excel offers several built-in rules that are easy to apply, such as when you want to identify the top or bottom values in a list, or dates within a given period. However, these rules only scratch the surface of Conditional Formatting’s potential. You can now use Conditional Formatting to provide a visual cue to users that attempt to overwrite a formula with a static value. You can also easily identify unlocked cells in a worksheet, which makes it less likely that you’ll inadvertently leave key input cells locked and inaccessible when the worksheet is protected. You also now know how to craft rules that will format certain cells based upon the value of an input cell.

Custom Conditional Formatting rules can be tricky to perfect because a single misplaced $ can cause a formula to behave in an unexpected fashion. In other...