Video conditionally format text

Try-it!Transcript



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

1. Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.

2. Click HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains. In the Text that Contains box, on the left, enter the text you want highlighted.

3. Select the color format for the text, and click OK.

Want more?

Apply conditional formatting to quickly analyze data

Add, change, find, or clear conditional formats

Take conditional formatting to the next level

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.