Add, change, find, or clear conditional formats in Excel

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

What do you want to do?

update or delete TOC

Format cells using a two-color scale

Format cells using a three-color scale

Format cells using data bars

Format all cells using an icon set

Format only 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

Use a formula to determine which cells to format

Find cells that have conditional formats

Change conditional formats

Clear conditional formats

Learn more about conditional formatting

Tip: If any cells in the range that you want to apply conditional formatting to contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value other than an error value.

Format cells using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, as shown below, you can specify that higher value cells have a more green color and lower value cells have a more yellow color.

Formatting all cells with a two-color scale

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Color Scales.

    Conditional Formatting

  3. Pick a two-color scale. The top color represents higher values, and the bottom color represents lower values.

    Tip: Hover over the color scale icons to see which icon is a two-color scale.

Format cells using a three-color scale

A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Color Scales.

    Conditional Formatting

  3. Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

    Tip: Hover over the color scale icons to see which icon is a three-color scale.

Format cells using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, and stretches to the left for negative values.

Data bars that highlight positive and negative values

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Data Bars, and then select a data bar icon.

    Conditional Formatting

Format all cells using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

The example shown here works with several examples of conditional formatting icon sets.

Different icon sets for the same data

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Icon Set, and then select an icon set.

    Conditional Formatting

    Tip: You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it. To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions in the New Formatting Rule box. You can also create your own combination of icon sets; for example, a green "symbol" check mark, a yellow "traffic light", and a red "flag."

Format only cells that contain text, number, or date or time values

To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast."

The example shown here works with examples of built-in conditional formatting criteria, such as Greater Than, and Top %. This formats cities with a population greater than 2,000,000 with a green background and average high temperatures in the top 30% with orange.

Formatting shows cities with more than 2 million, and top 30% of high temperatures

Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or date, only by number.

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Highlight Cell Rules.

    Conditional Formatting

  3. Select the command you want, such as Between, Equal To, or A Date Occurring.

  4. Enter the values to use, and then pick a format.

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department .

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Top/Bottom Rules.

    Conditional Formatting

  3. Select the command you want, such as Top 10 items or Bottom 10 %.

  4. Enter the values to use, and then select a format.

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Top/Bottom Rules.

    Conditional Formatting

  3. Select the command you want, such as Above Average or Below Average.

  4. Enter the values to use, and then select a format.

Format only unique or duplicate values

In the example shown below, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class (duplicate instructor names are highlighted in a rose color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.

Values in column C that aren't unique are colored rose, unique values in column D are green

Note: You can't conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

  1. Select one or more cells in a range, table, or PivotTable report.

  2. Click Home > click the arrow next to Conditional Formatting > Highlight Cells Rules.

    Conditional Formatting

  3. Select Duplicate Values.

  4. Enter the values to use, and then select a format.

Use a formula to determine which cells to format

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook.

  1. Click Home > click the arrow next to Conditional Formatting > Manage Rules.

    Conditional Formatting

  2. Do one of the following:

    • To add a conditional format, click New Rule.

    • To change a conditional format, do the following:

      1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

      2. To change the range of cells click Collapse Dialog Button image in the Applies to box to temporarily hide the dialog box, and then select the new range of cells. When you are done, select Expand Dialog Button image .

      3. Select the rule and click Edit rule.

  3. Under Select a Rule Type, click Use a formula to determine which cells to format.

    1. Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula.

      You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).

    2. Click Format in the bottom right of the box, to display the Format Cells box.

    3. Select the number, font, border, or fill format to apply when the cell value meets the condition, and then click OK.

      You can choose more than one format. The formats you select are shown in the Preview box.

      Example 1: Use two conditional formats with criteria that uses AND and OR tests    

      In the example shown here, the first rule formats two cells green if both conditions are true. If the result of that test is not True, the second rule formats two cells red if either of the conditions are False.

      Cells B4 and B5 meet their conditions, so they're formatted green

      A home buyer has budgeted up to $75,000 as a down payment and $1,500 per month as a mortgage payment. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green.

      If either the down payment or the monthly payment meet the buyer's budget, B4 and B5 are formatted red. Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells.

      Formula for first rule (applies green color)

      ==AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))

      Formula for second rule (applies red color)

      =OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))



      Example 2: Shade every other row by using the MOD and ROW functions    

      A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color. You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted. The rule uses this formula: =MOD(ROW(),2)=1.

      Every other row is shaded blue

      Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references.

Find cells that have conditional formats

If your worksheet has one or more cells with a conditional format, you can quickly locate them so you can copy, change, or delete the conditional formats. You can use the Go To Special command to either find only cells with a specific conditional format or find all cells with conditional formats.

Find all cells that have a conditional format

  1. Click any cell without a conditional format.

  2. Click Home > click the arrow next to Find & Select > Conditional Formatting.

Find only cells with the same conditional format

  1. Click the cell that has the conditional format you want to find.

  2. Click Home > click the arrow next to Find & Select > Go To Special.

  3. Click Conditional formats.

  4. Under Data validation, click Same .

Change conditional formats

  1. Click Home > click the arrow next to Conditional Formatting > Manage Rules.

    Conditional Formatting

  2. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

    To change the range of cells click Collapse Dialog Button image in the Applies to box to temporarily hide the dialog box, and then select the new range of cells. When you are done, select Expand Dialog Button image .

  3. Select the rule and click Edit rule.

  4. Make the changes you want.

  1. Select one or more cells in a range, table, or PivotTable report.

  2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

  3. Do one of the following:

    • To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.

    • To change a conditional format, do the following:

      1. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.

      2. Optionally, change the range of cells by clicking Collapse Dialog Button image in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog Button image .

      3. Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.

  4. Under Select a Rule Type, click Format only unique or duplicate values.

  5. Under Edit the Rule Description, in the Format all list box, select unique or duplicate.

  6. Click Format to display the Format Cells dialog box.

  7. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.

    You can choose more than one format. The formats you select are shown in the Preview box.

Clear conditional formats

  • Do one of the following:

    On a worksheet    

    1. Click Home > click the arrow next to Conditional Formatting > Clear Rules.

    2. Click Entire Sheet.

    In a range of cells, table, or PivotTable    

    1. Select the range of cells, table, or PivotTable to clear conditional formats for.

    2. Click Home > click the arrow next to Conditional Formatting > Clear Rules.

    3. Cick Selected Cells, This Table, or This PivotTable.

Learn more about conditional formatting

Conditional formatting helps you visually answer specific questions about your data. You can apply conditional formatting to a cell range, an Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.

The benefits of conditional formatting

Whenever you analyze data, you often ask yourself questions, such as:

  • Where are the exceptions in a summary of profits over the past five years?

  • What are the trends in a marketing opinion poll over the past two years?

  • Who has sales of more than $50,000 dollars this month?

  • What is the overall age distribution of employees?

  • Which products have greater than 10% revenue increases from year to year?

  • Who are the highest performing and lowest performing students in the freshman class?

Conditional formatting helps to answer these questions by making 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. A conditional format changes the appearance of a cell range based on conditions (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition.

You can sort and filter by format, including cell background color and font color, whether you have manually or conditionally formatted the cells. The following example shows conditional formatting that uses cell background colors, a 3 arrow icon set, and data bars.

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

Note: When you create a conditional format, you can reference only other cells on the same worksheet or, in certain cases, cells on worksheets in the same currently open workbook. You can't use conditional formatting on external references to another workbook.

Conditional formatting for a PivotTable report

Conditional formatting in a PivotTable report is different from conditional formatting in a cell range or an Excel table in several ways:

  • If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.

  • The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

    Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

  • There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box. The three methods of scoping (Home > Conditional Formatting ) give you greater flexibility depending on your needs:

Scoping by selection    Use this method if you want to select:

  • A contiguous set of fields in the Values area, such as all of the product totals for one region.

  • A non-contiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.

Scoping by value field    Use this method if you want to:

  • Avoid making many non-contiguous selections.

  • Conditionally format a set of fields in the Values area for all levels in the hierarchy of data.

  • Include subtotals and grand totals.

Scoping by corresponding field    Use this method if you want to:

  • Avoid making many non-contiguous selections.

  • Conditionally format a set of fields in the Values area for one level in the hierarchy of data.

  • Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

  • A column and its parent row field.

  • A row and its parent column field.

Conditional formatting across worksheets

You can use conditional formatting on cells that are referenced in another worksheet in the same workbook. This capability is not available between workbooks.

See Also

Change, find, or clear conditional formats dialog box options

Manage conditional formatting rule precedence

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×