Conditional formatting enables us to apply special formatting to cells and ranges that meet a given criterion. Excel contains a large library of formulas. But if these are just not enough, we have a chance to work with our own created rules.
This comprehensive tutorial will provide an in-depth analysis of the most frequently used conditional formatting functions and tricks. This guide contains step-by-step examples for basic, advanced, and expert users. In this tutorial, we’ll use Excel 2016 for demonstration purposes. The spreadsheet is compatible with Excel 2010 and Excel 2013 too.
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
- Data Bars – Get a quick overview of highs and lows
- Color Scales
- Icons sets and Indicators
- Advanced Conditional Formatting
- Apply Multiple Rules using ‘Stop if True’ rule
- Conditional Formatting and Formulas
- Working with logical operators (IF, AND, OR)
- Hierarchy of Conditional Formatting rules
- Make a rule dependent on the content of another cell
- Frequently asked questions
What is Conditional Formatting
Conditional formatting is a data visualization tool in several spreadsheet application. 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. We will introduce possibilities that are available with the help of Excel and 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. 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 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.” Note: We recommend this procedure to advanced users.
Highlight Cells using rules (if the quantity is greater than 200)
To highlight patterns and trends with conditional formatting, click 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 popup window 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. 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 popup window:
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.
Select the rule from the list that you want to modify. 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! The selected row will be highlighted in blue color. Then, click Edit Rule. A popup window 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
Frequently your spreadsheet looks like a traffic jam regarding too many conditional formatting rules. Excel has a hidden “feature”. If you have a large amount of unique conditional formatting 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 that are 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.
Want to know more about conditional formatting? Be with us in the next part also. At the end of the chapter, you will have advanced knowledge. The most important part of the guide will be the “How to make a rule dependent on the content of another cell” tutorial.
Use Data Bars to get a quick overview of highs and lows
Now we’ll learn about data bars. If you’re not using specific rules or conditions in your data range or cells, then data bars are great for showing the top or bottom values. We can apply it for the current selection (range or cell). It’s easy to visualize values using data bars in Excel. A shorter in-cell bar chart represents a lower value. A longer bar represents a higher value.
Data bars are perfect for displaying you the top-bottom values of your data.
To add data bars to the selected range, execute the following steps. First, we’ll apply data bars to the Quantity column. In this example, we want to visualize the quantity. Select the ‘H2: H24’ range of cells.
Click the Conditional Formatting button on the Home tab of the ribbon. You have two options – Gradient Fill and Solid Fill. Select your preferred style. In this case, we will choose a solid fill using blue color.
The dimension of the bar is based on the value of the cell compared to the rest of the selected range. All other cells are filled comparatively. If you change the values, Excel updates the data bars automatically.
Color scales are similar variants to Data Bars. If you want to get a quick overview of your data in Excel, use this tool. The shade of the color represents the value in the cell.
Explanation: What is the difference between color scales and data bars? Data bars representing the relationship between value and colors through the length of a bar. Color scales assign colors to your range based on the selected color scales.
To apply color scales using conditional formatting, select the data. In this case, jump to the column E and select the ‘I2: I24’ range.
Select the Conditional Formatting button on the Home tab and check the Color Scales drop-down menu. You’ll see different color schemes. Pick the preferred color scheme and click it.
Use the red-white-blue color scale. Your Worksheet shows the prices by color. Red cells are the higher values, white is the median, and blue cells are the lower values.
Explanation: By default, for three-color scales, Excel calculates the median. The cell that holds the maximum value ($99) is colored red. The cell that holds the median ($48) is colored white, and the cell that holds the minimum value ($15) is colored blue. All other cells are colored proportionally.
Icon sets and shapes
Icon sets are a good opportunity for us to visualize values in a range of cells easily. If you want to add an icon set, shape, or indicator, execute the following steps.
We’ll apply icon sets to the F column to highlight low, middle, and high-priced products.
Select the ‘J2: J24’ range – Purchase Price Column.
Click Conditional Formatting and select Icon Sets. You have various options for icons: Directional, Shapes, Indicators, and Ratings. Choose your favorite icon of these to fit the needs of your data.
In this example, we’ll use traffic light style shapes: green, yellow, and red traffic lights that indicate high, middle, or low values.
Here is the result below:
By default, for 3 shapes, Excel calculates the green-yellow-red dividers for 67% and 33%.
In this example, the minimum is $4.70, the maximum is $19.80
Above 67% (green shape) = min + 0.67 * (max-min) = 4,70 + 0,67 * (19,80 – 4,70) = 14,817
Below 33% (red shape) = min + 0.33 * (max-min) = 4,70 + 0.33 * (19.80 – 4.70) = 9,68
- A green shape will show for values equal to or greater than 14,81.
- A yellow shape will show values greater than 9,68 and equal to or less than 14,81.
- A red shape will show values less than 9,48.
Advanced Conditional Formatting
This section is only for advanced Excel users.
Apply Multiple Rules using ‘Stop if True’ rule
It often happens that with the use of conditional formatting, we create multiple rules.
Let’s see a simple example: In a given range which contains prices, we would like to apply two rules at the same time.
Rule 1: A traffic light style icon set (red-yellow-green) will indicate the measure of the value in the given cell compare to the other cell-values. Red is the upper 1/3rd, yellow is the middle 1/3rd, and green is the bottom third.
Rule 2: Emphasize the prices that belong in the upper 1/3rd, but here uses a different method of formatting! This is not a bad idea on its own!
Let’s see, will there be overlap in between the rules?
Yes, there will be! Because based on the applied icon set rule, it will fill in all the cells. And this will contain the values that belong to the upper 1/3rd. So how can we deal with the problem? Excel conditional function offers an elegant solution. Let’s see what happens when we use the Stop if True tool.
We selected the J2:J24 range and used a traffic light shape set to the J column to highlight low, middle, and high-priced products.
But what will happen if we want to highlight the top 33% of the purchase price too? If we create a new rule, we provide extra information, and it’s not necessary. To avoid visual accidents, we’ll use the stop if true rule.
Click OK to return to the Manage Rules box.
Check the Stop if true box to the right of the new rule you just created. Click OK.
Looks great! The given range will now only show the icon sets for the items with a purchase price NOT in the top 33%.
Conditional Formatting and Formulas
Now let’s talk about the logical functions. In this section, we’ll show you how to create effective formulas to build a complex rule.
Use multiple conditions for a Rule with logical operators (AND / OR)
Create a new rule to highlight any cell in the Description column that contains product codes.
Add a new rule. Choose ‘classic’ from the top drop-down list in the dialogue box.
Select ‘Use a formula to determine which cells to format’.
We want to search cells in the Description column for an ‘F’ and highlight that cell when both conditions are true. To do this, we’ll use Excel’s LEFT and RIGHT formulas, with the AND formula, to look for the A and X values.
Click OK. Your spreadsheet will focus all cells in Description that meet ALL conditions.
Explanation: We use AND at the beginning of the formula to show that both of the following conditions must be met in order to apply the conditional formatting. Use the LEFT function when you want to extract characters starting on the left side of the text. The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, LEFT(“A920X”,1) returns “A”. Use the RIGHT function when you want to extract characters starting at the right side of the text. The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT(“A920X”,”,1) returns “X”. This example is just one of the hundreds of different formulas you could enter with the AND function.
Hierarchy of Conditional Formatting rules
When using conditional formatting, some problems may arise; we are not familiar with the hierarchy.
- What is this mean? The rules are subordinate to each other.
- Is more rule applying to one cell than Excel has to decide by precedence rules which will get priority?
- What will happen when one rule overwrites the other? In this case, we may lose the given cell’s format. To prevent these unpleasant and unexpected events, we have to keep in mind the hierarchy of the rules.
- First and most important to know is that the newer rules will always assume precedence over the older ones. Try to think by the reversed logic sequence order.
- When Excel analyses and apply the rules relevant to a cell, then there are two options. In the first one, the rules do not conflict. The cells return with the awaited highlights (like yellow mark by red background color). In the second one, the rules conflict, and we will lose the highlights of conditional formatting has created. And on top of all that, we wouldn’t know which one. If we create our rules wisely, they will not conflict.
- Rules don’t conflict when they apply to different properties of the cell. We can use endless formatting methods, so we only talk about one single example here.
Example: Rules overlapping (Solution)
Let’s assume that there are two rules regarding one cell.
- If rule 1 is TRUE, the font is color red.
- If rule 2 is TRUE, the cell color is yellow.
Because we alter two different properties of the cell when we run the rules there will be no negative effect. Let’s see the possible causes. Overlap or the lack of it will change the highlight like this:
Where Excel gives a TRUE value for the analysis of both rules, we will experience the following. There will be cells with a yellow background and with red font color. So, we can recognize that here two rules have crossed each other’s way!
Explanation: because here we use two different properties of the cell, we can easily see the mistake. We call them mixed cells. If both of these rules had operated with the color of the background, we would be in big trouble!
To adjust the rule hierarchy in Excel, follow these steps. Just a few words about these two rules:
Rule 1: =$M2<$N$1-150
Explanation: All entries updated more than 150 days before the date in cell N1 must have their entire row colored yellow.
Rule 2: If quantities are less than 300, we must use a red fill.
Now apply the current conditional formatting rules to check the Worksheet.
Important! Check cell H8! Rule 1 overwrite rule 2! Cell H8 and cell M8 meeting the conditions, but rule 1 will be applied regarding the rule hierarchy.
Try to modify the order of rules!
- Change the first field from ‘Current Selection’ to ‘This Worksheet’ in the drop-down menu.
- Select the first rule. The rule will be highlighted.
- Click the move down arrow!
- Click OK
Now it looks different. That is because the rule that makes all cells in red if they are below 300 is at the top of the Conditional Formatting Rules Manager window.
Make a rule dependent on the content of another cell
So far, we have processed only and exclusively situations where the color of the given cell was specifically determined by the relationship of the values in a given range. What will happen if we would like to introduce a new variable? Conditional formatting enables us to determine highlights based on the value of a referred cell.
How does this look in practice? In the referred cell, we can modify the values arbitrarily. This cell is the variable of the conditional formatting. If the value changes, then the highlight of the given range (icons, shapes, custom formatting, etc.) will dynamically change also.
Let’s see a specific example!
In this case, I need to highlight all the dates in column H to be highlighted when they are more than 15 days newer than the date I enter in cell J2. Select the Updated column (range H2:H24) in the sample file.
Click Conditional Formatting on the Home tab of the ribbon, then click New Rule. A popup window will appear. Choose ‘Use a formula to determine which cells to format’.
Enter the formula =$M2>$N$1+15
Click the Format button and select your formatting style. Click OK.
Explanation of formula: In this expression, we evaluate values in column ‘M’. M2 is a reference to the first cell in the selected range. We can apply this conditional formatting rule for this range. We should use ‘$’ symbol for M2. Press F4 twice ($N$1), because N1 is an absolute value, the conditional formatting rule always use this cell. What’s the meaning of the second part of this expression ‘>$N$1+15’? Every cell that is more than 15 days after the date in cell N1 is appropriate to our criteria! Check the result below: the highlighted cells are more than 15 days after the date in cell N1.
Frequently asked questions and answers about Conditional Formatting
Can I copy a rule if I need it, or do I have to create a new one every time? Use the format painter, you always have the opportunity to duplicate a rule. You have to follow these steps. Click on the cell that has the conditional formatting you want to copy. Click Home > Format Painter. To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
Can I apply conditional formatting in various situations? Besides formatting cells, there are three other possibilities available. These require advanced or rather expert knowledge of Excel. It helps if you are familiar with VBA programming. The first is the possibility that you use shapes. Professional Excel maps can be created if we write some macros. Finally (to some surprise), special charts can be made with this technique.
Because I wouldn’t want to get lost in the rules, what can I do to reduce and lockout the margin of errors? In this case, the “Stop if True” tool is the most useful. But even better to use some Excel formulas (AND, IF, ISNUMBER) and to create a more complex rule with the combination of these. So, you don’t have to worry about the problems created by the rule hierarchy.
I’m concerned that conditional formatting will change the value of the cell. Is this true? No, this cannot happen. Conditional formatting (as suggested by its name) can change the format of the cell but can never change its value.
What can I do if in a given column – were already applying valid rules – still have to use dynamically changing values? For this, there’s a solution. If it is unavoidable to be a formula in the cell where you want to use conditional formatting, do the following: Create and use rules which help you to format the value of the cell even though it is not static. So, the cell where you would like to apply highlights should not be any part of a formula as far as possible, but the end result.
Overall, with the use of conditional formatting, we can make our lives a lot easier. Use data visualization 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. On our blog, there’s an in-depth article called ‘How to create an Excel dashboard’ regarding conditional formatting.