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.


Download Practice Workbook

You can download the practice workbook from here:


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

Suppose, you have a dataset containing employees’ daily wages and sales data. And 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 Windows operating system. You will find detailed explanations of methods and formulas here. I have used 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.

Conditional Formatting Color Scale Based on Another Cell in Excel


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

Conditional Formatting Color Scale Based on Another Cell in Excel


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

Conditional Formatting Color Scale Based on Another Cell in Excel

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

Insert Formulas and Apply Colors for Different Conditions

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

Insert Formulas and Apply Colors for Different Conditions

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.

Insert Formulas and Apply Colors for Different Conditions

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

Insert Formulas and Apply Colors for Different Conditions

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.

Insert Formulas and Apply Colors for Different Conditions

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

Insert Formulas and Apply Colors for Different Conditions

Read More: How to Use Conditional Formatting with 5 Color Scale in Excel


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

Make Serial of the Conditions

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

Make Serial of the Conditions

  • 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.

Conditional Formatting Color Scale Based on Another Cell in Excel

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

Conditional Formatting Color Scale Based on Another Cell in Excel

Read More: How to Use 4 Color Scale Conditional Formatting in Excel


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.

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. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo