Color formatting excel conditional formatting

Conditional Formatting

Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value.

Highlight Cells Rules

To highlight cells that are greater than a value, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Highlight Cells Rules, Greater Than.

4. Enter the value 80 and select a formatting style.

Result. Excel highlights the cells that are greater than 80.

6. Change the value of cell A1 to 81.

Result. Excel changes the format of cell A1 automatically.

Note: you can also use this category (see step 3) to highlight cells that are less than a value, between two values, equal to a value, cells that contain specific text, dates (today, last week, next month, etc.), duplicates or unique values.

Clear Rules

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Clear Rules, Clear Rules from Selected Cells.

Top/Bottom

To highlight cells that are above average, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Top/Bottom Rules, Above Average.

4. Select a formatting style.

Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Note: you can also use this category (see step 3) to highlight the top n items, the top n percent, the bottom n items, the bottom n percent or cells that are below average.

Conditional Formatting with Formulas

Take your Excel skills to the next level and use a formula to determine which cells to format. Formulas that apply conditional formatting must evaluate to TRUE or FALSE.

1. Select the range A1:E5.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click New Rule.

4. Select ‘Use a formula to determine which cells to format’.

5. Enter the formula =ISODD(A1)

6. Select a formatting style and click OK.

Result. Excel highlights all odd numbers.

Explanation: always write the formula for the upper-left cell in the selected range. Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.

Here’s another example.

7. Select the range A2:D7.

8. Repeat steps 2-4 above.

9. Enter the formula =$C2=»USA»

10. Select a formatting style and click OK.

Result. Excel highlights all USA orders.

Explanation: we fixed the reference to column C by placing a $ symbol in front of the column letter ($C2). As a result, cell B2, C2 and cell D2 also contain the formula =$C2=»USA», cell A3, B3, C3 and D3 contain the formula =$C3=»USA», etc.

Источник

Excel Conditional Formatting tutorial with examples

by Svetlana Cheusheva, updated on February 7, 2023

The tutorial explains all main features of Excel conditional formatting with examples. You will learn how to do conditional formatting in any version of Excel, efficiently use preset rules or create new ones, edit, copy and clear formatting.

Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and spot variances of cell values with a quick glance.

Many users, especially beginners, find it intricate and obscure. If you feel intimidated and uncomfortable with this feature, please don’t! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this tutorial 🙂

What is conditional formatting in Excel?

is used to apply certain formatting to data that meets one or more conditions. Just like usual cell formatting, it lets you highlight and differentiate your data in various ways by changing cells’ fill color, font color, border styles, etc. The difference is that it is more flexible and dynamic — when the data changes, conditional formats get updated automatically to reflect the changes.

Conditional formatting can be applied to individual cells or entire rows based on the value of the formatted cell itself or another cell. To conditionally format your data, you can utilize preset rules such as Color Scales, Data Bars and Icon Sets or create custom rules where you define when and how the selected cells should be highlighted.

Where is conditional formatting in Excel?

In all versions of Excel 2010 through Excel 365, conditional formatting resides in the same place: Home tab > Styles group > Conditional formatting.

Now that you know where to find conditional formatting in Excel, let’s move on and see how you can leverage it in your daily work to make more sense of the project you are currently working on.

For our examples, we will use Excel 365, which seems to be the most popular version these days. However, the options are essentially the same in all Excels, so you won’t have any problems with following no matter what version is installed on your computer.

How to use conditional formatting in Excel

To truly leverage the capabilities of conditional format, you need to learn how to utilize various rule types. The good news is that whatever rule you are going to apply, it defines the two key things:

• What cells are covered by the rule.
• What condition should be met.

So, here’s how you use Excel conditional formatting:

1. In your spreadsheet, select the cells you want to format.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. From a set of inbuilt rules, choose the one that suits your purpose.

As an example, we are going to highlight values less than 0, so we click Highlight Cells Rules > Less Than…

In the dialog window that appears, enter the value in the box on the left and choose the desired format from the drop-down list on the right (default is Light Red Fill with Dark Red Text).

When done, Excel will show you a preview of formatted data. If you are happy with the preview, click OK.

In a similar manner, you can use any other rule type that is more appropriate for your data, such as:

• Greater than or equal to
• Between two values
• Text that contains specific words or characters
• Date occurring in a certain range
• Duplicate values
• Top/bottom N numbers

How to use a preset rule with custom formatting

If none of the predefined formats suits you, you can choose any other colors for cells’ background, font or borders. Here’s how:

In the preset rule dialog box, from the drop-down list on the right, pick Custom Format…

In the Format Cells dialog window, switch between the Font, Border and Fill tabs to choose the desired font style, border style and background color, respectively. As you do this, you will immediately see a preview of the selected format. When done, click OK.

Click OK one more time to close the previous dialog window and apply the custom formatting of your choice.

• If you want more colors than the standard palette provides, click the More Colors… button on the Fill or Font tab.
• If you wish to apply a gradient background color, click the Fill Effects button on the Fill tab and choose the desired options.

How to create a new conditional formatting rule

If none of the preset rules meets your needs, you can create a new one from scratch. To get it done, follow these steps:

Select the cells to be formatted and click Conditional Formatting > New Rule.

In the New Formatting Rule dialog box that opens, select the rule type.

For example, to format cells with percent change less than 5% in either direction, we choose Format only cells that contain, and then configure the rule like shown in the screenshot below:

Click the Format… button, and then choose the Fill or/and Font color you want.

Click OK twice to close both dialog windows and your conditional formatting is done!

Excel conditional formatting based on another cell

In the previous examples, we highlighted cells based on «hardcoded» values. However, in some cases it makes more sense to base your condition on a value in another cell. The advantage of this approach is that irrespective of how the cell value changes in future, your formatting will adjust automatically to respond to the change.

As an example, let’s highlight prices in column B that are greater than the threshold price in cell D2. To accomplish this, the steps are:

1. Click Conditional formatting>Highlight Cells Rules >Greater Than…
2. In the dialog box that pops up, place the cursor in the text box on the left (or click the Collapse Dialog icon), and select cell D2.
3. When done, click OK.

As a result, all the prices higher than the value in D2 will get highlighted with the selected color:

That is the simplest case of conditional formatting based on another cell. More complex scenarios may require the use of formulas. And you can find several examples of such formulas along with the step-by-step instructions here:

Apply multiple conditional formatting rules to same cells

When using conditional formats in Excel, you are not limited to only one rule per cell. You can apply as many rules as your business logic requires.

For example, you can create 3 rules to highlight prices higher than $105 in red, higher than $100 in orange, and higher than $99 in yellow. For the rules to work correctly, you need to arrange them in the right order. If the «greater than 99» rule is placed first, then only the yellow formatting will be applied because the other two rules won’t have a chance to be triggered — obviously, any number that is higher than 100 or 105 is also higher than 99 🙂

To re-arrange the rules, this is what you need to do:

1. Select any cell in your dataset covered by the rules.
2. Open the Rules Manager by clicking Conditional Formatting > Manage Rules…
3. Click the rule that needs to be applied first, and then use the upward arrow to move it to top. Do the same for the second-in-priority rule.
4. Select the Stop If True check box next to all but the last rule because you do not want the subsequent rules to be applied when the prior condition is met.

What is Stop if True in Excel conditional formatting?

The Stop If True option in conditional formatting prevents Excel from processing other rules when a condition in the current rule is met. In other words, if two or more rules are set for the same cell and Stop if True is enabled for the first rule, the subsequent rules are disregarded after the first rule is activated.

In the example above, we have already used this option to ignore subsequent rules when the first-in-priority rule applies. That usage is quite evident. And here are another couple of examples where the use of the Stop If True function is not so obvious but extremely helpful:

How to edit Excel conditional formatting rules

To make some changes to an existing rule, proceed in this way:

1. Select any cell to which the rule applies and click Conditional Formatting > Manage Rules…
2. In the Rules Manager dialog box, click the rule you want to modify, and then click the Edit Rule… button.

In the Edit Formatting Rule dialog window, make the required changes and click OK to save the edits.

That dialog window looks very similar to the New Formatting Rule dialog box used for creating a new rule, so you won’t have any difficulties with it.

Tip. If you don’t see the rule you want to edit, then select This Worksheet from the Show formatting rules for drop-down list at the top of the Rules Manager dialog box. This will display the list of all the rules in your worksheet.

How to copy Excel conditional formatting

To apply a conditional format you’ve created earlier to other data, you won’t need to re-create a similar rule from scratch. Simply use Format Painter to copy the existing conditional formatting rule(s) to another data set. Here’s how:

1. Click any cell with the formatting you want to copy.
2. Click Home >Format Painter. This will change the mouse pointer to a paintbrush.

Tip. To copy the formatting to multiple non-contiguous cells or ranges, double-click Format Painter.

When done, press Esc to stop using the paintbrush.

Select any cell in your new dataset, open the Rules Manager and check the copied rule(s).

Note. If the copied conditional formatting uses a formula, you may need to adjust cell references in the formula after copying the rule.

How to delete conditional formatting rules

I’ve saved the easiest part for last 🙂 To delete a rule, you can either:

Open the Conditional Formatting Rules Manager, select the rule and click the Delete Rule button.

Select the range of cells, click Conditional Formatting > Clear Rules and choose the option that fits your needs.

This is how you do conditional formatting in Excel. Hopefully, these very simple rules we created were helpful to get a grasp of the basics. Below, you can find a few more tutorials that can help you understand the inner mechanics and expand conditional formatting in your spreadsheets far beyond its traditional uses.

Practice workbook for download

You may also be interested in

• How to conditionally format dates in Excel — how to apply Excel conditional formatting to dates using built-in rules and formulas.
• Change background color based on cell value — two quick ways to change the background color of cells based on their values.
• Alternate row colors in Excel — how to shade every other or every N row using table styles and conditional formatting.
• How to sum and count by color in Excel — how to sum and count colored cells with custom functions and VBA. The solutions work both for cells colored manually and with conditional formatting.
• How to highlight duplicates in Excel — how to use conditional formatting to highlight duplicate values and rows.
• Cell references in Excel conditional formatting — how to correctly use relative and absolute cell references in formula-based conditional formatting rules.
• Excel conditional formatting for blank cells — Everything you need to know about conditional format for empty cells in Excel.

Table of contents

Hello!
I find that in some files the conditional formatting «disappears» from one use to the next. I set it, it’s working fine. And when I go back to the file another time, I have to set it again. In other files it’s still there. I cannot work out why one would be different from the other. Is it to do with other settings in the individual files?
Thanks,
Manuela

i want the conditional formatting i created for a cell to be copied down to the next. but i want the continuation of the references as well. example the first cell’s reference is $G$1, the next cell should be $G$2 and so on. i can do this one by one. but is there a faster way to do it. or a shortcut or maybe a command for multiple cells like dragging down. because when i drag down. the reference cell is the same.

Hello!
If you want a conditional formatting formula to apply to a range of cells, don’t use an absolute reference. You need a relative reference. I recommend reading this guide: Excel formulas for conditional formatting based on another cell value.

Can you use conditional formatting to change background colour depending on the case of individual letter (CHAR) been LOWER or UPPER? i.e. ‘c’ or ‘C’

Hi!
For uppercase letters, this formula will return TRUE

I hope it’ll be helpful. If something is still unclear, please feel free to ask.

I prefer this:
=EXACT(LEFT(A2,1),UPPER(LEFT(A2,1)))

Sir
in m column 3 text Refund, Failed & Succes

now if text Success than a b c row is highted in colour

Hello!
To check if the text «Succes» is in the cell, use the formula

You can learn more about SEARCH function in Excel in this article on our blog.

Copyright © 2003 – 2023 Office Data Apps sp. z o.o. All rights reserved.

Microsoft and the Office logos are trademarks or registered trademarks of Microsoft Corporation. Google Chrome is a trademark of Google LLC.

Источник