What is Conditional Formatting in Excel?

What is Conditional Formatting in Excel?

6 mins read4K Views Comment
Rashmi Karan
Manager - Content
Updated on Jan 11, 2024 17:38 IST

Conditional Formatting is a popular tool in Excel that allows adding a different format to the cells per their conditions. This way, we can easily see if the value of a cell meets a series of previously predetermined conditions since the chosen format will automatically be applied to it. Learn what is Conditional Formatting in Excel and explore different presets of Conditional Formatting to analyze and present your data in the most effective and powerful way.



How To Use Conditional Formatting In Excel?

Conditional Formatting enables determining the format of a cell and interpreting that value based on the criteria we establish. It can be colour-based, which is one of the most commonly used. Still, we can also make cells that meet the conditions bold, italic, have a larger font size, or whatever else we want. 

Home ⇒ Styles ⇒ Conditional Formatting 

Read MS Excel Tutorials

Conditional Formatting Presets

Excel offers various built-in conditions and appearances to format the cells. These are called presets that quickly apply Conditional Formatting to your cells. These presets are grouped as follows –

Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
Excel has various functions ready to process your information to get the desired results. Familiarize yourself with each of them. Once you master various functions, you can create advanced MS...read more
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.

Highlight Cell Rules

The Highlight Cells Rules option finds values in specific cells. It allows us to apply a different format to cells whose values ​​are more significant, lesser or within a range we mark. We can also get a different format if they contain a specific text, or a date, or duplicate values.

In the below example, we have used Highlight Cell Rules to identify duplicates in a dataset. To achieve this, select the dataset where you need to highlight the duplicates.

Click OK to see the highlighted duplicate cells.

Must Read – What is MS Excel?


Top/Bottom Rules

Using Top/Bottom Rules, you can use any options from the top 10, the bottom 10, the top 10%, the bottom 10%, Above average and Below average, and pick the value from a data set. This way, we can delimit all the higher and lower values ​​with a certain point and work only with those that interest us. In addition, we can also take the ten values ​​directly above or below a specific value.

For example, we have selected the Top 10 items in this data set.

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more

Data Bars

Data bars are horizontal bars; they suggest the relationship of values in a cell range. You can add data bards to the cells and present them in a bar graph form. The higher the value, the longer the bar.

We have selected the Purple Data Bar to represent the values. Data Bars allow us to see the result visually, which are the central values ​​closest to the range's maximum value.

In this case, we have applied some gradient fill data bars to the range of cells, and we see how each has a length proportional to the one with the highest value. This means that the maximum value of the cell range will always be the full horizontal bar, and the others will be calculated proportionally.

How to Calculate the Difference Between Two Dates in Excel?
How to Calculate the Difference Between Two Dates in Excel?
Whether you need to determine the number of days between two events, measure the duration of a project, or calculate an individual’s age, Excel offers a range of functions and...read more
What is a CSV File, and How to Convert CSV to Excel?
What is a CSV File, and How to Convert CSV to Excel?
Learn what is a CSV file and how to create one. Also learn how to convert CSV to Excel.

Color Scales

Colour scales allow changing the colour of each cell depending on its value. Every color scale uses a two or 3-color gradient. This can be used to see the most purchased products in a company or the pages with the most traffic on a web page.

In the coding, for example, – in the Green-Yellow-Red colour scale, Green denotes the highest values, Yellow denotes average values, and Red suggests the lowest values. You can also see some other colour scales and choose the one that suits your requirements.

Icon Sets

Microsoft Excel allows us to put different icons depending on each of the cells' information. Although it is not one of the most used, it can also be helpful to view the information more easily.

We selected the 4 Arrows (coloured) icon to mark the higher and lower values. Check how these icons have marked the data. Green arrows facing the top are higher values, yellow ones are average, and the red ones facing the bottom are the lowest values.

Note – We have arranged this dataset from largest to smallest for easier understanding of the readers. If you don’t sort the data, the table will look like the one below.

In short, these are the different options for applying a conditional format in a given range of cells. This can be applied in the different rows and columns to achieve more visual documents instead of trying to analyze between so many numbers, which is sometimes more complicated if techniques are not used to highlight the information.

How To Find Cells With Conditional Formatting

To find the cells that have Conditional Formatting, you can do it directly with the 

Home Find & SelectGo To Special command, which will help you find the cells with a specific conditional format or all the cells with Conditional Formatting in our spreadsheet.

Once you identify the cells with the Conditional Formatting, you can copy, modify or delete the conditional formats.

If you are looking for all the cells with Conditional Formatting, go to the Home tab in the Editing group, click on Find, select the option to display all the options, and then click on Conditional Formatting. This way, we identify all cells with Conditional Formatting.

Now, if we want to find only the cells with the same Conditional Formatting applied, you need to do the following: 

Home ⇒ Edit ⇒ Find & Select tab. 

Click Go To Special, then Cells with Conditional Formatting and select All or Same to the active cell.

Remove Conditional Formatting

To remove Conditional Formatting from a cell, group of cells, table or pivot table, you must go to the Home tab and click Conditional Formatting >> Clear Rules. Then you can choose the option you want to remove Conditional Formatting as appropriate. 

  • Clear Rules from Selected Cells
  • Clear rules from the Entire Sheet
  • Delete rules from This Table
  • Delete rules from This Pivot Table

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel


How do you use conditional formatting in Excel?

To use conditional formatting, select the cell range, table, or the whole sheet Go to Home, then Styles and then click conditional formatting. Click New Rule. Select a style, for example, 3-Color Scale, select the conditions that you need to apply and click OK.

What do Icon sets do?

Icon sets add a specific icon to each cell based on its value. This feature helps to tag the data for better visualization.

How does conditional formatting work in Excel?

Conditional formatting allows highlighting specified values to the cells so that they are easier to identify. This feature changes the appearance of a cell range based on the criteria provided.

Can you conditionally format a cell based on another cell?

Yes, we can do it. To format a cell basis the value of another cell, use conditional formatting and create a formatting formula.

What are the limitations with formula-based conditional formatting?

Specific limitations with formula-based conditional formatting are - We can not apply icons, color scales, or data bars with a custom formula We are limited to standard cell formattings, such as number formats, fonts, colors and borders options.

About the Author
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio