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 in Excel is used to highlight the data on the basis of some criteria. It would be difficult to see various trends just for examining your Excel worksheet. Conditional Formatting in excel provides a way to visualize data and make worksheets easier to understand.
Excel Conditional Formatting allows you to apply formatting basis on the cell values such as colors, icons and data bars. For this, we will create a rule in excel Conditional Formatting based on cell value
How to write an if statement in excel?
IF function is used for logic_test and returns value on the basis of the result of the logic_test. Excel conditional formatting formula multiple conditions uses Statements like less than or equal to or greater than or equal to the value are used in IF formula
|=IF (logical_test, [value_if_true], [value_if_false])|
Let’s learn how to do conditional formatting in excel using IF function with the example.
Here is a list of Names and their respective Scores.
multiple if statements excel functions are used here. So, there are 3 results based on the condition. if then statements in excel is used via excel conditional formatting formula
Write the formula in C2 cell.
IF function only returns 2 results, one [value_if_True] and Second [value-if_False]
First IF function checks, if the score is less than 50, would get C grade, The Second IF function tests if the score is less than 75 would get B grade and the rest A grade.
Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl + D
Now we will apply conditional formatting to it.
Select Home >Conditional Formatting > New Rule.
A dialog box appears
Select Format only cells that contain > Specific text in option list and write C as text to be formatted.
Fill Format with Red colour and click OK.
Now select the colour Yellow and Green for A and B respectively as done above for C.
In this article, we used IF function and Conditional formatting tool to get highlighted grade.
As you can see excel change cell color based on value of another cell using IF function and Conditional formatting tool
Hope you learned how to use conditional formatting in Excel using IF function. Explore more conditional formulas in excel here. You can perform Conditional Formatting in Excel 2016, 2013 and 2010. If you have any unresolved query regarding this article, please do mention below. We will help you.
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.