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 |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Sana Sheikh) are made up for this lesson on data bars color scales and icon sets.
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 |
About this data: Demo table only — not real customer or company data. Built to practise data bars color scales and icon sets.
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.
Ravindra Bagale's Tip – मराठी
Icon sets मध्ये default thresholds Percent (67%, 33%) असतात – ते target चे नाही, range चे percent असतात. बरेच students हे न बदलता report देतात आणि 95% achievement ला red light येते. Edit Rule मध्ये Type = Number करून खरे thresholds टाका.
Ravindra Bagale's Tip – हिंदी
Icon sets में default thresholds Percent (67%, 33%) होते हैं – ये target का नहीं, range का percent होता है. बहुत से students इसे बदले बिना report दे देते हैं और 95% achievement पर red light आ जाती है. Edit Rule में Type = Number करके असली 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.
Loading practice tasks...
- Total clicks (Easy): Add up the "Clicks" column.
- Average spend (Easy): Average of the Spend (€) column, rounded to 2 decimals.
- Rows in Google (Easy): How many rows have Platform "Google"?
- Largest clicks (Easy): The largest value in the "Clicks" column.
- Clicks for Google (Medium): Total of the Clicks column where Platform is "Google".
- Google in Paris (Medium): Total of the Clicks column where Platform is "Google" and City is "Paris".
- Best in Google (Medium): Largest value in the "Spend (€)" column where "Platform" is "Google".
- Value of row 2 (Medium): Multiply the first-row Clicks by the first-row Spend (€).
- Look up Sneha Patil (Medium): The Clicks for "Sneha Patil" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Clicks" times "Spend (€)" for all rows.
- Share of Google (Hard): Share of the "Clicks" column that is "Google", rounded to 3 decimals.
- Total "Clicks" for YouTube (Hard): Add up the "Clicks" column for the rows where "Platform" is "YouTube".
- Above average? (Hard): "Above" if the "Spend (€)" value in the first row is above the average of the "Spend (€)" column, else "Below".
Worked example — data bars color scales and icon sets
Why
Rohan Patil in Pune and Sophie Martin abroad both need a clear, repeatable way to use data bars color scales and icon sets on a FreshGo Mart sheet without breaking totals.
What
Data Bars Color Scales And Icon Sets is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for data bars color scales and icon sets.
- Enter a small made-up value for Rohan Patil using $ where money appears.
- Add a second value for Sophie Martin using € so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once data bars color scales and icon sets is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
