Apply a Color Scale Based on Values in Google Sheets

 

Apply a Quick Conditional Formatting Color Scale

If you want to quickly apply a color scale in Google Sheets, you can use one of the nine presets. Select the cells that you want to format by clicking the first cell and dragging through the rest. Then, head to Format in the menu and choose “Conditional Formatting.”

This opens the Conditional Format Rules sidebar, where you’ll set up everything that you need. At the top of the sidebar, click the Color Scale tab. You’ll immediately see your selected cells with the default color scale applied. This gives you a nice example of how your data will look with the gradient.

In the Apply to Range box, confirm the cells that you want to format and make edits if necessary.

Then, to use a preset, click the color scale below Preview. Here, you’ll see your nine gradient options. You can try out each option if you like by selecting one and then viewing your data.

By default, the values use minimum and maximum for the color scale without a midpoint, but you can adjust these values in the next area. You can choose a number, percent, or percentile for each and include the midpoint if it applies. Then, type in the values for these other options in the boxes to the right.

When you finish setting up your rule, click “Done” at the bottom of the sidebar.

Create a Custom Conditional Formatting Color Scale

Maybe you’d like to use specific colors for your scale. It could be your school colors or those in your company logo. Google Sheets lets you tailor the color scale exactly as you like.

You’ll follow the same steps to set up your formatting rule. Select the cells, click Format > Conditional Formatting from the menu, and confirm the cell range in the sidebar.

Click the color scale below Preview and this time, choose “Custom Color Scale” at the bottom. To get a head start, you can choose a preset as the base for your custom scale.

To the right of each value, Minpoint, Midpoint, and Maxpoint, use the color button to display the palette. You can select a color or click “Custom” at the bottom. Then, enter a Hex code or use the slider and shade tool for the color that you want.

When you finish, click “Done” at the bottom of the sidebar to apply the rule.

Whether you use a preset or a custom color scale, the conditional formatting rule adjusts if you change your data. So, when you make edits, the formatting accommodates those changes.