In Excel, we can useformatting 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)
Use this workbook for the chapter.
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.Figure 21.1
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.
Note: If the wrong cell or group of cells is formatted, the formatting rule can be modified using the Conditional Formatting Rule Manager.
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.
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.
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.”)
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.
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.Figure 21.8
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)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.Figure 21.10