How to Change Cell Color Based on Value of Another Cell

How to Change Cell Color Based on Value of Another Cell

Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data.

A conditional format changes the appearance of cells on the basis of conditions that you specify. If the conditions are true, the cell range is formatted; if the conditions are false, the cell range is not formatted. There are many built-in conditions, and you can also create your own (including by using a formula that evaluates to True or False).

Note: Formatting options that affect the size of rows or columns are not available in Conditional Formatting Rules. This includes the font and font size, and certain border styles.

Conditional formatting of monthly record high temperature data for various locations, with colors that correspond to the values in an intuitive way (hotter values are more orange/red, while cooler values are more yellow/green)

Conditional formatting with three color scale

Conditional formatting that uses cell background colors to highlight different product categories, a 3-arrow icon set to show cost trends (up, level, down), and data bars to show differences between product mark-ups.

Cell backgrounds, icon sets, and data bars used as conditional formatting

You can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report. Note that there are a few extra considerations for conditional formatting in a PivotTable report - see the Apply conditional formatting in a PivotTable report section below on the Windows tab.

Apply conditional formatting in a PivotTable report
Use Quick Analysis to apply conditional formatting
Download a sample workbook
Format cells by using a two-color scale
Format cells by using a three-color scale
Format cells by using data bars
Format cells by using an icon set
Format cells that contain text, number, or date or time values
Format only top or bottom ranked values
Format only values that are above or below average
Format only unique or duplicate values
Set up your own conditional formatting rule
Use a formula to determine which cells to format
Copy and paste conditional formatting
Find cells that have conditional formatting
Manage conditional formatting rules
Edit the order in which conditional formatting rules are evaluated
Clear conditional formatting

Note: You can't use conditional formatting on external references to another workbook.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Conditional formatting compatibility issues

Need more help?

Expand your skills
Get new features first

Related Videos