Excel Conditional Formatting Formula Examples for Excel Dashboards

by

Introduction

Conditional formatting is one of the most powerful visual tools in Excel. Unlike static colors or font styles, it reacts to the data it evaluates. By combining conditional formatting with custom formulas you can highlight trends, flag outliers, and turn raw numbers into an instant‑read dashboard.

How to Create Conditional Formatting with Formulas

  1. Select the cells, rows, or table you want to format.
  2. Go to Home ▸ Conditional Formatting ▸ New Rule.
  3. Choose Use a formula to determine which cells to format.
  4. Enter your formula in the box. The formula must return TRUE for cells that need formatting.
  5. Click Format… and set the font, fill, border, or icon style you prefer.
  6. Press OK to apply the rule.

Tip: When applying the rule to an entire row, lock the column reference (e.g., $A2) so Excel evaluates the same column for each row.

For You:

Boost Profits with Activity-Based Costing

Discover hidden costs and optimize profitability

Learn More

Common Formula Patterns

1. Simple comparisons

  • =A2>100 – highlight values greater than 100.
  • =A2="Completed" – format cells that equal the text “Completed”.

2. AND / OR logic

  • =AND($B2>0,$C2="Yes") – format the row only when both conditions are true.
  • =OR($D2<50,$E2="Low") – format when either condition is met.

3. ISBLANK and NOT

  • =ISBLANK($F2) – highlight empty cells.
  • =NOT(ISBLANK($F2)) – highlight cells that contain data.

4. Text functions

  • =SEARCH("urgent",$G2) – flag any cell containing the word “urgent”.
  • =LEFT($H2,3)="NY" – format rows where the first three characters are “NY”.

5. Date comparisons

  • =TODAY()-$I2>30 – highlight dates older than 30 days.
  • =MONTH($J2)=12 – format cells that fall in December.

Industry‑Specific Examples

Financial reporting

  • Negative profit margin: =$K2<0 – red fill for loss rows.
  • Variance > 20%: =ABS($L2-$M2)/$M2>0.2 – orange fill for high variance.

Sales performance

  • Quota attainment: =$N2>=$O2 – green fill when actual sales meet or exceed quota.
  • Stagnant growth: =AND($P2>0,$P2<$Q2) – yellow fill for low month‑over‑month growth.

Human resources

  • Upcoming hire dates: =AND($R2=TODAY()) – light‑blue fill for hires within the next 30 days.
  • Incomplete onboarding: =ISBLANK($S2) – bold red text for missing onboarding dates.

Quick Implementation Checklist

Step What to Do Tip
1 Identify the key KPI or metric to highlight. Use a separate column for the calculation if needed.
2 Write a formula that returns TRUE/FALSE. Test the formula in a regular cell first.
3 Apply conditional formatting using the formula. Lock column references for row‑wide rules.
4 Choose a clear visual style (color, icon set, data bar). Maintain accessibility – avoid red‑only cues.
5 Document the rule for future reviewers. Add a comment in the worksheet.

Next Steps – Ready‑Made Templates

Speed up your dashboard build with pre‑designed Excel templates that already include conditional‑formatting rules:

Each template is fully editable, so you can swap in your own formulas and branding.

Conclusion

Conditional formatting formulas turn static data into dynamic insights. By mastering simple operators like AND, OR, ISBLANK, and date functions, you can build dashboards that instantly draw attention to the information that matters most. For a hands‑on solution, download the Financial Dashboard Excel template and start visualising your key metrics today.

For You:

Download Excel & Financial Templates

Automated reports, dashboards, and financial planning tools

Learn More
Related: