There are several ways to color format cells in Excel, but not all of them accomplish the same thing. If you want to fill a cell with color based on a condition, you will need to use the Conditional Formatting feature.
Fill a cell with color based on a condition
Before learning to conditionally format cells with color, here is how you can add color to any cell in Excel.
Cell static format for colors
You can change the color of cells by going into the formatting of the cell and then go into the Fill section and then select the intended color to fill the cell.
In the above example, the color of cell E3 has been changed from No Fill to Blue color, and notice that the value in cell E3 is 6 and if we change the value in this cell from 6 to any other value the cell color will not change and it will always remain blue. What does this mean?
This means that cell color is independent of cell value, so no matter what value will be in E3 the cell color will always be blue. We can refer to this as static formatting of the cell E3.
Conditional formatting cell color based on cell value
Now, what if we want to change the cell color based on cell value? Suppose we want the color of cell E3 to change with the change of the value in it. Say, we want to color code the cell E3 as follows:
- 0-10 : We want the cell color to be Blue
- 11-20 : We want the cell color to be Red
- 21-30 : We want the cell color to be Yellow
- Any other value or Blank : No color or No Fill.
We can achieve this with the help of Conditional Formatting. On the Home tab, in the Style subgroup, click on Conditional Formatting→New Rule.
Note: Make sure the cell on which you want to apply conditional formatting is selected
Then select “Format only cells that contain,” then in the first drop down select “Cell Value” and in the second drop-down select “between” :
Then, on the first box, enter 0 and in the second box, enter 10, then click on the Format button and go to Fill Tab, select the blue color, click Ok and again click Ok. Now enter a value between 0 and 10 in cell E3 and you will see that cell color changes to blue and if there is any other value or no value then cell color revert to transparent.
Repeat the same process for 11-20 and 21-30 and you’ll see that number changes as per the value of the cell.
Conditional formatting with text
Similarly, we can do the same process for text values as well instead of numerical values by using the “Specific Text” in the first drop down and in the second drop-down select either of 4 values containing, not containing, beginning with, ending with and then enter the specific text in the text box.
For Example:
First, select the cell on which you want to apply conditional format, here we need to select cell B1. On the home tab, in the Styles subgroup, click on Conditional Formatting→New Rule.
Now select Format only cells that contain the option, then in the first drop down select “Specific Text” and in the second drop-down select either of the 4 options: containing, not containing, beginning with, ending with. In the example below, we use beginning with “J” and then select Format button to select Blue as the fill color.
Conditional format based on another cell value
In the example above, we are changing the cell color based on that cell value only, we can also change the cell color based on other cells value as well. Suppose we want to change the color of cell E3 based on the value in D3, to do that we have to use a formula in conditional formatting.
Now suppose if we want to change cell E3 color to blue if the D3 value is greater than 3 and to green if the D3 value is greater than 5 and to red, if D3’s value is greater than 10, we can do that with the conditional format using a formula.
Again follow the same procedure.
First, select the cell on which you want to apply conditional format, here we need to select cell E3. On the home tab, in the Styles subgroup, click on Conditional Formatting→New Rule.
Now select Use a formula to determine which cells to format option, and in the box type the formula: D3>5; then select Format button to select green as the fill color.
Keep in mind that we are changing the format of cell E3 based on cell D3 value, note that the cursor now is pointing at E3, which is the cell we use to set conditional format. The formula “=D3>5” means if D3 is greater than 5 then the value of E3 will change to green. Click ok and see the color of cell E3 changes to green as D3 right now contains 6.
Now let’s apply the conditional formatting to E3 if D3 is greater than 3. This means if D3>3 then cell color should become “Blue” and if D3>5 then cell color should remain green as we did it in the previous step.
Now, if you follow the above steps as we did for Green color, you will see that even if the cell value is 6, it is showing blue color and not green, because it takes the latest conditional formatting we set for that cell, and as 6 is also greater than 3 hence it is showing blue color but it should show green color.
So, we have to arrange the rules we have applied for any particular cells, we can do that by going into Manage Rules option of conditional formatting.
You can see all the rules applied to that cell and then we can arrange the rules or set their priority by using the arrow buttons. A number greater than 5 will also be greater than 3, hence greater than 5 rule will take higher priority and we can move it upward using the arrow buttons.
Now when you enter 6 in D3, the cell color of E3 will become green and when you enter 4, the cell color will become blue.
If you are tired of reading too many articles without finding your answer or need a real Expert to help you save hours of struggle, click on this link to enter your problem and get connected to a qualified Excel expert in a few seconds. You can share your file and an expert will create a solution for you on the spot during a 1:1 live chat session. Each session last less than 1 hour and the first session is free.
Leave a Comment