In this article we will learn how to color rows based on text criteria we use the “Conditional Formatting” option. This option is available in the “Home Tab” in the “Styles” group in Microsoft Excel.
Conditional Formatting: -Conditional Formatting is used to highlight the important points with color in a report.
To use If Conditional Formatting formula in Excel , we use the IF condition in the data and then define the conditional formatting options.
IF: -IF condition allows us to use multiple conditions in a cell. It makes the function flexible. Excel allows upto a maximum of 64 conditions which can be checked in a cell.
Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])
Example:Cells A2 and A3 contain the numbers 3 and 5 respectively. The condition is – if the number in the cell is 3, the formula should display “Yes,” otherwise “No”. =IF (A1=3,”Yes”,”No”)
As per the conditions specified, we get Yes in B1 and No in B2.
Let’s take an example and understand how to format the conditional If statement in Excel.
We have scores in the range A2:B12, in which column A contains the Students Name and column B contains the scores. In column C we want to return the grades according to the scores. And the grade criteria is – if the score is less than and equal to 50 then the grade should be “C”, if the score is greater than 50 and less than 75 then the grade would be “B” and if the score is equal to and greater than 75 then the grade would be “A”.
To return the grades follow the below given steps:-
Now lets follow the below given steps toapply Conditional Formatting with the If statement:-
To set the formatting for the rest of the grades follow the below given steps:-
After putting the “Conditional Formatting” conditions for all the grades you will get the data as shown below.. Also if you update the scores, the grades will change automatically.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.