What is Conditional Formatting in Excel?
Updated on Apr 03, 2023 | 13 min read | 6.5k views
Share:
For working professionals
For fresh graduates
More
Updated on Apr 03, 2023 | 13 min read | 6.5k views
Share:
Table of Contents
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.
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 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:
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:
This option lets you highlight only the values that are less than the specified number 40.
This option can be used to highlight only the numbers that lie between two specified values.
The option is used to highlight only the values that are equal to the given value.
The option lets you highlight just the words that you specify.
This option lets you highlight.
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.
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.
Let’s take the same set of data now. The range is B2:D8.
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.
This is used to highlight the cells that have the least values in the selected cells.
Assume that we have values from 0-1000.
The option highlights the top values of the given range of cells. The top 10% will highlight the cells having values from 900-1000.
The option highlights the cells having values of the bottom 10%.
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.
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.
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.
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.
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.
There are other three options in the conditional formatting drop-down list.
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:
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 provide you a different window just like in the New Rules option which lets you edit or delete any rules.
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.
The way to use conditional formatting is mentioned below
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.
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.
Get Free Consultation
By submitting, I accept the T&C and
Privacy Policy
Top Resources