Conditional formatting is one of the most visual features of Excel. When I started in data analysis, I was immediately fascinated by how a simple color rule could highlight a trend or a critical point in a table. Today, I invite you to go further by exploring three advanced types of formatting: icon sets, data bars, and color scales. Each of these will allow you to transform a grid of numbers into a real dashboard, and draw your audience’s attention to the essentials.
Why use advanced conditional formatting ?
When analyzing time series, KPIs, or financial indicators, one often encounters a block of numbers that is hard to read. Advanced conditional formatting:
- Improves readability: each value gains a “visual weight” depending on whether it is low, medium, or high.
- Guides analysis: your stakeholders immediately spot anomalies or performance areas.
- Saves time: no need to sort or filter, the eye goes where it matters.
1. Icon sets
Icon sets associate an image with each range of values. For example, a traffic light 🟢🟡🔴 or arrows 📈📉 to indicate an increase or decrease. Here is how to proceed:
Creation steps
- Select your cells and open the Home tab / Conditional Formatting / Icon Sets.
- Choose an icon style (3, 4, or 5 symbols).
- Customize thresholds in Percentage, Value, or Formula mode. For dynamic rules, you can use the IF function or more complex formulas.
- Uncheck “Show Icon Only” if you want to keep the number next to the symbol.
Tip: combine an icon set with a color rule for a “double alert” effect. For example, an orange triangle on a red background for critical values.
2. Data bars
Data bars turn each cell into a mini chart. A glance is enough to compare amounts, without using a separate chart.
Data bar settings
- Home menu → Conditional Formatting → Data Bars.
- Select a solid or gradient style.
- For a fairer comparison, set a custom minimum and maximum (fixed values, percentile, or formula).
- If your range includes negative values, choose a style that handles positive and negative bars distinctly.
Imagine a monthly sales table: you can display each month’s share relative to the year, or relative to a target. To go further, a formula based on SUMIFS allows you to calculate a dynamic threshold and integrate it into your data bar rule.
3. Color scales
With color scales, Excel applies a gradient between several colors according to the defined scale (2 or 3 colors). It is ideal for spotting extreme values or gaps.
Choosing a scale
| Type of scale | Recommended use |
|---|---|
| 2 colors | Compare high/low, on a binary axis (profit/loss). |
| 3 colors | Identify averages and extremes (hot/cold). |
In practice:
- Go to Conditional Formatting → Color Scales.
- Define the start, middle, and end points (values, percentages, or formulas).
- Consider perception: a gradient that is too contrasting can tire the eye, while one that is too soft may go unnoticed.
4. Rules based on a formula
Beyond predefined templates, you can write your own rules using an Excel formula. For example, to color in green all rows where the turnover exceeds the average of the column:
=B2 > MOYENNE(B:B)The VLOOKUP function can also be used, for example, to apply a format if a product code appears in a “VIP” list.
5. Best practices and pitfalls to avoid
- Limit the number of rules to avoid overloading the table.
- Document your criteria: add a comment or a legend to explain your threshold choices.
- Check compatibility if the file is shared (Excel Web or mobile does not support all options).
- Test on real data to avoid side effects (color jumps, inverted bars).
Conclusion
Icons, data bars, and color scales are all tools to make your Excel tables more expressive. By mastering the settings and, if necessary, using formulas like SUMIFS or VLOOKUP, you provide a visual navigation that guides analysis and enhances your data storytelling.
FAQ
How to adapt a set of icons to negative and positive values?
Choose an icon style dedicated to bipolar values or create two separate rules: one for positive values, another for negative ones.
Can I use multiple types of formatting on the same range?
Yes, but pay attention to the order of rule application. Excel executes rules from top to bottom. Enable “Stop If True” if necessary.
How to copy a conditional rule to another workbook?
Use the format painter (Home → Format Painter) or save your rule in a custom style via the rule manager.
Which formats are supported in Excel Online?
Color scales and data bars generally work, but some advanced icon sets may not display correctly.
Can conditional formats be exported to Power BI?
Not directly. Power BI has its own visuals and conditional formatting rules that must be recreated manually.
Article written by a data analysis enthusiast – thank you for reading! 😊
