In Excel, we can use conditional formatting to change the format of a cell-based on its value. We’ll use conditional formatting in this chapter to highlight important cells in a list of 25 or so different parts. (See Figure 21.1)

In the spreadsheet, we have price, lead time, order quantity, and part classification. Let’s suppose that these parts are being ordered from one of our vendors, and we want to keep our prices low, so we want to flag all of our parts that are over $80. We can customize cell formatting if their value exceeds $80 by creating conditional rules.

The Greater Than Rule

First, we’ll set up an input cell as a reference. Select two empty cells. Type Max price into the first, and $80 into the second. Now we’ll use the cell with the price as a reference for formatting other cells with a value greater than its own.

1. Select the cell(s) to be formatted.
2. Press the Conditional Formatting button on the ribbon toolbar.
3. Select Highlight Cells Rules and then Greater Than… to open an options window. (See Figure 21.2)

   

    
4. Type or select the input cell with the max price value into the reference field.
5. Select the desired format to be applied.
1. For this example, we’ll leave it on the default Light Red Fill with Dark Red Text.
6. Press OK to finish formatting.

Note: If the wrong cell or group of cells is formatted, the formatting rule can be modified using the Conditional Formatting Rule Manager.

1. Press the Conditional Formatting button on the ribbon toolbar.
2. Select Manage Rules… (See Figure 21.3)
   

   

    
3. Use the Applies to field to modify the cells to be formatted. (See Figure 21.4)
1. Additionally, the rule can be edited by selecting the rule and pressing Edit Rule….

The Less Than Rule

We’ll add another rule to format cells below a specific value. We’ll set up another input cell as a reference. Select two empty cells. Type Min price (for minimum price) into the first, and $40 into the second. Then we’ll select the desired cells to modify the formatting if their values are lower than the minimum price.

1. Select the cell(s) to be formatted.
2. Press the Conditional Formatting button on the ribbon toolbar.
3. Select Highlight Cells Rules and then Less Than… to open an options window. (See Figure 21.5)
4. Type or select the input cell with the minimum value into the reference field.
5. Select the desired format to be applied.

   

    

If we want to customize the formatting used for the rule instead of the default options, we would select the Custom Format… option in the drop-down list. A menu window will open with four tabs: Number, Font, Border, and Fill. For this example, we will choose to apply a green fill and a partial border format.

1. Select the Fill tab.
2. Select a preset light green.
3. Select the Border tab.
4. Select a preset dark green.
1. The border style may also be modified as desired.
5. Press the left edge button to apply the partial border. (See Figure 21.6)
6. Press OK to finish the custom format.
7. Press OK again to complete the conditional formatting rule.

The Between Rule

Another rule we can add is to format cells with values between a specific range. For this example, we’ll need to set up two input cells for the lowest and highest values in the range, and we’ll label them LT Bottom and LT Top respectively. The LT Bottom will be 6, and LT Top will be 12.

Note: The LT abbreviation represents “Lead Time.”)

1. Select the cell(s) to be formatted.
2. Press the Conditional Formatting button on the ribbon toolbar.
3. Select Highlight Cells Rules and then Between… to open an options window. (See Figure 21.7)
4. Type or select the input cell for the lowest value into the first reference field.
5. Type or select the input cell for the highest value into the second reference field.
6. Select the desired format to be applied.
7. Press OK to finish formatting.

Conditional Formatting Rules Manager

The Conditional Formatting Rules Manager lists all the rules applied to the selected cell(s). It can also show all rules in the worksheet by selecting the Show formatting rules for drop-down list on the top of the window. Any conditional formatting rule can be modified or deleted from the manager.

1. Press the Conditional Formatting button on the ribbon toolbar.
2. Select Manage Rules….
3. Press the Edit Rule… button to open an options window. (See Figure 21.8)
1. The new window will include four tabs: Number, Font, Border, and Fill. We can customize the format with various options from these tabs. Multiple format options can be selected and combined as well.

If more than one conditional formatting rule can apply to the same cell(s), the manager displays the rules that are applied in order from top to bottom; rules listed at the bottom are applied last and will override the previous rule formatting. To prevent overriding previous formatting rules, the Stop if True checkbox on the right for the rule to be last.

Data Bars and Icon Sets

The Data Bars and Icon Sets are also available to format cells based on their values. Data Bars may be used to view in-cell charts for quick visualization of data comparison. (See Figure 21.9)

Icon Sets can be useful to represent values at specific levels. For example, if we format an Order Quantity column to see which items have sufficient quantities, items that are reaching low inventory, and items that fall under the desired amount, we can select a basic icon set with green, yellow, and red icons. (See Figure 21.10)

Note: The rule may need to be modified to appropriately represent the desired effect.