What Excel feature can you use to automatically format cells that are greater than a specified value?

Use Excel conditional formatting to highlight worksheet cells automatically, based on rules (conditions) that you set. Make cells a different colour, or change border, font style, or number format. Rules can be based on a selected cell's contents, or values in a different cell. See the simple examples below.

What is Excel Conditional Formatting?

With Excel conditional formatting, you can highlight one or cells, when specific conditions (rules) are met.

For example, you can set a conditional formatting rules for a column where the cells contain a number:

• turn the cell red if its value is below 50

That makes it easy to spot the months with low sales.

How Does Conditional Formatting Work?

There are two steps for making conditional formatting work:

1. Select the cells that you want to format automatically
2. Set up the conditional formatting rule(s) for those cells

Usually, the rules are based on the contents of a cell -- the formatted cell, OR a different cell.

Here are examples of those 2 types of rules:

1. Change a cell's colour to red, if its value is below 50
2. Change all cells in a row blue, if the number in column B is below 30

What Cell Formatting Can You Change?

You can apply the following formats with your rules:

• Number: apply different number formats
• Font: show a different font style, or font colour, or add a strikethrough effect, or underline
  • You cannot change the font family, or font size
• Fill: change the cell fill colour and fill pattern
• Border: change the cell border colour and border style
  • You can't change the border thickness

What Cell Formatting Cannot Change?

In Conditional formatting, you cannot apply formatting options that would affect:

• the row height, OR
• the column width

For example, you cannot apply formatting to change any of the following:

• font family
• font size
• superscript
• subscript
• border thickness

Border Options

On the Borders tab for normal cell formatting, there are 6 thin border options, and 7 thicker border options. Those options are shown at the left, in the image below.

On the Borders tab for Conditional Formatting, only the 6 thin border options are available. Those options are shown at the right in the image below.

Example 1: Cell Color Based on Amount

On this worksheet, there is a list of six months, and the units sold per month.

To quickly see the months with poor sales, we'll use conditional formatting to highlight numbers below 50, with light red cell fill colour.

How to Highlight Low Numbers in List

To highlight the low numbers (below 50), follow these steps:

• Select the number cells - B2:B7
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command

• Next, in the list of options, point to Highlight Cells Rules
• In the next list that appears, click on Less Than

The Less Than dialog box opens, with

• a number automatically entered in the first box
• cell formatting selected in the second box

On the worksheet, that formatting has been applied to some of the selected cells.

Choose Settings for Less Than

Instead of leaving the default settings in the Less Than dialog box, make the following changes:

• In the first box, type 50 -- we want to highlight numbers that are less than that number
• In the second box, click the drop down arrow
• Click on Light Red Fill
• Click OK, to apply the Conditional Formatting to the selected cells

Conditional Formatting on Selected Cells

On the worksheet, the conditional formatting rule was applied to the selected cells

• Cells with a number below 50 have light red fill color
• Other cells have their original fill colour

Test the Conditional Formatting

To test the conditional formatting rule, make the following changes:

• Change the January number to 33
  • Now it's below 50, and automatically gets light red fill colour
• Change the May number to 52
  • Now it's NOT below 50, so the light red fill colour is automatically removed

Less Than Dialog Default Settings

When the Less Than dialog box opens, there is a number in the first box.

To get that default number, here's what Excel does:

• Find the highest number (Max) in the selected cells
• Find the lowest number (Min) in the selected cells
• Use the Average of those two numbers

For the default cell formatting, Excel shows the first item from the drop down list of options.

Example 2: Highlight With Top 10 Rule

A quick way to highlight cells is with the conditional formatting Top 10 rule. Despite its name of Top 10, you can adjust the rule to highlight any number of items

In this example, there are 6 months of sales amounts, for 4 different regions.

• At the right, there is a total for each month
• At the bottom, there is a total for each region
• At the bottom right, there is a grand total

We'll highlight the top 5 sales amounts, using light green fill colour, without including any of the totals.

Here are the steps to highlight the top 5 amounts:

• Select the number cells - B2:E7, where the region/month sales are entered
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, in the list of options, point to Top/Bottom Rules
• In the next list that appears, click on Top 10 Items...

The Top 10 Items dialog box opens, and

• 10 is automatically entered in the first box
• cell formatting is selected in the second box

On the worksheet, that formatting has been applied to the 10 highest amounts in the selected cells.

NOTE: If there are duplicate numbers, you might see more than 10 numbers highlighted

Set the Number

Instead of leaving the default settings in the Top 10 Items dialog box, make the following changes:

• In the first box, type 5, or use the scroll buttons to change the number
• In the second box, click the drop down arrow
• Click on Custom Format
• In the Format Cells dialog box, click on the Fill tab, then click on light green
• Click OK to close the Format Cells dialog box
• Click OK, to apply the Conditional Formatting to the selected cells

Conditional Formatting on Selected Cells

On the worksheet, the conditional formatting rule was applied to the selected cells

• Cells with the top 5 amounts have light green fill
• Other cells have their original fill colour

Change Conditional Formatting Rule

After you create a conditional formatting rule, you might want to change it.

For example, in the previous example, a Top/Bottom rule was created, to highlight the top 5 amounts. Now, we'd like to change that Top/Bottom rule, and highlight only the top 3 amounts.

Existing Conditional Formatting Rule

If you want to change an existing Top/Bottom rule, or any other type of conditional formatting rule, do not choose the option again, from the list of Conditional Formatting options, in the drop down menu.

• Choosing that option again will create a NEW rule, instead of changing the existing rule.
• You'll end up with two conflicting rules, instead of one correct rule

To successfully change a conditional formatting rule, follow the steps below. Those steps show how to find the existing rule in the Conditional Formatting Rules Manager, and change it there.

Correctly Change Existing Rule

In this example, a Top/Bottom rule was set up in cell B2:E7 (in the previous section)

To successfully change that existing conditional formatting rule, follow the steps below:

• Select cells - B2:E7, where the original conditional formatting rule was applied
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, at the bottom of the list of options, click on Manage Rules

Conditional Formatting Rules Manager

The Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells.

• Note: To see other rules, you can select from the "Show Formatting Rules For" drop down list, at the top of the Rules Manager dialog box.

To change one of the conditional formatting rules:

• Select a rule in the list, and click the Edit Rule button
• In the Select a Rule Type section, the current rule type is highlighted
• In the Edit the Rule Description section, the current settings are shown
  • In the screen shot below, the rule currently highlights the Top 5 values

In the bottom section, Edit the Rule Description, you can make changes to the rule and its formatting

• For this example, change the number of values to format, from 5 to 3
• Click OK, to apply the rule change, and return to the worksheet
  • OR, click Cancel, and the rule will not be changed

Delete Conditional Formatting Rule

If you create a conditional formatting rule, you might want to delete that rule later. For example, you no longer want to highlight the top amounts.

There are two options for deleting rule, and the steps are shown below:

• You can delete ALL the conditional formatting rules from selected cells, or the entire sheet
• Or, you can delete SPECIFIC conditional formatting rules from selected cells, or the entire sheet

Delete ALL Rules

If you want to remove all the conditional formatting rules from selected cells, or the entire sheet, follow these steps.

• Select the cells where you want to delete ALL the rules
  • Or, select any cell on the worksheet, if you want to delete all rules on the active sheet
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, near the bottom of the list of options, click on Clear Rules
• Click on one of the options:
  • Clear Rulees from Selected Cells
  • OR, Clear Rules from Entire Sheet

WARNING: The rules will be cleared immediately, with no verification message.

Tip:If you change your mind about clearing the rules, you can use the Undo command (Ctrl+Z), before doing anything else.

Delete Specific Rules

To delete a specific rule, follow these steps, to select rules in the Conditional Formatting Rules Manager:

• Select cells - B2:E7, where the original conditional formatting rule was applied
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, at the bottom of the list of options, click on Manage Rules

Conditional Formatting Rules Manager

The Conditional Formatting Rules Manager dialog box opens, showing a list of rules for the selected cells.

NOTE: To see other rules, you can select from the drop down list at the top of the dialog box.

To delete one of the conditional formatting rules:

• Select a rule in the list
• At the top of the list, click the Delete Rule button
• Click OK, to apply the rule change, and return to the worksheet
  • OR, click Cancel, and the rule will not be deleted

Video: Use 2 Rules to Format Cells

In this example, you'll set conditional formats that colour a cell, based on its own value. There will be 2 conditional formatting rules for the selected cells:

• turn green if it contains a value higher than 75
• turn red if it contains a value lower than 50.

This video shows the steps, and the written steps are below the video.

List on Worksheet

This example is shown in the video (above), and you can download the Excel file, to get the sample data.

Or, set up your own sample data, by following these steps.

1. First, type the headings:
   • Cell D1: Month
   • Cell E1: Units
2. Then, type a list of the month names in cells D2 to D13
3. Next, type amounts in cells E2 to E13, using the sample data shown below as your guide

Type Rule Amounts on Worksheet

When you're setting limits for your conditional formatting rules, those limits are easier to see and maintain, if you put them on a worksheet.

• NOTE: In this example, the limits are entered on the same sheet as the formatted cells. However, you could put the limits on a different worksheet, so nobody accidentally changes them.

Follow these steps to set up the high and low amounts for the formatting rules:

1. In cell I1, type the high value -- 75
2. In cell I2, type the low value -- 50

3. Select the cells to be formatted. In this example, cells E2:E7 are selected.
4. On the Ribbon's Home tab, click Conditional Formatting

5. To format the high values, click Highlight Cell Rules, then click Greater Than...

6. In the Greater Than window, delete the value that appears, and click on cell I1, where the High value is entered.

7. Click the drop down list for formats, and click Custom Format.

8. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.

9. Click OK to close the Format Cells window, and click OK to close the Greater Than window.

The cells with values greater than 75 are now coloured green.

Apply 2nd Rule

To colour the low values in red fill, you can apply a second conditional formatting rule to the cells.

1. Select the cells to be formatted. In this example, cells E2:E7 are selected.
2. On the Ribbon's Home tab, click Conditional Formatting

3. To format the high values, click Highlight Cell Rules, then click Less Than...

4. In the Less Than window, delete the value that appears, and click on cell I2, where the Low value is entered.

5. Click the drop down list for formats, and click Custom Format.

6. In the Format Cells window, click the Fill tab, and click on the green fill colour that you want.

7. Click OK to close the Format Cells window, and click OK to close the Less Than window.

The cells with values greater than 75 are now coloured green, and cells less than 50 are red.

Fix Conditional Formatting Extra Rules

After you set up conditional formatting rules, you might see a problem with new rules being created automatically -- you could end up with hundreds of extra rules!

This usually happens because a conditional formatting rule refers to a cell in a different row.

• In the screen shot below, a new rule was added automatically when the entry in row 10 was deleted.
• The rule refers to a cell in the current row (A3), and a cell in the row above (A2)

You can see how to clean up those extra rules, manually or with a macro, on the Fix Conditional Formatting Extra Rules page.

Conditional Formatting Icons

In Excel 2007 and later versions, you can use built-in icon sets for conditional formatting. These icon set are a quick way to highlight the high, medium and low values in a list of numbers.

Many of the icon sets use shapes in the traditional colours of green, red and yellow. A few of the icon sets are in other colours, such as all grey, or black and white.

In this example, there is a list of month names, with quantities. We'll apply a conditional formatting icon set to highlight the high, medium and low quantities in that list.

To apply conditional formatting icons, follow these steps:

• Select the number cells - B2:B7
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, in the list of options, point to Icon Sets
• In the next list that appears, click on the first icon set in the Directional section.

On the worksheet, that formatting has been applied to the selected cells.

• High numbers have a green up arrow
• Medium numbers have a yellow right arrow
• Low numbers have a red down arrow

Customize Your Own Icon Set

In Excel 2010 and later versions, you can customize the Conditional Formatting Icons, to some extent. Here’s how to do that, and build your own custom set of icons, by choosing from the individual built-in icons.

For this example, the list shows month names, and the number of errors recorded each month.

• Currently, the icon set shows a green traffic light for high numbers and a red traffic light for low numbers.
• We'd like the opposite colours applied, because a high number of errors is a bad thing, not a good thing!
• None of the built-in icon sets have red, yellow, green in that order

Select Different Icons

Here's how to change the icons, to create a custom icon set for this data:

• First, select the cells where the icon set was applied
• On the Excel Ribbon, click the Home tab
• In the Styles group, click the Conditional Formatting command
• Next, at the bottom of the list of options, click on Manage Rules

The Conditional Formatting Rules Manager opens, showing a list of rules for the selected cells.

• Select the icon set rule in the list, and click the Edit Rule button
• In the Select a Rule Type section, the current rule type is highlighted
• In the Edit the Rule Description section, the current Icon Style is shown
• To change the first icon from green to red, click the down arrow, to see all the built-in icons
• Click on the Red traffic circle icon
  • In the screen shot below, the 1st icon has been changed
• Repeat that step, to change the 3rd icon from red to green
  • In the screen shot below, the 3rd icon is being changed

After you've selected your custom icons, complete these steps:

• Click OK, to apply the rule change, and return to the worksheet
  • OR, click Cancel, and the icon set will not be changed

On the worksheet, the custom icon set has been applied to the selected cells.

• High numbers have a red traffic circle - more errors in those months
• Medium numbers have a yellow traffic circle
• Low numbers have a green traffic circle -- fewer errors in those months

Conditional Formatting Data Bars

If cells contain numbers, you can add conditional formatting data bars, to show the differences among the amounts.

Watch this short video to see how to set up data bars in a cell, and the written instructions are on the Conditional Formatting Data Bars page.

Pivot Table Conditional Formatting

To use conditional formatting in an Excel pivot table, you'll need to do a couple of extra steps. Otherwise, the conditional formatting will not adjust automatically, if you change the pivot table layout.

Watch this video to see the steps for applying conditional formatting to pivot tables cells.

Then the video shows how to adjust the rule, so new cells are correctly formatted if the pivot table layout changes. The written instructions are on the Pivot Table Conditional Formatting page.

Get the Sample File

Conditional Formatting Basics: Click here to get a zipped sample Conditional Formatting file with the examples from this tutorial. The zipped workbook is in xlsx format, and there are no macros in the file.

Pivot Table Conditional Formatting: To see the pivot tables example, download the Pivot Table Conditional Formatting workbook. The zipped Excel file is in xlsx file format, and does not contain any macros.

More Examples: For more Conditional Formatting rules and advanced examples, go to the Conditional Formatting Examples page.

Related Tutorials

Conditional Formatting - Based on another cell

Conditional Formatting - Examples

Conditional Format Overview

Fix Conditional Formatting Extra Rules

Conditional Formatting - Currency

Conditional Formatting - Documentation  

Conditional Formatting - Data Bars