What are conditional formatting rules?
A conditional formatting rule is an Excel command that determines conditions under which a cell will be automatically highlighted and the format to be applied in those cases (e.g., a color fill, a bar, specific fonts or icons).
Strictly speaking, all conditional formatting in Excel is done by creating rules. Although you don’t see it at that moment, each time you use a preset conditional formatting command, a rule is automatically created in the background. You can then see and update that rule in Excel Rules Manager [Home >> Conditional Formatting (in Styles) >> Manage Rules…].
Why do we need conditional formatting rules?
Even though some basic conditional formatting tasks can be accomplished with preset conditional formatting commands, more specific conditional formatting needs require the use of formatting rules. The latter approach offers much more flexibility, allowing for almost limitless conditional formatting solutions.
Let’s say we have a list of 25 students with their test scores, and we need to highlight three groups of scores:
- 50 and below – with red cross
- Between 50 and 80 – with yellow exclamation mark
- 80 and higher – with green check mark
The respective preset conditional formatting command [menu Home >> Conditional Formatting (in Styles) >> Icon Sets (in Indicators) >> 3 Symbols (Uncircled)] splits the data set evenly in three-thirds and wouldn’t allow specifying the group borders as described above. However, a new formatting rule can be created to get the exact effect we need.
Please see Figure 1 for a comparison of conditional formatting with 50-80 borders (new rule) vs. a preset conditional formatting command (each icon gets one-third of the values). Note the difference for students 6, 7,15, and 20. E.g., for Student 6, score 49 is below the 50-point threshold and gets a red cross in column B. However, given other scores in the data set, it ranks higher than bottom third and, therefore, gets a yellow exclamation mark in column C.
How to do it
Here is how you can set the formatting rule described in the example above:
- Select the range of cells to be formatted (B2:B26 in this case)
- Go to menu Home >> Conditional Formatting (in Styles) >> New Rule …
- In the New Formatting Rule window (see Figure 2):
- Rule Type: Format all cells based on their values
- Format Style: Icon Sets
- Icon Style: 3 Symbols (Uncircled)
- Rules >> Type: Number
- Rules >> Value: 50 and 80
Alternatively, you can first create the rule with the preset formatting command, and then update it to reflect 50-80 borders:
- Select the range of cells to be formatted
- Create a rule by using a preset command [menu Home >> Conditional Formatting (in Styles) >> Icon Sets >> 3 Symbols (Uncircled)]
- Go to Rules Manager [Home >> Conditional Formatting (in Styles) >> Manage Rules…]
- Select the existing rule and press Edit Rule… [see Figure 3]
- In the Edit Formatting Rule window (identical to New Formatting Rule window in Figure 2), update Rule Type to Number and Rule Value to 50 and 80 (similar to steps 3d and 3e above)
Formatting Rule window
Essentially, every conditional formatting rule can be described by a few parameters (see Figure 4):
- Rule Type (e.g., cells that contain specific values, top/bottom ranked values, formula)
- Rule Description:
- Condition – describes the cases when the cell should be formatted (e.g., when cell value is higher than 1 but lower than 5)
- Format – describes the formatting to be applied when the condition is satisfied (e.g., color fill, patterns or specific fonts)
Conditional Formatting Rule Types
There are 6 Rule Types offered by Excel. First five types simply extend the functionality of the preset conditional formatting commands, while the last one truly unlocks the full power of conditional formatting by allowing to use a formula in Rule Description field:
Format all cells based on their values – extends the functionality of Data Bars, Color Scales, and Icon Sets preset formatting commands (see Figure 5):
- Data Bars: in New Formatting Rule window, set Format Style field to Data Bar and you can customize a lot of settings not offered by the preset Data Bars command (e.g., pick gradient fill for the bar, hide cell values and show bars only, manage axis)
- Color Scales: 2-Color Scale and 3-Color Scale options of Format Style field help you create customized heat maps (e.g., changing minimum, maximum, and midpoint values, adjusting colors)
- Icon Sets: Icon Sets option of Format Style field allows customization of the preset Icon Set command in Excel (e.g., creating custom icon combinations, changing value ranges for each icon, hiding cell values)
Format only cells that contain – extends the functionality of the Highlight Cell Rules function (see Figure 6):
- Cell value, Specific Text, and Dates Occurring – increased customization for Greater Than…, Less Than…, Between…, Equal To…, Text that Contains…, and A Date Occurring… options of the Highlight Cell Rules command. E.g., they offer options like not equal to, not between, greater than or equal to, and a lot of additional formatting alternatives
- Blanks, No Blanks, Errors, No Errors – creating formatting rules for cells that are empty/not empty/have errors/don’t have errors. Those alternatives are not offered by any preset command
- Format only top or bottom ranked values – extends the functionality of the Top/Bottom Rules function [Top 10 Items…, Top 10%…, Bottom 10 Items…, and Bottom 10%… options] by offering a lot of additional formatting alternatives
- Format only values that are above or below average – extends the functionality of the Top/Bottom Rules command [Above average… and Below average… options] by offering a lot of additional formatting alternatives as well as some new options like 1 standard deviation above, etc.
- Format only unique or duplicate values – extends the functionality of the Highlight Cell Rules command [Duplicate Values… option] by offering a lot of additional formatting alternatives
- Use a formula to determine which cells to format – the most advanced and flexible option that Excel offers in conditional formatting. By allowing to enter a formula in the Rule Description field, this Rule Type provides a toolkit for almost limitless alternatives in conditional formatting. If you want to learn the full power of conditional formatting in Excel, we highly recommend reading a separate post about conditional formatting formulas.
Conditional Formatting Quiz
0 of 3 questions completed
Take a quick quiz to understand how well you know Excel Conditional Formatting:
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Time has elapsed
You have reached 0 of 0 points, (0)
Question 1 of 3
Please select all relevant situations when conditional formatting can be potentially usedCorrect
You might consider reviewing conditional formatting section on our site
Question 2 of 3
What conditional formatting command would you use for highlighting the rows for all female students in the table below?
Name Test Score Gender Cohort Major Home state Student 1 80 male 1 Major A MA Student 2 38 female 3 Major A PA Student 3 64 male 4 Major B GA Student 4 63 female 1 Major A NJ Student 5 26 male 5 Major B TXCorrect
You might consider reviewing conditional formatting formulas section on our site
Question 3 of 3