Conditional formatting is a data visualization tool in spreadsheets used to highlight and differentiate cells or ranges with colors or icons.
At first, you will learn the essential skills. The second part of the tutorial will be about using 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 tasks carrying a high risk.
Fundamentals of Conditional Formatting
Before we walk you through all the 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 provides. 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 straightforward 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 interest beginners. In Excel, there are many built-in rules and conditions available. We also considered users with different capabilities when making the tutorial. We will learn about this subject from a detailed guide.
User-defined Conditions: The default settings are often unsuitable for the given task. No problem; let’s make our own rules. Use the Excel formulas to reach the required results. We must note that we can use all Excel formulas to create the rules.
Multiple Conditions: We can simultaneously apply multiple rules for one cell or range. If more rules are active simultaneously, which one will prevail? We will discuss this in detail in the chapter “Rule Hierarchy and Precedence.”
Highlight Cells using rules (if the quantity is greater than 200)
Click the Highlight Cells Rules functions to highlight patterns and trends with conditional formatting. It will be straightforward to identify the cells that meet your criteria. This is a basic color formatting method for cells and ranges.
Select the range for which you want to use a rule and apply highlight rules. In our example, we will highlight any product whose quantity is greater than 200 units. For example, select the range’ H2: H24‘.
In the Home tab of the ribbon, click Conditional Formatting, then click Highlight Cell Rules. Finally, 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. Remember 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 of converters in column G, 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 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. Next, select yellow fill color with dark yellow text using the drop-down menu, then click OK.
Tip: You can apply a custom format if the default style is unsuitable. Use the drop-down menu and select the Custom format option to create your format. 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 also use important highlight rules for your values: Greater than…, Equal to…, or Between.
Editing a conditional formatting rule
This section will show you how to manage conditional formatting rules. You can modify the selected rule using the conditional formatting rules manager. Use this function to edit or delete some of these rules later.
This function can be accessed using the Home Tab and the Conditional Formatting button. Then, select Manage Rules from the drop-down list. By default, you’ll see a dialog box:
By default, the “Show formatting rules for:” is set to “Current Selection”. Next, 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 you want to modify. Then, click the rule you want to change to edit a rule. In this example, we want to highlight the top 10 values in the Total value column. Currently, the top five are highlighted.
Click the Top 5 rows! 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 be the reason for the slow calculations. In this case, you should eliminate some 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 smart to delete all rules from a worksheet!
In this case, we want 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 click on the conditional formatting rule you want to remove.
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 to use the basic rules with Excel.
Conditional Formatting and Formulas
You’ll find formula examples here and learn more about custom formulas.
All examples are based on a few simple steps:
- Select the range that you want to apply the format
- Add ‘New Rule’
- Enter the formula
- Select format style
- Click ‘OK, then click ‘Apply.’
That’s all.
Check out our definitive guide with examples of Excel formulas.
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:
=AND($B4=$E$4,$C4<=SMALL(IF(city=$E$4,sales),3))
where “city” is the named range B4:B12, and “sales” is the named range C4:C12.
How to use conditional format if the cell is blank?
In the following example, you want to highlight values in one column when values in one or more columns are blank. A basic formula based on the OR and ISBLANK functions is used to test for blank or empty cells. For example, if any cell in a corresponding row in the range B4:E12 is blank, the OR function returns TRUE. Thus, the trigger will be fired, and the cell in column F will be highlighted using light blue.
Use the conditional formatting formula:
=OR(ISBLANK(B4),ISBLANK(C4),ISBLANK(D4),ISBLANK(E4))
How to use Conditional Formatting to highlight past due dates in Excel
You will apply a formula in the example to determine “past due dates.” The formula will check if the variance between dates exceeds 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 must use the stop-if-true checkbox for Rule 1 and Rule 2.
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:
- First, the start date is less than equal to the other end dates in the column.
- The end date is greater than or equal to at least one other start date.
Create a new column to check the conditions. Then, apply the formula to cell F4 and copy the formula down.
=SUMPRODUCT(($D4<=$E$4:$E$8)*($E4>=$D$4:$D$8))>1
Result:
Now, create a conditional formatting rule. First, select the cells you want to format, in this case, range C4:F8. Then, use the rule below to highlight overlapping dates.
=$F4=TRUE
So, click OK to apply the rule. If the result is TRUE, the given row cells in the selection will be highlighted.
Final thought
Finally, take a closer look at the advanced conditional formatting rules:
- You should know how the conditional formatting rule hierarchy works if you work with multiple rules.
- The Stop if True option helps you to manage overlaps between rules.
- Color ranking helps you 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! We can highlight all the key information that fits the criteria. Knowing and applying conditional formatting rules decreases the time spent on data analysis so that we can be much more productive. We can effectively support company decisions by recognizing patterns (whether positive or negative).
Related articles
- How to build a dashboard in Excel
- Sort based on cell color
- Color scales
- How to use the Quick Analysis Tool
- Highlight every other row
- Compare two columns in Excel
- Build a Heat Map
- Change shape color based on cell value
- How to use data bars
- Apply colorful icon sets
- Find duplicates
- How to use Conditional Formatting based on another cell
- Show protected cells