Excel color cell based on text

You can use the conditional formatting feature in Excel to help draw attention to cells that contain specific text in which you are interested. For instance, if you have a range of cells and you want to know which ones contain the letters "shawn," then you can do the following:

1. Select the range of cells.
2. With the Home tab of the ribbon displayed, click the Conditional Formatting option in the Styles group. Excel displays a palette of options related to conditional formatting.
3. Choose New Rule. Excel displays the New Formatting Rule dialog box.
4. In the Select a Rule Type area at the top of the dialog box, choose Format Only Cells that Contain.
5. Using the left-most drop-down list in the criteria area, select Specific Text. (See Figure 1.)


Figure 1. The New Formatting Rule dialog box.

6. Make sure the center drop-down list is Containing.
7. In the right-most box enter "shawn" (without the quote marks).
8. Click Format to display the Format Cells dialog box. (See Figure 2.)


Figure 2. The Format Cells dialog box.

9. Using the controls in the dialog box, specify a format that you want used for those cells that contain the specified text. For instance, you may want bold text in a red typeface.
10. Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 9 should now appear in the preview area for the rule.
11. Click OK.

There is a gotcha to be aware of when looking for specific text: The condtional formatting rule will consider the cell a match if it contains the text you specify in step 7 anywhere within the cell. So, for instance, if you use the text "shawn" in step 7, and the cell contains "My name is Shawn," then for the purpose of this type of format, this is a match.

The reason this works this way is because you specified "Containing" in step 6. If you want some other type of match, then you'll need to pick some different setting in step 6.

The reason for highlighting text with conditional formatting is to draw the user’s attention to those cells – so they find relevant information faster🏃

You apply conditional formatting with what is called conditional formatting rules.

It’s really easy to insert one of those.

1. Select all the cells where the text you want to highlight can be.

In the sample data, I want to identify all L compatible adapters. So, I select all cells in column B (from cell B2 and down).

2. In the middle of the Home tab, click ‘Conditional Formatting’.

3. Hover your cursor over ‘Highlight Cells Rules’ and select ‘Text that Contains’.

4. In the dialog box that appears, write the text you want to highlight, in the left field.

As you type it, you can see the conditional formatting applied instantly.

The formatting being applied is the standard conditional formatting format.

5. To change the format, click the drop-down arrow in the right field and select another of the formatting presets.

6. Or you can create your own format by clicking ‘Custom format’.

7. After clicking ‘Custom Format’ the ‘Format Cells’ dialog box appears.

This is the same as the normal ‘Format Cells’ dialog box you’re using when you apply normal formatting in a spreadsheet.

Use whatever formatting style you want for your conditional formatting rule.

And that’s it!

Now, all cells containing the text “L compatible” are highlighted with the chosen formatting.

Pretty cool, right?💪

Because conditional formatting is dynamic, it changes when the data changes. So if the content of a cell changes from whatever to “L compatible” that cell gets highlighted too.

Kasper Langmann, Microsoft Office Specialist

Highlight numbers with conditional formatting

Sometimes you’re not reading all your data in your sheets, you’re just looking for specific numbers.

That can be a tedious process, especially if you have a large data set.

Let’s dive into our inventory list again🤿

If the quantity in stock of an item reaches below 100 it’s critical that you find out, so you can buy more units of that particular item.

Let’s make a simple conditional formatting rule to highlight cells.

1. Select the cells in the quantity column (cells C2 and down).

With those selected cells, we need to apply a ‘Highlight Cells’ conditional formatting rule.

2. In the Home tab of the ribbon, click ‘Conditional Formatting’.

3. Hold your cursor over ‘Highlight Cells Rules’ and click ‘Less than’.

This conditional formatting rule allows you to apply conditional formatting if the value of a cell is less than what you’re defining in a few seconds.

4. In the left field, write the number the cell value should be less than to apply conditional formatting.

As you type, you can see the format of this conditional formatting rule being applied to the selected cells.

Right now, it’s just the standard format: Light Red Fill with Dark Red Text.

Classic👍

5. In the right field, click the drop-down button if you want to change to another formatting preset.

6. Or you can click ‘Custom Format’ to control the formatting style 100%.

And that’s it.

Watch all the values below 100 being highlighted automatically. Beautiful, isn’t it?💕

This conditional formatting rule can be modified to other situations if you take a step back.

For instance, you can turn it upside down and highlight cells that are above 100.

Or cells with values between 50 and 150.

The possibilities are almost endless.

Highlighting entire rows based on a formula

Entire rows look nice when they are formatted in a certain way. Instead of random colored cells everywhere.

So, let me show you how to color an entire row with a conditional formatting rule if the value in column C is less than 100👀

1. Select the entire data set. The selected cells indicate how much of each row is highlighted by the conditional formatting rule.

2. Click ‘Conditional Formatting’ on the Home tab and click ‘New Rule.’

3. In the dialog box that appears, select ‘Use a formula to determine which cells to format’ from the ‘Rule Type’ menu.

4. Right below, enter this formula:

=$C1<100

So, if the value in column C drops below 100, the conditional formatting rule triggers.

You can’t refer to entire columns, so you need to refer to C1. NOT C2, although it seems more logical⚙️

If your data starts in row 3, 5, 10, or whatever other row, the reference still goes to C1 (or whatever column you’re using).

5. Now chose your formatting style from the ‘Custom Format’ button.

Or settle for the standard preset.

And behold!

All the cells in your data rows are now formatted, and not just in 1 column like they typically are with conditional formatting.

Editing and removing conditional formatting rules

The layout and use of a workbook are rarely static, so the conditional formatting that you apply to your sheet may have to be edited at some point.

For instance, the previous highlight cells rules we set… Maybe it’s time to change them.

Luckily that’s fairly easy.

You can edit any conditional formatting rule from the conditional formatting rules manager.

Edit the conditional formatting rule and formatting style

1. Click ‘Conditional Formatting’ from the Home tab and select ‘Manage Rules’.

After you click ‘Manage Rules…’ you’ll see a ‘Conditional Formatting Rules Manager’ dialog box.

Change the following 3 points as you see them on in the manager.

2. Set ‘Show formatting rules for:’ to ‘This Worksheet’ to show all conditional formatting rules for the entire sheet.

3. Find the rule you want to edit (there might be several on the list).

4. Click the ‘Edit Rule’ button.

Another dialog box appears. From here you can change anything about the rule.

Below we’ll go through the different options shown in the screenshot above.

Circle 1) Here you can change the ‘Rule Type’. If you don’t want to apply conditional formatting when “something” happens in a cell, you can change it to apply on all cells and fill them with color based on their value compared to the average of the data.

Circle 2) If you keep the ‘Rule Type’ you can change the “trigger” of the formatting. The trigger depends entirely on which type of conditional formatting rule you choose in circle 1. In this case, you can change the custom formula.

Circle 3) From this little (familiar) box you can change the formatting that you applied in the first place. If you don’t want your cells to turn yellow when it drops below cell value: 100, you can have them turn purple instead.

As you see there are more than enough options.

But wait – there’s more!

Edit the conditional formatting rules range

All of the above is very nice, but if you accidentally selected the wrong area when you made the conditional formatting rule – then it doesn’t help to change the ‘Rule Type’ or whether the cells should be red or blue.

And if you spent 5 minutes entering a unique formula to “trigger” the formatting, then it sucks to delete it and do it all over.

To change the range of cells that the conditional formatting applies to, you don’t need to go to the ‘Edit Rule’ box above.

You just need to click in the ‘Applies to’ column at the rule you want to change in the ‘Conditional Formatting Rules Manager’ box.

If you can’t remember the range that the rule should apply to, click the tiny red arrow to select the range manually.

That’s how you edit a conditional formatting rule✏️

So if you screw one up?

You don’t have to delete it – simply just edit it.

Delete a conditional formatting rule

Sometimes you see a workbook with too many conditional formatting rules.

This can create immense confusion and on top of that, it can make your workbook slow.

In this case, you need to remove conditional formatting from the spreadsheet. Maybe even all of it!

Click the ‘Conditional Formatting’ button on the ‘Home’ tab and hover your mouse over ‘Clear Rules’.

Then click ‘Clear Rules from Entire Sheet’.

That was pretty easy. But it’s not always clever to clear all conditional formatting rules from a sheet!

Sometimes some rules should still apply.

To select specifically which conditional formatting rules are to be removed, click ‘Manage Rules…’ instead.

Here you’ll find the conditional formatting rules manager dialog box that you can use to clear rules, in addition to editing rules.

To delete specific conditional formatting rules:

1. Click the drop-down arrow that initially says “Current Selection”. Change it to “This Worksheet”.
2. Select the conditional formatting rule you don’t want anymore.
3. Press ‘Delete Rule’ to actually clear rules.

Remember to click OK when you’re done.

And that’s how you actually clear rules✏️

That’s it – Now what?

That’s how to highlight cells with conditional formatting.

Like all the cells that contain a specific word or a number.

Heck, you even learned how to remove conditional formatting, create a new rule from a formula, and much more.

Well done💪

Conditional formatting is a part of Excel that can think for itself. Meaning it makes its own decisions. It “reads” the cell and reacts on the content.

There are several other parts of Excel that react to the content of cells – just like conditional formatting does.

And you most definitely want to learn those parts.

So, I’ve made a free course teaching you 2 of the best conditional functions Excel has to offer: IF and SUMIF.

Also, you learn VLOOKUP and pivot tables.

Click here to enroll in my free 30-minute course and I’ll send it to your inbox right away.

Other resources

There are several other ways of using conditional formatting.

Instead of highlighting specific cells, you can visualize high/low values in an entire data set in a few seconds with data bars, icon sets, and color scales.

Another cool feature of conditional formatting is that you can highlight duplicate values.

Or you can use this neat conditional formatting tricks to highlight every other row.

Oh, and here’s the best way to remove normal formatting, in case you want to apply new formatting.

Frequently Asked Questions

If you’re looking for a specific answer to a specific question, please see the FAQ section below. Maybe you’ll find what you need😊

What is conditional formatting in Excel?

Conditional Formatting is formatting that changes when the cell that it’s based on changes.

This is very different from normal formatting which is static and does not change unless you make the change yourself.

For example, you can make a cell change color when its content drops below 100.

Where is conditional formatting in Excel?

Conditional formatting is located on the Home tab on the Ribbon in Excel. The Ribbon is the main menu, on the top of your screen, where most Excel features are accessible from buttons.

Select your data and click the conditional formatting button to use any of the conditional formatting rules.

Can I copy conditional formatting in Excel?

Yes, you can use the format painter to copy and paste conditional formatting quite easily.

Select the cells with conditional formatting and from the Home tab, click the ‘Format painter’ button.

How do I color a cell in Excel based on value?

Can you use an if statement to color a cell?

How do you dynamically color a cell in Excel?