Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconGeneralbreadcumb forward arrow iconWhat is Conditional Formatting in Excel?

What is Conditional Formatting in Excel?

Last updated:
1st Apr, 2023
Views
Read Time
13 Mins
share image icon
In this article
Chevron in toc
View All
What is Conditional Formatting in Excel?

Imagine if you have hundreds of employees working under you and you need to know about the details of only twenty employees’ salaries and their departments. It is impossible to do such a thing by just pointing out, that’s where Conditional formatting comes in handy. Thus, it becomes imperative to know what is conditional formatting in excel.

Conditional formatting is a feature that allows us to sort the only cells that match according to the condition that we provide. It is mostly used to highlight or emphasise certain data and to visualize the data using bars, scales, etc. 

Let’s get started now to see what is conditional formatting in excel with example.

Select Home >> Conditional Formatting tool located in the Styles group, which has further options. Now click on the arrow.

Ads of upGrad blog

There are options like:

1. Highlight Cells Rules

2. Top/Bottom Rules

3. Data bars

4. Color Scales

5. Icon sets.

Read: Advanced Excel Formulas

Highlight Cells Rules

Highlight Cells Rules option is used to highlight only those cells that satisfy the condition. These are the part of preset rules.

Let’s take a dataset as a common example and highlight them concerning the types. Here let’s consider the range B2: D8.

There are categories under this option, such as:

Greater than

The option lets you highlight the numbers that are greater than the number that is set. 

Consider that we need the numbers greater than 362 to be highlighted. 

Some options can be selected for setting the colour to be highlighted.

Output:

Less than

This option lets you highlight only the values that are less than the specified number 40.

Between

This option can be used to highlight only the numbers that lie between two specified values.

Equal to

The option is used to highlight only the values that are equal to the given value.

Text that contains

The option lets you highlight just the words that you specify.

A date occurring

This option lets you highlight.

Duplicates

The option lets you highlight the cells that have a number repeated or the numbers that are not repeated depending on the option selected.

Example: If we have the value 194, the cells that have 194 as its value will be highlighted if we click on the option Duplicate and vice versa if we click on the option Unique.

Top / Bottom Rules

Considering you want to know about the best or worst values of a huge dataset, you can use this option. Top/ Bottom rules are another set of preset rules that allow you to input a specific number, and the output would be according to the range you previously specified.

  • Top 10 items
  • Top 10% items
  • Bottom 10 items
  • Bottom 10% items
  • Above Average
  • Below Average

 Let’s take the same set of data now. The range is B2:D8.

Top 10 items

The option lets you highlight the top 10 out of the selected cells. However, the number can be changed. By default, though, it is 10 in all the categories.

Bottom 10 items

This is used to highlight the cells that have the least values in the selected cells.

Assume that we have values from 0-1000.

Top 10% items

The option highlights the top values of the given range of cells. The top 10% will highlight the cells having values from 900-1000.

Bottom 10% items

The option highlights the cells having values of the bottom 10%.

Above Average

The Excel calculates the average value of the selected range and will highlight only those cells that are above the average value. Consider an average value 42.5, then the cells having values above this value will be highlighted.

Below Average

This option follows the same rules, but the cells having values lower than the average value will be highlighted, and that’s the only difference.

Data Bars

The data bar is an option that shows the value of the cells selected in the form of data bars. These data bars vary in the size depending upon the value in the selected cells. Example, for a higher value, the bar will be longer, and for smaller values, it would be shorter. 

We could see that the cells in column B have different kinds of data bars. For numbers such as 65, 173, the data bar is shorter compared to the data bars for numbers such as 387, 496, etc. 

Color Scales

The color scale is an option used to indicate the different set of values or cells with different types of options. In any option, the top row of color indicates the larger value and the last row of color indicates the smaller value. Here let’s take the third set.

Icon Sets

The icon sets are different kinds of highlighters that can be used to interpret data easily without having to look keenly.

The values which are less than or equal to 200 turns to red and others turn to green. 

Other Important Features

There are other three options in the conditional formatting drop-down list. 

New Rule

A new rule option lets you reset all the values or conditions that you have given in any of the options in a separate window. Using this you could do conditional formatting using formula too. The steps are as follows:

  • Click on Use a formula to determine which cells to format.
  • Enter the cell value by clicking and dragging the column where you have to use the formula. We cannot take $C$2 because only that cell will be included since we need the whole column, we would use $C2. 
  • Click Format >> Fill >> OK and again OK.

Clear Rules

Clear Rules again contain two more options, and they are Clear from the selected cells and Clear from the Entire sheet. They are used to clear the entire rule either from the rows that are selected or from the entire sheet depending upon the option you choose.

Manage Rules

Manage Rules provide you a different window just like in the New Rules option which lets you edit or delete any rules.

What is the use of conditional formatting?

The conditional formatting helps in highlighting certain values and makes certain cells much easier to identify. This affects in altering the appearance of a cell range, which is based on a condition. The conditional formatting can be applied to a range of cells, an Excel table, Excel for Windows or a whole pivot table.

The tool of conditional formatting helps in structuring the data and makes data management and working with the data a lot smoother and easier. Moreover, the working of conditional formatting helps in saving time, and money and brings much more accuracy to the work.

How to use conditional formatting in Excel?

The way to use conditional formatting is mentioned below

  1. Select the cells where you want to format.
  2. Go to Home Tab → Styles Group → Conditional Formatting
  3. Choose the rules according to your requirements
  4. Choose the format of your choice

How to Learn Excel For Free?

Ads of upGrad blog

The blog focused on understanding what is conditional formatting in-depth. Thus, we could see that MS Excel has so many time-efficient features which could be more useful and could make you more productive. 

  • Head to the upStart page
  • Select the “Getting Started with Excel – Basics & Advanced” course
  • Click the “Register” button
  • Complete the registration process

That’s it. After completing these steps, you can start learning Excel’s fundamentals and its applications in data analysis. This course will give you the necessary foundation to learn more advanced concepts later on. Head to our upStart page and enroll in this program today.

If you have any questions or suggestions regarding this course, please let us know through the comment section below. We’d love to hear from you.

Profile

Dilip Guru

Blog Author
He is a Growth Hacker, Digital Marketer & Blogger. He loves solving problems of scale and long term digital strategy.
Get Free Consultation

Select Coursecaret down icon
Selectcaret down icon
By clicking 'Submit' you Agree to  
UpGrad's Terms & Conditions

Our Popular MBA Course

Frequently Asked Questions (FAQs)

1What is MS Excel?

In 1985, Microsoft Corporation launched a spreadsheet application and software called Microsoft Excel. Since then, it has been one of the most popular spreadsheet programs which are used till date. The first version of Excel that was released in 1985 was for Mac, and for Windows, it was released in 1987. It is a spreadsheet program used to create worksheets in the form of rows and columns. Tables are employed to store and organise data in Excel. On the strength of the features, it offers like, formulas to perform complex calculations on extensive data, using filters to sort and select data, effortless representation in the forms of charts, analysis option using pivot tables, etc., a multitude of sectors.

2Are basic and advanced Excel different? If yes, then how?

Before all else, basic and advanced Excel are not two different software or programs. They are the tools put to use in Excel, which differ on the grounds of the level of the ken and intricacy. In advanced Excel, complex functions, including lofty calculations, data-driven computation, etc., are performed. In basic, Excel is used to perform ordinary calculations like aggregate, average, mode, etc. In advanced, formulas, as well as their use, are knotty. For example, INDIRECT, MATCH, SUMPRODUCT, etc. Data representation in Excel varies as per the level. In basic, charts and graphs are used, while in advanced, pivot tables, Visual Basic for Applications (VBA), etc., are used.

3What is conditional formatting?

By the same token as conditions in real life, wherein a decision is taken upon fulfilment of specific criteria or statements, conditional formatting is done in Excel. Upon entering the requirements, only those cells, rows, or columns which bring off the conditions are highlighted or displayed differently than other cells. It is a feature in Excel to change the appearance and bring into notice the cell or range of cells that satisfy the mentioned conditions, making decision-making facile.

4What are the three types of conditional formatting?

The three types of conditional formatting are Background Colour Shading, Foreground Colour Shading, and Data bars.

5What is formatting?

Formatting refers to the change of appearance, of text or numbers and make them look more presentable and easy to work with, especially when one has a large worksheet.

6Where can you do formatting in Excel?

The formatting in Excel can be done in Modify an Excel Style-Right-click the applied style → Home → Styles → Select Modify → Format.

Explore Free Courses

Suggested Blogs

How to Negotiate Salary: 8 Key Tips You Need to Know Before Negotiating
900434
Introduction Salary Negotiation is one thing everyone goes through in their lifetime at least once. You have finally landed the desired job offer, an
Read More

by Kamal Jacob

07 Nov 2023

17 Exciting Final Year Projects Ideas & Topics 2023 [Latest]
503749
Summary: In this article, you will learn the 17 Exciting Final Year Projects Ideas & Topics 2023. Take a glimpse below. Python Final Year Projec
Read More

by Rohit Sharma

07 Nov 2023

Top 7 Courses After 12th Commerce Without Maths [2023]
196771
Summary: In this article, you will learn about the top 7 courses after 12th commerce without Maths. B.Com (Bachelor of Commerce) B.Com (H) (Bachelor
Read More

by Nitin Gurmukhani

19 Oct 2023

Top 25 Highest Paying Jobs in India [2023] – Latest & Trending
2020630
Summary: In this article, we are going to learn about the top 25 highest paying jobs in India. Professions / Jobs Average Salary 2023 Highest Sala
Read More

by Nitin Gurmukhani

15 Oct 2023

Top 25 Highest Paying Jobs in the World in 2023 [A Complete Guide]
944349
Summary In this article, you will learn about the top 25 highest-paying jobs in the world. And you will be able to get the answer to ‘Which job has t
Read More

by Nitin Gurmukhani

11 Oct 2023

Career Options for Maths Students in 2023 [Top 10 Courses For Maths Students]
67617
Are you in love with the science of numbers and want a career in this field? How many of you wonder which are all the best career options for maths st
Read More

by Dilip Guru

03 Oct 2023

Top 10 Best Career Options for Science Students: Which Should You Select in 2023
198303
Choosing a career stream or science line job is one of the most path-breaking moments of an individual’s life. It defines the future course of their p
Read More

by Dilip Guru

28 Sep 2023

Top 13 BCA Project Ideas & Topics For Students in 2023
20486
As a Bachelor of Computer Applications (BCA) student, one of the essential aspects of the curriculum is completing a project that showcases their expe
Read More

by Pavan Vadapalli

28 Sep 2023

Which Engineering Stream has Highest salary in India? Which One Should You Choose?
1444
The engineering stream in India is one of the most sought-after and revered. As the digital age propels the demand for roles like software engineers a
Read More

by upGrad

24 Sep 2023

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon