Use the Quick Analysis tool to conditionally format cells in a range that have duplicate text, unique text, and text that is the same as text you specify. You can even conditionally format a row based on the text in one of the cells in the row.
Apply conditional formatting based on text in a cell
-
Select the cells you want to apply conditional formatting to. Select the first cell in the range, and then drag to the last cell.
-
On the HOME tab, select
-
Select
-
In the Text that Contains box, enter the text you want highlighted on the left.
-
Choose the color format for the text, and select OK.
Want more?
Apply conditional formatting to quickly analyze data
In the first video in the course, we went over how to conditionally format cells in a range that contained the text “Oil”, using Quick Analysis.
You can also use Quick Analysis to conditionally format cells in a range that have duplicate text, unique text, and text that is the same as text you specify.
But what if you want to conditionally format a row, based on the text in one of the cells in the row?
In this example, I want a row to have a gray fill, if Discontinued for the row is set to Yes.
To do this, select the cells you want to conditionally format. Note that A2 is the active cell; we’ll need that for later.
Click Conditional Formatting, and then click New Rule. Select Use a formula to determine which cells to format.
In the rule field, since the active cell is A2, we need to type a formula that is valid for row 2 and will apply correctly to all of the other rows.
To do this, we type =$C2=“Yes”. We use an absolute reference for column C, $C, so that conditional formatting for each row evaluates the value in column C for that row.
And we put quotes around Yes. The quotes ensure that Excel evaluates the word Yes as text.
Next, we click Format, select the fill color we want, click OK, and click OK again.
And the rows with products that have been discontinued are filled with gray.
Up next, Copying and removing conditional formatting.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
