Concept
1. Sparklines
A sparkline is a mini chart in one cell. Example table (region × 12 months of FY2025-26 in B:M):
| Region | Apr | … | Mar | Trend |
|---|---|---|---|---|
| North | … | … | … | (sparkline) |
Select N2:N5 → Insert → Sparklines → Line → Data Range B2:M5 → OK. One sparkline per row appears.
Types: Line (trend), Column (compare periods), Win/Loss (positive vs negative, e.g. monthly profit vs loss or above/below target).
2. Make them useful
Sparkline tab:
- Show → High Point, Low Point, Last Point markers — the peak (Oct/Nov) jumps out.
- Axis → Same for All Sparklines (vertical min and max). By default each row is scaled to itself, so a small region's line looks as dramatic as a big one's. Same axis = honest comparison.
- Make the row taller and the column wider for readability.
- Sparklines move and copy with cells; they print well.
3. Data bars (conditional formatting)
Select a number column → Home → Conditional Formatting → Data Bars. Each cell gets a bar proportional to its value — a bar chart inside the table.
Fine-tune: Manage Rules → Edit:
- Show Bar Only if a separate column already shows the number.
- Minimum Number 0 (not "Automatic") so bars start from zero — otherwise the smallest value shows almost no bar, exaggerating differences.
- Solid fill reads better than gradient.
- Negative values: set the axis position and a red negative colour.
4. In-cell bars with REPT (works everywhere)
=REPT("█", ROUND(B2 / MAX($B$2:$B$9) * 20, 0))
Draws up to 20 blocks proportional to B2. Works in Google Sheets and old Excel, can be combined with text (&" "&TEXT(B2,"#,##0")), and you control the font colour.
5. Icon sets and colour scales — use sparingly
Icon sets (▲▼, traffic lights) are good for status (RAG, Module 7). Colour scales are good for heat maps (region × month). Don't stack data bars + icons + colour scales on the same column.
6. A compact report pattern
| Store | FY Revenue | Bar | YoY % | 12-month trend |
|---|---|---|---|---|
| Karol Bagh | 4,93,63,475 | ██████████ | … | ∿ |
Rows sorted by revenue, data bars starting at zero, YoY with a red/green number format, sparklines with high/low points and same axis.
Common mistakes
Sparklines each on their own scale when comparing rows. Data bars with automatic minimum (exaggerates). Using colour as the only signal (colour-blind readers — add a symbol or number too).