MinAI - Về trang chủ
Hướng dẫn
5/13•30 phút
Đang tải...

Conditional Formatting

Tự động highlight dữ liệu với rules, icon sets, data bars và color scales

0

🎯 Mục tiêu bài học

TB5 min

Sau bài học này, bạn sẽ:

✅ Sử dụng Highlight Cell Rules cho values, duplicates, dates

✅ Tạo Data Bars và Color Scales cho heatmap analysis

✅ Áp dụng Icon Sets (traffic lights, arrows, stars) cho KPIs

✅ Viết Formula-based rules cho custom formatting

✅ Quản lý rule priority và Stop If True

Thời gian: 30 phút | Độ khó: Intermediate | Yêu cầu: Hoàn thành Bài 4 - Charts

1

📖 Bảng Thuật Ngữ Quan Trọng

TB5 min
Thuật ngữTiếng ViệtMô tả
Conditional FormattingĐịnh dạng có điều kiệnTự động đổi format dựa trên giá trị
Highlight Cell RulesQuy tắc tô sáng ôRules cho greater/less than, duplicates
Data BarsThanh dữ liệuThanh visual so sánh trong cell
Color ScalesThang màuGradient color từ thấp đến cao
Icon SetsBộ biểu tượngTraffic lights, arrows, stars
HeatmapBản đồ nhiệtBảng sử dụng color scale
Formula-Based RuleQuy tắc công thứcCustom rule dùng formula
Rule PriorityThứ tự ưu tiênRules trên được áp dụng trước
Stop If TrueDừng nếu đúngNgừng check rules tiếp khi match
Duplicate ValuesGiá trị trùng lặpTìm và highlight data bị trùng

Checkpoint

Conditional Formatting tự động đổi format. 5 loại chính: Highlight Rules, Data Bars, Color Scales, Icon Sets, Formula-based. Bạn biết khi nào dùng loại nào chưa?

2

💡 1. Conditional Formatting là gì?

TB5 min

Conditional Formatting tự động thay đổi format cells dựa trên giá trị hoặc điều kiện.

  • 🔴🟡🟢 Traffic lights cho KPIs
  • 📊 Data bars cho so sánh visual
  • 🎨 Color scales cho heatmaps
  • ⚡ Instant insights không cần charts

Conditional Formatting vs Charts: CF hiển thị trực tiếp trên data table — không cần tạo chart riêng. Lý tưởng cho dashboards compact và executive reports.

Checkpoint

Conditional Formatting tự động format cells dựa trên giá trị. Ưu điểm: insights ngay trên data mà không cần tạo chart riêng. Bạn đã dùng CF bao giờ chưa?

3

🔍 2. Highlight Cell Rules

TB5 min

Home → Conditional Formatting → Highlight Cells Rules

2.1 Greater Than / Less Than

fxHighlight Revenue > $50,000
Rule: Greater Than → 50000
Format: Green Fill + Dark Green Text
RegionRevenueHighlight
North65,000🟢 Green
South38,000
East52,000🟢 Green
West41,000

2.2 Between / Text Contains

Between → 30000 and 50000Warning zone → Yellow Fill
Text Contains → "Urgent"Urgent tasks → Red Fill

2.3 Duplicate Values

Duplicate Values → Red Fill
Unique Values → Green Fill

2.4 Date Rules

Yesterday → Redoverdue!
This Week → Yellowcoming up
Next Week → Greenplanned

Duplicate Values rule chỉ so sánh trong range được chọn. Nếu muốn tìm duplicates across 2 sheets → dùng COUNTIF formula-based rule thay thế.

Checkpoint

5 loại Highlight Rules: Greater/Less Than, Between, Text Contains, Duplicate Values, Date Rules. Thường dùng nhất: Greater Than cho revenue targets, Duplicates cho data cleaning. Bạn đã thử chưa?

4

📊 3. Data Bars & Top/Bottom Rules

TB5 min

3.1 Top/Bottom Rules

Top 10 Items → Green highlight
Bottom 5% → Red highlight
Above Average → Green
Below Average → Red

3.2 Data Bars

Visual bars inside cells — so sánh nhanh không cần chart.

Data Bars — Visual comparison trong cells

RegionPerformanceValue
North
95K		95K
East
75K		75K
South
60K		60K
West
45K		45K
Central
25K		25K

3.3 Customize Data Bars

  • Min/Max type: Number, Percent, Percentile
  • Negative Values: Red bars
  • Show bar only: Hide numbers, bar fills entire cell
fxBudget Data Bars
Budget Used: 80%│████████│ Green bar ✅
Budget Used: 105%│██████████│ Red bar - over budget! ❌

"Show bar only" tạo mini bar chart trong cell — rất hiệu quả cho dashboard. Kết hợp với cột giá trị bên cạnh để vừa có visual vừa có số chính xác.

Checkpoint

Top/Bottom Rules highlight performers. Data Bars tạo mini bar charts trong cells. Gradient vs Solid fill tùy style. Customize được min/max, negative values. Bạn đã thử show bar only chưa?

5

🎨 4. Color Scales & Heatmaps

TB5 min

4.1 2-Color Scale

25K38K45K62K78K
🔴🟠🟡🟢🟢

Low (Red) ←── High (Green)

4.2 3-Color Scale

4560758595
🔴🟠🟡🟢🟢

Low (Red) ←─ Mid (Yellow) ─→ High (Green)

4.3 Heatmap Table

Ứng dụng kinh điển — Revenue by Region × Month:

Heatmap — Color Scale 3 màu

🔴 Low
🟢 High
JanFebMarAprMay
North85K78K62K88K92K
South55K48K65K72K58K
East32K55K78K60K45K
West68K35K42K58K75K

💡 East yếu Q1 (32K), West yếu Feb (35K) — instant pattern recognition!

Insight tức thì: East yếu nhất Q1, West yếu Feb.

Heatmap là visualization mạnh nhất khi cần scan pattern trong bảng lớn. Một cái nhìn → thấy ngay vùng mạnh/yếu. Stakeholders rất thích format này!

Checkpoint

2-Color Scale cho đơn giản, 3-Color Scale cho chi tiết hơn. Heatmap = Color Scale áp dụng cho bảng 2 chiều → instant pattern recognition. Bạn đã tạo heatmap đầu tiên chưa?

6

🚦 5. Icon Sets

TB5 min

6.1 Traffic Lights

ThresholdIcon
≥67%🟢 Green
≥33%🟡 Yellow
<33%🔴 Red

6.2 Arrows & Stars

TypeConditionIcon
ArrowsTăng (≥5%)▲▲
ArrowsGiữ (-5% to 5%)►
ArrowsGiảm (< -5%)▼
Stars90-100★★★★★
Stars50-69★★★☆☆
Stars0-29★☆☆☆☆

6.3 Custom Icon Rules

More Rules → Icon Sets → Custom thresholds:

Icon Sets — KPI Dashboard

StatusKPIValueTrend
🟢Revenue Growth+15%▲
🟡Customer NPS72►
🔴Churn Rate8.5%▼
⭐Satisfaction4.2/5★★★★☆

🟢🟡🔴 Traffic Lights — ngôn ngữ chung, ai cũng hiểu!

Traffic lights là "ngôn ngữ chung" của business. 🟢🟡🔴 ai cũng hiểu ngay. Dùng cho KPI dashboards, executive reports — không cần giải thích!

Checkpoint

Traffic Lights (🟢🟡🔴) cho status, Arrows (▲►▼) cho trend, Stars cho rating. Custom thresholds để fit business logic. Bạn đã áp dụng cho KPI report chưa?

7

📐 6. Formula-Based Rules

TB5 min

6.1 Highlight Entire Row

fxHighlight Entire Row
=$E2="Overdue"Formula rule
Apply to: =$A$2:$F$100
Format: Red fill
💡 $ trước column (E) nhưng không $ trước row (2) → Column cố định, row dynamic

6.2 Alternating Row Colors

fxZebra Stripes
=MOD(ROW(),2)=0Zebra stripes
Format: Light gray fill

6.3 Highlight Weekends / Compare Columns

fxMore Formula Rules
=OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)Weekends → Blue fill
=B2<C2Actual < Target → Red fill

Formula-based rules cần chú ý $ references! Column lock ($E2) giữ cố định cột, row unlocked để check từng dòng. Sai reference = rule áp dụng sai cells — hãy test trên vài rows trước!

Checkpoint

Formula-based rules = CF mạnh nhất — highlight cả row, zebra stripes, so sánh columns. Key: dùng $E2 (lock column, unlock row). Bạn đã tạo rule highlight entire row chưa?

8

🛡️ 7. Managing Rules

TB5 min

Rules Manager

Home → Conditional Formatting → Manage Rules

📋 Conditional Formatting Rules Manager

Rule (Applied in =$A$1:$F$100)FormatStop If True
1Cell Value > 90🟢 Green Fill☐
2Cell Value 50–90🟡 Yellow Fill☐
3Cell Value < 50🔴 Red Fill + Bold☑
4=$E2="Overdue"🟣 Entire Row Red☐

⬆ Drag to reorder priority · ☑ Stop If True ngừng check rules bên dưới

  • Rules áp dụng từ trên xuống dưới
  • Rules trên có priority cao hơn
  • Stop If True: Ngừng check rules tiếp nếu match
  • Clear Rules: Clear Selected Cells hoặc Entire Sheet

Checkpoint

Rules áp dụng top-down (trên trước). Stop If True ngừng check khi match. Dùng Manage Rules để xem/sắp xếp/xóa tất cả rules. Bạn đã kiểm tra Rules Manager chưa?

9

🏋️ 8. Thực hành

TB5 min
Thực hành trên Excel

Kết hợp nhiều loại CF trên cùng bảng data. Hãy thử tạo KPI dashboard!

Exercise 1: Sales Performance Heatmap

Tạo bảng Revenue by Product × Month với:

  1. Color Scale cho values
  2. Icon Set cho Growth % column
  3. Data Bars cho Total column
  4. Highlight top 3 cells Green, bottom 3 Red

Exercise 2: KPI Dashboard

KPIValueTargetStatus
Revenue125K100K▲ 🟢
Costs85K80K▼ 🔴
NPS7875► 🟢

Apply: Traffic lights cho Status, Data bars cho Value, Formula rule: Red nếu Costs > Target

Checkpoint

Bạn đã apply được Highlight Rules, Data Bars, Icon Sets và Formula-based rule trong các bài tập chưa? KPI Dashboard có hiển thị đúng traffic lights không?

10

� 9. Tổng Kết

TB5 min

Kiến thức đã học

Chủ đềNội dung chínhTầm quan trọng
Highlight RulesGreater/Less, Duplicates, DatesData cleaning & monitoring
Data BarsMini bar charts trong cellsQuick visual comparison
Color Scales2/3-Color gradient heatmapsPattern recognition
Icon SetsTraffic lights, arrows, starsKPI dashboards
Formula RulesCustom logic, highlight rowsAdvanced analysis
Managing RulesPriority, Stop If True, ClearMaintainability

Câu hỏi tự kiểm tra

  1. Data Bars và Color Scales khác nhau thế nào?
  2. Khi nào cần dùng Formula-based rule thay vì built-in rule?
  3. "Stop If True" trong Manage Rules có tác dụng gì?
  4. Icon Sets phù hợp cho loại dữ liệu nào?

Bài tiếp theo: Data Validation & Protection — Drop-downs, input rules, bảo vệ worksheet

Key Takeaways:

  • Highlight Rules: Greater/Less, Duplicates, Dates — data cleaning & monitoring
  • Data Bars = mini bar charts trong cells cho visual comparison nhanh
  • Color Scales = gradient heatmaps cho pattern recognition
  • Icon Sets = traffic lights, arrows — lý tưởng cho KPI dashboards
  • Formula-based rules cho custom logic nâng cao
  • Conditional Formatting giúp data "tự nói" — thấy ngay outlier, trend, và KPI!