Color scale conditional formatting in Google Sheets

Sometimes, adding color effects to a spreadsheet can be a terrific way to complement your data. If you are displaying a range of values like sales totals, for example, you can use color scales in Google Sheets.

With conditional formatting, you can apply a two- or three-color scale quite easily. Plus, you can take advantage of the preset color gradients or use your own custom colors. We’ll show you how to do both.

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.

Confirm or edit the cell range

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.

Choose a preset color scale

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.

Adjust the values

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.

Choose a color or create a custom color

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.

Color scale changes to data

If you want to apply your color scale rule to additional cells in your spreadsheet, you can copy the formatting in Google Sheets easily.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »