2.7 Data Bars, Color Scales and Icon Sets
| Type | Shows | Good for |
|---|---|---|
| Data Bars | A bar inside the cell, length ∝ value | Sales by store |
| Color Scales | 2- or 3-colour gradient | Heat map of orders by hour × city |
| Icon Sets | Arrows, traffic lights, ratings | Target achieved / near / missed |
Steps in Excel
- Select store sales › Home › Styles › Conditional Formatting › Data Bars › choose Solid Fill green.
- Select an hour × city grid › Color Scales › Green – Yellow – Red (or reverse for "low is good").
- Select Achievement % › Icon Sets › 3 Traffic Lights.
- Customise thresholds: Manage Rules › Edit Rule › set Type to Number instead of Percent, e.g. green when ≥ 1 (100%), yellow when ≥ 0.9, else red. Tick Show Icon Only if you want no numbers.
Worked example – target tracker.
| Store | Target | Actual | Achievement | Icon (rule) |
|---|---|---|---|---|
| Pune – Wakad | ₹2,50,000 | ₹2,71,300 | 109% | green (≥ 100%) |
| Kolhapur – Tarabai Park | ₹1,50,000 | ₹1,41,600 | 94% | yellow (≥ 90%) |
| Solapur – Murarji Peth | ₹1,20,000 | ₹96,000 | 80% | red |
Ravindra Bagale's Tip
Icon sets madhe default thresholds Percent (67%, 33%) aastat – te target cha nahi, range cha percent asto. Khup students he na badlata report detat aani 95% achievement la red light yete. Edit Rule madhe Type = Number karun khare thresholds taka.
Practice task
Build the target tracker for five stores. Apply data bars to Actual and 3 traffic lights to Achievement with number thresholds 1 and 0.9.