While you are working in excel, a time comes when you need to work at a fast pace, but you are unable to do so or there are some features that you want to utilize yet they come under conditional formatting.

But whenever you google the same or try a formula with it, you end in screwing up🤕!

Fix 6 Common Excel Errors You’re Tired of seeing

So, this blog is all about conditional formatting. We will be explaining every feature that conditional formatting provides🤓.

Conditional Formatting in Excel (Step-by-Step Tutorial) 85

Highlight Cells Rules

It has the following features:

  1. Greater Than
  2. Less Than
  3. Between
  4. Equal To
  5. Text that Contains
  6. A Date Occurring
  7. Duplicate Values
  1. Greater Than
Conditional Formatting in Excel (Step-by-Step Tutorial) 86

As you can see in the image above, we have taken product ID, Product Code, Colour and Sales. Now, let’s say we want to highlight the sales which are greater than 65000.

Conditional Formatting in Excel (Step-by-Step Tutorial) 87

Step 1: So, to do the same we will select the sales range as we want to highlight the sales data which is greater than 65000.

Conditional Formatting in Excel (Step-by-Step Tutorial) 88

Step 2: Now, we will go to the styles and select conditional formatting. In the same, we will select “Highlight Cells Rules” and then we will click on “Greater than” (just like it is shown in the image above).

Conditional Formatting in Excel (Step-by-Step Tutorial) 89

Step 3: Now,as you can see in the image above, a “Greater Than” dialogue box will appear on the screen.

There you need to enter the value i.e. 65000 (as per the example) and as per your wish, you can select the colour you want.

Step 4: Now as you can see that most of the colours are shades of red, yellow or green, so if you want to pick, say blue, you need to go to the option of custom format (like it’s shown in the image above).

Conditional Formatting in Excel (Step-by-Step Tutorial) 90

Step 5: Then you need to go to the “Fill” and choose the color you want, and press “OK”.

Conditional Formatting in Excel (Step-by-Step Tutorial) 91

Step 6: After choosing the color you want, you will return back to the Greater Than dialogue box, where all you have to do is press “OK”.

Conditional Formatting in Excel (Step-by-Step Tutorial) 92

Step 7: And there you go, all the sales values which are below 65000 are highlighted with “blue” color.

Conditional Formatting in Excel (Step-by-Step Tutorial) 93

Now, we will move to the second option:

  • Less Than
Conditional Formatting in Excel (Step-by-Step Tutorial) 94

As you can see in the image above, we have taken the same data to test Less Than of Conditional Formatting. Also, we have added some color which looks bit attractive and glowy now!

Conditional Formatting in Excel (Step-by-Step Tutorial) 95

Step 1: So, to start with the process of highlighting the sales value which are less 25000, the first thing that we need to do is to select the sales range.

Conditional Formatting in Excel (Step-by-Step Tutorial) 96

Step 2: After the same, we do as follow:

Styling < Conditional Formatting < Highlight Cells Rules < Less Than…

(Just like it’s shown in the image above👆).

Conditional Formatting in Excel (Step-by-Step Tutorial) 97

Step 3: After we select the Less Than option, a dialogue box will appear on the screen like the one it’s shown in the image above.

All we have to do is jot down the value, like we did i.e. 25000 and we will pick a color of choice which will be highlighting the data.

Conditional Formatting in Excel (Step-by-Step Tutorial) 98

Step 4: The same process comes, i.e. go to the Fill section and select the color then press OK.

Conditional Formatting in Excel (Step-by-Step Tutorial) 99

Step 5: The second you press OK you return back to the Less Than dialogue box, again press Ok.

Conditional Formatting in Excel (Step-by-Step Tutorial) 100

Step 6: And there you go, it’s that simple.

Conditional Formatting in Excel (Step-by-Step Tutorial) 101
  • Between

Now, let’s say we want to highlight the sales data made between 40000 to 60000.

*Please Note: As the values are in small quantity, so its easy to figure out things, but in the real World, this is not how it works. There are thousands of data entries which is why conditional formatting is not a joke! So, you need to take it seriously. (P.S. you are reading this for a reason😜!)

Conditional Formatting in Excel (Step-by-Step Tutorial) 102

As you can see in the image above, we have used the same data we used in the greater than and less than.

Conditional Formatting in Excel (Step-by-Step Tutorial) 103

Now, to highlight the sales data which is between 40000 to 60000, this is what we do:

Step 1: The first thing that needs to be done is to select the sales range🤓.

Conditional Formatting in Excel (Step-by-Step Tutorial) 104

Step 2: Next, we will go through the same process🤔 like we did in the greater than and less than but instead, this time we will choose the Between option in the end.

Styling < Conditional Formatting < Highlight Cells Rules < Between

Conditional Formatting in Excel (Step-by-Step Tutorial) 105

Step 3: Then a dialogue box will appear for the same, there we need to fill in the values so be more accurate or specific, so we will put 40000 on one side and 60000 on the other, then we will pick the color we want.

Conditional Formatting in Excel (Step-by-Step Tutorial) 106

To pick the color all we got to do is go to the fill section, select the color of choice and press ok, like we did before for the other options.

Conditional Formatting in Excel (Step-by-Step Tutorial) 107

Step 4: After returning to the Between dialogue box, we will again press OK.

Conditional Formatting in Excel (Step-by-Step Tutorial) 108

Step 5: And BAAAMMM🔫! There you go😎! All the sales values that lie between 40000 to 60000 are highlighted with blue color.

Conditional Formatting in Excel (Step-by-Step Tutorial) 109

Now we will move to the fourth option:

  • Equal To

Now let’s say we want to find the sales value which is exactly equal to 45000.

Conditional Formatting in Excel (Step-by-Step Tutorial) 110

To find the same, this is what we will do:

Step 1: We will select the sales range.

Conditional Formatting in Excel (Step-by-Step Tutorial) 111

Step 2: Next, we will do as it’s shown in the image above:

Styling < Highlight Cells Rules < Equal To

Conditional Formatting in Excel (Step-by-Step Tutorial) 112

Step 3: Then we will jot down the value in the dialogue box i.e. 45000 and we will choose the color of choice.

Step 4: You see, if you are okay with picking up the colors which are already available in the option, then it’s great, but if it’s not then you can always choose the option of custom format.

Conditional Formatting in Excel (Step-by-Step Tutorial) 113

Step 5: After selecting the custom format, you need to select the Fill option from the dialogue box which just popped up on your screen.

Step 6: In the Fill option, we can choose the color of choice and then press ok.

Conditional Formatting in Excel (Step-by-Step Tutorial) 114

Step 7: After the same, we return to the equal to dialogue box, where again we need to press OK👍.

Conditional Formatting in Excel (Step-by-Step Tutorial) 115

Step 8: And there it is, the sales value 45000 is highlighted in the selected range.

Please note: If there would be no value which would be equal to what you filled in, then nothing will be highlighted.

Conditional Formatting in Excel (Step-by-Step Tutorial) 116
  • Text that Contains
Conditional Formatting in Excel (Step-by-Step Tutorial) 117

Now to test the following feature, we will highlight the text that contains Grey.

Excel is a very efficient built software, it’s always UpToDate.

So, if we are talking about last month, it will highlight the dates occurring in last month only!

Conditional Formatting in Excel (Step-by-Step Tutorial) 118

Now, let’s get started with the text that contains Grey 😇.

Step 1: All we have to do is select the whole range, its not necessary to always select that range where all the answers are, if you want, you can just select the whole table.

Please note: In the previous examples, as we were dealing with numbers and the other range (say product ID) also had numbers in it, it could lead into a huge confusion. Plus, we were looking for sales data, so it clears the doubt!

Conditional Formatting in Excel (Step-by-Step Tutorial) 119

Step 2: After the selection of the table, we will do the following:

Styling < Conditional Formatting < Highlight Cells Rules < Text that Cont

Conditional Formatting in Excel (Step-by-Step Tutorial) 120

Step 3: Then a dialogue box will appear on the screen (just like the one shown in the image above).

There you need to fill the data that you want to show in highlights.

Step 4: So, as you can see in the image above, we have jotted down Grey, and as we are okay with the color red, so all we have to do now is press OK.

*Please Note: If you want to choose any other color, then you can follow the process that we have showed in the earlier options, i.e. Click on drop down menu < Custom Format < Fill option < Choose the color of your choice < Press OK.

Conditional Formatting in Excel (Step-by-Step Tutorial) 121

Step 5: And there you go, the text that contains Grey have been highlighted with the Light Red color.

  • Date Occurring

What we are going to do is that we will highlight the dates that are of the last month.

Conditional Formatting in Excel (Step-by-Step Tutorial) 122

Step 1: Select the table range😊 (as shown in the image above).

Conditional Formatting in Excel (Step-by-Step Tutorial) 123

Step 2: Next, do the following process:

Styling < Conditional Formatting < Highlight Cells Rules < A Date Occurring…

(Just like it’s shown in the image above)

Conditional Formatting in Excel (Step-by-Step Tutorial) 124

Step 3: Then, a dialogue box will appear whereas you can see in the image above, a bunch of options will be provided, and you can choose accordingly. Now, as per the example we need the dates that are occurring in the last month, so we will select Last month from the drop-down menu

Conditional Formatting in Excel (Step-by-Step Tutorial) 125

Step 4: Then we will select the color of choice (let’s say yellow) just like it’s shown in the image above. 

Conditional Formatting in Excel (Step-by-Step Tutorial) 126

Step 5: Then we will press OK.

*Please note🤓: You need to be careful while adding your specifications (be it any option. This is because, if you require the dates occurring of last month yet you select Last week then it can mess things up.

Conditional Formatting in Excel (Step-by-Step Tutorial) 127

Step 6: And there you go… All the dates that are occurring in the Last Month are highlighted with the color Yellow.

Conditional Formatting in Excel (Step-by-Step Tutorial) 128

It’s easy, right?

  •  Duplicate😱 or Unique🤔:
Conditional Formatting in Excel (Step-by-Step Tutorial) 129

As you can see in the image above, we have taken 2 Ranges’. Range 1 and Range 2 where we have mentioned some numbers in it.

This type of a method is used when you are dealing with thousands of data entries and you need to check whether the order so placed in the beginning got delivered or not. This can be confirmed by duplicity. If the no. is present in both the ranges, it means that it was placed and got delivered. But if the same is only showing up in order placed, this means that the following product is a unique value hence, not delivered.

Conditional Formatting in Excel (Step-by-Step Tutorial) 130

Step 1: Now the first thing to do is to select both the ranges.

Conditional Formatting in Excel (Step-by-Step Tutorial) 131

Step 2: Then do the following:

Styling < Conditional Formatting < Highlight Cells Rules < Duplicate Values…

Conditional Formatting in Excel (Step-by-Step Tutorial) 132

Step 3: The second you click on Duplicate Values; a dialogue box will appear on the screen just like the one shown in the image above.

Now, all you have to do is select Duplicate in the first drop down menu, and select the color of choice in the second drop down menu👻.

And there you go, you can observe that all those data that are duplicate, i.e. present in both the ranges have been highlighted with Light Yellow color.

Conditional Formatting in Excel (Step-by-Step Tutorial) 133

Step 4: Now, to highlight those data that are unique, i.e. not present in the 2nd Range, this is what we will do:

Styling < Conditional Formatting < Highlight Cells Rules < Duplicate Values…

Conditional Formatting in Excel (Step-by-Step Tutorial) 134

Step 5: The second you click on duplicate values; you need to select Duplicate from the first drop down menu and the color of choice (let’s say light green👽) from the second drop down menu.

Conditional Formatting in Excel (Step-by-Step Tutorial) 135

Step 6: And there you go, all those data that are present in both the ranges, i.e. duplicate are highlighted with Light Yellow color and the ones which are Unique are highlighted in Light Green color.

Conditional Formatting in Excel (Step-by-Step Tutorial) 136

 Top/ Bottom rules

These are 6 in number:

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

Now, let’s get started with the first option:

  1.  Top 10 items…
Conditional Formatting in Excel (Step-by-Step Tutorial) 137

Kindly ignore the left-hand side and right-hand side data, as it is something for the later.

Let’s concentrate in the middle first.

Conditional Formatting in Excel (Step-by-Step Tutorial) 138

Okay, so as you can see, we have taken the sales data of some organization which is of the month January.

Now, to begin with the Top 10 items,

Step 1: We will select the Sales range.

Conditional Formatting in Excel (Step-by-Step Tutorial) 139

Step 2: Then we will do as follows:

Styling < Conditional Formatting < Top/ Bottom Rules < Top 10 items…

(Just like it’s shown in the image above👆.)

Conditional Formatting in Excel (Step-by-Step Tutorial) 140

Step 3: Next, we’ll see a dialogue box appear on the screen where in the first option we can select the no. because its necessary to always have the data of top 10, you can also have a data of top 3, or etc. And the second is for color of choice which you know already (about how it works!).

Conditional Formatting in Excel (Step-by-Step Tutorial) 141

Step 4: After you put the specifications in the dialogue box🤓, you can feel free to hit OK, and see that the top 10 items have been highlighted.

This feature is very beneficial for organizations where they need to keep a record of such things because these things help to make a sale forecast later.

  • Top 10%
Conditional Formatting in Excel (Step-by-Step Tutorial) 142

Then we will move to Top 10% which is shown in the image above.

By top 10% we mean that from all the data that you have selected, this is the value which comes in the top 10%.

Step 1: To find out the same, the procedure remains the usual:

Styling < Conditional Formatting < Top/ Bottom Rules < Top 10%…

Step 2: A dialogue box appears; you fill in the specifications and Booommm! You get your answer right on the screen.

  • Bottom 10 items

This feature is also very helpful, it helps an organization look over the products which are losing their market value.

Conditional Formatting in Excel (Step-by-Step Tutorial) 143

Step 1: The procedure to figure out the same is as follows:

Styling < Conditional Formatting < Top/ Bottom Rules < Bottom 10 items…

Step 2: A dialogue box appears, add the specifications and press OK.

  • Bottom 10%
Conditional Formatting in Excel (Step-by-Step Tutorial) 144

Just like there’s a top 10% there is a bottom 10% as well. And to find out the same, this is what you can do:

Step 1: Follow the process: Styling < Conditional Formatting < Top/ Bottom Rules < Bottom 10%…

Step 2: A dialogue box appears, add the specifications, and booommm!

  • Above Average

Now we will talk about Above average. In this there is nothing like top 10 or bottom 10, or top N (any number you would like to put) or bottom N. It shows you the average of overall.

By average, it does not mean the calculations, by average it just figures the middle value and highlights the above of it!

Step 1: So, to do the same this is what you can do:

Styling < Conditional Formatting < Top/ Bottom Rules < Above Average…

Step 2: A dialogue box appears, choose the color of your choice😕 and press OK!

Conditional Formatting in Excel (Step-by-Step Tutorial) 145

Booommmm! As you can see in the image above, the above average data has been highlighted.

Conditional Formatting in Excel (Step-by-Step Tutorial) 146
  • Below Average

Step 1: The same goes with Below Average i.e.

Styling < Conditional Formatting < Top/ Bottom Rules < Below Average…

Step 2: Pick a color and press OK.

Conditional Formatting in Excel (Step-by-Step Tutorial) 147

So, it’s easy, right?

Conditional Formatting in Excel (Step-by-Step Tutorial) 148

 Data Bars

You pretty much know what data bars are, so this feature of conditional formatting, let’s you format all the cells in the style of data bars. By throwing in some color, it describes the numeric value into a data bar.

Let’s take an example, to get rid of the confusion:

As you can see in the image above, we have taken a bunch of sales values. 

Conditional Formatting in Excel (Step-by-Step Tutorial) 149

Step 1: The first thing that we need to do is select the sales data.

Conditional Formatting in Excel (Step-by-Step Tutorial) 150

Step 2: Then we need to do the following:

Styling < Conditional Formatting < Data Bars < Color

You can pick the fill of your choice, by fill, we mean gradient fill like the one in the image above, or the solid fill.

Conditional Formatting in Excel (Step-by-Step Tutorial) 151

Step 3: After picking the color of your choice and selecting the mode of fill, you get your data in data bars. As you can see in the image above, all the data bars have the length in accordance to the value they hold.

Conditional Formatting in Excel (Step-by-Step Tutorial) 152

Data bars in cells, fun isn’t it😍?

 Color Scales

Conditional Formatting in Excel (Step-by-Step Tutorial) 153

Look at the left-hand side of the sheet (in the image above).

Some Roll nos. as well as the marks scored by them are mentioned. Now, if we want to color scale the marks then this is what we have to do:

Conditional Formatting in Excel (Step-by-Step Tutorial) 154

Step 1: The first thing that needs to be done is the selection of the Marks range.

Conditional Formatting in Excel (Step-by-Step Tutorial) 155

Step 2: Next, we need to do the following:

Styling < Conditional Formatting < Color Scales < Choose the color of choice.

Step 3: Now, please note that whatever color you pick, you need to observe the order of it. For example, the color chosen above states that the darkest shade is the highest marks and the lightest i.e. white color is the least.

You can always check the color effect and then choose the color of choice or whichever option is appropriate to you.

Conditional Formatting in Excel (Step-by-Step Tutorial) 156

And there you go… Color scales help you to scale your data by adding some color into it😁. It defines the highest value and the lowest value through the color and variates the middles values falling in between with the shades used in the highest and lowest value.

Conditional Formatting in Excel (Step-by-Step Tutorial) 157

It brings your data the color it needs!

Icon Sets

This feature is amazing as it lets you bring a whole type of an icon set in your data.

Conditional Formatting in Excel (Step-by-Step Tutorial) 158

As you can see on the right-hand side of the image above, we have mentioned a simple rating data, i.e. 1 to 5.

Now to add the icons which would best fit in to this situation will be as follows:

Conditional Formatting in Excel (Step-by-Step Tutorial) 159

Step 1: The first thing to be done is to select the range in which you want to input the icon sets.

Conditional Formatting in Excel (Step-by-Step Tutorial) 160

Step 2: You need to follow this procedure which is:

Styling < Conditional Formatting < Icon Sets < Ratings < Choose the option of your choice

Conditional Formatting in Excel (Step-by-Step Tutorial) 161

And booooommm💣!

You can also use the same method over the sales data.

Example:

Conditional Formatting in Excel (Step-by-Step Tutorial) 162

As you can observe in the image above🤓, by using the same procedure we have rated the sales values as well.

Conditional Formatting in Excel (Step-by-Step Tutorial) 163

Icons – cute, right🤗?

Conditional Formatting with the help of a Formula

There are instances when people feel more comfortable by using a formula then going through the whole façade of styling < conditional formatting < and so on…

So, if you are looking forward to a situation which can highlight the specific data as per your needs then you can always use the combination of conditional formatting and formula.

Let’s take an example for the same😊:

Conditional Formatting in Excel (Step-by-Step Tutorial) 164

So, if you want to use conditional formatting with a function of choice then you need to do the following:

Styling < Conditional Formatting < New Rule < Use a formula to determine which cells to format < input a formula < Select the color of choice < Press OK.

Conditional Formatting in Excel (Step-by-Step Tutorial) 165

Now, as you can see in the image above👆, we have used the OR Function. The OR Function is a formula that requires logics. So, say we need to highlight the grey color in the color range, this is what we will input:

=OR(C2=”grey”)

Please note: When you use a formula or function with conditional formatting, you don’t get to see the options, by options we mean that when we type an equal to sign in the excel sheet, we get a bunch of options, but in this case you are on your own, so be careful while typing the formula.

Second, when you use a formula in conditional formatting, it edits your formula i.e. the cells you select in it, it edits the same into either absolute or relative reference (the dollar sign $) which you can see in the GIF given in the end, so you need to make sure that you remove the same.

To remove the same, you need to press Function and F4 together till the cells are back to normal.

Third🤔, when you input a formula in the bar (as shown in the image above), there is no particular format which is selected at that time i.e. the color which many people skip due to the hurriedness. So, do not forget to choose a color or else your data will be highlighted in white color (matching the cell color), making no difference at all.

Forth, before you even begin with this whole procedure, you need to do one thing, you need to select the range first then go to styling < conditional formatting < new rule < and etc. etc.🤷

And in the formula even if it’s the matter of the whole range, you need to only select the first cell👀.

In simple words, when we use a formula in sheet, we drag the same for the other cells later, but in conditional formatting, we select the range first and then we pull the formula on one cell that automatically works for the rest of the selected cells🕶.

Conditional Formatting in Excel (Step-by-Step Tutorial) 166

And there you go; the color grey has been highlighted😊.

Conditional Formatting in Excel (Step-by-Step Tutorial) 167

You can use any type of formula with conditional formatting yet people do not use the functions of COUNT because all those features are already present in the conditional formatting.

Here’s another example in which we have used the function of ISODD (it highlights the odd numbers in the range). This type of a formula can help you divide a group into two, or etc.🤷

Conditional Formatting in Excel (Step-by-Step Tutorial) 168

So, Conditional Formatting is easy, isn’t it😉?