Conditional Formatting
By the Syncfusion DevTutorials team · Last updated
Highlight values greater or less than a limit, mark duplicates, and add data bars, color scales, and icon sets. Then build a sales dashboard in the Chapter 7 project.
In a long list of numbers, the important ones can be hard to spot. Conditional formatting colors cells automatically based on their values: overdue amounts turn red, top scores turn green, and repeated entries stand out. When the numbers change, the colors update by themselves. In this lesson, you'll highlight values greater or less than a limit, mark duplicates, and add data bars, color scales, and icon sets. At the end, you'll build a sales dashboard in the Chapter 7 project.
Open the Excel sandbox and follow along. All of these options are under Conditional Formatting on the Home tab.
Conditional formatting is live. Unlike a fill color you apply by hand, it checks the rule again every time a value changes, so the right cells stay highlighted.
Greater Than
- Select the numbers, such as B2:B20.
- Choose Conditional Formatting > Highlight Cells Rules > Greater Than.
- Type the limit, such as
1000, and choose a format, like Green Fill with Dark Green Text. - Click OK.
Every cell over 1000 turns green.
Less Than
Use the same steps for Less Than. For example, highlight stock levels below 10 in red so you know what to reorder.
Other rules in the same menu include Between, Equal To, and Text that Contains.
Duplicate Values
Highlight Cells Rules > Duplicate Values colors every value that appears more than once. It's a quick way to catch the same invoice number or email address entered twice.
Data Bars
Data Bars draw a bar inside each cell. Bigger values get longer bars, so the table looks like a mini bar chart.
Choose Conditional Formatting > Data Bars and pick a color.
Color Scales
Color Scales shade each cell with a color gradient, such as green for the highest values, yellow for the middle, and red for the lowest. This is useful for spotting patterns across a large table, like a heat map of monthly temperatures.
Choose Conditional Formatting > Color Scales and pick a scale.
Icon Sets
Icon Sets put a small icon in each cell based on its value: up, sideways, and down arrows, traffic lights, or stars. They are useful for showing performance at a glance.
Choose Conditional Formatting > Icon Sets and pick a set.
Icon sets and color scales compare each cell with the others in the range, so the same value can get a different color in a different range. Apply them to the whole column of similar values at once.
Managing and clearing rules
- Clear Rules > Clear Rules from Selected Cells removes rules from the selection; Clear Rules from Entire Sheet removes them all.
- Manage Rules lists every rule so you can edit, reorder, or delete it.
Use one or two kinds of conditional formatting per table. Data bars, color scales, and icons on the same cells can make the table harder to read, not easier.
Common mistakes
| Mistake | What happens | How to fix it |
|---|---|---|
| Rule applied to one cell | Only that cell is highlighted | Select the whole range, then apply the rule |
| Headings included in the range | Headings get colored or skew data bars | Select the numbers only |
| Numbers stored as text | Greater Than rules do not trigger | Convert them to real numbers |
| Too many rules on the same cells | Colors conflict and confuse | Clear rules and keep one or two |
| Expecting manual fill to update | Old highlights stay on changed values | Use conditional formatting instead |
Exercise
Click Reset in the sandbox header. Type in A1:B7:
Product,StockPens,150Paper,8Folders,45Pens,60Tape,5Staples,90
Then do each task and press Check.
Chapter project
Put Chapter 7 together. You'll turn a year of sales figures into a small dashboard with a chart and conditional formatting.
Bookshop sales dashboard
What to remember
- Conditional formatting colors cells automatically, and updates when values change.
- Highlight Cells Rules: Greater Than, Less Than, Between, Duplicate Values, and more.
- Data Bars draw bars in cells; Color Scales shade a gradient; Icon Sets add arrows, lights, or stars.
- Select the data only, not the headings.
- Clear Rules removes rules from a selection or the whole sheet.
That's the end of Chapter 7. In Chapter 8, you'll set up pages, print, and share your workbooks.