Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

Table of Contents

Conditional Formatting Excel

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.
 

 microsoft excel training
 

 

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.1 

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.2

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. 

Example 3.3


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 clear condition formatting rules 
 

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. 

How apply multiple condition formatting rules to the same cells 


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.  
 

Condition Formatting based on the value of another cell 
 

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! 

Frequently Asked Questions

What types of industries or professions can benefit most from mastering VLOOKUP in Excel? faq-arrow

Mastering VLOOKUP in Excel is invaluable for professionals in finance, data analysis, and marketing. Industries like banking, analytics, and sales leverage this function extensively for efficient data retrieval and decision-making. Human resources and inventory management professionals find VLOOKUP indispensable for organizing large datasets.

How does the course address common challenges and errors encountered in VLOOKUP functions? faq-arrow

The course provides practical solutions to common VLOOKUP challenges, covering issues like incorrect data types and table array limitations. Through hands-on exercises and troubleshooting tips, participants gain confidence in navigating and resolving errors in VLOOKUP functions. 

What are the other resources and offers provided by The Knowledge Academy? faq-arrow

The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide. 

Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
 

What is Knowledge Pass, and how does it work? faq-arrow

The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.

What are the related Project Management courses and blogs provided by The Knowledge Academy? faq-arrow

The Knowledge Academy offers Microsoft Excel Courses including Microsoft Excell, PowerPoint, Project Training. These courses cater to different skill levels, providing comprehensive insights into Minitab vs Excel

Our Office Application Blogs covers a range of topics related to Project Management Businesses, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Microsoft Excel skills, The Knowledge Academy's diverse courses and informative blogs have you covered.
 

Get A Quote

WHO WILL BE FUNDING THE COURSE?

cross
UP TO 40% OFF

NEW YEAR Big Sale

red-starWHO WILL BE FUNDING THE COURSE?

close

close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

close

close

Press esc to close

close close

Back to course information

Thank you for your enquiry!

One of our training experts will be in touch shortly to go overy your training requirements.

close close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.