Conditional formatting enables us to apply special formatting to cells and ranges that meet a certain given criterion. Excel contains large library of predefined formulas. But if these are just not enough we have a chance to work with our own created rules.
When you create your excel dashboard, you will frequently want to use conditional formatting to create visual dashboard components.
Excel Spreadsheets offer many possibilities for us to store and organize data. Usually we have to process data sets. How can we identify for decision support the necessary key information? The management can make decisions based on these factors. At first it doesn’t seem like an easy task. For these challenges we have a flexible and effective tool: conditional formatting.
Before we go any further, let’s see what today’s subject will be!
This comprehensive tutorial will provide 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 downloadable spreadsheet is compatible with Excel 2010 and Excel 2013 too.
What is conditional formatting?
Conditional formatting is a present tool in several spreadsheet application. For example, Google Sheets, Microsoft Excel or Apple Numbers. 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. It’s first and foremost function is to direct the attention to the most important 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 high risk.
Furthermore, it can help us to make a huge data set easily understandable with the help of visualization tools.
Finally let’s talk about the most important function! Conditional formatting can dramatically modify a spreadsheet. We can send information by colors to the users. This signal can be positive, if say in some months we exceed the planned income. But it also can be an alert if the case is in the revers.
As an assessment we can conclude that conditional formatting is on one hand a decision supporting tool and on the other hand it is capable of forecasting because we can apply arbitrary rules, so we can even use it for scenario analysis also.
Learn the 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 a very simple logic. If “X” criteria is true, then apply rule “Y”. Let’s see a simple example: “X” criteria is: “The sales price is more than $50.” “Y” criteria is defined as: color all applicable cells red. What will happen now? By applying the if-then rule, every cell will be colored red where sales price is > $50, more than $50. Important to know that the number of rules in conditional formatting is virtually endless.
Predefined Conditions (built in presets): This option can be interesting for beginner users. In Excel there are an overwhelmingly large number of pre-defined rule and condition available. When making the tutorial we have also thought about users with different capability. Consequently, we will learn about this subject from a comprehensive and 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. There are no limiting conditions at all.
Applying Multiple Conditions: We can apply multiple rules for one cell or even a range at once. This arises questions. If there are more rules active at the same time which one will prevail? In the tutorial we will talk about this in detail in the chapter “rule hierarchy and precedence.” Note: We recommend this procedure to advanced users.
Overall, with the use of conditional formatting we can make our lives a lot easier. Use the data visualization wisely! With this we can highlight all key information that fit the criteria. Knowing and applying conditional formatting rules decrease the time spent on data analysis.
Therefore, we can be a lot more productive. With recognizing the patterns (may they be positive or negative) we can effectively support company decisions.
Apply Conditional formatting in Excel – Basics
First, let us see the basics! You have an existing data set. You will take the first step through apply conditional formatting to your prepared data set. In this section you will learn how to apply the most used built-in formatting rules using this step-by-step guide.
Apply Highlight Rules to your data set
To highlight patterns and trends with conditional formatting you can use 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.
Excel provides many useful options for color formatting. We strongly recommend you download the sample workbook.
- The first thing you need to open our prepared workbook in Excel. If you want to work with your data enter the data manually because you can easily apply the necessary steps to your data set. In this example, we are using a simple product comparison template. The template contains all relevant information about products.
2. 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 values in the Quantity, it’s range ‘D2:D24’.
3. In the Home tab of the ribbon click Conditional Formatting. Then hold and navigate your mouse pointer over the option Highlight cells rules. Click Greater than.
4. Now a popup window will appear. Type 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 on the range with which the event is associated.
Just forget the standard option and select 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.
Tip: If you are unsatisfied with the result you can select other presets using a drop-down list. Or you can create your own formatting style.
5. If you are looking for all the M types converters in the column B, 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. To identify text cells, repeat step 2.
6. Select the range with text. In this example, you will select the range from C2 to C24, all our ‘Product Name’.
7. Click Conditional Formatting, hover the mouse over Highlight Cells Rules and choose Text that Contains…
8. Enter ‘M type’ in the text box. In this example select yellow fill with dark yellow text using drop-down menu then click OK.
9. Let us see the result below:
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 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…
Creating Top-Bottom Rules
The conditional formatting Top/Bottom rules allow you to apply formatting to cells that meet a statistical condition with reference to other cells in the range (for example above average, within top 10%, etc.).
Tip: Good to know that these conditions will only be applied to cells containing numeric values.
1. In this example, we’ll highlight the top three total values. Click the top of the Total values column to select the range ‘G2:G24’.
2. Click Conditional Formatting. Then Top/Bottom Rules and Top 10 Items. The default value in this case is 10. To change the default value, enter your value in the New Formatting Rule Box.
3. In the box that appears, change 10 to 3, since we only want to highlight the top three values. Click the drop-down menu and select green fill with dark green text. Click OK.
4. Your range and worksheet will be updated and highlight the top three values in the Total value column.
Using Data Bars
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 displaying you 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. That’s all, nothing special.
1. To add data bars, 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 ‘D2:D24’ range of cells.
2. Click the Conditional Formatting button in 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 custom formatting style using the More Rules menu.
3. Your worksheet will now reflect the applied rule.
Tip: 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!
Setting up color scales
Color scales are similar variant 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 bar? 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.
1. To apply color scales using conditional formatting select the data. In this case jump to the column E and select the ‘E2:E24’ range.
2. Then 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.
3. 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 (82) is colored red. The cell that holds the median (60) is colored white, and the cell that holds the minimum value (36) is colored blue. All other cells are colored proportionally.
Apply Icon sets, Shapes, Indicators and Ratings
Icon sets are a good opportunity for us to visualize values in a range of cells easily. To add an icon set, shape or indicator execute the following steps.
1. We’ll apply icon sets to the F column to highlight low, middle, and high-priced products. Select the ‘F2:F24’ range.
2. 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.
3. Here is the result:
Explanation: By default, for 3 shapes, Excel calculates the green – yellow – red dividers for 67% and 33%. In this example the minimum is $4.60, the maximum is $19.40
Above 67% (green shape) = min + 0.67 * (max-min) = 4,60 + 0,67 * (19,40 – 4,60) = 14,51
Below 33% (red shape) = min + 0.33 * (max-min) = 4,60 + 0.33 * (19.40 – 4.60) = 9,48
A green shape will show for values equal to or greater than 14,51. A yellow shape will show for values greater than 9,48 and equal to or less than 14,51.
A red shape will show for values less than 9,48.
Edit a Conditional Formatting Rules
In this example 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.
1. This function can be accessed using the Home Tab and Conditional Formatting button. The select Manage Rules from the drop-down list. By default, you’ll see a popup window:
2. 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.
3. Select the rule from the list that you want to modify. To edit a rule, click the rule you want to change. In this example, we want to highlight the top 10 values in the Total value column. Currently the top three is highlighted.
4. Click the Top 3 row! The selected row will be highlighted with blue color. Then, click Edit Rule.
5. A popup window opens where you can change the given conditions of the rule. Type 10 in the number field and click OK.
6. We’ll get the Manage Rules box back. Click OK to save the changes.
Delete a Conditional Formatting Rules
Frequently your spreadsheet looks like a traffic jam regarding too many conditional formatting rules.
Excel has a hidden “feature”. If you have large amount of unique conditional formatting rules that may indeed be the reason for the slow calculations. In this case, you should to eliminate some of the rules in the sheet.
Tip: Excel provide a quick way to delete rules. Click the Conditional Formatting button on the Home tab and select clear rules.
But it’s not smart decision to delete all rules from a worksheet!
1. In this case we would like to delete the rules from the Column C (Product Names). Select the ‘C2:C24’ range.
2. To properly delete a rule, select Manage Rules box and choose “This Worksheet” to select specifically which conditional formatting rules that are to be removed.
3. 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 2016. If you would like 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.
We will introduce the following subjects: How to create new rules and how to use Excel formulas. The advanced section most important element will be the “How to make a rule dependent on the content of another cell” tutorial.
Advanced Conditional Formatting Functions in Excel
As we have mentioned in the introductory now we would like to show new stuff to those who are more familiar with Excel conditional formatting. Fortunately, you don’t have to throw away the sample workbook you have used so far! We will demonstrate all further actions in the previously introduced worksheet.
Create a new rule and apply the Stop if True rule
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 lights 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 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.
1. We selected the F2:F24 range and used a traffic light shape set to the F column to highlight low, middle, and high-priced products.
But what will happen if we want to highlight the top 33% of purchase price too? If we create a new rule, we provide extra information and it’s not necessary. To avoid from visual accident, we’ll use the stop if true rule.
2. As first step, select the F2:F24 range and create a new condition on this column.
3. To open the Manage Rules box click Conditional formatting then Manage rules.
It’s important to select the ‘Current Selection’ from the drop-down menu, because we’ll only append the rule on this column.
Use a different formatting style to highlight the top 33%. So, select Top from the left drop-down menu, type 33 in the second field. Then check the “% of the selected range” box.
To apply color formatting, click Format and a new window opens. Click the Font tab and select the red color.
4. 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.
5. The given range will now only show the icon sets for the items with a purchase price NOT in the top 33%.
Use multiple conditions for a Rule with logical operators (AND / OR)
1. Create a new rule to highlight any cell in the Description column that contains product codes. Go to the Description column and select this range of cells, B2:B24
2. Click Conditional Formatting then select New Rule.
3. Choose ‘classic’ from the top drop-down list in the dialogue box. Select ‘Use a formula to determine which cells to format’.
4. We want to search cells in the Description column for a B 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.
• 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 at the left side of 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 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 hundreds of different formulas you could enter with the AND function.
6. Click OK. Your spreadsheet will focus all cells in Description that meet ALL conditions.
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 arbitrary. This cell is the variable of the conditional formatting. If the value changes than 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.
1. A popup window will appear. Choose ‘Use a formula to determine which cells to format’.
2. Enter the formula =H2>$J$2+15
3. Click Format button and select your formatting style.
4. Click OK
Explanation of formula:
In this expression, we evaluate values in column H.
H2 is a reference to the first cell in the selected range. We can apply this conditional formatting rule for this range. It’s not necessary to use ‘$’ symbol.
We should use ‘$’ symbol for H2. Press F4 twice ($J$2), because J2 is an absolute value, the conditional formatting rule always use this cell.
What’s the meaning of the second part of this expression ‘>$J$2+15’? Every cell that is more than 15 days after the date in cell J2 is appropriate to our criteria!
Check the result below: the highlighted cells are more than 15 days after the date in cell J2.
Highlight Rows Based on Drop-Down List
Data validation is a very useful Excel feature. If we combine the drop-down list with conditional formatting than we can supply various highlights to the values chosen by the user. In this case the conditional formatting will only apply to the elements of the drop-down list.
1. In this example we’ll use an additional range (column H) to apply conditional formatting for the drop-down list items. It’s easy to create a new column. Click Home Tab, click Insert then Insert sheet Columns.
2. Add a name for this column: ‘Modified by’
3. Insert a new sheet in this workbook to create a drop-down list. Fill the column A with the Operator names. We’ll use these names in the main sheet.
4. We will include in our drop-down list the range A2:A11
5. To convert this list into an excel table select the range then click Insert tab on the ribbon and click Table.
6. To define a name based on this list click Formulas tab then click Define name.
7. In the popup window, enter the name of the list. Choose a name, in this example we’ll call the list ‘Operators’. Finally Click OK.
8. To check the created list, use the Name manager.
9. Jump to our main worksheet. We’ll show you how to implement the drop-down list into new column-The best practice is using Data validation.
10. Select the ‘Modified by’ column to apply data validation to this range of cells. Next click the Data tab on the ribbon. To create a drop-down list, click ‘Data Validation’ and ‘Data Validation…’
11. In the dialogue box, under the ‘Allow’ menu choose List type from the drop-down menu. The data will be restricted in this range to a list.
12. As source you can add the defined names. Enter =Operators in the source field then click OK. The list has been successfully created.
13. Now populate the column by selecting a name from the drop-down list.
14. We’ll apply a simple conditional formatting using names. Create a simple rule for this range. If the cell value = ‘Steve’ we’ll use green fill for the given cell.
15. That’s all. Now we have combined the conditional formatting feature with data validation using Excel.
Hierarchy and precedence of conditional formatting rules
When using conditional formatting some problems may arise is 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.
In this chapter you will get useful information about precedence of conditional formatting rules.
1. 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.
2. When Excel analyses and apply the rules relevant to a cell than 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 conditional formatting has created. And on top of all that we wouldn’t know which ones… If we create our rules wisely they will not conflict.
3. 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.
Let’s assume that there are two rules regarding one cell. In application of rule 1 the color of the font is red. In application of rule 2 the background of the cell 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 cases. Overlap or the lack of it will change the highlight like this:
Where rule 1 is true, the font will be red and if the result of rule 2 is true, the background will be yellow.
Where Excel gives a TRUE value for the analysis of both rules we will experience the following. There will be cells with 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 would have operated with the color of the background, we would be in a big trouble!
To adjust rule hierarchy in Excel, follow these steps. Just a few words about these two rules:
Rule 1: =$G2<$J$2-150
Explanation: All entries updated more than 150 days before the date in J2 must have their entire row colored yellow.
Rule 2: If quantities less than 300, we must use red fill.
Now apply the current conditional formatting rules to check the Worksheet. The rule 1 overwrite the rule 2 because 0 and 116 are meet the conditions but rule 1 will be applied regarding the rule hierarchy.
Try to modify the order of rules!
1. Change the first field from ‘Current Selection’ to ‘This Worksheet’ in the drop-down menu.
2. Select the second rule. The rule will be highlighted.
3. Click the move up arrow!
4. Click OK.
Now it looks different. That is because the rule that makes all cells in red if they are below 300 is in the top of the Conditional Formatting Rules Manager window.
Frequently asked questions and answers about Conditional formatting
Q: Can I use conditional formatting to create Excel dashboards?
A: Very good question! Yes, we specifically recommend this technique. If you examine it carefully all of the best dashboards use this method. We can dynamically change the appearance and creating the rules is easy. The use of highlight obvious in this case also.
Q: Can I copy a rule if I need it or do I have to create a new one every time?
A: In Excel with the help of the popular 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.
Q: What other fields of application there are to conditional formatting?
A: Besides formatting cells there are three other possibilities available. These require advanced or rather expert knowledge of Excel. Helps if you familiar with VBA programming.
The first is possibility is 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. After the Q and A section we will introduce some really special fields of application.
Q: Because I wouldn’t want to get lost in the rules what can I do to reduce and lock out margin of errors?
A: In this case the “Stop of 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 rule hierarchy.
Q: I’m concerned that conditional formatting will change the value of the cell. Is this true?A: No, this cannot happen. Conditional formatting (as suggested by its name) can change the format of the cell but can never change its value.
Q: What can I do if in a given column – where already applying valid rules – still have to use dynamically changing values?A: 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 helps 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.
Q: I am a beginner in Excel and don’t know many formulas. Is there any other resource where I can learn more from?
A: Yes, on the site of the official Office blog you can find extended description about every formula. With the use of this you will be quickly familiar in the use of complex formulas.
Conditional formatting is a versatile Excel tool. You could see that the possibilities are almost endless if you have to work with cells containing various types of data or values. We hope our tutorial has reached its goal and you could get a comprehensive idea about this subject. In the future you may see articles that use the highlight of cells in one way or another.