.jpeg)
Conditional formatting is useful in data analysis when we need to identify important values, patterns, and exceptions in a dataset quickly. It helps us apply colors, icons, and visual effects to cells automatically based on the values they contain, so the data can be understood more easily. In Excel, it is used to handle sales reports, student marks, attendance sheets, inventory lists, budget sheets, and other table-based data.
In this article, we will learn about conditional formatting in data analysis, its uses, and some commonly used Excel conditional formatting rules.
What Is Conditional Formatting in Data Analysis?
Conditional formatting is an Excel feature that changes the appearance of a cell, such as its fill color, font color, or border, when the cell meets a specific condition. The formatting updates automatically when the cell value changes.
For example, if a dataset contains sales values, we can highlight the values above the target in green and the values below the target in red. Similarly, we can use data bars to compare the sales of different employees at a glance.
Sample Dataset Used in This Article
We will use the following dataset in the examples. The headings are in row 1, and the data is in the range A2:D6.
| Employee (A) | Department (B) | Sales (C) | Due Date (D) |
|---|---|---|---|
| Amit | Sales | 5000 | 05-10-2026 |
| Neha | HR | 3000 | 28-09-2026 |
| Rahul | Sales | 7000 | 15-10-2026 |
| Priya | IT | 4000 | 28-09-2026 |
| Karan | HR | 6000 | 20-10-2026 |
Common Conditional Formatting Options
Excel provides several conditional formatting options that are useful for data analysis. Some commonly used ones are:
| Option | Purpose |
|---|---|
| Highlight Cells Rules | It highlights cells based on conditions like greater than, less than, between, equal to, text that contains, a date occurring, or duplicate values. |
| Top/Bottom Rules | It highlights the top or bottom values, percentages, or values above or below average. |
| Data Bars | It adds bars inside cells to show the size of values. |
| Color Scales | It applies a gradient of colors based on the value range. |
| Icon Sets | It adds icons such as arrows, flags, or traffic lights to show levels. |
| New Rule | It creates a custom rule, including rules that use formulas. |
| Manage Rules | It edits, deletes, or changes the order of existing rules. |
| Clear Rules | It removes conditional formatting from cells or the whole sheet. |
How to Apply Conditional Formatting
The basic process is the same for all conditional formatting options.
Steps
- Select the range of cells we want to format.
- Go to the Home tab.
- Click on Conditional Formatting in the Styles group.
- Choose a rule type and set the condition and format.
- Click OK.
Highlight Cells Rules
Highlight Cells Rules are used to format cells that meet a simple condition. They are useful when we need to find values above a target, within a range, or matching specific text.
Greater Than and Less Than
This rule is used to highlight numbers that are higher or lower than a given value.
Steps
- Select the Sales range C2:C6.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
- Enter 4500 in the value box.
- Choose a format, such as Light Green Fill with Dark Green Text, and click OK.
Output:
The sales values 5000, 7000, and 6000 are highlighted in green.
Explanation:
In the above example, we select the Sales range. After that, we apply the Greater Than rule with 4500 as the value. Finally, Excel highlights every cell in that range whose value is higher than 4500.
Between
The Between rule highlights values that fall within a given range. For example, entering 4000 and 6000 highlights the values 5000, 4000, and 6000 in the Sales column.
Equal To and Text That Contains
The Equal To rule highlights cells that exactly match a value. The Text That Contains rule highlights cells that include a particular word or letters. For example, if we apply Text That Contains with the word "HR" on the Department range, Excel highlights the cells of Neha and Karan.
Date Occurring
This rule is used to highlight dates based on a time period, such as Yesterday, Today, Tomorrow, In the Last 7 Days, Last Week, This Week, or Next Month. It is useful for tracking deadlines and recent entries.
Duplicate Values
This rule is used to highlight repeated values in a range.
Steps
- Select the Due Date range D2:D6.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose Duplicate or Unique, select a format, and click OK.
Output:
The date 28-09-2026 is highlighted twice because it appears in both D3 and D5.
Explanation:
In the above example, we select the due date range. After that, we apply the Duplicate Values rule. Finally, Excel highlights the cells that contain a repeated value.
Top/Bottom Rules
Top/bottom rules are used to highlight the highest or lowest values in a range. They are useful when we need to find the best and worst performers.
The available options are:
- Top 10 Items
- Top 10%
- Bottom 10 Items
- Bottom 10%
- Above Average
- Below Average
Example
To highlight the top two sales values, select C2:C6 and go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items. Change the number from 10 to 2 and click OK.
Output:
The values 7000 and 6000 are highlighted.
Explanation:
In the above example, we apply the Top 10 Items rule on the Sales range. After that, we change the number to 2. Finally, Excel highlights the two largest values in the range.
Data Bars
Data Bars add a horizontal bar inside each cell. A longer bar represents a larger value, so we can compare values without reading each number.
Steps
- Select the Sales range C2:C6.
- Go to Home > Conditional Formatting > Data Bars.
- Choose a Gradient Fill or Solid Fill style.
Output:
Each cell in the Sales column shows a bar. The cell with 7000 has the longest bar, and the cell with 3000 has the shortest bar.
Explanation:
In the above example, we select the Sales range. After that, we apply a Data Bar style. Finally, Excel draws a bar in each cell, with its length based on the cell value compared with the other values.
Color Scales
Color scales apply a gradient of two or three colors to a range. The color of each cell depends on where its value falls between the lowest and highest values.
Steps
- Select the Sales range C2:C6.
- Go to Home > Conditional Formatting > Color Scales.
- Choose a scale, such as Green - Yellow - Red Color Scale.
Output:
The highest value is shown in green, the middle values in yellow, and the lowest value in red.
Explanation:
In the above example, we apply a three-color scale on the Sales range. After that, Excel compares each value with the minimum, midpoint, and maximum. Finally, it fills each cell with a color that shows its position in the range.
Icon Sets
Icon Sets add small icons, such as arrows, traffic lights, flags, or stars, next to the cell value. They are useful when we need to classify values into levels like high, medium, and low.
Steps
- Select the sales range C2:C6.
- Go to Home > Conditional Formatting > Icon Sets.
- Choose a set, such as 3 traffic lights.
Output:
The higher values show a green icon, the middle values show a yellow icon, and the lower values show a red icon.
Explanation:
In the above example, we apply the 3 Traffic Lights icon set on the Sales range. After that, Excel divides the values into three groups. Finally, it adds a different icon to each cell based on the group it belongs to.
Conditional Formatting Using Formulas
The New Rule option with a formula gives us full control. It is useful when we need to format a whole row based on the value of one cell, or when the condition is more complex than the built-in rules.
Syntax
The rule uses a formula that returns TRUE or FALSE. Excel applies the format when the result is TRUE.
Steps
- Select the data range A2:D6.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter the formula and choose a format.
- Click OK.
Example 1: Highlight an Entire Row
=$B2="HR"
Output:
The complete rows of Neha and Karan are highlighted.
Explanation:
In the above example, we use a formula that checks the Department column. After that, we use the dollar sign before the column letter so that the column stays fixed while the row changes. Finally, Excel highlights every full row where the department is HR.
Example 2: Highlight Overdue Dates
=$D2<TODAY()
Output:
The rows with the due date 28-09-2026 are highlighted, because those dates are before today's date, 01-10-2026.
Explanation:
In the above example, we use the TODAY function to compare each due date with the current date. After that, the formula checks whether the due date is earlier than today. Finally, Excel highlights the rows that are overdue.
Example 3: Highlight Using Two Conditions
=AND($B2="Sales",$C2>5000)
Output:
The row of Rahul is highlighted.
Explanation:
In the above example, we use the AND function to check two conditions. After that, Excel tests whether the department is Sales and whether the sales value is greater than 5000. Finally, it highlights only the rows where both conditions are true.
Highlighting Blank Cells and Errors
Conditional formatting can also be used to find missing or incorrect data. We can use the New Rule option with the following formulas.
| Purpose | Formula |
|---|---|
| Highlight blank cells | =ISBLANK(C2) |
| Highlight error values | =ISERROR(C2) |
| Highlight text values in a number column | =ISTEXT(C2) |
Explanation:
These formulas return TRUE when the cell is blank, contains an error, or contains text. Excel then applies the chosen format to those cells, which helps us find data problems quickly before the analysis.
Managing and Clearing Rules
Manage Rules
To edit, delete, or change the order of rules, go to Home > Conditional Formatting > Manage Rules. In the dialog box, we can choose to show the rules for the current selection or for the entire worksheet. If more than one rule applies to the same cell, the rule at the top has higher priority. The Stop If True option prevents lower rules from being applied.
Clear Rules
To remove conditional formatting, select the range, then go to Home > Conditional Formatting > Clear Rules. We can clear the rules from the selected cells or from the entire sheet.