How to do Conditional Formatting in Excel?

When we start learning Microsoft Excel, we get to learn a lot of formulas and tricks. Being a beginner one thing that excited me a lot was ‘how to do Conditional Formatting in Excel’. As a beginner, I used to see a lot of sheets that were doing Conditional Formatting, and it was engaging to me. The sheets were switching cell color if the value inside the cell was above or below some number. This is just one case of conditional formatting. Further in this article, we are going to learn a lot of things that come under conditional formatting. In our last article we discussed Goal Seek, this time it is conditional formatting, So let’s begin.

How to do Conditional Formatting in Excel

Before we start talking about how to do conditional formatting, it is important to know what is conditional formatting in the first place. So I will be writing some examples of conditional formatting further in this article, and all of them are examples of conditional formatting. Be informed that these are not the only examples but there can be hundreds of examples of conditional formatting, these are just a few. Let’s begin.

Here is a mark sheet of some students. Don’t laugh at the names, I was just making this marks sheet, and these names come to my mind so I wrote them. That is all. I will be using this mark sheet to demonstrate Conditional Formatting.

Conditional Formatting Examples

1. The marks below 33 will be highlighted with a Red background in the cell.
2. The marks equal to 33 will be highlighted with a Yellow background in the cell.
3. The marks above 33 will be highlighted with a Green background in the cell.
4. If the cell contains a Pass word inside it then it would be highlighted as Green.
5. If the cell contains a Failed word inside it then it would be highlighted as Red.

I hope you have got the idea what conditional formatting is. Now let me tell you the types of conditional formatting.

Types of Conditional Formatting in Excel

1. Highlight Cells Rules
2. Top/Bottom Rules
3. Data Bars
4. Color Scales
5. Icon Sets

Highlight Cells Rules

In highlight Cells Rules we have several options.

1. Greater Than
2. Less Than
3. Between
4. Equal To
5. Text that Contains
6. A Date Occurring
7. Duplicate Values

Lets talk about each option one by one.

Greater Than

This option is used when you want to highlight a based on if the value inside the cell is Greater than a number. For example highlight, this cell is the cell has a value greater than 33, just like the example above. This option will be used. To use this option follow the steps below.

1. Select the cell or the range of cells where you want to set this conditional formatting.
2. Select the Home Tab in the Main Menu.
3. Click on Conditional Formatting on the right-hand side.
4. Move to Highlight Cells Rules.
5. Click on Greater Than.

Look at the picture below and notice the highlighted options. All the options that we are going to discuss are available near by.

The below window will pop up on the screen, you need to fill in the value. This value is the one that you want to set as the reference for Greater Than function. The values inside the selected range that are greater than this reference value will be highlighted.

Let’s say you put 33 in the format cells field. All the values greater than 33 will be selected. Next to Format Cells that are Greater Than field you have a drop-down list to select the color that you want to use to highlight the values. You will have an option to choose the custom color as well.

Less Than

Less than function is like Greater than function but it selects the values less than the reference value and highlights them. Less than option is available just below the Greater Than. Follow the steps for Greater than function and you will find less than.

1. Select the cell or the range of cells where you want to set this conditional formatting.
2. Select the Home Tab in the Main Menu.
3. Click on Conditional Formatting on the right-hand side.
4. Move to Highlight Cells Rules.
5. Click on Less Than.

Between

Between option can select the values between two numbers. Let’s say we need to select the values between 25 to 50 from a range of numbers. In such a situation, we will use Between function of conditional formatting. Between function is available just below Less Than.

Equal To

Equals To function, the name is self-explanatory. It is available just below Between function. It asks you for a value and selects only those cells that have the value Equals to the given value.

Text That Contains

Sometimes you have a range of cells and you want to highlight those cells that have a certain word in them. This option selects the value based on the string values inside it. Let’s say you have a range of cells with Pass and Failed words in it. You want to highlight Pass in Green color background and Failed in Red color background. This Option will be handy for you. Just select the range of these cells and then choose the option Text that Contains.

It will ask you for the word and next to that word it will ask you for the formatting for that cell. You need to provide the word and next to that you will provide the formatting. In case the range has any cell with that word inside it. It will apply the selected formatting to that cell or cells.

A Date Occurring

A Date Occurring is a function that selects and highlights the cells with Dates in it. When you have a range of cells with dates in it and you want to select the cells on Yesterday, Today, Tomorrow, In last 7 days etcetera. You will be able to do it easily. Look at the screenshot below with all the options that A Date Occurring has to offer.

Next to this Today, Tomorrow field you have the option to provide the formatting. In case any field is found in the range according to the option selected. The formatting will be applied.

Duplicate Values

Duplicate Values is the last option in the Highlighted Cells Rules category. This option will select the duplicate value cells and highlight them. One thing to note about this option is that it works like an Exact Match. If you have a Name in the first cell Will A and in the second cell it finds Will B. According to this option, it is not a match. It needs Will A or Will B in both fields.

After applying the above formatting to a Microsoft Excel sheet, the sheet will look something like this.

Top/Bottom Rules

Just like Highlight Cells Rules we have several options in this category as well.

1. Top 10 items.
2. Top 10 %.
3. Bottom 10 items.
4. Bottom 10 %.
5. Above Average.
6. Below Average.

Top 10 items

This option is made to select and highlight the top 10 values from the range of cells. The above range starting from 33 to 45. If we select the range and use the Top 10 items. It will select 12 values. Why 12 you might ask. Because there are two values that come multiple times. Those two values are 56 and 87. The range will look like this.

This option is not just about selecting the Top 10 items. In case you want to select Top 15 or Top 16 items you have an option to do that as well. Let me show you.

When using this option, these spin buttons you can use and increase and decrease the top items or just type it in that box. Then select the cell formatting and press the OK button. This same window will appear for Top 10 %, Bottom 10 items, Bottom 10 %. You can customize all of them by using this option.

Top 10 %

This option is made to select the top 10 percent values from the selected range. Let’s you run a school and in class 10 most of the students scored very well. You want to award the top 10 percent of students. You need to put their scores in a list in Microsoft Excel and use the Top 10 % option in Conditional Formatting.

The above list shows the score of 30 students and when we use the Top 10 % option from conditional formatting. We see we get only three names. Those names are Mark, Emanuel, and Scarlet.

Just like the Top 10 items option, you can customize the percentage here and make it Top 16 % or Top 22 % or whatever you like as per your need.

Bottom 10 Items

The Bottom 10 items are just the opposite of Top 10 items. It selects the Bottom 10 values and highlights them. There is no other difference. If you have understood the Top 10 items option nicely then I know you have understood this option too. So start using it. Let’s move on to the next option.

Not to mention this Bottom 10 items option can be made Bottom 5 items or Bottom 20 items or whatever your requirement is. Just like Top 10 items you can change it too.

Bottom 10 %

The bottom 10 % option selects the bottom 10 percent values. Look at the school example of the Top 10 Percent option where you wanted to award Top 10 percent students. Let’s say this time you want to send the latter to the parents/guardians of bottom performing students. You can change it as per your need.

Above Average

Above Average option selects the above-average values from the range of values. Let me make it clear for you from the above example. Look at the students’ scores example from the Top 10 % option. That example has scores of 30 students. Some of all the scores are 2194. We need to know the average so we will divide 2194 by 30 because of this score of 30 students. The average will become 73.1333.

Now from the scores, any student who has a score above 73.1333 will come under above average.

You can see in the screenshot above, all the values above 73.1333 are selected. If you start adding more students to this list so the average will go down and more people will come under above average if they score above 73.1333. I hope you have understood this option. Lets us move to the next option.

Below Average

This option is self-explanatory if you have understood the Above Average Option. This is just the opposite of the Above Average option. If we take the example of the Above Average option, the Below option will select those values above 73.1333.

I hope the article was helpful for you. This is not the only article, I will be writing more articles on Conditional formatting with Charts. So please wait for a few days. See you later. Till then.

Stay Safe Stay Healthy, Take Care!