Conditional Formatting Excel with Examples

Want your data in a spreadsheet to look visually appetising? Then learn about Conditional Formatting Excel and how to use it. It is a feature in Excel that allows you to apply specific formatting to your cells according to certain criteria. It is available in various spreadsheet applications and Excel is one among them.

These days Excel is being used in almost every field. According to Statista, Microsoft Excel has seen 10.61 million downloads in the 2nd quarter of 2022. This means the usage of this tool to sort data is going to surge in the years to come.

For your data to look more informatic and readable, Conditional Formatting is a one-stop-solution. This option is very easy to find as it is present in the very first Excel tab. This blog helps you with the basics of Conditional Formatting so you can create, edit, and copy any formatting rule. Let's delve in to learn more!

Table of Contents

1) What is Conditional formatting in Excel?

2) Creating a new Conditional Formatting Rule

3)  How to clear Conditional Formatting Rules?

4)  How to apply Multiple Conditional Formatting Rules to the same cells?

5) Conditional Formatting based on the value of another cell

6) Conclusion
 

What is Conditional Formatting in Excel?

A formatting feature of Microsoft Excel, Conditional Formatting allows users to format cells and their data based on some conditions specified by the user. It helps to spot significant trends and also enables you to make sense of your data. It is so flexible that it allows you to edit, copy, and paste any rule in the Conditional Formatting.
 

Creating a new Conditional Formatting Rule

Now that you know What is Conditional Formatting in Excel, let’s understand how to apply its rules. Here's a detailed overview:

Example 3.1 

To create a New Rule, select the cell range and then click on Conditional Formatting > New rule. Here's how:
 

Example 3.2 

The New Formatting Rule dialog window opens, from where you can select the rule type. 

In our example, we have selected Format only unique or duplicate values from the rule type options. 

Example 3.3 

You need to configure the changes by clicking on the Format button and selecting a suitable Fill color or a Font option of your choice. We have selected yellow background color to apply the rule type. The selected formatting rule gets applied once you click on the OK button, as seen in the image below. 

How to clear Conditional Formatting Rules?

The Clear Rules function helps you with quick and easy removal of Conditional Formatting in selected range and in entire worksheets.​ Here's how you can do that:

To clear the Conditional Formatting rules: 

Select the cell range. Click on Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. 

You can clear the rules from the entire sheet by clicking on Clear Rules from Entire Sheet. You can also use the keyboard shortcut “Ctrl+Shift+F” to clear Conditional Formatting.

How to apply Multiple Conditional Formatting Rules to the Same Cells? 

You are not limited to any single rule type when applying Conditional Formatting for the cells in the worksheet. You can apply multiple formatting rules as per the logical requirement. 

We have created three different rules in the example below to show you how it works. The rule types applied here are Below Average, Top 10% of the values, and Cell Value between a certain specified range.  

To re-arrange the rules: 

You can select the cell in your data which is covered by rules. 

Click on Conditional Formatting > Manage Rules... for the Rules Manager Window to open. 

You can click on the formatting rule that needs to be applied first and drag it to the top by using the upward arrow. You can make the changes based on priority. 

The Stop If True check box can be checked if you want to apply a prior condition to the rules. 

Also, the dialog box as seen above has other options like New Rule, Edit Rule, and Delete Rule to make any further changes to the applied Conditional Formatting rules. 

Conditional Formatting based on the value of another cell 

The advantage of applying this type of formatting is that the formatting of the applied cell range will change automatically as per the changes made in the cell value referred. This automatic response to change makes this type of formatting a unique feature and can save an ample amount of time in making future changes. Let us see, how this process can be formatted with the below example: 

Select the range of cells in the table. Click on Conditional Formatting > Highlight Cells Rules > Greater Than... In our example, the Rule Type we have selected is Greater Than 60% 

a) Then, place the cursor in the cell box and select cell E2. Apply the effects as required from the dropdown menu on the right of the dialog box and click on OK.  
 

Conclusion 

Mastering Conditional Formatting in Excel can significantly enhance your data visualization and analysis capabilities. By leveraging its powerful features and applying them strategically, you can highlight trends, outliers, and critical insights within your datasets. Whether you're a beginner or an experienced user, understanding the versatility of Conditional Formatting Excel can streamline your workflow and improve the impact of your Excel projects. Reading this blog post should help you to apply the Conditional Formatting rules in Excel efficiently. 

Learn how to create advanced formulas, macros and much more in our MS Excel training. Become a Microsoft Excel Expert. Sign up for Microsoft Excel Expert Training Now!