Conditional Formatting Nâng Cao: Heatmap, Data Bars, Icon Sets Và Custom Formula Rules

Conditional Formatting biến bảng số nhàm chán thành dashboard trực quan — không cần tạo chart. Nhưng hầu hết người dùng chỉ biết highlight ô đỏ/vàng/xanh. Bài viết này giới thiệu 15 kỹ thuật nâng cao biến bảng Excel thành "Power BI thu nhỏ".

1. Color Scales — Tạo Heatmap #

Color Scales tô màu gradient từ min đến max — giống heatmap trong Power BI.

1.1 Heatmap cơ bản #

Chọn vùng dữ liệu → Home → Conditional Formatting → Color Scales → chọn gradient (đỏ-vàng-xanh hoặc trắng-xanh đậm).

1.2 Heatmap tùy chỉnh #

// New Rule → Format Style: 3-Color Scale
// Minimum: Type=Percentile, Value=10, Color=Đỏ
// Midpoint: Type=Percentile, Value=50, Color=Vàng  
// Maximum: Type=Percentile, Value=90, Color=Xanh lá

// Mẹo: dùng Percentile thay Number để tránh outlier ảnh hưởng

Ứng dụng: Bảng doanh thu theo Tháng × Khu vực — nhìn 1 cái biết ngay tháng nào, khu vực nào hot/lạnh.

2. Data Bars — Biểu Đồ Thanh Trong Ô #

Data Bars vẽ biểu đồ thanh ngang ngay bên trong ô — trực quan hóa mà không cần chart riêng.

// 2.1 Data Bar cơ bản
// Conditional Formatting → Data Bars → chọn màu

// 2.2 Data Bar với giá trị âm
// Manage Rules → Edit Rule:
// Negative Value and Axis → chọn màu riêng cho giá trị âm
// Axis Setting: Automatic (thanh âm chạy ngược)

// 2.3 Chỉ hiện thanh, ẩn số
// Edit Rule → tích "Show Bar Only"

• Dùng Data Bars cho cột tỉ lệ %: hoàn thành, tiến độ, tăng trưởng

• Kết hợp Data Bars + Icon Sets: thanh xanh + icon ✅ cho mục đạt KPI

• Data Bars cho cả giá trị âm: so sánh vs budget, vs cùng kỳ

3. Icon Sets — Tín Hiệu Trực Quan #

3.1 Icon Sets có sẵn #

Conditional Formatting → Icon Sets → chọn loại:

• 🔴🟡🟢 Traffic Lights: tốt cho KPI trạng thái

• ↑→↓ Directional Arrows: tốt cho so sánh tăng/giảm

• ⭐ Ratings: tốt cho đánh giá/xếp hạng

• ✅❌ Symbols: tốt cho đạt/không đạt

3.2 Tùy chỉnh Icon Sets với Custom Rule #

// Ví dụ: Tăng trưởng doanh thu
// ≥10%: ↑ xanh (tốt)
// 0-10%: → vàng (bình thường)  
// <0%: ↓ đỏ (xấu)

// Edit Rule:
// Icon 1 (↑ green): when value >= 10 (Percent = Number)
// Icon 2 (→ yellow): when value >= 0
// Icon 3 (↓ red): khi còn lại

3.3 Chỉ hiện icon, ẩn số #

Edit Rule → tích "Show Icon Only" → cột trở thành cột icon thuần, rất gọn cho dashboard.

4. Formula Rules — Custom Conditions #

Đây là kỹ thuật mạnh nhất: dùng công thức TRUE/FALSE để quyết định format.

4.1 Highlight toàn bộ dòng theo điều kiện #

// Chọn toàn bộ bảng A2:G100
// New Rule → Use a formula:

// Highlight dòng có trạng thái "Trễ hạn"
=$E2="Trễ hạn"
// Format: nền đỏ nhạt, chữ đỏ đậm

// Highlight dòng có giá trị > trung bình
=$D2>AVERAGE($D$2:$D$100)
// Format: nền xanh nhạt

Quan trọng: Dùng $ ở cột ($E2) nhưng KHÔNG dùng $ ở dòng (E$2). Điều này đảm bảo cùng cột E nhưng mỗi dòng kiểm tra riêng.

4.2 Highlight ô trùng lặp #

// Highlight giá trị xuất hiện 2+ lần
=COUNTIF($A:$A, A2)>1

// Highlight giá trị chỉ xuất hiện 1 lần (unique)
=COUNTIF($A:$A, A2)=1

4.3 Highlight cuối tuần #

// Cho cột ngày tháng
=OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)
// Format: nền vàng nhạt

4.4 Highlight theo deadline #

// Quá hạn: đỏ
=$C2<TODAY()

// Sắp đến hạn (trong 3 ngày): cam
=AND($C2>=TODAY(), $C2<=TODAY()+3)

// Còn hạn: xanh
=$C2>TODAY()+3

4.5 Tô sọc xen kẽ dòng (zebra stripes) #

=MOD(ROW(),2)=0
// Format: nền xám nhạt → bảng dễ đọc hơn nhiều

5. Kỹ Thuật Dashboard Pro #

5.1 Sparkline + Conditional Formatting #

Kết hợp Sparkline (Insert → Sparkline) với Conditional Formatting cho ô bên cạnh:

• Cột A-L: dữ liệu 12 tháng (ẩn đi)

• Cột M: Sparkline Line chart

• Cột N: =L2-K2 (thay đổi tháng cuối) + Icon Set arrow

5.2 Progress bar bằng Conditional Formatting #

// Tạo progress bar trong 1 ô:
// 1. Giá trị ô = 75% (hoặc 0.75)
// 2. Data Bar → Solid Fill, màu teal
// 3. Edit Rule: Maximum = 1 (100%)
// 4. Show Bar Only = tích
// Kết quả: thanh progress bar đẹp trong ô!

6. Quản Lý Rules #

• Manage Rules (Conditional Formatting → Manage Rules): xem, sửa, xóa, đổi thứ tự

• Thứ tự quan trọng: rule trên được ưu tiên. Tick "Stop If True" để ngừng kiểm tra rules sau

• Copy rules: Format Painter hoặc Paste Special → Formatting

• Clear rules: Conditional Formatting → Clear Rules → From Selection/Entire Sheet

• Tránh quá nhiều rules trên 1 sheet (>10 rules + 10000+ ô = chậm)

Kết Luận #

Conditional Formatting là cách nhanh nhất biến bảng số thành dashboard trực quan mà không cần tạo chart. Color Scales cho heatmap, Data Bars cho so sánh, Icon Sets cho trạng thái, Formula Rules cho logic phức tạp — bộ tứ này đủ để tạo báo cáo ấn tượng.

Khám phá thêm Excel Dashboard tại Trà Đá Data! 🍵