Skip to content

Conditional Formatting in Excel

Conditional formatting is a feature in Excel that allows you to alter the appearance of a cell based on a given rule or condition.

For example, let’s say you have a list of names.

You can apply conditional formatting to this list of names to automatically add a background fill color to any cells containing duplicate values.

In this case, the rule is: there must be a duplicate value among the selected range. And the applied formatting is the fill color.

And this is only one example of conditional formatting. There are many different applications of conditional formatting available in Excel.

Here are several examples.

Highlight Cell Rules

Excel lets you highlight cells that contain values that are:

  • Greater than a specific value
  • Less than a specific value
  • Between two indicated values
  • Equal to a specific value
  • Text that contains a specific value
  • A date occurring in a specific time frame
  • Duplicate or unique values

To apply this type of conditional formatting rule, follow the steps below.

  1. Select the cells you want to format
  2. Go to Home Conditional Formatting > Highlight Cell Rules > Select the Rule
  3. Complete the information in the dialog box that appears
  4. Click on Ok

After you complete these steps, Excel will apply the conditional formatting to the range of cells you selected.

Highlight Cell Rules Example

Here are some sales numbers.

 

The goal is to highlight all values greater than 35 with conditional formatting. Start by selecting the range of sales numbers.

Then go to Home Conditional Formatting > Highlight Cell Rules > Greater Than.

In the box that appears, enter 35 and select the highlight colors. Then click on Ok.

The conditional formatting is applied, and Excel highlights all sales numbers greater than 35.

Top & Bottom Rules

Excel also gives you the option to create top and bottom conditional formatting rules.

With these rules, Excel lets you highlight cells with values that are:

  • Among the top ten items
  • In the top 10%
  • Among the bottom ten items
  • In the bottom 10%
  • Above average
  • Below average
  • And more

To apply this type of conditional formatting rule, follow the steps below.

  1. Select the cells you want to format
  2. Go to Home Conditional Formatting > Top/Bottom Rules > Select the Rule
  3. Complete the information in the dialog box that appears
  4. Click on Ok

Let’s take a look at an example.

Top & Bottom Rules Example

Here are some sales numbers.

The goal is to highlight all values that are below average with conditional formatting. Start by selecting the range of sales numbers.

Then go to Home Conditional Formatting > Top/Bottom Rules > Below Average.

In the box that appears, select the highlight colors. Then click on Ok.

The conditional formatting is applied, and Excel highlights all sales numbers that are below average.

Other Conditional Formatting Rules

The options you have with conditional formatting do not end here. Check out these other articles to see how you can create other types of conditional formatting rules in Excel.

This Post Has One Comment

  1. I love these rule because it makes conditional formatting so much easier! I’m less familiar with the top and bottom rules, so I’m so glad that I got to learn more about them in this post!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top