Conditional Formatting in Excel - Create New Rule, Multiple Conditional Formatting, Basic Uses

In Microsoft Excel, Conditional Formatting is a very good tool to view or identify specific data very quickly. Conditional formatting is very popular and very useful tool because of its' property to highlight data based upon one or more condition provided. 

Conditional formatting is also able to highlight specific data depending upon the result of a given formula.

Conditional formatting is really a powerful feature in Excel when it comes to applying different formats to data that meets certain conditions. It can help you to highlight the most important information in your spreadsheets and identify variances of cells' values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners.

Please read this article till the end, practice this tool in Excel and you will be able to master this tool very beautifully.

So, friends, lets' learn it.

Excel conditional formatting - The Basic

We use conditional formatting in Excel to format our data in different ways by changing cells' fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain criteria or conditions or some output of formula.

You can apply conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell's value. We do this by creating rules or conditions where we define when and how the selected cells should be formatted.

Under the Home tab you can find Conditional formatting in the Styles section as shown in the below picture.

Conditional Formatting

When we click on the Conditional Formatting button we see a drop down list as shown in the below image.

Conditional Formatting

Conditional formatting in Microsoft Excel is one of the flexible tools as it allows you to use custom rules and in built rules. Let us discuss about the inbuilt or default rules in conditional formatting in Microsoft Excel. 

How to create Excel conditional formatting

In Home tab under Styles you will find Conditional formatting button, under this drop down list the first option is Highlight Cells Rules which is also sub categories into eight categories, each of these categories are self explanatory.

Default Rules in Excel Conditional Formatting

Conditional formatting rules in Excel define 2 key things:

• What cells the conditional formatting should be applied to, and

• Which conditions should be met.

Steps

1. In your Excel spreadsheet, select the cells you want to format then

2. Go to the Home tab > Styles group and click Conditional Formatting. You will see a number of different formatting rules.

3. Suppose we need to apply conditional formatting only to the Target Column's numbers Greater than 30.5, we choose Highlight Cells Rules > Greater Than... as shown in the below picture.

Default Rules Greater than in Excel Conditional Formatting

Of course, you can go ahead with any other rule type that is more appropriate for your data, such as:

o Format values greater than, less than, between, equal to

o Highlight text containing specified words or characters

o A date occurring

o Highlight duplicates

o Format specific dates

4. Select the format you want from the drop-down list. You can choose one of the pre-defined formats or click Custom Format... to set up your own formatting as shown in the below picture.

Custom Formatting in Excel Conditional Formatting

5. When you click on the OK button a formatting window will open where you can customize cell or font color according to you.

Formatting Window

Creating an Excel conditional formatting New Rule

If none of the ready-to-use formatting rules meets your needs, you can always create a new one. To create your own Conditional Formatting rule you need to follow few steps.

1. Select the cells to which you want to apply the conditional format and click Conditional Formatting > New Rule.

Conditional Formatting through New Rule

2. The New Formatting Rule dialog opens and you select the needed rule type. For example, let's choose "Format only cells that contain" and opt to format the cell values between 30 and 40.

Conditional Formatting through New Rule

3. Click the Format... button and set up your formatting.
Click OK to close the open windows and your conditional formatting is done!

Conditional Formatting Applied using New Rule

Excel conditional formatting based on cell value

Sometimes we need to format cells depending on the value they contains, Microsoft Excel's Conditional Formatting provides us options to do this. 

For Example if we need to highlight the cells in Target column containing value 22, we can use this property of Conditional Formatting to highlight those cells.

Steps are

1. Select the Column or select the range

2. Click on Conditional Formatting >Format all cells based on their values

3. Now select the formatting as per your requirement as shown in the below picture.

Conditional Formatting Based upon Cell Value

Multiple conditional formatting rules

When using conditional formatting in Excel, you are not limited to only one rule per cell. You can apply as many rules as your project's logic requires.

1. Click Conditional Formatting > Manage Rules... to bring up the Rules Manager.

Conditional Formatting with Multiple Rules

2. Click New Rule, this will open the New Formatting RUle window.

3. Click Format Only Cells That Contains for example,

4. Click on the drop down list near Cell Value and select Equal to and type 22 in the box

5. Select Format which will open Format Cells window

6. Select proper formatting and click OK twice.

7. The Rules Manager will look like the below image.

Conditional Formatting with Multiple Rules

8. Now if you want to enter another rule Click on New Rule and repeat the steps above.

9. You can Delete or Edit rules by clicking Delete Rule or Edit Rule button.

Conditional Formatting with Multiple Rules

This Steps on Conditional Formatting the Basics, in another article I will discuss about the advanced conditional formatting.
Hope you have enjoyed this article. Thank you for reading...