Conditional formatting is used to highlight the required information.
Conditional Formatting changes the appearance of the cell on the basic of the condition which we specify . This makes it easy to highlight the information by using data bars, color scales, icons and colors. Conditional formatting helps you to explore and analyze the data, detect critical issues and identify patterns and trends.
Below we have various types of Conditional Formatting, we can use it as per our requirements :
1. Highlight Cell Rules
The Excel Conditional Formatting Highlight Cells Rules allow you to apply formatting to highlight cells that satisfy one or more specific conditions.
These conditions can relate to numeric values (e.g. greater than, between), dates (e.g. a date occurring...) or text values (e.g. text containing...). You can also use the Highlight Cells rules to highlight cells containing duplicate values.
A. Greater Than
You can use conditional formatting in Excel to quickly highlight cells that contain values greater/less than a specified value.
Step 1: Create the Data. Example I have created the Data as "Student Data" including Name, Section, City and Percentage.
Step 2: Select the Data and Go to Conditional Formatting.
Step 3: Select the Greater Than . Box will appear as below and set the Percentage and select the color to highlight the particular data.
Example: See the image below. I want to see the value greater than 90% and Color is Light Red.
Step 4: Consider the Image below. As you can see the number which are above 90% is highlighted.
B. Less Than
You have to follow the same steps as that of Greater Than. The Only difference is that in this, the lower values will be displayed.
Step 1: Select the Table. Go to Conditional Formatting and Select Less Than
Step 2: Select the "Less Than".
Step 3: Box will appear as below. As i have mentioned the value 70%. This will display the values which is less the 70%. As per your requirement, you can have get your results. You just need to mention the value.
Step 4: As you can see in the below image. This have displayed the results in yellow color.
C. Between
Some times we have huge data for example Student Data, Bank Data etc. We have to select the data like in Student Data we have to select the Students admission between the month of March and June. So in that situation if we search one by one , it will take lots of time. So we can use the Conditional Formatting - Between.
Step 1: Select the Data. Go to Conditional Formatting and Select Between
Step 2: Mention the Percentage. As i have created the Student Data which Contains the result so i want to see the students who have scored between 60% to 80%.
Step 3: So i got the results. In the below image you can see that the students and the Percentage is highlighted with green color.
In Case of Student Data, If you want to see that how many students took admission in the month of March and June, Simply mention the Month. You will get the result.
D. Equals To
Equals To is used to highlight the data which have same results. Just like in Student Data, Sometimes students score same percentage or you want to see the result which is Equal To some of the given value, the this is used.
Step 1: Select the Data. Go to Conditional Formatting and select Equals To.
Step 2: Mention the Value. As i have mention 82%, because i want to see the results that how many students scored this percentage.
Step 3: As in the below image you can see that i have 2 students who's percentage is 82%.
E. Text That Contains
This is used to Highlight the data which contains the same value or the particular Word you want to Search like In School Data its Pass or Fail, etc.
In the below example i want to see the result for the same name.
Step 1: Select the Data. Go to Conditional Formatting. Select Text That Contains.
Step 2: Mention the name or any word you want to highlight. Example, i have mentioned Aditi.
Step 3: See the below image. I got the result. In my data there are 3 students who's name is Aditi.
This Conditional Formatting - Text That Contains can be used in many ways. It can be use in Manufacturing Industry, Schools, Banks etc.
F. Date occurring
Conditional Formatting Date Occurring helps you to find the product of particular Data. Or to find the Manufacturing of the particular product.
Step 1: Select the Data and Go to Conditional Formatting and select Data Occurring.
Step 2: Select the date for which you want to search. In this we have few option:
yesterday
Today
Tomorrow
Last 7 days
Last Week
This Week
Next Week
Last Month
This Month
Next Month
Step 3: As i have select "Last Month"
Step 4: See the image below. The result shows all the dates which are occurring in the last month.
This is best used in manufacturing Industry or Packaging industry like Amazon
G. Duplicate Values
This is same as that of "Text That Contains" . The only difference is that Text That Contains gives you the particular result like the word which you want to search or you want to find the duplicate word or as per your choice.
In Duplicate Value. it will show you all the values or text which have duplicacy. We have three option in this
Duplicate
Unique
Like in this I have selected the Duplicate value so it will show me the Values throughout the Data.
See the example below:
Step 1: Select the Data. Go to Conditional Formatting and Select the Duplicate value.
Step 2: Then select Duplicate and select the color which you want to highlight the data.
Step 3: See the image below. I got the results for my Data
2. Top/Bottom Rules
The Excel Conditional Formatting Top/Bottom Rules allow you to apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.).
TOP/Bottom rules is used to highlight the cell which has the highest values in the selected cell and similar for the bottom. These conditions will only be applied to cells containing numeric values.
A. Top 10 Items
Conditional Formatting Top 10 Items is used to Highlight the Top 10 Items. For Example in School or University we want to see the Top 10 Students who have scored the highest marks, we can use this for the result.
As I have created the small Student Data having Name, Section, City and Percentage.
B. Top 10%
This will show you the highest scored percentage or highest data or a number. The Result depends upon the data you have.
C. Bottom 10 Items
Conditional Formatting Bottom 10 items will show you the bottom 10 students who have scored.
See the below image to understand much well.
D. Bottom 10%
This will show you the lowest scored percentage or a number. The Result depends upon the data you have.
E. Above Average
F. Below Average
3. Data Bars
The Excel Conditional Formatting Data Bars formats the selected cells with colored bars. The length of the data bar represents the value in the cell. The longer the bar, the higher the value.
A. Gradient Fill
B. Solid Fill
4. Colors Scales
The Excel Conditional Formatting Colors Scales applies a two- or three-color gradient to the cells. Different shades and colors represent specific values.
5. Icon Sets
Excel conditional formatting icon sets will help you visually represent your data with arrows, shapes, check marks, flags, rating starts and other objects.
How to Clear the Rules?
There are two simple ways to clear the Conditional formatting rules.
Step 1: Select the Conditional Formatting and go to Clear Rules.
Step 2: You can select From where you want to clear the rules. If you want to Clear Rules from particular Cell or Row or Column then select Clear Rules from Selected Cells.
If you want to clear the Rules from the entire data then Select Clear Rules from Entire sheet.
There is the second way to Clear the Rules. which you can say as Shortcut.
Step 1: Select the data
Step 2: Small icon will appear. Click the icon and select Clear Rules.
See the below image to see the results
Advantages of Using Conditional Formatting
It allows cells within a particular spreadsheet to be formatted automatically in accordance to what is in the cell.
The spreadsheet will have the ability to format itself as you are working on it in real time. This means that anything you do within the spreadsheet will be brought to your attention quicker if the cells were to change their format.
Conditional Formatting saves lots of time
The conditional formatting can allow you to control data in you spreadsheet in a way that will help you analyze the data in a much deeper way. This will give you a much better understanding of your statistics in the long run.
Sofia Sondh
The Tech Platform