30 Công Thức Conditional Formatting Thực Tế Nhất

Conditional Formatting biến bảng tính nhàm chán thành dashboard trực quan: tô màu hàng chẵn/lẻ, highlight deadline, phát hiện dữ liệu trùng, tạo heatmap... tất cả bằng công thức. Bài viết tổng hợp 30 công thức CF thực tế nhất.

Conditional Formatting Là Gì? #

Conditional Formatting (CF) tự động thay đổi định dạng ô (màu nền, font, border, icon) dựa trên giá trị hoặc công thức. Khi dùng công thức, bạn kiểm soát hoàn toàn logic tô màu.

Cách thêm CF bằng công thức: Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" → nhập công thức trả về TRUE/FALSE.

Quy tắc vàng: Công thức CF luôn được viết cho ô TRÊN CÙNG BÊN TRÁI của vùng áp dụng. Excel tự điều chỉnh cho các ô còn lại (giống kéo công thức). Dùng $ để cố định hàng/cột khi cần.

Nhóm 1: Tô Màu Hàng & Cột #

1. Hàng chẵn #

=MOD(ROW(),2)=0
Áp dụng: $A$2:$Z$1000 | Màu: xanh nhạt

2. Hàng lẻ #

=MOD(ROW(),2)=1

3. Tô xen kẽ mỗi 3 hàng #

=MOD(CEILING(ROW()-ROW($A$2)+1,3)/3,2)=1
→ Tô 3 hàng, bỏ 3 hàng, lặp lại

4. Cột chẵn #

=MOD(COLUMN(),2)=0

Nhóm 2: So Sánh & Ngưỡng #

5. Giá trị lớn hơn trung bình #

=A1>AVERAGE($A:$A)
→ Tô các ô có giá trị cao hơn trung bình cột

6. Top 5 giá trị cao nhất #

=A1>=LARGE($A$2:$A$100,5)
→ Tô 5 giá trị lớn nhất (có thể nhiều hơn 5 ô nếu có giá trị bằng nhau)

7. Bottom 3 giá trị thấp nhất #

=A1<=SMALL($A$2:$A$100,3)

8. Giá trị ngoài khoảng cho phép #

=OR(A1<$G$1, A1>$G$2)
→ $G$1 = min, $G$2 = max. Tô đỏ các ô vi phạm

9. Giá trị âm #

=A1<0
→ Tô đỏ cho số âm (lỗ, chi vượt)

10. Chênh lệch > 10% so với kỳ trước #

=ABS((B2-A2)/A2)>0.1
→ Tô khi biến động quá 10% (dùng cho so sánh budget vs actual)

Nhóm 3: Ngày Tháng & Deadline #

11. Ngày hôm nay #

=A1=TODAY()

12. Quá hạn (ngày đã qua) #

=AND(A1<TODAY(), A1<>"")
→ Tô đỏ cho deadline đã quá hạn

13. Sắp đến hạn (trong 7 ngày) #

=AND(A1>=TODAY(), A1<=TODAY()+7, A1<>"")
→ Tô vàng cho deadline trong tuần tới

14. Cuối tuần (Thứ 7, Chủ nhật) #

=WEEKDAY(A1,2)>5
→ Type 2: Mon=1,Sun=7. Giá trị >5 = Sat/Sun

15. Ngày trong tháng hiện tại #

=AND(MONTH(A1)=MONTH(TODAY()), YEAR(A1)=YEAR(TODAY()))

Nhóm 4: Text & Điều Kiện Logic #

16. Ô chứa keyword #

=ISNUMBER(SEARCH("lỗi",A1))
→ Tô bất kỳ ô nào chứa từ "lỗi" (không phân biệt hoa/thường)

17. Ô bắt đầu bằng ký tự cụ thể #

=LEFT(A1,2)="PC"
→ Tô các chứng từ bắt đầu bằng "PC" (Phiếu chi)

18. Ô trống #

=A1=""
→ Highlight ô chưa nhập liệu

19. Ô chứa công thức (không phải giá trị nhập tay) #

=ISFORMULA(A1)
→ Tô ô chứa công thức để phân biệt với dữ liệu nhập tay

20. Ô chứa lỗi #

=ISERROR(A1)
→ Tô đỏ ô có #VALUE!, #REF!, #DIV/0!, #N/A...

Nhóm 5: Trùng Lặp & Unique #

21. Giá trị trùng lặp #

=COUNTIF($A:$A,A1)>1
→ Tô tất cả ô có giá trị xuất hiện hơn 1 lần

22. Chỉ lần trùng thứ 2 trở đi #

=COUNTIF($A$1:A1,A1)>1
→ Giữ nguyên lần đầu, chỉ tô từ lần 2 (dùng range mở rộng)

23. Giá trị unique (xuất hiện đúng 1 lần) #

=COUNTIF($A:$A,A1)=1

24. Trùng lặp theo nhiều cột #

=COUNTIFS($A:$A,$A1,$B:$B,$B1)>1
→ Tô khi cả A VÀ B đều trùng (dùng cho check trùng đơn hàng + khách)

Nhóm 6: Tô Cả Hàng Theo Điều Kiện #

Mẹo: Để tô cả hàng, cố định cột bằng $ nhưng KHÔNG cố định hàng.

25. Tô cả hàng khi cột Status = "Đã thanh toán" #

=$D1="Đã thanh toán"
Áp dụng: $A$1:$Z$1000 (toàn bộ bảng)
→ $D cố định cột D, nhưng hàng thay đổi theo

26. Tô hàng có giá trị MAX #

=$C1=MAX($C:$C)
→ Tô cả hàng chứa doanh thu cao nhất

27. Tô hàng khi ô trong cùng hàng bị trống #

=$E1=""
→ Tô vàng hàng nào chưa nhập cột E (ví dụ: cột ghi chú bắt buộc)

Nhóm 7: Nâng Cao #

28. Heatmap theo % (gradient thủ công) #

// Rule 1: Xanh đậm (top 20%)
=A1>=PERCENTILE($A$2:$A$100,0.8)

// Rule 2: Xanh nhạt (40%-80%)
=A1>=PERCENTILE($A$2:$A$100,0.4)

// Rule 3: Vàng (20%-40%)
=A1>=PERCENTILE($A$2:$A$100,0.2)

// Rule 4: Đỏ (bottom 20%)
=A1<PERCENTILE($A$2:$A$100,0.2)

Thứ tự ưu tiên: Rule 1 trên cùng → Stop If True

29. Checkerboard (bàn cờ) #

=MOD(ROW()+COLUMN(),2)=0
→ Tô xen kẽ theo kiểu bàn cờ (hữu ích cho bảng lớn)

30. So sánh 2 cột — highlight khác biệt #

// Áp dụng cho cột B
=AND(B1<>A1, B1<>"")
→ Tô ô trong cột B khác cột A (dùng kiểm tra sau khi import dữ liệu)

Mẹo Sử Dụng CF Hiệu Quả #

1. Thứ tự rule quan trọng — Rule trên được ưu tiên. Dùng ↑↓ để sắp xếp.

2. Stop If True — Bật để không chạy rule bên dưới nếu đã khớp.

3. Quá nhiều CF làm chậm Excel — Giới hạn vùng áp dụng thay vì toàn cột.

4. Debug CF — Dùng Manage Rules (Home → CF → Manage Rules) để xem tất cả rule.

5. Copy CF sang sheet khác — Format Painter hoặc Paste Special → Formatting.

Câu Hỏi Thường Gặp (FAQ) #

Tại sao CF không hoạt động? #

3 nguyên nhân phổ biến: (1) Công thức trả về text thay vì TRUE, (2) Tham chiếu ô không đúng (thiếu/thừa $), (3) Rule bị đè bởi rule khác ở trên. Kiểm tra bằng Manage Rules.

CF có giới hạn số rule không? #

Về lý thuyết không, nhưng quá 50-100 rule sẽ khiến file chậm đáng kể. Nên gộp logic bằng OR/AND thay vì tạo nhiều rule riêng.

CF có copy được khi Paste Values không? #

Không. Paste Values chỉ giữ giá trị, mất hết CF. Dùng Paste Special → Formatting để copy CF, hoặc Format Painter.

Cách xóa tất cả CF trong vùng? #

Chọn vùng → Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.

Tổng Kết #