The Complete Guide to Conditional Formatting in Excel

This tutorial gives a complete view of how to use conditional formatting and what options are available. It also gives you a list of examples you can learn to use in the real world while working with data.

What is Conditional Formatting?

Conditional Formatting is an option to apply to a cell or a range with a condition. When you use it, it tests a condition first, and if that condition is TRUE, then only it will apply the Formatting. For example, you want to use red for all the cells in a range with an error or all the blank cells.

You can use conditional Formatting in two ways:

• Pre-Defined Rules
• Custom Rule with a Formula

In this tutorial, we will learn about both.

Simple Example to Understand Conditional Formatting

Let’s take an example to understand conditional Formatting. In the example below, you have name data here and must highlight the cells with an “Aryan” in the name.

1. For this, go to Home > Conditional Formatting > Highlight Cells Rule > Text that Contains.
   
2. Once you click on the options, it will show you a dialog box where you can enter the text “Aryan” and then define the color you want to apply to the matching cell.
   
3. It will instantly highlight the cell where the text “Aryan” is there. And after that, click OK.
   

Let’s understand this: Here, we have a condition test for the cells with the text “Aryan” in it. And only the cells where you have this text are highlighted.

Note: Conditional Formatting is one of the most useful tools to help you quickly analyze data in real-time in Excel.

Available Pre-Defined Conditional Formatting Rules

Five major options have pre-defined rules for you to use in conditional formatting. With pre-defined options, there comes the pre-defined formatting to apply.

1. Highlight Cells Rule

With the highlight cells rule, you can apply conditional formatting to the cells by checking their value. If you want to highlight a cell where a number is less than 1500, you can use the less than option from the list.

Here you have options to deal with all the kind of data, like, text, dates, and numbers.

2. Top/Bottom Rules

With the Top and Bottom options, you can analyze your data quickly. There are options to highlight the top 10 or bottom 10 values and values below or above average.

Even further, you can customize these options per your needs; let’s say, if you want to highlight the top 20 values instead of the 10, you can do that.

3. Data Bars

Data bars are tiny in-cell charts that can help you create a data bar based on the values in the selected cells. It considers the highest and lowest value and inserts data bars based on them in all the selected cells.

Tip: When you change the column’s width, the data bar adjusts its width according to that.

4. Color Scales

Color Scales help you highlight the cells based on your values in the range. There are two or three-color scales that you can use. It has 12 pre-defined color scales which you can use.

These color scales help you quickly identify data in no time.

5. Icon Sets

With Icon sets, you can apply the icon to a cell based on its values compared to those in the range. For example, if a value is higher in the range, it will get an upwards arrow; if it’s lower, it will be a downward arrow.

And as you can see, you have several icons sets to use.

Note: These pre-defined rules will be useful in over 80% of situations. And if you want to go further, you can create a custom rule.

Create a Custom New Rule

And you can also create custom rules if you want. When you click on the “New Rule”, it opens a dialog box where you can choose to create a custom rule to apply conditional Formatting.

In this dialog box, you can select any options we discussed above and apply them customized. And in this dialog box, you have one more option which allows you to apply a rule with a custom formula. You can learn more from these detailed tutorials to use conditional Formatting in a specific way:

Clear Rule

Conditional Formatting is a formatting, and you can remove it from a cell or a range of cells without removing the values.

Or you can also use the keyboard shortcut to Alt > E > A > F to clear all the formatting from the selected cell or the range.

Managing Rules

This option helps you manage all the conditional formatting rules in a workbook or selection. With Rules Manager, you can:

1. Edit or Delete a Rule.
2. Create a Duplicate or a New Rule.
3. Change the Applied to Range.
4. And can also change the order of the rules.

Stop IF True

When you check the stop is a true option for a rule, it will stop applying further rules on the cell when a condition is true on a cell.

Using Cell Formatting in Conditional Formatting

Conditional Formatting allows you to apply formatting to a cell using the format cells options. You get all the formatting options that you have in it.

• Number formatting
• Font Formatting
• Border
• Cell Formatting

Identify All the Cells where Conditional Formatting is Applied

When you open the go to special option from Home > Find and Select > Go To Special.

In the Go To Special dialog box, select the conditional Formatting, and then click OK.

And the moment you click OK, it selects the cells where the conditional formatting is applied.

Few Points to Consider Before using Conditional Formatting.

1. Excessive conditional formatting in the workbook can make it a heavy and slow Excel file.
2. While using formulas with conditional formatting, you must take care of the cell reference.
3. If you share an Excel workbook with someone else, the conditional formatting stays intact.