Related topics
×
Use conditional formatting
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Use conditional formatting

Conditionally format text

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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. Select the first cell in the range, and then drag to the last cell.

  2. On the HOME tab, select   Conditional formatting button Conditional Formatting

  3. Select Conditional highlight cell rules button Highlight Cells Rules and choose  Text that contains button  Text that Contains.

  4. In the Text that Contains box, enter the text you want highlighted on the left.

  5. Choose the color format for the text, and select 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.

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.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×