The default color scale in the “Conditional format rules” is subtle. For this demonstration, we’ll use more dramatic colors.
IDG
Next, under the “Format rules” heading, click Min value under “Minpoint” to open its dropdown menu. For our example, select Number. Type 10 in the entry box to the right.
Click the paint can icon to the right. From the color selection panel that opens, select the color red.
To set up a color range, assign a Minpoint, Midpoint, and Maxpoint value and color.
IDG
Select Number from the dropdown lists under “Midpoint” and “Maxpoint” too, and type in 60 and 100, respectively. Click their paint can icons and select the color yellow for Midpoint and green for Maxpoint. As you set these points and colors, you’ll see a preview of the whole color range just above.
Click the Done button.
Now when the number 60 is selected from the dropdown list, the cell’s background color turns to yellow. When you select 70, the background color turns to a yellow that has a tinge of green mixed in. When you select 100, the cell’s background color will be fully green.
Using a color scale provides a visual indicator for the values of items in a dropdown list.
IDG
Manage conditional formatting colors
If you want to change the background colors you’ve assigned to the items in a dropdown list, click to select the cell that contains the dropdown list, then click Format > Conditional formatting to open the “Conditional format rules” sidebar. In the sidebar you’ll see a list of the color assignments you’ve made for the dropdown list, each with its own color swatch.
Click the trashcan to remove a conditional color assignment.
IDG
To remove a color: Move the pointer over the color swatch and click the trashcan icon that appears to the right of the swatch.
To change a color: Click the color swatch. If it’s a single color, the sidebar will switch to the “Single color” panel. If it’s a range of colors, the sidebar will switch to the “Color scale” panel. Click the paint can icons on either panel to change colors.
To add a new color: Click Add another rule. The sidebar will switch to the “Single color” panel. If you want to assign a range of colors to the items in your dropdown list, click Scale color on the upper right to switch to this panel.
This article was originally published in November 2022 and updated in April 2024.
