What is Conditional Formatting in Excel?
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.
Content
- How To Use Conditional Formatting In Excel?
- Conditional Formatting Presets
- How To Find Cells With Conditional Formatting
- Remove Conditional Formatting
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
Best-suited MS Excel courses for you
Learn MS Excel with these high-rated online courses
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 –
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.
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.
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 four Arrows (coloured) icon to mark the higher and lower values. Check how these icons have marked the data. Green arrows facing the top indicate higher values, yellow ones indicate average values, and red ones facing the bottom indicate 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 & Select ⇒ Go 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
FAQs
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.
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
Top Picks & New Arrivals
