Chapter 22
Using Data Visualizations and Conditional Formatting

Many people feel their eyes glaze over when they encounter a screen full of numbers. Fortunately, Microsoft offers data-visualization features to Excel that make those screens full of numbers easier to interpret.

The following are some of the possibilities in data visualization:

• Adding data bars (that is, tiny, in-cell bar charts) to cells based on the cell value. In Excel 2019, data bars can be negative, include an axis, and have new scaling options.

• Adding color scales to cells based on the cell value. This is often called a heat map. Whereas the old conditional formatting would allow you to apply one color if a value exceeds a certain amount, a color scale applies a range from a gradient based on how high the value is.

• Adding icon sets (think traffic lights) to cells based on the cell value.

• Adding color, bold, italic, strikethrough, number formatting, and so on to cells based on the cell values.

• Quickly identifying cells that are above average.

• Quickly identifying the top n or bottom n% of cells.

• Quickly identifying duplicate values.

• Quickly identifying dates that are today or yesterday or last week.

• Sorting by color or by icon (after you’ve added icons or color).

Although it is easy to set up basic conditional formatting, you need to know a few tricks, which you discover later in this chapter, for creating better conditional formatting than most people will figure out on their own.

Using Data Bars to Create In-Cell Bar Charts

A data bar is a swath of color that starts at the side of a cell and extends into the cell based on the value of the cell. Small numbers get less color. The largest numbers might be 100% filled with color. This creates a visual effect that enables you to visually pick out the larger and smaller values. Figure 22.1 shows many examples of data bars.

Many options are available in Excel 2019 data bars:

• Data bars can be solid or a gradient.

• Values of zero get no data bar, as shown in cell E10.

• Data bars can be negative. Negative bars are shown in a different color and usually extend to the left of a central axis. You have three choices in where to place the zero axis. In cells B14:B20, the setting is Automatic. Because the largest positive number is further from zero than the smallest negative number, the axis appears slightly to the left of center. This allows the bar for 4.5% in B15 to appear larger than the bar for –3.3% in B17. You can also force the axis to appear in the center, as in cells C14:C20. Or, in a bizarre setting, you can force the negative bars to extend in the same direction as the positive values, but with a different color. There are two philosophical ways to show the negative bars. You can assign –3.3% the most color because it is farthest from zero, or you could assign –1.3% the most color because it is the mathematically the largest of the negative numbers (–1.3% > –3.3%). Excel 2019 uses the latter method.

• To “reverse” a data bar—to show the most color for the largest number—multiply the numbers by –1 to make them negative. Use a custom number format of “0;0;0;” to display the negative numbers as positive. Make the negative bars extend in the same direction as positive. You end up with the surprising results shown in G1:G11.

• You can control the color of the positive bar, the positive bar border, the negative bar, the negative bar border, and the axis color.

• Bars can now extend right to left, as shown in cells C3:C10. This allows comparative histograms as in C2:E10.

• You can specify the scale of the data bars. Although the scale is initially set to automatic, you can specify that the min or max is set to a certain number or to the lowest value, a percentage, a percentile, or a formula.

• You can choose to show only the data bar and to hide the number in the cell. This is how I managed to get words in cells E14:E20. The numbers are hidden by the conditional formatting dialog box, and then a linked picture of the words is pasted over the cells. Because the data bars are on a drawing layer above the regular drawing layer, this works.

• All data bars in a group have the same scale. This is unlike sparklines, where the scale is allowed to change from graphic to graphic.

  Note

  If you don’t like the six basic colors Excel offers for data bars, you can choose any other color, as described in the next section.

Creating Data Bars

Creating data bars requires just a few clicks. Follow these steps:

1. Select a range of numeric data. Do not include the total in this selection. If the data is in noncontiguous ranges, hold down the Ctrl key while selecting additional areas. This range should be composed of numbers of similar scale. For example, you can select a column of sales data or a column of profit data.

2. From the Home tab, select Conditional Formatting, Data Bars. You see six built-in colors for the data bars: blue, green, red, orange, bright blue, and pink. The colors appear in both solid and gradient forms. Select one of them. The result is a swath of color in each cell in the selection, as shown in Figure 22.2.

   

Customizing Data Bars

By default, Excel assigns the largest data bar to the cell with the largest value and the smallest data bar to the cell with the smallest value. You can customize this behavior by following these steps:

1. From the Conditional Formatting drop-down list on the Home tab, select Manage Rules.

2. From the Show Formatting Rules drop-down list, select This Worksheet. You now see a list of all rules applied to the sheet.

3. Click the Data Bar rule.

4. Click the Edit Rule button. Alternatively, you could double-click the rule in step 3. You see the Edit Formatting Rule dialog box, as shown in Figure 22.3.

   

A number of customizations are available in this dialog box:

• Select the Show Bar Only setting to hide the numbers in the cells and to show only the data bar.

• For the Minimum and Maximum values, you can choose from Automatic, Number, Percent, Percentile, Formula, and Smallest/Largest Number. If you select Automatic, Excel chooses a minimum and maximum value. You can override this by setting one value to a specific number.

• In the Bar Appearance section, you can specify gradient or solid fill for the bar. You can specify a solid border or no border. Two color chooser drop-down lists enable you to change the color of the bar and the border.

• The Bar Direction drop-down list enables you to select Context, Left to Right, or Right to Left. The default choice of Context is always left to right, unless you are in an international edition of Excel in which the language reading order is right to left.

When you choose Negative Values and Axis, you have new settings to adjust the color of the bar and the border for negative bars. You can also control whether the zero axis is shown at the cell midpoint or at an automatic location based on the relative size of the negative and positive numbers. If the axis is shown, you can adjust the color as well.

Showing Data Bars for a Subset of Cells

In the data bars examples given in the previous sections, every cell in the range receives a data bar. But what if you just want some of the values (for example, the top 20% or the top 10) to have data bars? The process for making this happen isn’t intuitive, but it is possible. You apply the data bar to the entire range. Then you add a new conditional format (a very plain format) to all the cells that you don’t want to have data bars. For example, you might tell Excel to use a white background on all cells with values outside of the top 10.

The final important step is to manage the rules and tell Excel to stop processing more rules if the white background rule is met. This requires clever thinking. If you want to apply data bars to cells in the top 10, you first tell Excel to make all the cells in the bottom 21 look like every other cell in Excel. Turning on Stop If True (in the Conditional Formatting Rules Manager dialog box) is the key to getting Excel to not apply the data bar to cells with values outside of the top 10.

Figure 22.4 shows data bars applied to only the top 10 states.

Using Color Scales to Highlight Extremes

Color scales are similar to data bars. Instead of having a variable-size bar in each cell, however, color scales use gradients of two or three different colors to communicate the relative size of each cell. Here’s how you apply color scales:

1. Select a range that contains numbers. Be sure not to include headings or total cells in the selection.

2. Select Conditional Formatting, Color Scales from the Home tab.

3. From the Color Scales fly-out menu, select one of the 12 styles to apply the color scale to the range. (Note that this fly-out menu offers subtle differences that you should pay attention to. The first six options are scales that use three colors. These are great onscreen or with color printers. The last six options are scales that use two colors. These are better with monochrome printers.)

In a two-color red/white color scale, the largest number is formatted with a dark red fill. The smallest number has a white fill. All the numbers in between receive a lighter or darker shade of pink based on their position within the range (see Figure 22.5).

You are not limited to the color scales shown in the fly-out menu. If you select Home, Conditional Formatting, Manage Rules, Edit Rule, you can choose any two or three colors for the color scale.

You also can choose where to assign the smallest, largest, and midpoint values (see Figure 22.6). Column E and the Edit Formatting Rule show how to highlight central values. Using a three-color scale, the minimum and maximum are set to white, whereas the middle numbers are assigned a color.

You should be aware of one strange situation: Normally, Excel lets you mix conditional formatting in the same range. You might apply both a color scale and an icon set.

If you have a three-color scale applied to some cells and choose a different three-color scale from the fly-out menu, the latter choice overwrites the first choice.

However, Excel treats two-color scales as a different visualization than three-color scales. If you have a three-color scale applied and you then try to switch it to a two-color scale using the fly-out menu, Excel creates two rules for those cells. The latter two-color scale is the only one to appear in Excel 2019, but you might be confused when you go to the Manage Rules dialog box to see two different rules applied to the cells.

Using Icon Sets to Segregate Data

Icon sets, which were popular with expensive management reporting software in the late 1990s, have now been added to Excel. An icon set might include green, yellow, and red traffic lights or another set of icons to show positive, neutral, and negative meanings. With icon sets, Excel automatically applies an icon to a cell, based on the relative size of the value in the cell compared to other values in the range.

Excel 2019 ships with 20 icon sets that contain three, four, or five different icons. The icons are always left-aligned in the cell. Excel applies rules to add an icon to every cell in the range:

• Three-icon sets—For the three-icon sets, you have a choice between arrows, flags, two varieties of traffic lights, signs, stars, triangles, and two varieties of what Excel calls 3 Symbols. This last group consists of a green check mark for the good cells, a yellow exclamation point for the middle cells, and a red X for the bad cells. You can get the symbols either in a circle—that is, 3 Symbols (Circled)—or alone on a white background (that is, 3 Symbols). One version of the arrows is available in gray. All the other icon sets use red, yellow, and green.

• Four-icon sets—For the four-icon sets, there are two varieties of arrows: a black-to-red circle set, a set of cell phone power bars, and a set of four traffic lights. In the traffic light option, a black light indicates an option that is even worse than the red light. The power bars icons seem to work well on both color displays and monochromatic printouts.

  Tip

  After creating several reports with icon sets, I have started to favor the cell phone power bars, which look good in both color and black and white.

• Five-icon sets—For the five-icon sets, there are two varieties of arrows, boxes, a five-power bar set, and an interesting set called 5 Quarters. This last set is a monochromatic circle that is completely empty for the lowest values, 25% filled, 50% filled, 75% filled, and completely filled for the highest values.

Setting Up an Icon Set

Icon sets require a bit more thought than the other data visualization offerings. Before you use icon sets, you should consider whether they will be printed in monochrome or displayed in color. Several of the 20 icon sets rely on color for differentiation and look horrible in a black-and-white report.

To set up an icon set, follow these steps:

1. Select a range of numeric data of a similar scale. Do not include the headers or total rows in this selection.

2. From the Home tab, select Conditional Formatting, Icon Sets. Select one of the 20 icon sets. Figure 22.7 shows the five-power bar set selected.

   

Moving Numbers Closer to Icons

In the top rows of Figure 22.8, the icon set has been applied to a rectangular range of data. The icons are always left aligned. Numbers are typically right-aligned. This can be problematic. Someone might think that the icon at the left side of cell G3 is really referring to the right-aligned number in F3, for example.

You might try centering the numbers to get the numbers closer to the icons in rows 7–9 in Figure 22.8. This drives purists crazy because the final digit of the 100 in cell H8 doesn’t line up with the final digits of cells H7 and H9.

A better solution is shown in rows 12–14. Keep the numbers right aligned, and use the Increase Indent icon to move the numbers closer to the icon.

If you don’t want to show numbers at all, you can edit the conditional formatting rule and select Show Icon Only. Rows 17–19 show this solution. Ironically, when the numbers are no longer displayed, you can position the icons by using the Left Align, Center Align, and Right Align icons.

The over-the-top solution in rows 22–24 involves using Show Icon Only and then pasting a linked picture of the numbers from other cells.

Here are the steps to create rows 22 through 24:

1. Select one of the cells with the icon set formatting.

2. From the Home tab, select Conditional Formatting, Manage Rules.

3. In the Conditional Formatting Rules Manager dialog box, click the Icon Set rule and then click Edit Rule.

4. In the middle of the Edit Formatting Rule dialog box, select Show Icon Only. Click OK twice to close the two dialog boxes.

5. Select all the cells that contain icons and click the Align Center button on the Home tab.

6. Page down so that you are outside of the printed range. Stay in the same column. Set up a formula to point to the number in the top-left corner of the icon set range. Copy this formula down and over to be the same size as your icon set range. This gives you a range of just the numbers.

7. Format this range of numbers to be right-aligned with an indent of 1.

8. Copy this range of numbers.

9. Go back to the original set of icons and select Paste, Picture Link. A picture of the original numbers appears behind the icons.

Mixing Icons or Hiding Icons

As of Excel 2010, it became possible to mix icons from different sets. In Figure 22.9, A2:C7 is a five-icon set with a mix of icons—gold star, green flag, yellow caution sign, and so on. You start with any five-icon set. Edit the rule and change the icon for each position.

In A11:D17, scores of 95 and higher receive a gold star, and all other scores get no icon. The open dialog box shows how this is done; the lower two rules show No Cell Icon.

Using the Top/Bottom Rules

The top/bottom rules are a mix of the old- and new-style conditional formatting. They are similar to the old conditional formatting because you must select one formatting scheme to apply to all the cells that meet the rule. However, they are new because rather than specifying a particular number limit, you can ask for any of these conditions:

• Top 10 Items—You can ask for the top 10, top 20, or any number of items.

• Top 10%—If 20% of your records account for 80% of your revenue, you can highlight the top 20% or any other percentage.

• Bottom 10 Items—To highlight the lowest-performing records, select Bottom 10.

• Bottom 10%—To highlight the records in the lowest 5%, select Bottom 10%.

• Above Average—You can highlight the records that are above the average. As with all the other rules, the average is recalculated as the numbers in the range change.

• Below Average—You can highlight the records that are below the average.

To set up any of these conditional formatting rules, follow these steps:

1. From the Home tab, select Conditional Formatting, Top/Bottom Rules, and then choose one of the six rule types shown in Figure 22.10.

   

2. The dialog box for above/below average does not require you to select a threshold value, but for the other four rule types, Excel asks you to enter the value for N. As you change the spin button, the Live Preview feature keeps updating the selection with the appropriate number of highlighted cells.

3. The drop-down list portion of the dialog box initially shows Light Red Fill with Dark Red Text. When you select the drop-down list, you have six default styles and the powerful Custom Format option. If one of the six styles is suitable, choose it. Otherwise, proceed to step 4.

4. If you choose Custom Format, you are taken to a special version of the Format Cells dialog box. This version has Number, Font, Border, and Fill tabs. You can choose settings on one or more of these tabs. Click OK to close the Format Cells dialog box.

5. Click OK to close the dialog box for your particular rule.

Excel adds the rule to the list of rules. By default, rules added most recently are applied first.

Using the Highlight Cells Rules

The traditional conditional formatting rules appear in the Highlight Cells Rules menu item of the Conditional Formatting drop-down list, along with several new rules. The traditional rules include Greater Than, Less Than, Between, and Equal To. Note that slightly obscure rules such as Greater Than or Equal To are hidden behind the More Rules option. The following are the traditional rules:

• Text That Contains—This rule enables you to highlight cells that contain certain text.

• A Date Occurring—With this rule, you can define conceptual rules such as yesterday, today, tomorrow, last week, this week, next week, last month, this month, next month, or in the last seven days.

• Duplicate Values—With this rule, you can highlight both records of a duplicate or highlight all the records that are not duplicated.

  Troubleshooting

  Excel’s definition of unique values does not match what you likely mean by unique values.

  To the Excel team, a unique value is a value that occurs exactly once in the range. It does not seem helpful in most situations. Would Amazon care about the products that sold exactly one time? Outside of someone trying to find one-hit wonders from the Billboard music charts, I am not sure when finding values that occur exactly once is useful.

  Instead, I would prefer that Excel highlight the cells that would be left after using Remove Duplicates. Highlight the first occurrence of each product whether it occurs once or 100 times.

  To highlight the first occurrence of a value in C2:C100, use a formula-based condition where =COUNTIF(C$1:C2,C2)=1. Be cautious to use only one dollar sign in that formula. This formula creates an expanding range that looks from C1 down to the current row.

  To create a formula-based conditional format, use Home, Conditional Formatting, New Rule, Use A Formula To Determine Which Cells To Format.

The options for Highlight Cells Rules are shown in Figure 22.11.

Highlighting Cells by Using Greater Than and Similar Rules

You might think that Greater Than and the similar rules for Less Than, Equal To, and Not Equal To are some of the less powerful conditional formatting rules. In fact, these are the first rules described in this chapter that you can use to base the conditional format threshold on a particular cell or cells. This enables you to build some fairly complex rules without having to resort to the formula option of conditional formatting.

To set up a rule to highlight values greater than a threshold, follow these steps:

1. Select a range of data. Unlike with the other rules, you might choose to include totals in this selection.

2. Select Home, Styles, Conditional Formatting, Highlight Cell Rules, Greater Than to display the Greater Than dialog box.

3. Enter a threshold value in the Greater Than dialog box.

4. Choose one of the six formats from the With drop-down list. Or choose Custom Format from the With drop-down list to have complete control over the number format, font, borders, and fill.

5. Click OK to apply the format.

By way of example, let’s look at several options for filling in the threshold value in the Greater Than dialog box. Figure 22.12 shows the conditional formatting rule for all cells greater than 200,000. This is a simple threshold value.

You can specify a cell as the threshold value. You can either use the reference icon at the right side of the box or type an equal sign and the cell reference. In Figure 22.13, the formula highlights any cell that does not exceed the quota in row 1 above the current cell using =D$1.

The formula in Figure 22.13 has to be written for the active cell. Although D4:I15 is the selected range, the name box shows that D4 is the active cell. The formula of =D$1 is compared to the active cell of D4. The threshold cell then becomes the cell in row 1 that is in the same column as each cell in the selection.

The Greater Than concepts discussed here apply equally well to the Less Than, Equal To, and Between rules. If you need to access other rules, such as Less Than or Equal To, you can follow these steps:

1. Set up the rule by using Less Than.

2. From the Conditional Formatting icon, select Manage Rules.

3. Select the Less Than rule and click Edit Rule.

4. Use the drop-down list shown in Figure 22.14 to select Less Than or Equal To.

   

Comparing Dates by Using Conditional Formatting

The date feature was added in Excel 2007. If you are familiar with the reporting engine in Quicken or QuickBooks, the list of available dates will seem similar. A nice feature is that Excel understands the dates conceptually. If you define a feature to highlight dates from last week, the rule automatically updates based on the system clock. If you open the workbook a month from now, new dates are formatted, based on the conditional formatting.

Some of the date selections are self-explanatory, such as Yesterday, Today, and Tomorrow. Other items need some explanation:

• A Week is defined as the seven days from Sunday through Saturday. Choosing This Week highlights all days from Sunday through Saturday, including the current date.

• In The Last 7 Days includes today and the six days before today.

• This Month corresponds to all days in this calendar month. Last Month is all days in the previous calendar month. For example, if today is May 1 or May 31, the period Last Month applies to April 1 through April 30.

Figure 22.15 shows the various formatting options, with a system date of June 22, 2018.

The date formatting option would be particularly good for highlighting the items in a to-do list that are due, overdue, or about to be due.

Identifying Duplicate or Unique Values by Using Conditional Formatting

Conditional formatting claims that it can mark either duplicate or unique values in a list of values. It seems that Microsoft missed an opportunity to include a different version of unique values than the one that it included. It would be very useful if Microsoft had included an option to mark only the first occurrence of each unique item.

In column A of Figure 22.16, Excel has marked the duplicate values. Both Adam and Bill appear twice in the list, and Excel has marked both occurrences of the values. You might be tempted to sort by color to bring the red cells to the top, but you still have to carefully go through to delete one of each pair.

In column C of Figure 22.16, Excel has applied a conditional format to the unique values in the list. In Excel parlance, this means that Excel marks the items that appear only once in a list. If you would keep just the marked cells as a list of the unique names in the list, you would effectively miss any name that was duplicated.

In a perfect world, this feature would have the logic to include one of each name in the conditional format. The conditional formatting in column E resorts to using the fairly complex formula of =COUNTIF(E$1:E1,E2)=0 to highlight the unique values.

Using Conditional Formatting for Text Containing a Value

The Text That Contains formatting rule is designed to search text cells for cells that contain a certain value.

Figure 22.17 contains a column of cells. Each cell in the column contains a complete address, with street, city, state, and ZIP. It would normally be fairly difficult to find all the records for a particular state. However, this is easy to do with conditional formatting. Follow these steps:

1. Select a range of cells that contains text.

2. From the Home tab, select Conditional Formatting, Highlight Cell Rules, Text That Contains.

3. In the Refers To box, enter a comma, a space, and the state you want to find. Note that this test is not case sensitive (for example, searching for “, pa” is the same as searching for “, PA”).

4. Choose an appropriate color from the drop-down list.

5. Click OK to apply the format.

   

As with the Find dialog box, you can use wildcard characters. You can use an asterisk (*) to indicate any number of characters and a question mark (?) to indicate a single character.

Tweaking Rules with Advanced Formatting

All the formats available from icons on the Conditional Formatting group are referred to as quick formatting. According to legend, the Excel team bought and read a number of Excel books, and if the author spent a page trying to explain a convoluted way to format something using formulas in conditional formatting, then that option became a quick formatting icon.

Every quick formatting item has an option at the bottom called More Rules. When you click this option and get to the New Formatting Rule dialog box, you find options that didn’t make it as quick formatting icons.

The next section of this chapter discusses using the formula option for conditional formatting. Almost anything is possible by using the formula option, but it is harder to use than the quick formatting icons. If Excel offers a built-in, advanced option, you should certainly use it instead of trying to build a formula to do the same thing.

The lists shown in Tables 22.1 and 22.2 are organized to show all the options for specific rule types. The six rule types are in the top of the New Formatting Rule dialog box. Items listed in the right column are advanced options that are available only by clicking More Rules.

Using a Formula for Rules

Excel has three dozen quick conditional formatting rules and twice as many advanced conditional formatting rules. What if you need to build a conditional format that is not covered in the quick or advanced rules? As long as you can build a logical formula to describe the condition, you can build your own conditional formatting rule based on a formula.

Some basic tips can help you successfully use formulas in conditional formatting rules. When you understand these rules, you can build just about any rule you can imagine.

Starting in Excel 2007, a formula is allowed to refer to cells on another worksheet. This enables you to compare cells on one worksheet to a worksheet from a previous month or to use a VLOOKUP table on another worksheet.

Getting to the Formula Box

To set up a conditional format based on a rule, follow these steps:

1. Select a range of cells.

2. In the Style group of the Home tab, select Conditional Formatting, Add New Rule.

3. In the New Formatting Rule dialog box, choose the rule type Use a Formula to Determine Which Cells to Format. You now see the New Formatting Rule dialog box.

The following sections give you some tips for building a successful formula.

Working with the Formula Box

Following are the key concepts involved in writing a successful formula:

• The formula must start with an equal sign.

• The formula must evaluate to a logical value of TRUE or FALSE. The numeric equivalents of 1 and 0 are also acceptable results.

• When you use the mouse to select a cell or cells on a worksheet, Excel inserts an absolute reference to the cell. This is rarely what you need for a successful conditional formatting rule. You can immediately press the F4 key three times to toggle away the dollar signs in the formula.

• You probably have many cells selected before starting the conditional formatting rule. You need to look at the left of the formula bar to see which cell in the selection is the active cell. If you write a relative formula, you should write the formula that will appear in the active cell. Excel applies the formula appropriately to all cells. This is a key point.

• If the dialog box is in the way of cells you need to select, you can drag the dialog box out of the way by dragging the title bar. If you absolutely need to get the dialog box out of the way, you can use the Collapse Dialog box button at the right side of the formula box. This collapses the dialog box to a tiny area. To return it to full size, you click the Expand Dialog box button at the right side of the collapsed dialog box.

• The formula box is one of the evil sets of controls that have three possible statuses: Enter, Point, and Edit. Look in the lower-left corner of the Excel screen. The status initially says that you are in Enter mode. This means that Excel is expecting you to type characters such as the equal sign. If, instead, you use the mouse or arrow keys to select a cell, Excel changes to Point mode. In Point mode, the selected cell’s address is added to the formula box.

  Caution

  The annoying thing about the formula box is that you always start in something called Enter mode. When you are in Enter mode, if you use any of the navigation keys (that is, Page Down, Page Up, left arrow, right arrow, down arrow, up arrow), Excel changes to Point mode and starts inserting random cell addresses at inappropriate places in your formula. Press F2 until you see Edit in the lower-left corner of the Excel window. You can now use the left and right arrows to move through the formula.

The following sections describe several useful conditional formatting rules. This list only scratches the surface of the possible rules you can build. It is designed to generate ideas of what you can accomplish by using conditional formatting.

Finding Cells Within Three Days of Today

The quick formatting feature offers to highlight yesterday or today or tomorrow, but what if you need to find any cells within three days of today, either plus or minus? If the active cell is B2, use a formula of =ABS(TODAY()-B2)<4.

Finding Cells Containing Data from the Past 30 Days

The Excel quick formatting option offers to highlight this month or last month. However, highlighting this month or last month can mean a number of vastly different things. Highlighting this month on the second of the month shows a lot of the future and only one day of the past. The same rule on the 29th of the month highlights a lot of the past and only a few days of the future. It would be more predictable to write a rule that shows the past 30 days.

You create this rule similarly to the way you created the Next Three Days rule in the preceding section. You first compare the date in the cell by using TODAY() to make sure the date in the cell is less than today. If the active cell is F4, you use the following formula:

Click here to view code image

=AND(F4<TODAY(),(TODAY()-F4)<=30)

To generalize this formula for other periods, such as the past 15 days or the past 45 days, you change the 30 to a different number.

Highlighting Data from Specific Days of the Week

The WEEKDAY() function converts a date to a number from 1 through 7. When used without additional arguments, the value of WEEKDAY(date) for a Sunday is 0 and for Saturday is 7.

Suppose the active cell is H4. If you needed to highlight all the Wednesdays, for example, you could check to see whether WEEKDAY(H4)=4. To find all the Fridays, you would check to see whether WEEKDAY(H4)=6. To find either date, you would use =OR(WEEKDAY(H4)=4,WEEKDAY(H4)=6).

To generalize this formula, you could substitute any number from 1 through 7 to highlight Sundays, Mondays, and so on.

Highlighting an Entire Row

Most conditional formatting highlights a cell based on the value in that cell. In this case, you would like to highlight the entire row for the row with the largest product sale.

In Figure 22.18, cell A2 is the active cell. You need to select the entire range of A2:G14. Your goal is to write a rule for all those cells that will always look at column D, but the row will vary based on the cell being evaluated. In this case, and in any case in which you want to highlight the entire row based on one column, you use the mixed reference with a dollar sign before the column letter. You want to see whether =$D2 is equal to the largest value in the range.

To find the largest value in column D, you use an absolute reference to D2:D14—that is, =MAX($D$2:$D$14). The conditional formatting formula for this specific case is =$D2=MAX($D$2:$D$14).

To change this rule to highlight the smallest value in column D, you change MAX to MIN. To base the test on another column, change D to the other column in three places in the formula.

Highlighting Every Other Row Without Using a Table

You might find yourself using the Format as Table feature only to add alternating bands of color to a table. If you don’t need the other table features, using a conditional format can achieve the same effect.

Do you remember when you were first learning to do division? You would express the quotient as an integer and then a remainder. For example, 9 divided by 2 is 4 with a remainder of 1, sometimes written as 4R1.

The trick to formatting every other row is to check the remainder of the row number after dividing by 2. Excel has functions that make this easy. First, =ROW() returns the row number of the given cell. Next, =MOD(ROW(),2) divides the row number by 2 and tells you the remainder. The task is then simply to highlight the rows where the remainder is equal to 1 or equal to zero.

In Figure 22.19, the active cell is A2. The formula to achieve the banding effect is =MOD(ROW(),2)=0.

To generalize this formula for your particular data set, you could change A2 to be the active cell’s address.

Combining Rules

Excel allows multiple conditions to evaluate to TRUE. In legacy versions of Excel, when a condition was met, Excel quit evaluating additional conditions. For each rule in Excel 2019, you can decide whether Excel should stop evaluating additional rules or whether Excel can continue evaluating rules.

For example, one rule might set the font color to blue. Another rule might set the font style to bold. Cells meeting both rules can be formatted in blue bold. Cells meeting one rule can be either blue or bold. Cells meeting neither rule will be in normal font style.

If two rules attempt to create conflicting formatting, Excel uses the first rule in the list. For example, if Rule 1 turns the font red and Rule 7 turns the font blue, the font is red.

Ten types of formatting can be changed in each cell. Naturally, each type conflicts with others of the same type. Only the first rule that evaluates to TRUE can change the fill color.

Very few formatting styles conflict with each other. Only the cell fill and the color scale are mutually exclusive. Otherwise, you can have up to nine rules evaluate to TRUE for any given cell. Table 22.3 illustrates the interplay between the ten formatting styles.

You can use a number of ways to clear conditional formats. A few quick options are available from the ribbon:

• You can highlight the entire range with conditional formatting and then use Home, Styles, Conditional Formatting, Clear, Selected Cells. This removes all conditions from the current selection.

• To clear all the conditional formats from the current worksheet, you can use Home, Styles, Conditional Formatting, Clear, Entire Sheet. This is handy if you have only one set of rules set up on the sheet. You can delete all the rules without having to select the entire range.

  Note

  Deleting columns or rows deletes the rules associated with those columns or rows. Selecting Home, Editing, Clear, All or Home, Editing, Clear, Formats removes the rules.

• If you have rules assigned to a pivot table or a table, you can select one cell in the pivot table or table. This enables new options for Home, Styles, Conditional Formatting, Clear, This Table or Home, Styles, Conditional Formatting, Clear, This PivotTable.

If you have multiple rules assigned to a range and you need to delete just a portion of those rules, you can use Home, Styles, Conditional Formatting, Manage Rules. In the Conditional Formatting Rules Manager dialog box, you should use the top drop-down list to display rules in the current selection, this worksheet, or any other worksheet. You can then highlight a specific rule and click the Delete Rule button.

Extending the Reach of Conditional Formats

In every example in this chapter, you have been advised to highlight the entire range before setting up the conditional format. It is also possible to assign a conditional format to one cell and then extend the rule to other cells. There are three ways to copy a conditional format:

• You can select a cell with the appropriate rule and then press Ctrl+C to copy it. Then you select the new range and select Home, Clipboard, Paste, Paste Special, Formats, OK to copy the conditional formatting from the one cell to the entire range.

• Select a cell with the appropriate rule. Click the Format Painter icon in the Home tab. Select a new range to paste the conditional format to the new range.

• You can select Home, Styles, Conditional Formatting, Manage Rules. Then you select a rule. In the Applies To column, you see the list of cells that have this rule. You can type a new range there or use the collapse button to make the dialog box smaller so that you can highlight the new range.

When you are using conditional formats that compare one cell to the entire range, using the second method is safer to ensure that Excel understands your intention.

Special Considerations for Pivot Tables

This section talks about the special conditional formatting options that are available for pivot tables.

A typical pivot table might contain two or more levels of summary data. In the pivot table in Figure 22.20, for example, cells H4:J16 contain sales data. However, if you tried to create a data bar for this entire range, the subtotal values in rows 9 and 15 would make the data bars in the other rows look too small.

To set up a data bar for the detail items in a pivot table, follow these steps:

1. Select a detail cell in the pivot table. In Figure 22.20, a cell such as H4 will do.

2. Choose any visualization from the Conditional Formatting drop-down list. In Figure 22.20, the 3-stars icons set is shown.

3. A tiny pivot icon appears to the right of the cell. Click this drop-down list to access three conditional formatting settings for pivot tables. The choices are the following:

   1. Selected Cells—You can apply the rule to just the one cell. This is not what you want in this case.

   2. All Cells Showing Sum Of Sales Values—You can apply the rule to cells including the total column, grand total row, and all the subtotal rows. Remember that the size of the grand total causes all the detail items to have data bars that are too small.

   3. All Cells Showing Sum Of Sales Values for “Customer” and “Product”—This is the option you use most of the time. The meaning of this option is dependent on careful selection of a detail cell in step 1. If you selected a subtotal row instead, this option would apply the data bars only to the subtotal rows.

The actual words in the second and third options vary, depending on the fields displayed in your pivot table. For successful pivot table formatting, select the third option.