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🤕!
So, this blog is all about conditional formatting. We will be explaining every feature that conditional formatting provides🤓.
Highlight Cells Rules
It has the following features:
- Greater Than
- Less Than
- Equal To
- Text that Contains
- A Date Occurring
- Duplicate Values
- Greater Than
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.
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.
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).
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).
Step 5: Then you need to go to the “Fill” and choose the color you want, and press “OK”.
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”.
Step 7: And there you go, all the sales values which are below 65000 are highlighted with “blue” color.
Now, we will move to the second option:
- Less Than
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!
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.
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👆).
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.
Step 4: The same process comes, i.e. go to the Fill section and select the color then press OK.
Step 5: The second you press OK you return back to the Less Than dialogue box, again press Ok.
Step 6: And there you go, it’s that simple.
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😜!)
As you can see in the image above, we have used the same data we used in the greater than and less than.
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🤓.
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
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.
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.
Step 4: After returning to the Between dialogue box, we will again press OK.
Step 5: And BAAAMMM🔫! There you go😎! All the sales values that lie between 40000 to 60000 are highlighted with blue color.
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.
To find the same, this is what we will do:
Step 1: We will select the sales range.
Step 2: Next, we will do as it’s shown in the image above:
Styling < Highlight Cells Rules < Equal To
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.
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.
Step 7: After the same, we return to the equal to dialogue box, where again we need to press OK👍.
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.
- Text that Contains
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!
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!
Step 2: After the selection of the table, we will do the following:
Styling < Conditional Formatting < Highlight Cells Rules < Text that Cont
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.
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.
Step 1: Select the table range😊 (as shown in the image above).
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)
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
Step 4: Then we will select the color of choice (let’s say yellow) just like it’s shown in the image above.
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.
Step 6: And there you go… All the dates that are occurring in the Last Month are highlighted with the color Yellow.
It’s easy, right?
- Duplicate😱 or Unique🤔:
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.
Step 1: Now the first thing to do is to select both the ranges.
Step 2: Then do the following:
Styling < Conditional Formatting < Highlight Cells Rules < Duplicate Values…
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.
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…
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.
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.
Top/ Bottom rules
These are 6 in number:
- Top 10 items…
- Top 10%…
- Bottom 10 items…
- Bottom 10%…
- Above Average…
- Below Average…
Now, let’s get started with the first option:
- Top 10 items…
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.
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.
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👆.)
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!).
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%
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.
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%
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!
Booommmm! As you can see in the image above, the above average data has been highlighted.
- 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.
So, it’s easy, right?
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.
Step 1: The first thing that we need to do is select the sales data.
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.
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.
Data bars in cells, fun isn’t it😍?
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:
Step 1: The first thing that needs to be done is the selection of the Marks range.
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.
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.
It brings your data the color it needs!
This feature is amazing as it lets you bring a whole type of an icon set in your data.
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:
Step 1: The first thing to be done is to select the range in which you want to input the icon sets.
Step 2: You need to follow this procedure which is:
Styling < Conditional Formatting < Icon Sets < Ratings < Choose the option of your choice
You can also use the same method over the sales data.
As you can observe in the image above🤓, by using the same procedure we have rated the sales values as well.
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😊:
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.
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:
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🕶.
And there you go; the color grey has been highlighted😊.
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.🤷
So, Conditional Formatting is easy, isn’t it😉?