Có thể bạn quan tâm: Excel Cho Người Đi Làm: Bí Quyết Thành Thạo, Tăng Năng Suất Và Thăng Tiến Nhanh Chóng
Giới thiệu chung
Excel là một công cụ mạnh mẽ trong việc quản lý, phân tích và trình bày dữ liệu. Trong số rất nhiều tính năng của Excel, Conditional Formatting (định dạng có điều kiện) là một trong những công cụ quan trọng giúp người dùng nhanh chóng nhận diện các mẫu, xu hướng hoặc ngoại lệ trong bảng tính. Khi kết hợp Conditional Formatting với các công thức logic như IF, người dùng có thể tạo ra những quy tắc phức tạp, tự động thay đổi màu sắc, biểu tượng hoặc kiểu chữ dựa trên giá trị và điều kiện tùy chỉnh.
Bài viết này sẽ cung cấp một hướng dẫn chi tiết, từng bước, về cách sử dụng Conditional Formatting Formula IF trong Excel. Chúng ta sẽ:
- Hiểu cơ bản về Conditional Formatting và công thức IF.
- Xây dựng các công thức IF cơ bản trong Conditional Formatting.
- Thực hiện các ví dụ thực tế từ đơn giản đến nâng cao.
- Giải quyết các lỗi thường gặp và tối ưu hoá hiệu suất.
- Chia sẻ mẹo, thủ thuật và các nguồn tài liệu tham khảo.
Hãy cùng khám phá và biến Excel thành một công cụ trực quan, tự động hoá công việc của bạn!
Có thể bạn quan tâm: Học Excel Cho Dân Văn Phòng: Từ Cơ Bản Đến Thành Thạo Trong 30 Ngày
1. Kiến thức nền tảng
1.1 Conditional Formatting là gì?
Conditional Formatting (định dạng có điều kiện) cho phép người dùng áp dụng định dạng (màu nền, màu chữ, biểu tượng, thanh dữ liệu,…) cho các ô dựa trên điều kiện mà ô đó thỏa mãn. Khi dữ liệu thay đổi, định dạng sẽ tự động cập nhật.
Các loại định dạng có điều kiện phổ biến
| Loại định dạng | Mô tả | Ví dụ |
|---|---|---|
| Highlight Cells Rules | Đánh dấu ô dựa trên các quy tắc như “Lớn hơn”, “Nhỏ hơn”, “Giữa” | Đánh dấu các giá trị > 1000 |
| Top/Bottom Rules | Đánh dấu các giá trị đứng đầu hoặc cuối | Top 10% |
| Data Bars | Thanh dữ liệu biểu thị mức độ | Thanh dài hơn cho giá trị lớn |
| Color Scales | Màu sắc gradient dựa trên giá trị | Xanh → đỏ |
| Icon Sets | Biểu tượng (mũi tên, dấu chấm…) | Mũi tên lên cho tăng, xuống cho giảm |
1.2 Công thức IF trong Excel
Công thức IF là một hàm logic trả về giá trị TRUE hoặc FALSE dựa trên một điều kiện:
IF(logical_test, value_if_true, value_if_false)
- logical_test: điều kiện kiểm tra (ví dụ: A1>100)
- value_if_true: giá trị trả về nếu điều kiện đúng
- value_if_false: giá trị trả về nếu điều kiện sai
Trong Conditional Formatting, chúng ta không cần value_if_true/value_if_false; chỉ cần logical_test trả về TRUE/FALSE để quyết định có áp dụng định dạng hay không.
1.3 Tại sao kết hợp IF với Conditional Formatting?
- Tùy biến sâu: Thay vì chỉ dùng các quy tắc có sẵn, bạn có thể tạo điều kiện phức tạp (nhiều điều kiện, tham chiếu tới nhiều cột, so sánh ngày tháng…).
- Đánh giá đa tiêu chí: Kết hợp nhiều tiêu chí trong một công thức (AND, OR).
- Động: Khi dữ liệu thay đổi, các quy tắc được tính lại tự động.
Có thể bạn quan tâm: Excel Bị Ẩn Dòng Đầu: Nguyên Nhân, Cách Khắc Phục Và Phòng Ngừa Hiệu Quả
2. Cú pháp và nguyên tắc viết công thức trong Conditional Formatting
2.1 Cú pháp chung
Khi tạo một quy tắc Conditional Formatting dựa trên công thức, bạn sẽ nhập công thức giống như công thức Excel bình thường, nhưng lưu ý:
- Công thức phải trả về TRUE/FALSE.
- Tham chiếu ô phải là tương đối (relative) hoặc tuyệt đối (absolute) tùy theo cách áp dụng quy tắc cho một vùng.
- Khi áp dụng cho một vùng (range), Excel sẽ tính công thức cho mỗi ô trong vùng dựa trên ô đầu tiên của vùng (ô “active cell”).
2.2 Tham chiếu tương đối vs tuyệt đối
| Loại tham chiếu | Mô tả | Ví dụ |
|---|---|---|
| Tương đối (A1) | Thay đổi khi sao chép công thức | Khi áp dụng cho A1:B10, công thức =A1>100 sẽ kiểm tra A1, B1, A2, B2… |
| Tuyệt đối cột ($A1) | Cột cố định, hàng thay đổi | =$A1>100 sẽ luôn kiểm tra cột A của hàng hiện tại |
| Tuyệt đối hàng (A$1) | Hàng cố định, cột thay đổi | =A$1>100 sẽ luôn kiểm tra hàng 1 của cột hiện tại |
| Tuyệt đối hoàn toàn ($A$1) | Không thay đổi khi sao chép | Luôn kiểm tra ô A1 |
2.3 Các hàm logic thường dùng
| Hàm | Mô tả | Ví dụ |
|---|---|---|
| AND() | Trả về TRUE khi tất cả điều kiện đúng | =AND(A1>0, B1<100) |
| OR() | Trả về TRUE khi bất kỳ điều kiện nào đúng | =OR(A1="Yes", B1="No") |
| NOT() | Đảo ngược giá trị TRUE/FALSE | =NOT(A1>0) |
| ISBLANK() | Kiểm tra ô rỗng | =ISBLANK(A1) |
| ISNUMBER() | Kiểm tra ô có số | =ISNUMBER(A1) |
| ISTEXT() | Kiểm tra ô có chuỗi | =ISTEXT(A1) |
3. Các ví dụ thực tế
3.1 Ví dụ 1: Đánh dấu các giá trị vượt ngưỡng
Mục tiêu: Đánh dấu màu đỏ các ô trong cột B nếu giá trị > 500.
Bước thực hiện:
- Chọn vùng B2:B100.
- Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Nhập công thức:
=B2>500. - Chọn định dạng (Fill → Red).
- Nhấn OK.
Giải thích: Khi Excel tính công thức cho ô B2, nếu B2>500 thì trả về TRUE → áp dụng màu đỏ. Khi tính cho B3, công thức tự động chuyển thành =B3>500 vì tham chiếu là tương đối.
3.2 Ví dụ 2: Đánh dấu hàng có ngày quá hạn
Mục tiêu: Nếu ngày trong cột C (ngày giao) đã qua ngày hôm nay, tô màu nền xanh cho toàn hàng (cột A–E).
Công thức:
=$C2<TODAY()
Thực hiện:
- Chọn vùng A2:E100.
- Tạo quy tắc mới, nhập công thức trên.
- Định dạng màu nền xanh.
Lưu ý: $C2 cố định cột C, nhưng hàng thay đổi khi áp dụng cho từng ô trong hàng.
3.3 Ví dụ 3: Kiểm tra nhiều tiêu chí (AND)
Mục tiêu: Đánh dấu màu cam các dòng mà:
– Số lượng (cột D) > 100
– Và trạng thái (cột E) = “Pending”
Công thức:
=AND($D2>100, $E2="Pending")
Áp dụng: Chọn A2:E100 → New Rule → Use a formula → nhập công thức → chọn màu.
3.4 Ví dụ 4: Sử dụng OR để đánh dấu các mức độ rủi ro
Mục tiêu: Nếu điểm rủi ro (cột F) > 80 hoặc mức độ “High”, tô màu đỏ.
Công thức:
=OR($F2>80, $G2="High")
3.5 Ví dụ 5: Đánh dấu các ô trống
Mục tiêu: Đánh dấu màu vàng các ô trống trong cột H.
Công thức:
=ISBLANK(H2)
Hoặc dùng:
=H2=""
3.6 Ví dụ 6: Đánh dấu các giá trị lặp lại trong một cột
Mục tiêu: Tô màu xanh các giá trị xuất hiện hơn một lần trong cột A.
Công thức (được nhập trong Conditional Formatting cho A2:A100):
=COUNTIF($A$2:$A$100, A2)>1
Giải thích: COUNTIF đếm số lần xuất hiện của giá trị hiện tại trong toàn cột. Nếu >1 → TRUE → tô màu.
3.7 Ví dụ 7: Đánh dấu các giá trị “ngày cuối tuần”
Mục tiêu: Nếu ngày trong cột B là Thứ Bảy hoặc Chủ Nhật, tô nền xám.
Công thức:
=OR(WEEKDAY($B2,2)=6, WEEKDAY($B2,2)=7)
WEEKDAY(date,2)trả về 1 (Thứ Hai) – 7 (Chủ Nhật). Vì vậy 6 = Thứ Bảy, 7 = Chủ Nhật.
3.8 Ví dụ 8: Định dạng dựa trên giá trị tính toán (IF lồng IF)
Mục tiêu: Nếu doanh thu (cột C) > 1000 → màu xanh; nếu 500‑1000 → màu vàng; nếu <500 → màu đỏ.
Cách thực hiện: Vì Conditional Formatting chỉ chấp nhận một công thức mỗi quy tắc, chúng ta tạo ba quy tắc:
Có thể bạn quan tâm: Excel Concat String With Separator: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
- Quy tắc 1 (xanh):
=$C2>1000 - Quy tắc 2 (vàng):
=AND($C2>=500,$C2<=1000) - Quy tắc 3 (đỏ):
=$C2<500
Đặt thứ tự ưu tiên (điều chỉnh “Stop If True” nếu muốn).
3.9 Ví dụ 9: Đánh dấu các mục “cũ” dựa trên ngày nhập
Mục tiêu: Nếu ngày nhập (cột D) cách ngày hiện tại hơn 180 ngày, tô màu xám.
Công thức:
=TODAY()-$D2>180
3.10 Ví dụ 10: Sử dụng hàm TEXT để so sánh chuỗi định dạng ngày
Mục tiêu: Đánh dấu các ô trong cột E có ngày tháng thuộc tháng 12 (dù năm nào).
Công thức:
=TEXT($E2,"mm")="12"
4. Xây dựng công thức IF phức tạp trong Conditional Formatting
4.1 Khi nào nên dùng IF lồng nhau?
Trong một số trường hợp, bạn muốn áp dụng một định dạng duy nhất dựa trên nhiều mức độ. Thay vì tạo nhiều quy tắc, bạn có thể dùng một công thức IF lồng nhau trả về TRUE/FALSE cho từng mức độ bằng cách kết hợp với hàm CHOOSE hoặc LOOKUP.
Ví dụ: Đánh dấu màu dựa trên mức độ rủi ro (Low, Medium, High)
- Low → màu xanh
- Medium → màu vàng
- High → màu đỏ
Công thức (được nhập trong quy tắc màu nền, với ba quy tắc khác nhau để áp dụng màu tương ứng):
=IF($F2="High",TRUE,FALSE) // Quy tắc màu đỏ
=IF($F2="Medium",TRUE,FALSE) // Quy tắc màu vàng
=IF($F2="Low",TRUE,FALSE) // Quy tắc màu xanh
Tuy nhiên, để giảm số quy tắc, ta có thể dùng Conditional Formatting với biểu tượng (Icon Sets) và công thức:
=CHOOSE(MATCH($F2,{"Low","Medium","High"},0),1,2,3)
Sau đó gán các biểu tượng tương ứng.
4.2 Sử dụng hàm SWITCH (Excel 2026 trở lên)
SWITCH cho phép so sánh một giá trị với nhiều trường hợp mà không cần IF lồng nhau.
Công thức:
=SWITCH($F2,"Low",TRUE,FALSE) // Quy tắc màu xanh
Bạn vẫn cần tạo quy tắc riêng cho mỗi trường hợp, nhưng công thức ngắn gọn hơn.
4.3 Kết hợp với hàm VLOOKUP hoặc XLOOKUP
Nếu bạn có một bảng tham chiếu (lookup table) xác định màu sắc hoặc mức độ, bạn có thể dùng VLOOKUP trong công thức Conditional Formatting để trả về giá trị TRUE/FALSE.
Ví dụ: Bảng tham chiếu trong G1:H3:
| G | H |
|---|---|
| High | 1 |
| Medium | 2 |
| Low | 3 |
Công thức:
=VLOOKUP($F2,$G$1:$H$3,2,FALSE)=1 // Đánh dấu màu đỏ cho High
5. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Giải pháp |
|---|---|---|
| #VALUE! | Công thức trả về lỗi vì dữ liệu không phù hợp (ví dụ: so sánh chuỗi với số). | Kiểm tra kiểu dữ liệu, dùng VALUE(), TEXT() để chuyển đổi. |
| Công thức không áp dụng | Tham chiếu ô sai (tương đối vs tuyệt đối). | Kiểm tra ô “active cell” khi tạo quy tắc; sử dụng $ cho cột/hàng cố định. |
| Quy tắc không hiển thị | Quy tắc bị đặt sau quy tắc khác có “Stop If True”. | Sắp xếp lại thứ tự quy tắc, bỏ “Stop If True” nếu không cần. |
| Hiệu suất chậm | Quá nhiều quy tắc hoặc công thức phức tạp trên dải dữ liệu lớn. | Giảm phạm vi áp dụng, dùng công thức đơn giản hơn, hoặc tạo bảng phụ để tính toán trước. |
| Định dạng không đồng nhất | Khi sao chép dữ liệu, công thức không cập nhật đúng. | Kiểm tra lại tham chiếu tuyệt đối, hoặc sử dụng Format Painter để sao chép quy tắc. |
| Conditional Formatting bị mất khi lưu file | Định dạng không được hỗ trợ trong định dạng file (ví dụ .csv). | Lưu dưới định dạng .xlsx hoặc .xlsm. |
5.1 Cách kiểm tra công thức trong Conditional Formatting
- Chọn một ô trong vùng đã áp dụng quy tắc.
- Vào Home → Conditional Formatting → Manage Rules.
- Chọn quy tắc và nhấn Edit Rule.
- Nhấn Show Formula để xem công thức.
- Sử dụng Evaluate Formula (trong tab Formulas) để kiểm tra từng bước nếu cần.
6. Tối ưu hoá hiệu suất khi sử dụng Conditional Formatting
- Giới hạn phạm vi: Đừng áp dụng quy tắc cho toàn cột (A:A) nếu chỉ cần 10,000 hàng. Hãy giới hạn cụ thể (A2:A10000).
- Sử dụng bảng (Table): Khi dữ liệu được định dạng dưới dạng Table, Excel tự động mở rộng quy tắc khi thêm hàng mới.
- Tránh lặp lại công thức: Nếu nhiều quy tắc dùng cùng công thức, cân nhắc hợp nhất chúng.
- Sử dụng cột trợ giúp: Tính toán các điều kiện phức tạp trong một cột phụ (ví dụ:
=AND(A2>0,B2<100)) và sau đó dùng Conditional Formatting dựa trên giá trị TRUE/FALSE của cột này. Điều này giảm tải tính toán trong Conditional Formatting. - Xóa quy tắc không dùng: Vào Manage Rules → Xóa các quy tắc không còn cần.
7. Mẹo và thủ thuật nâng cao
7.1 Định dạng dựa trên Ngày tháng động (Rolling dates)
- 30 ngày gần nhất:
=AND($A2>=TODAY()-30,$A2<=TODAY()) - Tuần hiện tại:
=WEEKNUM($A2,2)=WEEKNUM(TODAY(),2)
7.2 Đánh dấu giá trị Đỉnh và đáy trong một dãy
- Giá trị cao nhất:
=$A2=MAX($A$2:$A$100) - Giá trị thấp nhất:
=$A2=MIN($A$2:$A$100)
7.3 Sử dụng Custom Formula với INDIRECT để tham chiếu động
Nếu bạn muốn cho người dùng nhập một ô chứa tên cột, sau đó Conditional Formatting dựa trên cột đó:
=INDIRECT($D$1 & ROW())>100
Ở đây, D1 chứa ký tự cột (ví dụ “B”). Công thức sẽ kiểm tra B2, B3,… tùy theo vị trí.
7.4 Định dạng bằng Icon Sets dựa trên công thức
- Chọn vùng.
- Vào Conditional Formatting → Icon Sets → More Rules.
- Chọn “Format all cells based on their values” → “Use a formula to determine which cells to format”.
- Nhập công thức, rồi thiết lập các ngưỡng (≥, ≤) và biểu tượng tương ứng.
Ví dụ: Đánh dấu “↑” nếu tăng so với ngày trước, “↓” nếu giảm:
- Công thức cho ↑:
=B2>B1 - Công thức cho ↓:
=B2<B1
7.5 Conditional Formatting trong PivotTable
- Khi áp dụng quy tắc vào PivotTable, hãy chắc chắn rằng Applies to bao gồm toàn bộ vùng pivot (ví dụ:
=GETPIVOTDATA(...)có thể được dùng trong công thức). - Sử dụng Field Settings → Number Format để đồng bộ định dạng.
8. Thực hành: Dự án mini – Báo cáo KPI bán hàng
8.1 Yêu cầu
- Bảng dữ liệu: Cột A (Tên nhân viên), B (Doanh thu tháng trước), C (Doanh thu tháng hiện tại), D (Mục tiêu), E (Tỷ lệ đạt mục tiêu).
- Mục tiêu:
- Nếu doanh thu tháng hiện tại > doanh thu tháng trước → tô nền xanh.
- Nếu doanh thu < doanh thu tháng trước → tô nền đỏ.
- Nếu đạt mục tiêu (E ≥ 100%) → hiển thị biểu tượng “★”.
- Nếu chưa đạt mục tiêu nhưng tỷ lệ ≥ 80% → nền vàng.
8.2 Các bước thực hiện
- Tô nền xanh/red:
- Chọn C2:C100.
- Quy tắc 1 (xanh):
=C2>B2 - Quy tắc 2 (đỏ):
=C2<B2 - Biểu tượng “★”:
- Chọn E2:E100.
- New Rule → Use a formula →
=E2>=1 - Định dạng → Font → Symbol “★”, màu vàng.
- Nền vàng cho 80‑99%:
- Chọn E2:E100.
- New Rule → Use a formula →
=AND(E2>=0.8,E2<1) - Định dạng → Fill → Yellow.
8.3 Kiểm tra
- Thêm dữ liệu mới vào hàng 101, công thức sẽ tự động áp dụng vì chúng ta đã chọn phạm vi C2:C101 và E2:E101.
- Để mở rộng tự động, biến bảng thành Table (
Ctrl+T) và các quy tắc sẽ tự động điều chỉnh.
9. Tổng kết
Conditional Formatting kết hợp với công thức IF (và các hàm logic khác) là một trong những kỹ năng quan trọng giúp bạn:
- Tăng tốc độ phân tích: Nhìn ngay các xu hướng, ngoại lệ mà không cần lọc dữ liệu.
- Cải thiện giao diện báo cáo: Đưa ra thông tin quan trọng một cách trực quan.
- Tự động hoá: Khi dữ liệu thay đổi, định dạng cập nhật tự động, giảm công việc thủ công.
Các bước quan trọng:
- Xác định rõ mục tiêu định dạng.
- Chọn đúng phạm vi và xác định ô “active”.
- Viết công thức trả về TRUE/FALSE, chú ý tới tham chiếu tương đối/tuyệt đối.
- Kiểm tra và tinh chỉnh quy tắc trong Manage Rules.
- Tối ưu hoá bằng cách giới hạn phạm vi, dùng bảng trợ giúp, và loại bỏ quy tắc không cần.
Hy vọng bài viết này đã cung cấp cho bạn một cẩm nang toàn diện để khai thác sức mạnh của Excel Conditional Formatting Formula IF. Hãy thử áp dụng vào công việc thực tế, và bạn sẽ thấy Excel trở nên thông minh hơn bao giờ hết!
Nếu bạn còn thắc mắc hoặc muốn chia sẻ kinh nghiệm, hãy để lại bình luận. Chúc bạn thành công!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.