In this article, you will discover two fast methods to alter the background color of cells based on their values in Excel 2016, 2013, and 2010. Additionally, you will learn how to utilize Excel formulas to modify the color of empty cells or cells containing formula errors.

It’s well-known that altering the background color of a single cell or a set of data in Excel is simple—just click the Fill color button. However, what if you want to change the background color of all cells with a specific value? Moreover, what if you want the background color to adjust automatically whenever the cell value changes? Later in this article, you’ll find solutions to these questions and learn some helpful tips to help you pick the appropriate method for each task.

How to Dynamically Change a Cell's Color Based on Its Value

The background color will adjust depending on the cell's value.

Task: You have a table or a set of data, and you wish to change the background color of cells based on their values. You also want the color to change dynamically to reflect updates in the data.

Solution: You need to use Excel conditional formatting to highlight values that are greater than X, less than Y, or between X and Y.

Let’s say you have a list of gasoline prices in various states and you want prices greater than USD 3.7 to appear red, and those equal to or less than USD 3.45 to appear green.

Note: The screenshots for this example were taken in Excel 2010, but the buttons, dialogs, and settings are identical or nearly identical in Excel 2016 and Excel 2013.

Here’s what you do step-by-step:

1. Select the table or range where you want to change the background color of cells. In this example, we’ve selected $B$2:$H$10 (excluding the column names and the first column listing the state names from the selection).

2. Go to the Home tab, Styles group, and choose Conditional Formatting > New Rule….

3. In the New Formatting Rule dialog box, select "Format only cells that contain" under the "Select a Rule Type" box in the upper part of the dialog box.

4. In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as shown in the screenshot below.

   Next, click the Format… button to select the background color to apply when the above condition is met.

5. In the Format Cells dialog box, switch to the Fill tab and select the desired color, the reddish color in our case, and click OK.

6. Now you are back in the New Formatting Rule window, and the preview of your format changes is displayed in the Preview box. If everything looks good, click the OK button.

   The result of your formatting will resemble this:

   

   Since we need to apply another condition, i.e., change the background of cells with values equal to or less than 3.45 to green, click the New Rule button again and repeat steps 3 - 6 to set the required condition. Here is the Preview of our second conditional formatting rule:

   

   When you’re finished, click the OK button. What you have now is a well-formatted table that allows you to see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :)

   

   Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.

   

How to Permanently Change a Cell's Color Based on Its Current Value

Once set, the background color will not change no matter how the cell's contents might change in the future.

Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value changes.

Solution: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

This is one of those rare tasks that aren’t covered in Excel help files, forums, and blogs, and for which there is no straightforward solution. And this is understandable, because this task is not typical. Still, if you need to change the background color of cells statically i.e., once and forever unless you change it manually again, proceed with the following steps.

Find and Select All Cells That Meet a Certain Condition

There may be several possible scenarios depending on what kind of values you are looking for.

If you need to color cells with a particular value, e.g., 50, 100, or 3.4, go to the Home tab, Editing group, and click Find Select > Find….

Enter the needed values and click the Find All button.

Tip: Click the Options button in the right-hand part of the Find and Replace dialog to get a number of advanced search options, such as "Match Case" and "Match entire cell content". You can use wildcard characters, such as an asterisk (*) to find any string of characters or a question mark (?) to find any single character.

In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria:

Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl A to select all found entries. After that click the Close button.

This is how you select all cells with a certain value(s) using the Find All function in Excel.

However, what we actually need is to find all gas prices higher than 3.7, and regrettably, Excel's Find and Replace dialog does not allow for such things.

Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g., between -1 and 45, get the maximum / minimum value in a column, row, or range, find cells by font color, fill color, and much more.

You click the Select by Value button on the ribbon and then specify your search criteria on

The above is the detailed content of How to change background color in Excel based on cell value. For more information, please follow other related articles on the PHP Chinese website!