Conditional formatting is a data visualization tool in several spreadsheet applications. Mostly we use it to highlight, emphasize, and differentiate by using colors or other means (like applying icons) to cells or ranges relevant to us.
At first, you will learn the key skills. The second part of the tutorial will be about how to use Excel conditional formatting formulas and examples.
Table of contents:
- What is Conditional Formatting
- Learn the fundamentals of Conditional Formatting
- Highlight cells
- Highlight cells that contain text
- Edit a conditional formatting rule
- Delete a conditional formatting rule
- Conditional Formatting Formulas: Examples
What is Conditional Formatting
Conditional formatting offers a wide range of possibilities for Excel users. Its first and foremost function is to direct attention to the most crucial data points. Then, we can determine with rules what are the most important for us. These data points can be deadlines, excellent sales results, or maybe tasks carrying a high risk.
Fundamentals of Conditional Formatting
Before we walk you through all possibilities of conditional formatting, we have to stall a bit. It is necessary to know all the basics! First, you have to understand the structure that conditional formatting works by. So, let’s see a little overview. In short, we will summarize the most important rules for you.
Logical Operators (if-then rules): Every single conditional formatting rule is based on very simple logic. If “X” criteria are true, then apply the rule “Y”. Let’s see a simple example: “X” criteria are: “The sales price is more than $50.” “Y” criteria are defined as a color all applicable cells red. What will happen now? By applying the if-then rule, every cell will be colored red where the sales price is > $50, more than $50.
Predefined Conditions (built-in presets): This option can be interesting for beginner users. In Excel, there is a large number of built-in rules and conditions available. When making the tutorial, we have also thought about users with different capabilities. We will learn about this subject from a detailed guide.
User-defined Conditions: There are often situations when the default settings are not suitable for the given task. No problem, let’s make our own rules. Use the Excel formulas so we can reach the required results. We have to note that we can use all of the formulas of Excel to create the rules.
Multiple Conditions: We can apply multiple rules for one cell or even a range at once. If there are more rules active at the same time, which one will prevail? We will talk about this in detail in the chapter “rule hierarchy and precedence.”
Highlight Cells using rules (if the quantity is greater than 200)
To highlight patterns and trends with conditional formatting, click the Highlight Cells Rules functions. It will be very easy to identify the cells that meet your criteria. This is a basic color formatting method for cells and ranges.
Select the range you want to use a rule and apply highlight rules. In our example, we will highlight any product that’s quantity is greater than 200 units. Select the range ‘H2: H24’.
In the Home tab of the ribbon, click Conditional Formatting then click Highlight Cell Rules. Select ‘Greater than’.
Now, a dialog box will appear. Enter 200 In the left box. So, if you enter 200, something will happen when the value is greater than 200. But what will happen? You will define the trigger in the right box.
Explanation: Triggers can be defined in the range with which the event is associated. Just forget the standard option and select a light red fill from the drop-down list. If you click OK now, all the cells that are above 200 will be formatted based on the given rule.
Highlight cells that contain text
If you are looking for all the M types converters in column G, then you do not have to scan the screen for hours and hours. Instead, you can let conditional formatting do all the dirty work and highlight the given criteria easily and automatically.
Select the range with text. In this example, you will select the range G2:G24, all our ‘Product Name.’
Click Conditional Formatting, hover the mouse over Highlight Cells Rules, and choose Text that Contains…
Enter ‘M type’ in the text box. In this example, select yellow fill with dark yellow text using the drop-down menu then click OK.
Tip: You can also apply a custom format if the default style is not suitable for you. To create your own format, use the drop-down menu, and select the Custom format option. You can create custom styles by changing the font, border, or fill types. Keep in mind the basics of conditional formatting! Further options are available if you can apply different rules. You can apply important highlight rules for your values too: Greater than…, Equal to…, or Between…
Editing a conditional formatting rule
In this section, we’ll show you how to manage conditional formatting rules. You can modify the selected rule using the conditional formatting rules manager. Use this function if you want to edit some of these rules later or delete them.
This function can be accessed using the Home Tab and Conditional Formatting button. Then select Manage Rules from the drop-down list. By default, you’ll see a dialog box:
The “Show formatting rules for:” is set to “Current Selection” by default. Select the “This Worksheet” option from the drop-down list to display the conditional formatting rules you have applied to the actual Worksheet.
Use top-bottom rules and select the rule from the list that you want to modify. Then, click the rule you want to change to edit a rule. In this example, we would like to highlight the top 10 values in the Total value column. Currently, the top five are highlighted.
Click the Top 5 row! Excel highlights the selected rows with blue. Then, click Edit Rule. A dialog box opens where you can change the given conditions of the rule; type 10 in the number field. Finally, click OK.
We’ll get the Manage Rules box back. Click OK to save the changes.
Delete a conditional formatting rule
Sometimes your spreadsheet looks like a traffic jam regarding too many conditional formatting rules. This is because excel has a hidden “feature.” If you have many unique rules, that may indeed be the reason for the slow calculations. In this case, you should eliminate some of the rules in the sheet.
Tip: Excel provides a quick way to delete rules. Click the Conditional Formatting button on the Home tab and select clear rules.
But it’s not a smart decision to delete all rules from a worksheet!
In this case, we would like to delete the rules from Column G (Product Names). Select the ‘G2:G24’ range.
To properly delete a rule, select the ‘Manage Rules’ box and select which conditional formatting rules to be removed.
Click OK to delete the rule.
We hope you have found the first part of the tutorial interesting and exciting. In this, we have introduced how we can use the basic rules with the use of Excel.
Conditional Formatting and Formulas
You’ll find here 50+ formula examples and learn more about custom formulas.
All examples are based on a few simple steps:
- Select the range that you want to apply format
- Add ‘New Rule’
- Enter the formula
- Select format style
- Click ‘OK, then click ‘Apply.’
If you want to learn all about Excel Formulas, check our definitive guide with examples.
How do I highlight the lowest 3 values in Excel?
Create a formula to determine the 3 smallest values that meet specific criteria, use a formula based on the AND and SMALL functions. In the example, the formula used for conditional formatting is:
where “city” is the named range B4:B12, and “sales” is the named range C4:C12.
How to conditional format if the cell is blank?
In the next example, you want to highlight values in one column when values in one or more columns are blank. In this case, use a basic formula based on the OR and ISBLANK functions to test for blank or empty cells. If any cell in a corresponding row in the range B4:E12 is blank, OR function returns TRUE. Thus, the trigger will be fired, and the cell in column F will highlight using light blue.
Use the conditional formatting formula:
How to use Conditional Formatting to highlight past due dates in Excel
In the example, you will apply a formula to determine dates that are “past due.” The formula will check if the variance between dates is greater than a certain number of days. To create a color-coded table, use three conditional formatting rules for each interval.
Select the cells in range E4:E9 and apply the formulas.
- =(E4-D4)<5 if the variance is less than 5 days,
- =(E4-D4)<15 if the variance is between 5 days and 15 days
- =(E4-D4)>=20 if the difference between the two dates is greater than or equal to 20
Important: You have to use the stop-if-true checkbox for rule1 and rule2.
How to highlight overlapping dates in Excel
Sometimes you need to highlight cells where dates overlap. In the example, you can use conditional formatting formulas and apply the SUMPRODUCT function. What are overlapping dates? We are talking about overlapping dates if these two conditions are true:
- Start date less than equal on other end dates in the column.
- End date is greater than or equal to at least one other start date.
Create a new column to check the conditions. Apply the formula to cell F4 them copy the formula down.
Now create a conditional formatting rule. First, select the cells that you want to format, in this case, range C4:F8. Then use the rule below to highlight overlapping dates.
So, click OK to apply the rule. If the result is TRUE, the given row cells in the selection will be highlighted.
Finally, take a closer look at the advanced conditional formatting rules:
- If you are working with multiple rules, you should know how the conditional formatting rule hierarchy works.
- The Stop if True tool helps you to manage overlaps between rules.
- Color ranking helps you to identify the maximum value in a range and highlight them.
- Learn how to use Multiple Conditions and Formulas.
Conditional formatting makes our lives in Excel a lot easier.
Use formulas wisely! With this, we can highlight all the key information that fits the criteria. Knowing and applying conditional formatting rules decrease the time spent on data analysis. Therefore, we can be a lot more productive. By recognizing the patterns (may they be positive or negative), we can effectively support company decisions. Look at the in-depth article called ‘How to create an Excel Dashboard’ regarding conditional formatting on our blog.