Based on certain values in our worksheet, we might decide to format the sheet by assigning colors based on values. In this tutorial, we will explore how to change color in excel based on values in Excel 2013, 2010 and 2016. We will also learn how to use Excel formulas to change the background colors of cells with formula errors or blank cells.
Figure 1 – How to change cell color based on the value
How to Change Cell Color Based On Value with Conditional Formatting
When we have a table and wish to change the color of certain cells dynamically, Excel conditional formatting is a great tool we can use. It will help us highlight the values less than Y, greater than X or between X and Y. To begin:
- We will select the table or range where we want to change the background color of cells. Here we have selected the entire table.
- Now, we will go to the Home tab, select styles group and choose Conditional formatting
Figure 2 – Formula for changing color
- We will select New Rule and in the New Formatting Rule dialog box, under the Select a Rule type box, we can select any rule. For now, we will select “Format Only Cells that contain”.
- In the lower part of the dialog box, where we have Format Only Cells with section, we will set the rule conditions. Next, we will choose to format only cells with a cell value between 90 and 140.
Figure 3 – New formatting rule dialog box
- Now, we will click the Format button and choose the background color we want to apply
- In the Format Cells dialog box, we will switch to the fill tab and pick the color we want. Next, we click OK.
Figure 4 – How to change font color based on the value
- We will be redirected back to the New Formatting Rule window. Here, we will preview the format changes in the Preview box. Once we are satisfied with everything, we will click OK.
Figure 5 – How to change color
How to Change the Background Color for Special Cells (cells with formula errors or blanks)
- In the Home tab, we will go to the Styles group and click Conditional formatting
- Next, we will select New Rule. In the New Formatting Rule dialog, we will select the option Use a formula to determine which cells to format.
- Now, we will enter one of the following formulas in the format values where this formula is true field. In our example, we will select the formula for changing the color of blank cells
=IsBlank() – to change the background color of blank cells.
=IsError() – to change the background color of cells with formulas that return errors.
Where () should contain the highlighted cells.
Figure 6 – Color cell based on the value of another cell
- Alternatively, we can click on Format only Cells that contain and select Blanks in the lower end of the dialog box.
Figure 7 – Color cell based on the value of another cell
- We will select the Format button and choose the background color we want in the fill Tab.
Figure 8 – How to change font color based on the value
- In the Format Cells dialog box, we will switch to the fill tab and pick the color we want. Next, we will click OK.
- We will be redirected back to the New Formatting Rule window. Here we will preview the format changes in the Preview box. Once we are satisfied with everything, we will click OK.
Figure 9 – How to excel change cell based on the value of another cell.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment