Concept
1. Formula rules — the most flexible
Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Highlight a whole row where achievement < 90%:
=$E5<0.9
applied to $A$5:$F$52. The $ before E locks the column so every cell in the row checks E.
- Highlight the selected region (from a dropdown):
=$A5=inp_Region
- Top 3 values:
=E5>=LARGE($E$5:$E$52,3).
2. Icon sets with real thresholds
Default icon sets split by percent of the range — meaningless for KPIs. Edit the rule:
- Type: Number (not Percent).
- Green ✓ when value >= 1; Amber ! when >= 0.9; Red ✗ otherwise.
- Tick Show Icon Only if the number is in another column.
- Reverse Icon Order for "lower is better" KPIs.
3. Custom number formats — colour and arrows without rules
Format Cells → Number → Custom:
[Color10]▲ 0.0%;[Red]▼ 0.0%;0.0%
Positive: green ▲ 11.0% · Negative: red ▼ 3.4% (the minus sign is replaced by ▼) · Zero: 0.0%. Works in pivots too and survives refresh.
Lakh/crore warning: Excel's comma scaling (#,##0, and #,##0,,) divides by thousands/millions, never by lakh or crore — formats built that way show wrong numbers. For lakh/crore display, calculate a helper value (=x/10^5 or =x/10^7) and format it as 0.0" L" or 0.00" cr".
4. Heatmaps
Region × Month table → Conditional Formatting → Color Scales → white-to-accent (2-colour). Better than 3-colour red-yellow-green for magnitude (red would mean "bad" when it's just "small"). Set Minimum = Number 0 if zero is meaningful.
Capstone heatmap (₹ lakh, FY2025-26): the darkest cells are North in October and November (~119 each); East in July is the lightest (~29.3).
5. Managing rules
Home → Conditional Formatting → Manage Rules → Show formatting rules for: This Worksheet.
- Rules are applied top to bottom; use Stop If True so a red row isn't also painted amber.
- Check Applies to ranges — copying cells often creates duplicate fragmented rules (A5:F5, A6:F6…). Delete duplicates and set one range.
- Too many rules on large ranges slows the workbook.
6. Dashboard rules of thumb
- Highlight exceptions, not everything.
- One highlighting method per column (icon or colour or bar).
- Keep the same meaning everywhere: green = good, red = bad, accent = selected.
Common mistakes
Default percent-based icon thresholds. Relative/absolute $ mistakes in formula rules (only the first row works). Dozens of fragmented duplicate rules. Comma-scaling formats used for lakh/crore.