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.
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.
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.
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 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)
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)
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.
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)
Next, we’ll calculate the price difference by using a formula to multiply the old price by the randomly generated value as a percentage.
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.
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)
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)
We need to create a rule by selecting the cells to be formatted.
We’re going to format the part number cells with a light-yellow fill based on if the part’s class is A.
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)
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.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/conditional_formattiF.