How to change background color based on cell value in Excel?

When we deal with huge data in Excel, we may want to pick out some specific data and highlight them, such as change their background color. This article is talking about how to change the background color based on cell values in selections of Microsoft Excel quickly.

Change background color based on cell value with Conditional Formatting command

Change background color based on cell value with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Supposing you have a range of data, and now you need to color the values between 80 and 100, as the following screenshot shows, how could you change background color based on cell value?

doc-change-fill-color1


arrow blue right bubble Change background color based on cell value with Conditional Formatting command

Microsoft Excel's Conditional Formatting command can change background color for specific cells based on their values with following steps:

1. Select the range that you will work with;

2. Click the Home > Conditional Formatting > New Rule….

doc-change-fill-color2

3. In the New Formatting Rule dialog box, select and highlight the Format only cells that contains item in the Select a Rule Type: box.

4. In the Format Only Cells with section, specify the conditions that you need.

  • In the first drop down box, select the Cell Value;
  • In the second drop down box, select the between;
  • In the third and fourth box, enter the filter conditions, such as 80, 100.

doc-change-fill-color3

5. Click the Format button.

6. In the Format Cells dialog box, set the background color under Fill tab.

doc-change-fill-color4

7. Click OK button to save the settings. Then the specific cells with value between 80 and 100 are filled with the new background color in the selection. See screenshot:

doc-change-fill-color5


arrow blue right bubble Change background color based on cell value with Kutools for Excel

Using the Conditional formatting commands convers nearly 7 steps, and it may be hard to remember the specific settings during the operation. Therefore, we bring you another easy way: Kutools for Excel's Select Specific Cells tool, which can help you select the specific cells firstly, and fill them later.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

1. Select the range that you will work with;

2. Click the Kutools > Select Tools > Select Specific Cells….

doc-change-fill-color6

3. In the Select Specific Cells dialog box, check the Cell Option; specify the filter condition from Specific type, and then click OK button. See the following screenshot.

doc-change-fill-color7

4. Then cells containing values between 80 and 100 are selected in the selection. Go on to click the Fill button doc-change-fill-color9 under Home tab to change their background color. See screenshot:

doc-change-fill-color8

Click to know more about this Kutools for Excel's Select Specific Cells tool.


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Add comment


Security code
Refresh