How To Use Conditional Formatting Color Scale Based On Another Cell in Google Sheets

In this tutorial, you will learn how to use conditional formatting color scale based on another cell in Google Sheets

How To Use Conditional Formatting Color Scale Based On Another Cell in Google Sheets

Conditional formatting is a versatile tool in Google Sheets that allows you to highlight cells that meet particular criteria. For example, you can use conditional formatting to highlight all of the cells in a column that are greater than a certain value.

However, Google Sheets also allows you to add a color scale format to a range of values. For example, you may have a range of values where lower numbers are highlighted in red and larger numbers are highlighted in green.

We can also set the minimum and maximum values to consider when computing each color. These values can be set manually by the user but they may also be a cell reference.

In this guide, we will show you how to use conditional formatting to add a color scale based on another cell in Google Sheets.

How To Add Color Scale Conditional Formatting Based On Another Cell in Google Sheets

Here’s how to add color scale conditional formatting based on another cell in Google Sheets.

Step 1

Select the range where you want to add color scale formatting.

In this example, we’ll add a color scale to our score field.

Step 2

Next, select the Conditional formatting option under the Format drop-down menu.

Step 3

In the Conditional format rules panel, select the Color scale option on the top-right.

Step 4

In the Format rules section, select Number from the drop-down list for both the Minpoint and Maxpoint.

Step 5

We’ll use the formulas =INDIRECT(“Sheet2!B1”) and =INDIRECT(“Sheet2!B2”) to refer to the minpoint and maxpoint values.

We’ll need to use the INDIRECT function since Google Sheets does not allow external cell references for custom formulas.

Step 6

Next, you may choose the colors for the minpoint and maxpoint. The colors chosen here will define the color scale used for the range. Click on Done to apply these rules.

Step 7

The selected range should now have a color scale that follows the minpoint and maxpoint values specified in another sheet.

Step 8

We can try to modify the min and max values to adjust the range of the color scale.

Our range with a color scale will update with the appropriate colors automatically.

Summary

This guide should be everything you need to use conditional formatting color scale based on another cell in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.