It’s a feature that enables you to highlight cells based on certain criteria. It helps you to focus on key information, visualize data, make worksheets easier to understand, and keep you on top of your workload. It allows you to apply it like data bars, colors, icons, etc., to one or more cells based on the cell value.
Each rule has two components:
* A conditional expression that returns TRUE or FALSE.
* A format that should be applied if the condition is met.
You can either use the built-in formatting rules available or create your own formula as per your requirement. Adding your own formula to a conditional formatting excel rule gives you the power and flexibility to apply exactly the Formatting you need. You can use Formatting to highlight cells meeting the conditions you specify.
For example, you would want to highlight:
* Dates that fall in the next week
* Top 10 customers by sales
* All duplicate entries
* Scores that are above average, etc.
How to Use Conditional Formatting In Excel
To understand how conditional Formatting works, let’s consider a basic example. In the data below:
You have a list of details regarding various invoices. There are a few entries that are duplicates, and you want to highlight those invoice numbers. You can use the conditional formatting in excel to do this. Follow the steps below to get the desired output:
* Select the cells you want to format, i.e., A2:A21
* Go to Home > Under Styles Group > Select Conditional Formatting
* Click Highlight Cells Rules > Duplicate Values
* In the dialog box, select the formatting style > Click OK
* The duplicate invoice numbers will be highlighted.
Just like highlighting duplicate values, there is various in-built Conditional formatting functionality in Excel. Let’s look at each of those functionalities one by one:
Highlight Cells Rule
Greater than – You can use conditional formatting in Excel to quickly highlight cells that contain values greater than a specified value. For instance – You can use Excel to highlight sales values that have exceeded the sales target specified by the manager or highlight costs that are lower than the budget specified. The steps to do this are as follows:
* In the dialog box, type in the amount that should be the sales target and select the Formatting.
* Select the cells where you have the Formatting
* Go to the Home tab > Conditional Formatting > Highlight Cells Rule > Greater than
* Click OK
Note: If you want the condition to be “greater than or equal to” instead of just “greater than,” you can edit the existing condition. To do that, select the Manage Rule options from the Conditional Formatting excel dropdown. In the dialog box, click on “Edit Rule,” and from the dropdown, select “greater than or equal to.”
Date Occurring – Click on Home > Conditional Formatting > Highlight Cells Rule > A Date Occurring.
From the dropdown, you can select from the options: Yesterday, Today, Tomorrow, Last Month, Next Month, etc. Finally, you choose the formatting of one of the pre-defined formats or set up your custom format. One of the limitations is that the conditional format is always applied based on the current date.
Top/Bottom Rules
This rule is helpful to highlight/identify the top (or bottom) nth data in the set (by value or percentage). It can be used to identify top customers by sales amount or bottom candidates by their score. It can also be used to mark values that are above or below average. Note that this works only for cells with a numeric value.
You can follow the steps below to apply this conditional formatting excel rule:
* Select the range/dataset.
* Go to Home > Conditional Formatting > Top/Bottom Rules > Select appropriate rule _Top 10%, Bottom 10, above average, etc.)
* Based on the selection in step 2, a dialog box will open. Let’s say you have selected Top 10 Items from the dropdown, and the dialog box will give you the option of changing the value of ’10’ to a different value and choosing the Formatting to apply to cells satisfying the chosen criteria.
* Click OK to get the result.
Excel Data Bars
Data Bars is simply a bar graph inside a cell, and the length of the bar relates the value of the cell to other cell values in the selected range. A short bar means the value in that cell is low compared to the rest of the values in the range and a long bar means the value is high compared to the rest of the values in the range.
Note: Make sure that you do not include any Row/Column total in the range.
You can follow the steps below to understand how to use conditional formatting in excel:
* Select the range/dataset.
* Go to Home > Conditional Formatting > Data Bars. You’ll see two options – one for Gradient Fill and one for Solid Fill. Their function is identical; just select the option and color you prefer.
Click OK, and then the sheet will reflect the added rule.
Color Scales
The color scale in conditional Formatting excel applies color to cells in a cell range based on their values. The color scales are shades with gradations of two or three colors. If it is a two-color scale, the lowest value is assigned one color, and the highest value is assigned another color, and the rest are a blend of these two colors. It can be used to highlight general patterns in data, especially with a large data set.
You can follow the steps below to apply this conditional formatting rule:
* Select the range/dataset.
* Go to Home > Conditional Formatting > Color Scales and then click the color scale format that you want.
* The top color represents larger values, the center color represents middle values, and the bottom color represents smaller values.
* Click OK.
Icon Sets
The icon set is used to display icons that correspond to the values in the cell. This rule contains various icon formatting shapes like:
* Directional: Up/down arrow
* Shapes: Traffic lights, circles, and other colored shapes
* Indicators: Colored flags and Correct/Cross icons
* Ratings: Star Rating, Signal, and other shapes
Steps For Using Conditional Formatting Excel
You can follow the steps below to apply this conditional formatting rule:
* Select the range/dataset.
* Go to Home > Conditional Formatting > Icon Sets and then click the icon set that you want.
* By default, it highlights values greater than 67% numbers in Green, 33% to 67% in yellow, and less than 33% in red color. An example is shared below.
* Click OK, and you will get the desired result.
Rules Manager In Conditional Formatting Excel
After selecting any conditional formatting rules from the built-in menu, you may need to edit the rule to define a different format. For this, you need to select one of the cells in the range, go to Home > Conditional Formatting > Rules Manager.
All the existing conditional formatting applied to the current selection will appear in the box.
Note: From the dropdown list, change Current Selection to This Worksheet to view all conditional formatting rules in this worksheet.
You can select the Edit Rule option in the dialog box to modify the existing conditional formatting applied.
If Icon set is applied to the range of data, you can select the edit rule, and in the “Edit Formatting Rule” box, you can either edit the value or Icon as per your needs and click OK. Then click on “Apply” to see the change you have applied and if you are OK with the result, click OK.
Conditional Formatting In Excel There are various things you can do using the Rule Manager option, and these are especially useful if you’ve applied multiple rules to a worksheet. They are summarized below:
New Rule – To create a conditional formatting rule
* Edit Rule – To edit an existing rule
* Delete Rule – To delete an applied rule
* Up/Down Button – To change the position of a rule when multiple rules exist. To move the selected rule up in precedence, click Up. To move the selected rule down in precedence, click Down. These rules are evaluated one at a time, starting with the rule listed at the top. A later rule cannot override the Formatting already modified by a preceding rule.
* Stop if True Checkbox – Select the Stop If True checkbox to stop rule evaluation at a specific rule.
* Applies to Column – Specifies the range of cells to which a rule is applied. If you want to change the range, you can do it from here.
How to Use Conditional Formatting In Excel Based On Text
Conditional Formatting allows you to format a cell based on the text entered in it. Let’s understand with the help of an example.
In the example above, you have a list of transactions, and you want to highlight the cells in Column D for which the order hasn’t been delivered yet, i.e., the text in that cell is “No.” To do that:
* Select the range D2:D19
* Go to Home > Conditional Formatting > Highlight Cells Rule > Equal To
* In the dialog box, type the word “No.”
* Select the pre-defined formats from the dropdown or select custom format
* Click OK.
Troubleshooting Conditional Formatting In Excel
Sometimes, even though you follow the steps to apply a specific conditional format, the rules you apply don’t display your intended results. Troubleshooting of those errors is covered in this section of the article.
When multiple rules return TRUE for the same cell
Let’s look at an example. Suppose in the data below you want to fill cells with green color when the Sales Amount is greater than 30,000 and fill sales column’s cells with yellow color when the Country is the UK.
Now let’s add the first rule, i.e., fill cells with green color when the Sales Amount is greater than 30,000. To do that, Go to Home > Conditional Formatting > Highlight Cells Rule > Greater than, type 30000, and then select custom from the dropdown. Go to File Tab, select green color.
This will give you the result as shown below; all values greater than 30000 are now green.
Now let’s add the second rule, i.e., fill sales column with yellow color when the Country is the UK. Select Sales Column, Go to Home > Conditional Formatting > New Rule > Use formula. Type =$C2=” UK” and then click on format and under the fill Tab select yellow.
Click OK, and the result will be as shown below:
You might have noticed that cell D2, which was initially green fill as the value was greater than 30,000, has now been changed to yellow. This is because cell D2 satisfies both the rules and since both rules apply the same format, i.e., fill color, precedence will matter here. Had the formats been different, Excel would have applied both formats.
The rule you enter first has the least precedence, and the rule you enter last has the highest precedence as, by default, it sits at the top. So, here the yellow fill rule overlaps the fill green rule.
This will force Excel to fill the cells with a value greater than 30,000 green in color even though it meets the condition for other rules having the same format.
Another error you might encounter is that format should be applied to one column, whereas the condition should be checked from another column. It might happen that both have been applied to the same column. To prevent it, make sure that you select the column where the format must be applied and use the cell reference of the column where the condition is when mentioning the formula. If you forget to do so, you can simply change the column to which the format will be applied by clicking on “Manage Rule” under the Conditional Formatting option in Home Tab.
Lastly, if none of these points can fix your error, it means that there is an issue with the formula itself. Had the formula been typed directly on the worksheet, you could have used Evaluate feature to check each step one by one thoroughly. But unfortunately, this feature cannot be used when the formula is typed in the conditional formatting box. So, what to do?
You can check your conditional formatting excel formulas directly on the worksheet, so you can see what they’re doing. You can enter the formula across the range of the cells matching the exact shape of your data, i.e., the number of rows and columns of the data.
When a condition is met, and it returns TRUE, the format is applied, and when it returns FALSE, Excel does nothing. By writing the formula directly on the worksheet, you can visualize how formulas will behave before you create a rule. In this way, you make your formula free of any error and then paste that formula on the conditional formatting rule.
This article has focused on covering all possible topics related to Conditional Formatting excel, and by following this, you would have understood how powerful and flexible this feature is. With just a few clicks of conditional Formatting, you can add powerful insights to your data.
For more exciting and helpful tips related to Excel, keep following our blogs!
