Adding color to cells in Excel based on the cell value is a useful way to visualize data and make important values stand out. Conditional formatting is the feature in Excel that allows you to automatically apply formatting like color fills to cells based on certain rules or criteria.
In this guide, I’ll walk through the steps to add color scales, data bars, and icon sets to cells based on their values. I’ll also show you how to create custom rules to color cells based on specific text, dates, blanks, duplicates, and more. Let’s dive in!
Color Scale Conditional Formatting
A color scale allows you to set a gradient of two or more colors that correspond to lower, middle, and higher values in your data range. This makes it easy to visualize values on a relative scale.
To add a color scale:
1. Select the cells you want to format.
2. On the Home tab, click Conditional Formatting > Color Scales > choose the color scale you want.
3. In the Conditional Formatting Rules Manager, you can modify the rules, minimum/maximum values, and color ranges for your scale.
For example, here’s a color scale applied to expense amounts:
| Expense Type | Amount |
|---|---|
| Lodging | $450 |
| Travel | $150 |
| Meals | $75 |
| Entertainment | $200 |
The color scale makes it easy to see that Lodging was the highest expense, while Meals was the lowest.
Data Bars Conditional Formatting
Data bars provide a visual representation of cell values in the form of horizontal bars. The length of the data bar corresponds to the value amount.
To add data bars:
1. Select the cells to format.
2. On the Home tab, click Conditional Formatting > Data Bars > choose a fill option.
You can control the data bar minimum and maximum values under Conditional Formatting Rules.
Here are data bars applied to monthly sales figures:
| Month | Sales |
|---|---|
| January | $30,000 |
| February | $20,000 |
| March | $65,000 |
| April | $40,000 |
The length of the bars allows us to quickly see that March had the highest sales.
Icon Sets Conditional Formatting
Icon sets display an icon in the cell based on the cell value. This provides a quick visual indicator of high, medium, and low values.
To add icon sets:
1. Select the cells to format.
2. On the Home tab, click Conditional Formatting > Icon Sets > choose an icon style.
You can customize the icons, values, and colors in the Conditional Formatting Rules Manager.
Here icons are applied to product inventory levels:
| Product | Units in Stock |
|---|---|
| Product A | 500 |
| Product B | 100 |
| Product C | 50 |
| Product D | 200 |
The icons make it easy to see at a glance which products have low, medium, or high inventory levels.
Custom Conditional Formatting Rules
In addition to the built-in options, you can create custom conditional formatting rules based on formulas, specific text, dates, blanks, duplicates, and more.
To add a custom rule:
1. Select the cells to format.
2. On the Home tab, click Conditional Formatting > New Rule.
3. In the New Formatting Rule dialog box, define your rule type, format, and formula if applicable.
4. Click OK to apply the rule.
For example, let’s highlight expense amounts over $100 in red font:
1. Select the expense amount cells.
2. New Rule > Format only cells that contain.
3. Specify Cell Value > Greater than > 100.
4. Set format to red font.
5. OK to apply.
Now amounts over $100 display in red:
| Expense | Amount |
|---|---|
| Dinner | $50 |
| Hotel stay | $225 |
| Transportation | $75 |
The custom rule precisely targets just the cells you want to format.
Duplicate Values Rule
To highlight duplicate entries, you can apply a custom rule to flag duplicates with color:
1. Select the data range.
2. New Rule > Format only unique or duplicate values.
3. Check Duplicate for the values to format.
4. Set format, such as light red fill.
5. OK to apply.
This will color all duplicates in the selection:
| Product | Sales Rep |
|---|---|
| Table | John |
| Chair | Mary |
| Table | Chris |
| Desk | Chris |
Now it’s easy to see there are two sales reps assigned to Table.
Top and Bottom Rules
To highlight the top or bottom values like top 10, bottom 5, etc., use a Top/Bottom rule:
1. Select the data range.
2. New Rule > Format only top or bottom ranked values.
3. Set the number of top/bottom items and the format.
4. OK to apply.
For example, to highlight the top 3 sales amounts in green:
| Salesperson | Sales |
|---|---|
| Jeremy | $20,000 |
| Nancy | $50,000 |
| Mark | $35,000 |
| Chris | $60,000 |
| Sarah | $65,000 |
The top values stand out visually while preserving the actual numbers.
Data Validation Dropdowns
For selecting from preset options, you can create data validation dropdown lists tied to conditional formatting:
1. Set up data validation with a list of allowed entries.
2. Apply conditional formatting rules based on the data validation cell value.
For example, create a dropdown for “Poor”, “Average”, and “Excellent”:
| Student | Grade | Performance |
|---|---|---|
| John | 82 | Average |
| Jane | 74 | Poor |
| Joe | 95 | Excellent |
Then apply red fill for “Poor”, yellow fill for “Average”, green fill for “Excellent”.
The dropdown selection will determine the color applied.
Working with Rules
– Click Conditional Formatting > Manage Rules to see and edit all rules on a sheet.
– To remove a rule, go to Manage Rules and delete it.
– Use the Stop If True option to have lower priority rules get overridden by higher ones.
– Ctrl+click to select multiple ranges, then create one rule for all.
– Use the Applies to box in New Formatting Rule to choose a different range for existing rules.
Conclusion
Conditional formatting in Excel provides a very useful way to visualize values, spots trends and outliers, and highlight important data at a glance.
The built-in options like color scales, data bars, and icons create relative comparisons, while custom rules give you pinpoint control.
Leverage conditional formatting to better understand your Excel data in dashboards, reports, and data analyses. Just remember it doesn’t change the underlying data, only the presentation.
Additional Examples
Here are a few more examples of conditional formatting rules you can use:
Color code text values
Use custom rules to color cells based on specific text values, like coloring pass/fail values:
| Test | Result |
|---|---|
| 1 | Pass |
| 2 | Fail |
| 3 | Pass |
| 4 | Fail |
Highlight weekends
Add a custom rule to color cells containing weekend dates:
| Date | Sales |
|---|---|
| Saturday, Jan 5 | $1000 |
| Tuesday, Jan 8 | $500 |
| Sunday, Jan 13 | $800 |
Flag empty cells
Make empty cells obvious by filling blank cells yellow:
| Name | Department |
|---|---|
| John | Engineering |
| Sarah | Marketing |
Color code regions
Use region highlight colors like red, yellow, green to visualize data:
| Month | Sales |
|---|---|
| January | $10,000 |
| February | $7,000 |
| March | $5,000 |
So in summary, conditional formatting gives you a powerful way to visualize Excel data at a glance. The key is choosing rules and colors that bring out the insights in your particular data.