Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Background Color How To Change Color In Excel Based On Value – Excelchat

How To Change Color In Excel Based On Value – Excelchat

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

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc