What does the conditional formatting do?
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. Show
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?Excel Conditional Formatting 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 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 - on the Home tab, in the Styles group. 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 ExcelTo 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:
So, here's how you use Excel conditional formatting:
In a similar manner, you can use any other rule type that is more appropriate for your data, such as:
How to use a preset rule with custom formattingIf none of the predefined formats suits you, you can choose any other colors for cells' background, font or borders. Here's how:
Tips:
How to create a new conditional formatting ruleIf none of the preset rules meets your needs, you can create a new one from scratch. To get it done, follow these steps:
Excel conditional formatting based on another cellIn 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:
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 in these articles:
Apply multiple conditional formatting rules to same cellsWhen 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:
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 rulesTo make some changes to an existing rule, proceed in this way:
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 formattingTo 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:
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 rulesI've saved the easiest part for last :) To delete a rule, you can either: 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. Practice workbook for downloadExcel conditional formatting - examples (.xlsx file) More about Excel Conditional FormattingHere are a few more tutorials that can help you understand the inner mechanics and expand conditional formatting in your spreadsheets far beyond its traditional uses:
|