Updated June 12, 2023
Part -12 – Conditional formatting in Excel
Download Excel Templates – conditional formatting in ms excels.
Conditional formatting in ms excel (without solution)
Conditional formatting in ms excel (with solution)
Transcript For The Video – Conditional formatting in ms excel
In this video tutorial, we are going to learn about what exactly is conditional formatting in ms excel and what conditional formatting means in terms of dynamically formatting the cells. Here, in this case, we will take an example of percentage annual salaries, and let’s say you know we want to highlight all employees who have more than 8% of the total salary. And let’s say we also want to highlight those who receive, let’s say less than 3% of the total salary. The highlight can be of a color of your choice; maybe you know we can highlight these in yellow or orange and the other one in, let us say, green. So this is what we want; essentially, we want the system or Excel to identify these conditions automatically and highlight those who have taken more than 8% of the total salary. In this case, this will be in Melvin and maybe Grace. So these are the two who would be highlighted in orange and likewise, those who receive less than 3% of the total salary should be highlighted in green.
So, we want it to be done dynamically so that we don’t have to do it time and again. Every time we, you know, the change the employee salary or a maybe every time a new employee comes in. In this table, you know, he should get automatically adjusted for this kind of formatting. So in order to do this set of conditional formatting in ms excel, let’s go to the home TAB and click here on conditional formatting in ms excels. But before that, one thing which we need to make sure is we need to choose the cells on which we want to perform the conditional formatting. So let’s choose these cells first and let’s click here conditional formatting in ms excel and go to highlight cell rooms, under the highlight cells rules, you will find it. It mentions greater than, less than, between, equal to, etc.
You will find we need to make use of greater than symbol, and here we need to we are provided space where we can write 8% and the one which we need to format is custom format; go to fill, and we want to choose the orange color so we have chosen orange color and next thing we need to do is press OK. So here we have Melvin and Aaron. Both of them have been highlighted because they have their salaries of more than 8%. The other set of things which we also wanted to do in the same range is that we want to highlight those who have or who are earning less than 3% of the total salaries. So let’s check this range again; click on conditional formatting in ms excel and go to highlight cell rows. This time we need to select less than, and once we click on less than, we need to type the percentage 3%, and then will again use the custom formatting, and this time we will highlight those in green and press OK.
So here we find there are four guys who are earning less than 3% of the total salaries. So this is the way in which you can dynamically format the cells, and probably, if I have to show your point here, you know, let’s just update Sara’s salary for the minute and see what happens. If I make it as 10,000 $ her salary also gets updated, and the other set of formula also gets updated, so this is the way in which you know it kind of functions, or let’s say you know if we need to kind of update the salary of Melvin instead of 9200 let me put it as 920. So what will happen again; he comes under the 3% bracket and the colors of the cell get dynamically updated? So I’ll go back to the place where we came from, and this is how you know conditional formatting in ms excel can be applied.
So this is a very interesting way another way where you can actually apply conditional formatting in ms excel is by using the formulas. So let me show you a way first how to remove the conditional formatting. Let’s say I want to remove the conditional formatting from this set of cells. You cannot do it by just deleting, or you know the trying to no fill the cell. This will not remove your set of conditional formatting. The conditional formatting in ms excel can be removed only from the place it was created, so you need to click here again on conditional formatting. And then, click on the clear rules, and there is two option clear rules from the selected cells or clear rules from the entire sheet.
So I cleared rules from selected cells because that is what I wanted to do, and here you find that all the conditional formatting has been undone. So this is how you can remove conditional formatting. Now I was actually talking about applying the formula in conditional formatting. Let’s say I want to highlight a cell that has maximum and which has the minimum, so what I mean by maximum and minimum is out of this range who has the highest percentage or the maximum percentage in terms of total salary and also highlight the one which has the minimum salary. So this means that we may have to dynamically update this conditional formatting in ms excel using the formula which we have earlier seen as like this maximum and minimum.
So we need to apply this set of formulas on this range within the conditional formatting. So for this, what we need to do is, we just select the cells again; we go to conditional formatting in ms excel. This time, we select the new rule Ok, so go to the new rule, and you will find that there are various kinds of options. Format all cells based on the value, format only cell that contains format only top and bottom ranked values, so one which we must be interested in basically formats only cells that contains.
In this case, you may find that there is a drop-down menu here, so we need to choose cell value, and in the second drop-down, we need to choose equal to, and once you have to equal to here, you need to type the formula like the one you were usually typing in maximum salaries and minimum salaries. So let’s do that we type maximum, which is equal to MAX, and bracket OPEN and select the full range which we want for the calculation of maximum salary. And we do that, and let’s say we want to format that with blue, so, and the next thing that we need to do is just press OK. So we see that Melvin, who actually take home the maximum salary, is highlighted, i.e., 9.66%.
Now again, we can do the same thing to calculate or highlight the minimum. So, we go back to the conditional formatting in ms excel. We click on new rules; we select the format only cells that contain the drop-down cell value. Here, we need to choose equal to, and this time we apply the minimum formula. So this is MIN bracket open, we choose the cell on which we want to perform the calculations, and we close the bracket, and let us say we format this using the red font or the red background, and we just press OK. So that’s about it, so basically you can, within your conditional formatting, provide a numerical number or, let’s say dynamic formula with which you know we have seen in our case.
Recommended Articles
Here are some articles that will help you to get more detail about the conditional formatting in ms excels so just go through the link.
