Home > Format > Conditional > Examples Excel Conditional Formatting 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. Make rules based on a selected cell's contents, or values in a different cell. Examples and videos below. Also see the Advanced Conditional Formatting Examples page.

1) What is Excel Conditional Formatting?

With Microsoft 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.

• ✅  To follow along, get the Excel sample file in the download section

2) Conditional Formatting Examples

Here are two 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

2.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, I used conditional formatting. It highlights any numbers below 50, with light red cell fill colour.

2.1.1) 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.

2.1.2) 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

2.1.3) 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

2.1.4) 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

2.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. Also, you can set it to show Top or Bottom values.

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 highest value 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 values, you might see more than 10 numbers highlighted

2.2.1) 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

2.2.2) 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

3) 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.

3.1) 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.

3.2) 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

3.3) 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

• From the first drop-down menu, you can select Top or Bottom.
  • Leave this as Top, for this example
• In the number box, you can enter a different number
  • For this example, change the number from 5 to 3
• There is also a % of the selected range checkbox, that you can check, to highlight a specific percentage of the top or bottom numbers
  • For this example, leave the box empty, to show a specific number of top or bottom values
• Click OK, to apply the rule change, and return to the worksheet
  • OR, click Cancel, and the rule will not be changed

4) 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

4.1) 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.

4.2) 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

4.3) 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

5) 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.

⏰ Video Timeline ⏰

• 0:00 Highlight High Numbers
• 1:14 Highlight Low Numbers
• 1:59 Change a Number
  

5.1) 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

5.2) 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.

5.3) 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.

6) Get the Excel Files ✅

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.

7) Down the Rabbit Hole 🐰

In this section, you'll find related Excel tips, shortcuts, skills, and details. These things are nice to know, but not essential, so you can skip this section, and get back to work. Or dive in, for some extra Excel fun and knowledge!

--1) What Cell Formatting Can You Change?

--2) What Cell Formatting Cannot Change?

--3) Less Than - Default Number

--4) Rules Manager Tips

7.1) 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

7.2) 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

7.2.1) Border Thickness

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.

7.3) Less Than - 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.

7.4) Rules Manager Tips

After you set up Conditional Formatting rules in Microsoft Excel, you might want to review the rules, or do some troubleshooting. To see the Conditional Formatting rules in the active worksheet, follow these steps:

• On the Excel Ribbon, click the Home tab
• Click the Conditional Formatting command
• Click Manage Rules
• At the top of the Conditional Formatting Rules Manager dialog box, select "This Worksheet" from the drop down list
  • 

Each rule is listed, and shows its formula, format, range it applies to, and check box for "Stop if True".

Quickly Check the Formulas

Only a small part of each formula is visible, and you can't show more. Unfortunately,

• you cannot adjust the size of the Rules Manager window
• you cannot adjust the column widths in the list of rules

However, you don't have to click the Edit Rule button, to see a full formula.

• To see a full formula, point to one of the rules
• Its full formula will appear in a pop up.

After viewing the formula, if you need to edit it, click the Edit Rule button, and make your changes.

Extra Conditional Formatting Rules

When you check the Conditional Formatting Rules Manager, you might see a problem with new rules that have been created automatically. There might be a few rules duplicated, or you might see hundreds of extra rules!

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

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Conditional Formatting

Conditional Formatting - Based on another cell

Conditional Formatting - Examples

Highlight Duplicates

Highlight Numbers

Data Bars | Color Scales | Icon Sets

Custom Icon Sets

Alternate Row Colour

Conditional Format Overview

Fix Conditional Formatting Extra Rules

Conditional Formatting - Currency

Conditional Formatting - Documentation