How to do Conditional Formatting in Excel

You might have come across a situation in which you need to analyze a huge data in Excel within a short time frame. That makes you worried how difficult it could be to analyze thousands of Excel rows with data values and present a final report. Well, there is no need to worry because we are going to tell you about a great feature of Excel called as Conditional Formatting which is going to make your data analysis a breeze.

Let us take an example of a University which has conducted an entrance exam for Management Courses. Below sheet represents the students Application number along with Name, High School Percentages, Certification Exam Score, Entrance Test Score, Sponsorship Status and whether they are applying for scholarship or not.

Refer below image

Now you need to present analysis for the data to the Selection Committee so that they can make a wise decision and publish the results accordingly. To do manual analysis over the data is surely a time-consuming task and might not be accurate. So, we will use the functionality of Conditional Formatting in Excel to bring out various analysis results out of the data. Check the various case scenarios explained below.

To find out the names of all Women Candidates in list who are married (Having Title – Mrs.)

For finding all Married Women candidates from the list we will select the data range from Cell B3:B25 and click on Conditional Formatting under Styles Group in Home Tab. Further click on “New Rule” which will open a pop up box.

Refer image below

Now you will observe different Rule Types. Select the Rule Type “Format only Cells that Contain”. In the section of Edit the Rule Description change the value to Specific Text in the first dropdown, beginning with in the second and our Text “Mrs.” In third dropdown.

Refer below image

Now, you need to set the formatting option. Click on the format box and select the color to highlight the data post application of defined condition and select Italics under Font section. You can also define other formatting options such as making text Bold, Effects, Border etc. Once you have completed the formatting options Click on OK.

Refer below image

Now all those Candidates will be highlighted having Title as “Mrs”. At the beginning of their name.

Note – We did not used the conditional formatting of “Text Contains” since in that case Cell B17 would also become highlighted which does not contain title but similar keywords.

Refer below image

Let us understand another case for finding out the highest scores from each of the Qualifying Exams.

To find out the Top Five High Scores in list.

For finding the Top five scores in respective exams we will use Conditional formatting. Select the data range from C3:C25 and click on Conditional Formatting and New Rule.

Now select the Rule type as “Format only top or bottom ranked values” and change value of drop down to Top and value to 5 in Edit description section. Now select the formatting options from the last section as desired.

Refer below image

On clicking Ok Excel will highlight the Top Five scores under High School Percentage Column.

Refer below image

Similarly, we can find out the top five scores in Certification and Entrance Test by applying conditional formatting and choosing a different color scheme to identify each data uniquely.

Refer below image

Note – In the above image for column D there are seven highlighted entries since multiple candidates have scored same number.

Let us consider a case where we need to highlight scores more than 90, scores in the range of 60 to 89 and scores less than 60.

To utilize the icon set feature of Conditional Formatting.

We will use the icon sets for highlighting the score ranges by selecting all the data range. Select Cell range C3:E25 and click on Conditional Formatting – icon sets menu item – More Rules.

Refer below image

Select the Rule Type as “Format all cells based on their values”, format style to Icon Sets and Icon Style to the desired style, set the range indicators and click Ok. For our case, we will put value as >=90 and type as Number for Right Icon, for exclamation icon we will put value as <90 and >=60 – type – Number and for Wrong it will consider all values <60.

Refer below image

Observe the conditional formatting of icon set which clearly highlights the scores as per the defined criteria.

To Highlight the Data Value of a Column.

Let us now take a scenario in which we need to highlight the data value of a column. Suppose, we need to highlight all the candidates which are industry sponsored. We will create a new rule and in the Rule Type select “Format only cells that contain”, select “Cell Value”, Select “Equal to” and “INDUSTRY SPONS” in the edit section. Choose desired formatting and apply.

Similarly, we can highlight all the candidates who have applied for Scholarships.

Refer below image

To remove the Conditional formatting choose Clear Rules then select among Cells or Sheet. You can also manage the Conditional Formatting Rules from the Manage Rules, Change Rule priority and delete the rules.

Refer below image

Conditional formatting menu has several predefined conditions ready for use such as Top 10, Below 10, Average, Duplicates, Date Occurring, Greater than & Less than etc.

Using Data Bars and Color Schemes.

To highlight the value of cells by usage of bars we will use the Data Bars under Conditional Formatting.

Select the data range and type of Data Bar you will observe that selected cells gets filled up with the data value bar indicating the cells with higher or lower values.

Similarly, you can use the Color scales on the selected data to color cells within a range of defined criteria based on Lowest value to Highest, Number, Percent or Formula.

Refer below image

So, after doing several case analysis we can easily deduce many information from the data set such as Name of Married Women Candidates, Candidates who have failed in all three tests based on the criteria, Top scores achieved, Candidates with Industry Sponsorship etc. It is a very useful feature of Excel which quickly lets you find out any variance in data range or simplify your data for further analysis or presentation.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

Tags: No tags

Add a Comment

Your email address will not be published. Required fields are marked *