Chapter 22: Conditional Formatting


Overview

  • How can I visually indicate whether recent temperature data is consistent with global warming?

  • How does the Highlights Cells conditional formatting feature work?

  • How do I check or customize my rules?

  • What are the great new data bars, color scales, and icon sets included with Excel 2007 conditional formatting?

  • How can I color-code monthly stock returns so that every good month is indicated in one color, and every bad month in another?

  • Given quarterly corporate revenues, how can I indicate quarters in which revenues increased over the previous quarter in one color, and quarters in which revenues decreased from the previous quarter in another?

  • Given a list of dates, how can I indicate weekend dates in a specific color?

  • Our basketball coach has given each player a rating between 1 and 10 based on the player’s ability to play Guard, Forward, or Center. Can I set up a worksheet that visually indicates the ability of each player to play the position to which she’s assigned?

  • What does the Stop If True option in the Manage Rules dialog box do?

Conditional formatting lets you specify formatting for a cell range depending on the contents of the cell range. For example, given exam scores for students, you can use conditional formatting to display in red the names of students who have a final average of at least 90. Basically, when you set up conditions to format a range of cells, Microsoft Office Excel checks each cell in the range to determine whether any of the conditions you specified (such as exam score>90) is satisfied. Then Excel applies the format you choose for that condition to all the cells that satisfy the condition. If the content of a cell does not satisfy any of the conditions, the formatting of the cell remains unchanged. Conditional formatting in Excel 2007 has been completely revised and expanded. Let’s show you how to use these exciting new conditional formatting features.

To view your conditional formatting options, select the range you want to format. Then, on the Home tab of the Ribbon, in the Styles group, click the Conditional Formatting arrow. (See Figure 22-1.)

image from book
Figure 22-1: Conditional formatting commands

A submenu of conditional formatting options appears, as shown in Figure 22-2.

image from book
Figure 22-2: Conditional formatting options

  • Highlight Cells Rules allows you to assign a format to cells whose contents meet one of the following criteria:

    1. Within a specific numerical range

    2. Match a specific text string

    3. Within a specific range of dates (relative to the current date)

    4. Occur more than once (or only once) in the selected range

  • Top/Bottom Rules allows you to assign a format to any of the following:

    1. N largest or smallest values in a range

    2. Top or bottom n percent of numbers in a range

    3. Numbers above or below the average of all the numbers in a range

  • Data Bars, Color Scales, and Icon Sets allow you to easily identify large, small, or intermediate values in a selected range. Larger data bars are associated with larger numbers. With Color Scales you might, for example, have smaller values appear in red and larger values in blue, with a smooth transition applied as values in the range change from small to large. With Icon Sets, you can use sets of up to five symbols to identify different ranges of values. For example, you might display an arrow pointing up to indicate a large value, pointing to the right to indicate an intermediate value, and pointing down to indicate a small value.

  • New Rule allows you to create your own formula to determine whether a cell should have a specific format. For example, if a cell exceeds the value of the cell above it, you could apply the color green to the cell. If the cell is the fifth largest value in its column, you could apply the color red to the cell, etc.

  • Clear Rules allows you to delete all conditional formats you have created for a selected range or for the entire worksheet.

  • Manage Rules allows you to view, edit, or delete existing conditional formatting rules, create new rules, or change the order in which Excel applies the conditional formatting rules you have set.

  • How can I visually indicate whether recent temperature data is consistent with global warming?

  • This is a perfect job for applying the Excel 2007 Top/Bottom conditional formatting rules. The file Globalwarming.xlsx (see Figure 22-4 on the next page), contains the average world temperatures for the years 1856–2005 (we have hidden the years 1866–1989). These temperatures are deviations from a base level of 15 degrees Centigrade. If there has been global warming, we would expect the numbers in recent years to be larger than the numbers in earlier years. To determine if recent years are warmer, we first select the range B3:B152 containing the temperatures. On the Home tab, in the Styles group, click Conditional Formatting, and then on the submenu, click Top/Bottom Rules. Next, we select Top 10 Items and fill in the dialog box as shown In Figure 22-3.

    image from book
    Figure 22-3: Highlighting the ten highest temperatures in red

  • Next, we again selected the range B3:B152 and went back to set the Top/Bottom rules. This time, we selected Bottom 10 Items, and chose to highlight the 10 smallest numbers in orange. Note that the 10 highest temperatures occurred since 1990, whereas three of the 10 lowest temperatures occurred before 1865. In a similar fashion, in column C we highlighted the top 10 percent of the temperatures in red, and the bottom 10 percent in green. Finally, in column D we highlighted above-average temperatures in green and below-average temperatures in red. Note that all years earlier than 1865 had below average temperatures, whereas all years from 1990 or later had above average temperatures.

  • Conditional formatting is a powerful visual tool that can be used to demonstrate that the Earth (for whatever reason) seems to have become warmer in the recent past.

  • Note that clicking the arrow shown on the right side of Figure 22-3 displays a list of options, including Custom Format. Selecting this option displays the Format Cells dialog box, which allows you to create a custom format that is applied when the conditional formatting condition is satisfied.

    image from book
    Figure 22-4: Conditional formatting using Top/Bottom rules

  • How does the Highlights Cells conditional formatting feature work?

  • The file Highlightcells.xlsx (see Figure 22-5) demonstrates how the Excel Highlight Cells features are used. For example, suppose we want to highlight all duplicate names in C2:C11 in red. Simply select the cell range C2:C11. Then click Conditional Formatting in the Styles group, click Highlight Cells Rules, click Duplicate Values, and then choose Light Red Fill With Dark Red Text. Click OK to apply the rule so that all names occurring more than once (John and Josh) are highlighted in red.

    image from book
    Figure 22-5: Using the Highlight Cells rules

  • Now suppose that we want to highlight in red all cells in the range D2:D11 that contain the text Eric. We simply select the cell range D2:D11. Click Conditional Formatting, click Highlight Cells Rules, and then click Text That Contains. Enter Eric in the left box, and choose Light Red Fill With Dark Red Text on the right. As shown in Figure 22-5, we see that both Eric and Erica are highlighted (Erica, of course, contains the text string Eric).

  • Suppose we have a list of dates (such as E2:E11), and we want to highlight any cell that contains yesterday’s date and any date during the last seven days in red. See the worksheet Rightway in the file Highlightcells.xlsx. Assume as shown in Figure 22-5 that the current date is March 16, 2007. Note that cell E3 contains the formula =TODAY()–1, so cell E3 will always contain yesterday’s date. Cell E4 contains the formula =TODAY()–5. We began by selecting the cell range we want to format (E2:E11). Then we clicked Conditional Formatting, Highlight Cell Rules, and then A Date Occurring. In the A Date Occurring dialog box, we selected Yesterday and Green Fill With Dark Green Text and clicked OK. Next, we selected the A Date Occurring option again, and this time selected the In The Last Seven Days option with Light Red Fill With Dark Red Text. Note that formats created earlier have precedence over formats created later (unless you later reverse the order of precedence, as explained below). This explains why 3/16/2007 is formatted in green rather than red.

  • How do I check or customize my rules?

  • After creating conditional formatting rules, you can view your rules by clicking Manage Rules on the Conditional Formatting submenu. For example, select the dates in E2:E11, click Conditional Formatting, click Manage Rules, and you will see the rules displayed in Figure 22-6. You can see that our Yesterday formatting rule will be applied before our Last 7 Days formatting rule.

    image from book
    Figure 22-6: Conditional Formatting Rules Manager dialog box

  • From the Manage Rules dialog box, we can do the following:

    • Create a rule by clicking the New Rule button.

    • Edit or change a rule by clicking the Edit Rule button.

    • Delete a rule by selecting it and then clicking the Delete Rule button.

    • Change the order of precedence by selecting a rule and then clicking the up arrow or down arrow.

  • To illustrate the use of the Manage Rules dialog box, we copied the previous worksheet (simply right-click the worksheet tab, click Move Or Copy, and then check the Create A Copy box) to the Wrongway worksheet of the Highlightcells.xlsx file. We selected the Last 7 Days rule and clicked the up arrow. The Last 7 Days rule now has higher precedence than the Yesterday rule, so E3 will be formatted red and not green. Figure 22-7 shows how the Conditional Formatting Rules Manager dialog box looks, and Figure 22-8 shows that no cells in column E are formatted in green.

    image from book
    Figure 22-7: The result of giving the Last 7 Days rule higher precedence than the Yesterday rule

    image from book
    Figure 22-8: After changing the precedence of rules, the Yesterday format will never be applied.

  • What are the great new data bars, color scales, and icon sets included with Excel 2007 conditional formatting?

  • When you have a long list of numbers, it would be nice to have a visual indicator that enables you to easily identify large and small numbers. Data bars, color scales, and icon sets (all new in Excel 2007) are perfect tools to display differences in a list of numbers. The file Scalesiconsdatabars.xlsx illustrates the use of these exciting new tools.

  • Figure 22-9 shows the use of data bars. We begin by applying the default data bars to the data in D6:D15. We first select the data in D6:D15, click Conditional Formatting, and then click Data Bars. Then we select blue data bars to automatically create the format shown in column D of Figure 22-9. Cells containing bigger numbers also contain longer blue bars. The default option is to have the shortest data bar associated with the smallest number in the selected range, and the longest data bar associated with the largest number.

    image from book
    Figure 22-9: Visually distinguishing numeric values by using data bars

  • If, after clicking Data Bars, we click More Rules, then the New Formatting Rule dialog box shown in Figure 22-10 on the next page is displayed. (You can also display this dialog box by clicking Manage Rules, and then either clicking Edit Rule or double-clicking on the rule.) From this dialog box, you can change the criteria used to assign the shortest and longest data bars to cells. In E6:E15, we chose to assign the shortest bar to the number 3 and the longest bar to the number 8. As shown in Figure 22-9, all numbers in column E that are less than or equal to 3 have the shortest bar, all numbers that are greater than or equal to 8 have the longest bar, and the numbers between 3 and 8 have the graduated bars. Note that in the Edit Formatting Rule dialog box, you can check the Show Bar Only box to display only the color bar and not the cell value in the conditionally formatted cells.

    image from book
    Figure 22-10: Customizing your data bars

  • Next, in column F we chose to assign the shortest bar to numbers in the bottom 20 percent of the range F6:F15, and the longest bar to numbers in the top 20 percent. In other words, all numbers <=1+.2(10–1)=2.8 will have the shortest data bar, and all numbers >=1+.8(10–1)=8.2 will have the longest data bar. Figure 22-9 shows that in column F, the numbers 1 and 2 have the shortest data bar and the numbers 9 and 10 have the longest data bar.

  • In cell range G6:G13, we associated the shortest data bar with all numbers on or below the 20th percentile of the data (1), and we associate the longest data bar with all numbers on or above the 90th percentile of the data (9).

  • Now let’s use color scales to summarize some data sets. Like the Highlight Cells rule, a color scale uses cell shading to visually display the difference in cell values. Let’s look at an example of a three-color scale. (The Colorscaleinvestment.xlsx file and Figure 22-11 illustrate the use of a three-color scale. Note that we have hidden rows 19-75; to unhide them, select rows 18 and 76, right-click the selection, and then click Unhide.) We selected the annual returns on Stocks, T-Bills, and T-Bonds in cells B8:D81 (). We clicked Conditional Formatting, Color Scales, and then More Rules to display the Edit Formatting Rule dialog box, which we filled in as shown in Figure 22-12.

    image from book
    Figure 22-11: Three-color scales

    image from book
    Figure 22-12: Customizing a three-color scale

  • Note that we chose the color red to indicate the lowest return, green to indicate the highest return, and orange to indicate the return at the midpoint. Amazingly, Excel 2007 makes small changes to the color shading of each cell based on the value in the cell. In column B of Figure 22-11, the lowest return is shaded red. As the returns approach the 50th percentile, the cell color gradually changes to yellow. Then, as the returns increase from the 50th percentile toward the largest return, the cell color changes from yellow to green. Note that most of your green and red cells are associated with stocks. This is because annual stock returns are more variable than bond or T-Bill returns. This causes large or small stock returns to occur quite frequently. Virtually all annual returns for T-Bills or T-Bonds are yellow, because the low variability of annual returns on these investments means that intermediate returns occur most of the time.

  • We created some two-color scales in the Scalesiconsdatabars.xlsx file (shown in Figure 22-13 on the next page). We selected the range of cells and then clicked Conditional Formatting and Color Scales. You can select the color combination you want from the given list or create your own by clicking More Rules.

  • We chose a two-color scale indicating lower values in white and higher values in dark blue.

    • In cell range D19:D28, we chose to make the lowest value white and the highest value blue. Note that as numbers increase, the cell shading becomes darker.

    • In cell range E19:E28, we chose to make values less than or equal to 3 white and values greater than or equal to 8 blue. Note that for numbers between 3 and 8, as numbers increase, the cell shading becomes darker.

    • In cell range F19:F28, we chose to make values in the bottom 20 percent of the range white and numbers in top 20 percent blue. For numbers in the middle 60 percent, cell shading becomes darker as numbers increase.

    • In cell range G19:G28, we chose to make values on or below the 20th percentile of data white, and values on or above the 80th percentile blue. For all other numbers, cell shading becomes darker as numbers increase.

  • We can also display numerical differences by using icon sets. (See Figure 22-14 and the Scalesiconsdatabars.xlsx file.) An icon set consists of three to five symbols. The user defines criteria to associate an icon with each value in a cell range. For example, we may use a down arrow for small numbers, an up arrow for large numbers, and a horizontal arrow for intermediate values. The cell range E32:G41 contains three illustrations of the use of icons. For each column, we used the red down arrow, the yellow horizontal arrow, and the green up arrow.

    image from book
    Figure 22-13: Two-color scales

    image from book
    Figure 22-14: Icon sets

  • Here is how we assigned icons to different range of numerical values.

    • After selecting the numbers in E32:E41, we clicked Conditional Formatting, and clicked Icon Sets, and then, we clicked More Rules, and chose the 3 Arrows (Colored) icon set. In column E, we want numbers less than 4 to display a down arrow, from 4 to 8 to display a horizontal arrow, and 8 or larger to display an up arrow. To accomplish this goal, we set the options in the Edit Formatting Rule dialog box as shown in Figure 22-15.

      image from book
      Figure 22-15: Assigning icons to numerical values

    • After selecting the numbers in F32:F41, we clicked Conditional Formatting, and then clicked Icon Sets. Then we clicked More Rules, and selected the 3 Arrows icon set. In column F, we want numbers in the top 20 percent of the range from smallest to largest (numbers greater than or equal to 1+0.8(10–1) =8.2) to have an up arrow, numbers in the bottom 20 percent of range (numbers <=1+0.2(10–1) =2.8) to have a down arrow, and all other numbers to have a horizontal arrow. To accomplish this goal, we set up the dialog box as shown in Figure 22-16 on the next page.

      image from book
      Figure 22-16: Assigning icons to percentage values

    • In a similar fashion, we set up our formatting rule for G31:G42 so that up arrows are placed in cells containing numbers in the top 20 percent of all values, (>=8), and down arrows are placed in cells containing numbers in the bottom 20 percent of all values (<1).

  • Optional settings include Reverse Icon Order, which associates the icons on the left with small numbers and the icons on the right with larger numbers, and Show Icon Only, which hides the contents of the cell.

  • How can I color-code monthly stock returns so that every good month is indicated in one color, and every bad month in another?

  • The file Sandp.xlsx, shown in Figure 22-17, contains monthly values and returns on the Standard & Poor’s stock index. Suppose that you want to highlight in green each month in which the S&P index went up by more than 3 percent, and to highlight in red each month in which it went down more than 3 percent.

    image from book
    Figure 22-17: Conditional formatting highlights returns in the S&P stock index

  • I begin by moving to cell C10 (the first month containing an S&P return), and selecting all monthly returns by pressing Ctrl+Shift+Down Arrow. Next, I click Conditional Formatting, Manage Rules, New Rule, and select Format Only Cells That Contain. Then I fill in the dialog box as shown in Figure 22-18. We tell Excel to format cells in the selected range that are >0.03 and, after clicking Format, we select the desired Format (green fill).

    image from book
    Figure 22-18: Applying special formatting to S&P returns greater than 3 percent

  • Notice that the lists for fonts and font size aren’t available, so your choice of formatting can’t change the font or font size. The Fill tab provides the option to shade cells in a color you choose, whereas the Borders tab lets you create a border for cells that satisfy your conditional criteria. After clicking OK in the Format Cells dialog box, you’re returned to the Conditional Formatting dialog box. We now select New Rule again, and in a similar fashion set things up so that all cells containing numbers that are less than –0.03 have a red fill. (See Figure 22-19.)

    image from book
    Figure 22-19: Coloring stock returns less than -3 percent in red and greater than 3 percent in green

  • When you now click OK, all months with an S&P return that’s greater than 3 percent (see cell C223, for example) are displayed in green, and all months with an S&P return of less than –3 percent (see cell C18) are displayed in red. Cells in which the monthly returns don’t meet either of our conditions maintain their original formatting.

  • Here are some useful tips concerning conditional formatting:

    • To delete conditional formatting (or any format) applied to a range of cells, simply select the range of cells, click Conditional Formatting, click Clear Rules, and then click Clear Rules from Selected Cells.

    • To select all the cells in a worksheet to which conditional formatting applies, press F5 to display the Go To dialog box. In the dialog box, click Special, select Conditional Formats, and then click OK.

    • If you wish to edit a conditional formatting rule, click Manage Rules, and then either double-click the rule or click Edit Rules.

    • You may delete a specific conditional formatting rule by clicking Manage Rules, clicking on the rule, and then clicking Delete Rule.

  • Note that after we have entered both rules, the red format rule is listed first (because it was created more recently than the green format rule). In the Conditional Formatting Rules Manager dialog box, rules are listed in order of precedence. In our example, it does not matter which rule is listed first because no cell could satisfy the criteria for both rules. If rules conflict, however, the rule listed first will take precedence. To change the order of conditional formatting rules, simply select a rule and click the up arrow to move the rule up in precedence, or the down arrow to move the rule down in precedence.

  • Given quarterly corporate revenues, how can I indicate quarters in which revenues increased over the previous quarter in one color, and quarters in which revenues decreased from the previous quarter in another?

  • The file Toysrusformat.xlsx contains quarterly revenues (in millions) for Toys “R” Us during the years 1997–2002. (See Figure 22-20.) We’d like to highlight quarters in which revenues increased over the previous quarter in green, and to highlight quarters in which revenues decreased over the previous quarter in red.

    image from book
    Figure 22-20: HIghlighting increased sales in green and decreased sales in red

  • The Use A Formula option in the Conditional Formatting Rules Manager dialog box enables you to use a formula to define conditions that Excel checks before it applies formatting to a cell. We’ll use this option in this example, but before we work with the Formula Is option, let’s look at how Excel evaluates some logical functions.

  • What happens when we type a formula such as =B3<2 in cell B4? If the value in B3 is a number smaller than 2, Excel returns the value TRUE in cell B4; otherwise, Excel returns FALSE. You can refer to the file Logicalexamples.xlsx, shown in Figure 22-21, for other examples like this. As you can see in Figure 22-21 on the next page, you can also use combinations of AND, OR, and NOT in formulas.

    • In cell B6, the formula =OR(B3<3, C3>5) returns the value TRUE if either of the conditions B3<3 or C3>5 is true. Because the value of C3 is greater than 5, Excel returns TRUE.

    • In cell B7, the formula =AND(B3=3,C3>5) returns TRUE if B3=3 and C3>5. Because B3 is not equal to 3, Excel returns FALSE. In cell B8, however, the formula =AND(B3>3,C3>5) returns TRUE because B3>3 and C3>5 are both true.

    • In cell B9, the formula =NOT(B3<2) returns TRUE because B3<2 would return FALSE, and a not false value becomes TRUE.

    image from book
    Figure 22-21: Logical functions

    Now let’s look at how the Use A Formula feature allows us to create a conditional format in a range of cells. Begin by selecting the range of cells to which you want to apply a conditional format. Then, click Conditional Formatting, followed by Manage Rules, to display the Conditional Formatting Rules Manager dialog box. Click New Rule, and then select Use A Formula To Determine Which Cells To Format option (the last option). To use the Formula option, we enter a formula (the formula must start with an equals sign) that is TRUE if and only if we want the cell in the upper-left corner to be assigned the chosen format. Our logical formula will copy like an ordinary formula to the remainder of the selected range, so judicious use of dollar signs ($) is needed to ensure that for each cell of the selected range, the formula will be TRUE if and only if we want our format to apply to the cell. Click Format, and then enter the formatting you want. Click OK. After clicking OK in the Conditional Formatting dialog box, your formula and formatting are copied to the whole cell range. The format will be applied to any cell in the selected range that satisfies the condition defined in the formula.

    Returning to the file Toysrusformat.xlsx, let’s focus on highlighting in green the quarters in which revenues increase. Basically, what we want to do is select the range E5:E25 (there is no prior quarter to which we can compare the revenue figure in cell E4), and then instruct Excel that if a cell’s value is larger than the cell above it, highlight the cell in green. Figure 22-22 shows how to set up the desired rule.

    image from book
    Figure 22-22: Conditional formatting settings that will display in green the quarters in which revenue increased

    If you enter =E5>E4 by pointing to the appropriate cells, be sure you remove the $ signs from the formula in the Conditional Formatting dialog box or the formula won’t be copied. Probably the easiest way to insert or delete dollar signs is to use the F4 key. When you highlight a cell reference such as A3, pressing F4 cycles dollars signs in the following order: A3, $A$3, A$3, $A3. Thus, if we start with $A$3, pressing F4 changes cell reference to A$3. The formula in this example ensures that cell E5 is colored green if, and only if, sales in that quarter exceed the previous quarter. After clicking OK, you’ll find that all quarters in which revenue increased are colored green. Notice that in cell E6, for example, the formula was copied in the usual way, becoming =E6>E5.

    To add the condition for formatting cells in which revenue decreased, select the range E5:E25 again, open the Conditional Formatting Rules Manager dialog box, click New Rule, and then select Use A Formula To Determine Which Cells To Format. Enter the formula =E5<E4, and then click Format. On the Fill tab, change the fill color to red, and then click OK twice. The Conditional Formatting Rules Manager dialog box will now appear as shown in Figure 22-23.

    image from book
    Figure 22-23: These conditions will display quarters in which revenue increased in green, and quarters in which revenue decreased in red.

    You can use the Formula option with Color Scales, Data Bars, and Icon Sets. Simply select the ‘Use a formula to determine which cells to format’ option (which we will simply call the Formula option) when setting the criteria for your scale, bar, or icons.

    Given a list of dates, how can I indicate weekend dates in a specific color?

    The file Weekendformatting.xlsx (see Figure 22-24 on the next page), contains several dates. We want to highlight all Saturdays and Sundays in red. To do this, I first copied the formula WEEKDAY(C6,2) from cell D6 to D7:D69. Choosing Type=2 for the WEEKDAY function returns a 1 for each Monday, a 2 for each Tuesday, and so on, so that the function returns a 6 for each Saturday and a 7 for each Sunday.

    image from book
    Figure 22-24: Using the WEEKDAY function to highlight weekend days in red

    I now select the range D6:D69, click Conditional Formatting, and then click Manage Rules. After clicking New Rule and the Formula option from the Rules Manager, I fill in the dialog box as shown in Figure 22-25.

    image from book
    Figure 22-25 The Edit Formatting Rule dialog box set up to display weekend days in red font

    After clicking OK, each date having its weekday equal to 6 (for Saturday) or 7 (for Sunday) is colored red. Note the formula =OR(D6=6,D6=7) implies that a cell entry of 6 or 7 will activate the red font color. Of course, we could have used Format Only Cells That Contain and used >=6 or >5 to obtain the same formatting .

    Our basketball coach has given each player a rating between 1 and 10 based on the player's ability to play Guard, Forward, or Center. Can I set up a worksheet that visually indicates the ability of each player to play the position to which she’s assigned?

    The file Basketball.xlsx, shown in Figure 22-26, contains ratings given to 20 players for each position and the position (1=Guard, 2=Forward, 3=Center) played by each player. We would like to fill with red the cell containing the rating for each player for the position to which she’s assigned.

    image from book
    Figure 22-26: This worksheet rates each player’s ability to play a position.

    Begin by selecting the range C3:E22, which contains the players’ ratings. Click Conditional Formatting, and then Manage Rules. Then click New Rule, and choose the Formula option. Now fill in the dialog box as shown Figure 22-27.

    image from book
    Figure 22-27: The Edit Formatting Rule dialog box set up to show player ratings in red fill.

    The formula =$A3=C$1 compares the player’s assigned position to the column heading (1, 2, or 3) in row 1. If the player’s assigned position is set to 1 (Guard), her rating in column C, which is her Guard rating, appears in red. Similarly, if the player’s assigned position is set to 2, her Forward rating in column D appears in red. Finally, if the assigned position is set to 3, her Center rating in column E appears in red.

    What does the Stop If True option in the Manage Rules dialog box do?

    Suppose the Stop If True option is checked for a rule. If a cell satisfied this rule, all lower precedent rules are ignored. To illustrate the use of Stop If True, suppose you have created a data bar format, but you don't want data bars to appear in cells having values greater than or equal to 50. Simply use Manage Rules to add a first format to the cell range which takes effect only if the value of the cell is >=50. Make the first format use an automatic (black) font and check Stop If True for the first format. Now, for all cells having values greater than or equal to 50, Excel will stop and not use the data bar format.




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net