When you work with spreadsheets, you will quickly figure out how useful is conditional formatting in Excel. We can use it for basically every set of data and highlight only the data that are falling under a certain category.
In the example below, we will show how to use conditional formatting when we have a certain text in our data set.
Use Conditional Formatting When Cell Contains Yes
For the example, we will use the list of loans that were applied for, banking officers, and simple yes/no to show whether the loan was approved or not.
We want to highlight only those loans that were approved. To do so, we will select the column C, go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains:
On a pop-up window that appears, we will input a desired word- “Yes”, and simply click OK.
Once we do that, we will have the desired cells highlighted:
If we want to filter out only these values, we will select the first row in our first three columns, go to Data >> Sort & Filter >> Filter.
When we click on a dropdown menu and go to Sort by Color, you will notice that we can choose the one with the pink fill.
When we do that, only the loans that were approved are filtered:
If we want to highlight any other column based on the values in column C, all we have to do is select that column (in our case column A) and go to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> More Rules:
When we click it, we have to select the last option: Use a formula to determine which cells to format, and input the following formula:
We will define a format for these cells as green fill, just like in the picture below:
Once we click OK, we will have all the cells in column A in which column C is the value “Yes” populated in green color.