Apply conditional formatting in Excel

Conditional formatting gives you the ability to apply colors to cells that correspond to specific conditions such duplicate values or values that meet specific criteria—such as "greater than 100", or "equals Revenue” . There are options to Highlight Cells Rules and configure Top/Bottom Rules. You can also show how individual cells rank against a range of values with Data Bars, Color Scales and Icon Sets. Conditional formatting is dynamic, which means that formatting will automatically adjust so as your values change.

Conditional formatting options

Conditional formatting with Color Scales

Conditional Formatting selections

Conditional formatting with three color scale

Note: Prior to Excel 2007, conditional formatting rules had to be created with a formula, and only supported three levels. Since Excel 2007, conditional formatting has many predefined rules that are easy to apply, and can support up to 64 levels. If you have a formatting situation that can't be created with the pre-defined conditional formats, you can  use a formula to apply conditional formatting.

In this topic, we’ll demonstrate several methods of applying some of these pre-defined conditional formatting rules. There are thousands of potential combinations, so you should experiment with them until you find what works best for you. Also, take time to think about your audience, since too much complexity can make it difficult to understand your intent.

Follow the steps below to highlight duplicate items in a column of data:

Conditional Formatting duplicate values highlighted

  1. Choose the range in which you need to format duplicate values with a color, such as the example of Employee Name here.

  2. On the Home tab, click Conditional Formatting > Highlight Cells Rules > Duplicate Values.

    Duplicate values

  3. Select a format from the options drop-down list, then click OK.

    Highlight Duplicates formatting options

The range will display format that highlights duplicate items in the color you chose.

Sort by color

Take your duplicate search a step further by sorting your list by color. This can make it easier to apply bulk changes to the duplicates, such as deleting them.

Follow these steps:

  1. Click anywhere in your data range, then click Data>Sort & Filter>Sort.

  2. Select Employee Name for the Sort by option, choose Cell Color in the Sort On drop-down, and then choose a color in Order, and choose On Top in the last drop-down. Click OK to apply this formatting.

Data > Sort > Sort by Color options

The sorted table will group the duplicates at the top:

Conditional formatting with Duplicates values sorted to the top of a list

The Top 10 Items option can quickly identify the top performers in a range, such as the top 10 customers in a list according to rank. Similarly, you could also choose the Bottom 10 Items, Top/Bottom 10%, or Above/Below Average. You can change the top 10 to whatever value you require, after specifying a rule.

Follow the steps below to see how to highlight the top 10 revenue performances.

Conditional Formatting Top 10 Items applied to a range
  1. Select the range you want to format. In the figure above, we have chosen the Revenue column.

  2. On the Home tab, click Conditional Formatting > Top/Bottom Rules > Top 10 Items.

    Conditional Formatting Top 10 format options
  3. You can then adjust the number of options you want up or down, and the fill color.

    Conditional formatting Top 10 Items selection

When you use conditional formatting to show Data Bars, Excel draws a bar in each cell whose length corresponds to the value of the cell relative to the other cells in the selected range.

Follow the steps below to walk through an example.

Conditional Formatting Data Bars applied to a range
  1. Select the range that you want to format. In the figure above, we have chosen the Revenue column.

  2. On the Home tab, click Conditional Formatting > Data Bars . Then choose the Gradient or Solid Fill style of your choice.

    Conditional formatting Data Bar Style Gallery
  3. If you want to sort the list after applying your data bars, simply select the column. Then, on the Data tab, click Sort & Filter, and choose either the Quick Sort Ascending or Quick Sort descending option to sort in ascending or descending order.

Use a set of icons to visually indicate where values fall within certain ranges on a number or percent scale. In this section, we look at comparing regional performance to a standard with positive Conditional Formatting Positive Icon , neutral Conditional formatting Neutral icon , and negative Conditional formatting Negative icon icons.

Follow the steps below to see how it's done.

Conditional Formatting Icon Set applied to a range

  1. Select the range you want to format.

  2. On the Home tab, click Conditional Formatting > Icon Sets. Then,. choose the icon set style of your choice.

    Conditional formatting Icon Set options


  3. Excel will try to interpret your data and format accordingly. If you need to change it, go to the Home tab, click Conditional Formatting > Manage Rules. Then choose Icon Set Rule > Edit Rule. Then adjust the “Display each icon according to these rules” section. In this case, we're setting the rule so that anything greater than 45,000 is positive, anything between 40,000 and 45,000 is neutral, and anything less than 40,000 is negative.

    Conditional Formatting Icon Set options dialog

Using Color Scales, you can highlight values to show a range and compare highs and lows, in this case Jan-Jun.

Conditional formatting with three color scale

Follow these steps:

  1. Select the range that has the values you need to format.

  2. On the Home tab, click Conditional Formatting > Color Scales. Then choose the scale of your choice. In this case, we used the Red – Yellow - Green Color Scale.

    Red yellow green color scale

If you’d like to apply formatting to every other row in your data, you can do that with a conditional formatting formula. However, it’s much easier to simply format your data as a table. Select a cell within your data range and go to Home > Styles > Format as Table. Then choose the style of your choice from the Style Gallery, and Excel will instantly transform your data into a table.

Excel Table Style Gallery

If you only want the table formatting, but not the additional functionality of a table, you can convert the table back to a range. Click anywhere within the table range, then click the Table Tools tab on the Ribbon > Design > Tools > Convert to Range.

You can learn more about Excel Tables here: Create or delete an Excel table in a worksheet.

If none of the above options is what you’re looking for, you can create your own conditional formatting rule in a few simple steps:

  1. Pick the cells you want to format.

  2. On the Home tab, click Conditional Formatting > New Rule.

    New formatting rule

  3. Create your rule and specify its format options, then click OK. You can also see Use a formula to apply conditional formatting for more details.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×