# Getting Started
What is Conditional Formatting?
Conditional formatting in Excel is a feature that allows you to automatically apply formatting—like colors, icons, or data bars—to cells based on their values. This makes it easier to quickly identify trends, patterns, or outliers in your data.
You can highlight all the sales figures above a certain number, flag overdue tasks, or even color-code data trends.
If you’re analyzing monthly sales data, for example, you can instantly see which months are performing well against your targets.
Example of conditional formatting
How it works: The Basics of Conditional Formatting in Excel
First select the cell you want to format. Then, go to the home tab of the ribbon and click on “Conditional Formatting”.
Location of conditional formatting
A drop-down menu will appear where you can choose from various rule types (styles of conditional formatting), such as highlighting cells that contain specific text, values above or below a certain threshold, duplicate values, etc.
Once you’ve selected a rule type, you can then define the specific criteria for your rule. When you’re done, click OK, and the formatting will be applied to the cells that meet your criteria.
Conditional Formatting Styles in Excel
There are a range of conditional formatting styles in this section:
- Highlight cells rules
- Top/bottom rules
- Color scales
- Data bars
- Icon sets
Highlight cells rules
The highlight cell style is highly versatile, allowing you to apply formatting to a variety of different data types.
You can emphasize specific cells based on criteria you set, such as values greater than a certain number, text that contains specific keywords, or dates within a particular range.
Highlight cell rules
Top/Bottom rules
This style automatically identifies the top or bottom values in a range of data. While 10 is the default value, you can adjust this to show any number of the top or bottom data points.
Top/Bottom rules
In the example below, we use the Top 10 and Bottom 10 styles to show just the top 3 and bottom 3 salespersons in our data.
Example of applying the top and bottom 10 style
Color Scales
The color scales style allows you to format a table of data as a heatmap. You can highlight the range of data points from lowest to highest using the color gradient of your choice.
Color Scales
The style is very useful for quickly making sense of a table that contains a lot of numbers.
In the example below, we show each salesperson’s monthly sales. Without the color scale formatting applied, this would look like a confusing sea of numbers, and it would take more than a few minutes to figure out what’s important.
Instead, applying the color scale makes it much easier to identify the highest and lowest performing salesperson and month.
Example of applying color scales
Data Bars
This style adds horizontal bars within cells to visually represent the relative value of each cell, making it easy to compare data at a glance.
This style has quite a few options for changing how it looks and behaves. You can customize colors and choose whether to display the bar only or include the cell’s numerical value.
Data Bars
In the example below, we add data bars instead of the sales values. These bars make it quick and easy to identify the highest and lowest performing months.
Example of applying Data Bars
Icon Sets
Lastly, there is the icon set style. These are similar to the data bars in that you have quite a few customization options, such as whether to display the icon only or both the icon and value.
Icon sets
Formula-Driven Conditional Formatting
If your conditions are straightforward, like highlighting cells greater than a specific value or finding duplicates, predefined rules are quick and easy.
However, when your conditions depend on changing values or more complex logic, formulas provide a lot more flexibility. If you need to combine multiple conditions or custom rules, such as highlighting cells that are above average and also below a certain threshold, formulas can handle this complexity.
Simple example: highlight rows based on sales targets
Let’s say you have a list of regions in column B with their associated sales values in column C and a sales target in cell $C$7.
We want to highlight any sales figure above or below this target. To do this, we’ll need to apply two rules.
First, select the cell in column C to apply the rule.
In the conditional formatting drop-down menu, select “Greater than” and enter the cell that contains the target ($C$7). This formula checks if the value in each cell of column C is greater than the value in cell $C$7.
Simple example of formula-driven conditional formatting
Choose the green color format to apply to the cell that meets the condition. Then, repeat this process with the “Less than” formatting option in the conditional formatting drop-down menu and apply the blue color format.
we can quickly see which regions hit their target and which did not.
Formula driven conditional formatting
Whenever you change the sales target in cell $C$7, the conditional formatting will automatically update to reflect the new target. This makes your formatting dynamic and responsive to change in your data without the need to adjust the rule manually each time.
Advanced example: highlight cells based on average sales
Suppose we have a table showing the total sales by salesperson, and we want to identify the top performers based on the average for the group.
Our data is structured as follows:
- Column A: Salesperson
- Column B: Sales value
First, select the first cell in the range to which you want to apply the formatting. In this example, it is C3.
Select “New Rule” from the conditional formatting dropdown menu. Then, select the rule type “Use a formula to determine which cells to format”.
Here, we must enter a formula that compares each cell to the average of the sales data.
The formula should be =C2<AVERAGE($C$2:$C$10).
Advanced example of formula-driven conditional formatting
Then, choose how you would like the cells that meet the criteria to be formatted, such as a fill color or font color. Click OK to apply the rule.
Lastly, copy the formatting from this first cell and paste it onto each subsequent cell in the range. This will automatically adjust the relative cell reference in our formula so that the correct cell is used for each calculation.
Below is the result. We can see that three out of eight salespeople performed above the average.
Advance formula driven conditional formatting
How to use Conditional Formatting as a Data Analysis Tool
Using conditional formatting in Excel helps you quickly spot important information without needing advanced analysis skills, making it easier to understand and communicate your data insights.
This tool simplifies data interpretation, saving you time and effort in identifying critical areas that need attention.
Conditional formatting as data analysis tool
Conditional Formatting Best Practices
Start simple
When you’re new to conditional formatting, start with basic rules before experimenting with more complex formulas and conditions.
Begin by applying simple highlight rules, such as formatting cells that are greater than a specific value or highlighting duplicates. This approach helps you understand the impact of conditional formatting on your data.
Use consistent colors
Using consistent colors across your conditional formatting rules keeps your spreadsheets clear and consistent.
For example, use green to indicate positive performance, red for negative, and yellow for warnings or alerts.
This consistency in color schemes makes it easier for you and others to interpret the data quickly, reducing confusion and ambiguity later on.
Avoid overuse
While conditional formatting is a powerful tool, overusing it can make your data harder to read and understand. Applying too many rules or overly complex formatting can make your spreadsheet look cluttered, which will only lead to confusion.
Use dynamic named ranges
Dynamic named ranges are a great way to ensure your conditional formatting rules adapt to changes in your data. A dynamic named range automatically adjusts its size as data is added or removed, ensuring your formatting remains relevant as things change.
The easiest way to create a dynamic range is to format your data as a table. This way, your formulas and conditional formatting rules will use table and column names instead of referring to their column or row letters and numbers.
Conclusion
Conditional formatting is a great tool for creating rules that highlight the most important data in your spreadsheets.
It’s really easy to get started with conditional formatting in Excel, and there are many pre-built formatting styles to choose from, such as highlighting cells, using color scales, adding data bars, and more.