CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Conditional Formatting 2

Data bar formatting can be modified from the preset to function more appropriately for the data. In this chapter, we’ll work through different settings that can be applied to the formatting rules. 

Practice Spreadsheet

Use this workbook for the chapter.

First, we need to select the cells to be formatted, press the Conditional Formatting button, and select any preset Data Bar format.

BUS115_image_23.1.png
Figure 23.1
 

Notice the data bar fills cells based on the highest value in the selected range by default. In this case, the highest value is 16, but what if we want the data bar to be based on a higher value like 100? We will need to modify the format rule.

  1. Press the Conditional Formatting button on the ribbon toolbar.
  2. Select Manage Rules….
  3. Select the data bar rule to be modified and press Edit Rule… (See Figure 23.2)
BUS115_image_23.2.png
Figure 23.2
 

The window that opens provides options for changing the rule type, format style, minimum and maximum values, and bar appearance settings. For this example, we’ll change the value settings by selecting the type to be Number and input 0 for the minimum and 100 for the maximum. Confirm the change to close the window. The data bars will update to reflect each cell’s value in relation to the maximum 100.

Color Scale Rules

Color scaling, also known as a heat map, will fill cells with one of three colors based on the value in the cell. This style of formatting is used to visually represent low to high values. (See Figure 23.3)

BUS115_image_23.3.png
Figure 23.3
 

For example, we can select the Green - Yellow - Red Color Scale to format high numbers with green, low numbers in red, mid-average numbers in yellow, and everything between varying in shade. (See Figure 23.4)

BUS115_image_23.4.png
Figure 23.4
 

What if we need to use red for high numbers and green for low or simply different colors? We can select the Red - Yellow - Green Color Scale instead, or edit the formatting rules to customize the color scheme and value settings. (See Figure 23.5)

Other color scaling options are available to use only two colors, such as the Green - White Color Scale to format the highest values in a dark color that progressively lightens toward white for low values.

BUS115_image_23.5.png
Figure 23.5
 

Icon Set Rules

Icon sets are useful for showing a change in value. If we want to see an increase or decrease in price, we’ll need individual cells with the old and new values. For this example, we’ll set up a random number generator to represent the change value. (See Figure 23.6)

  1. Type =RANDBETWEEN and a left parenthesis ( ( ) in a new cell.
  2. Type a bottom value, then a comma ( , ).
    1. In this example, we’ll insert -10 as the bottom value.
  3. Type a top value, then a right parenthesis ( ) ).
    1. In this example, we’ll insert 100 as the top value.
  4. Press Enter to complete the formula.
BUS115_image_23.6.png
Figure 23.6
 

Next, we’ll calculate the price difference by using a formula to multiply the old price by the randomly generated value as a percentage.

  1. Type an equals sign ( = ) to begin the formula.
  2. Select the cell containing the old price.
    1. In this example, the old price is contained in L3.
  3. Type an asterisk ( * ) to multiply.
  4. Select the cell containing the randomly generated value.
    1. In this example, the random value is contained in M3.
  5. Type a forward slash ( / ) to divide.
  6. Type 100.
  7. Press Enter to complete the formula. (See Figure 23.7)
    BUS115_image_23.7.png
    Figure 23.7
     

Note: We can copy and paste the price difference value over the completed formulas by using Paste Value without the formula to prevent continuous changes based on the randomly generated number.

Next, we need to add the old price with the price difference to calculate the new price in a separate cell.

Finally, we determine the percentage change by dividing the new price by the old price minus one.

  1. Type an equals sign ( = ) to begin the formula.
  2. Select the cell containing the new price.
    1. In this example, the new price is contained in O3.
  3. Type a forward slash ( / ) to divide.
  4. Select the cell containing the old price.
    1. In this example, the new price is contained in L3.
  5. Type a hyphen ( - ) to subtract.
  6. Type a 1 and press Enter to complete the formula. (See Figure 23.8)
BUS115_image_23.8.png
Figure 23.8
 

Now we can format the price change percentage cells to include icon sets. The icons can visually show positive or negative changes. However, the preset settings don’t appropriately demonstrate increase or decrease, so we’ll need to edit the rule.

The formatting rule settings include icon style and value designations to determine when and what icon is displayed. (See Figure 23.9)

BUS115_image_23.9.png
Figure 23.9
 

Format by Formula

Up to this point, we have learned how to format a cell based on the value it contains. Now we’ll learn how to format a cell based on another cell’s value using formulas. In this example, we want to format a list of part numbers based on related data cells. (See Figure 23.10)

BUS115_image_23.10.png
Figure 23.10
 

We need to create a rule by selecting the cells to be formatted. 

  1. Press the Conditional Formatting button in the ribbon toolbar.
  2. Select New Rule…
  3. Select the Use a formula to determine which cells to format rule type. 
  4. Press the Format… button to choose a format. 

We’re going to format the part number cells with a light-yellow fill based on if the part’s class is A.

  1. Type an equals sign ( = ) in the Format values where this formula is true input field.
  2. Select the cell containing the appropriate class value.
    1. The selected cell will be anchored by default in the formula. However, we do not want the cell to be anchored for this formula, so we’ll remove the anchor by pressing F4 or deleted the dollar signs ( $ ).
  3. Type an equals sign ( = ) to determine if the selected cell matches the next input.
  4. Type double quotation marks ( " ) to indicate a textual string.
  5. Type the letter A.
  6. Type double quotation marks ( " ) to close the text string and complete the formula (=W3="A"). (See Figure 23.11)
BUS115_image_23.11.png
Figure 23.11
 

Note: To format the full row of cells associated with the appropriate part number, the formatting rule must apply to the whole range of data, and the formula must anchor the column (=$W3="A"). (See Figure 23.12)

BUS115_image_23.12.png
Figure 23.12
 

If we want to format the rows of parts over a specific price, we can create an input cell containing the threshold value. Then, we'll add a conditional formatting formula to check the part's price in relation to the input cell and format the cells with a red border.

  1. Type an equals sign ( = ) in the Format values where this formula is true input field.
  2. Select the cell containing the part's price.
    1. The selected cell needs to anchor the column, but not the row to work properly.
  3. Type an greater than sign ( > ).
  4. Select the input cell containing the desired threshold value.
  5. Confirm formatting rule formula. (See Figure 23.13)
BUS115_image_23.13.png
Figure 23.13
 

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!