Excel Conditional Formatting Color Scale Based on Another Cell

Excel only allows applying color scale conditional formatting based on only those cells. You can use the way “Use Formula” option to apply conditional formatting based on another cell but then you will lose the ability to apply the color scales gradients. So, you have to apply the colors individually using a formula. In this article, I will show you how to create a color scale with conditional formatting based on another cell in Excel.

How to Create Color Scale with Conditional Formatting Based on Another Cell: with Easy Steps

Suppose, you have a dataset containing employees’ daily wages and sales data. You want to apply color scale formatting into the wages column based on the sales value. And you want to format them using the conditions shown in the Color Scales table. In this section, I will show you one quick and easy method to apply a conditional formatting color scale based on another cell in Excel on the Windows operating system. You will find detailed explanations of methods and formulas here. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If any methods won’t work in your version then leave us a comment.

Step 1: Go to More Rules Option to Input Conditions

At first, select the cells of the Wages/Day
Then go to the Home tab in the top ribbon.
Then, select Conditional Formatting >> Color Scales >> More Rules

Step 2: Insert Formulas and Apply Colors for Different Conditions

Now, you have to apply the rules of color scales using formulas. As I have made color scales of three colors so, I have to make three individual rules for three conditions.

Applying Red When Sales/Day Is Less Than 5:

After clicking on the More Rules option, the “New Formatting Rule” window will appear.
Select the last rule type saying “Use a formula to determine which cells to format”
Then insert this formula in the rule box:

= E5 < 5

Then, click on the Format

Now, the Format Cells window will come.
Go to the Fill tab and select the Red as Background Color.
Then, press OK.

Here, you will see the wage cell has become red if sales/day is less than 5.

Applying Yellow When Sales/Day Is Between 5 & 10:

Follow a similar way to make custom rules for making color scales.
Then, paste this formula into the rule description box

=AND(E5 >= 5, E5 <= 10)

Then, go to the Format option and select Yellow as the background color.

As a result, you will see the cells of wages become yellow of which sales/day is between 5 and 10.

Applying Yellow When Sales/Day Is Greater Than 10:

Follow the same way to apply the new rule in color scale formatting as mentioned before.
Then, write this formula in the box:

= E5 > 10

Then, go to the Format option again and select green as the background color.

Now, you will see the cells of wage/day of rows where sales/day is greater than 10 become green.

Step 3: Organize the Conditions Serially

Now, you have to make a serial of the conditions by which they will be applied to the cells.

For this, go to the Home Tab >> Conditional Formatting Option >> Manage Rules

Then, mark the boxes in the “Stop If True” column for all rules.

Now, drag the rule for (<5) to the first position using the Arrows icon and place the rule (>5 & <10) in the second position then the rule (>10) will be placed at third automatically.

Now, you can check by changing the values in the Sales/Day column whether the colors of wage/day change or not.

Things to Remember

You can’t apply color scale formatting directly based on another cell in Excel.
You have to apply the individual rules for conditions to apply the color scale based on other cells in Excel.

Download Practice Workbook

You can download the practice workbook from here:

Conditional Formatting Color Scale Based on Another Cell.xlsx

Conclusion

In this article, you have found how to apply conditional formatting color scale based on another cell in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Color Scales | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!