Use Of Conditional Formatting In MS Excel – You should Know

Part -12 – Conditional formatting in excel

 Download Excel Templates – conditional formatting in ms excel

Conditional formatting in ms excel (without solution)

Conditional formatting in ms excel (with solution)

Recommended courses

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 knew we want to highlight all employees who have more than 8% of the total salary. And let say we also want to highlight those who receive let say less than 3% of the total salary. They highlight can be of a color of your choice may be 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 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 these set of conditional formatting in ms excel lets go to the home TAB and click here on conditional formatting in ms excel 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 mention 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 were 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 more than 8%. The other set of thing 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 lets 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 the will again use the custom formatting and this time we will highlight those in green and press OK. So here we find there are 4 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 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 colours 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 say I want to remove the conditional formatting from these 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 clear rules from selected cells because that 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 an applying the formula in conditional formatting let say I want to highlight a cell which 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 these 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 say we want to format that with blue so and the next things 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 the 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 excel so just go through the link.

1.  Importance of Excel SUM MAX MIN AVERAGE
2. Excel VBA Macro
3. 9 Amazing Steps on How to Use Microsoft Excel Skills
4. Top 25 Useful Advanced Excel Formulas and Functions
5. 10 Easy Steps to Create a Pivot Chart in Excel 2016
6. 9 Amazing Steps on How to Use Microsoft Excel Skills