2.7 Data Bars, Color Scales and Icon Sets

By Ravindra Bagale Last updated: 25 September 2026

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

In icon sets, the default thresholds are Percent (67%, 33%) – a percent of the range, not of the target. Many students hand in the report without changing this, and a 95% achievement gets a red light. In Edit Rule, set Type = Number and enter the real thresholds.

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.