Conditional Formatting Cells In Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Dùng Concat Để Gộp Dấu Nháy Đôi (double Quotes) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới thiệu chung về Conditional Formatting

Conditional Formatting (định dạng có điều kiện) là một tính năng mạnh mẽ trong Microsoft Excel, cho phép người dùng tự động thay đổi định dạng (màu nền, màu chữ, biểu tượng, thanh dữ liệu, v.v.) của các ô dựa trên giá trị hoặc công thức nhất định. Thay vì phải kiểm tra từng ô một, bạn chỉ cần thiết lập quy tắc và Excel sẽ tự động áp dụng cho toàn bộ phạm vi dữ liệu. Điều này giúp:

• Nâng cao khả năng trực quan: Dễ dàng nhận ra các xu hướng, ngoại lệ, hoặc các giá trị quan trọng.
• Tiết kiệm thời gian: Loại bỏ công việc kiểm tra thủ công.
• Tăng độ chính xác: Giảm thiểu lỗi do con người khi đánh dấu hoặc phân loại dữ liệu.

Trong bài viết này, chúng ta sẽ đi sâu vào các khái niệm cơ bản, các loại định dạng có điều kiện, cách tạo và quản lý quy tắc, cũng như các kỹ thuật nâng cao như sử dụng công thức tùy chỉnh, áp dụng định dạng trên nhiều bảng tính, và tối ưu hoá hiệu suất.

Có thể bạn quan tâm: Cách Tắt Compatibility Mode Trong Excel: Hướng Dẫn Chi Tiết Và Những Lưu Ý Quan Trọng

2. Các loại Conditional Formatting cơ bản

2.1. Highlight Cells Rules (Quy tắc làm nổi bật ô)

Đây là nhóm quy tắc phổ biến nhất, cho phép bạn nhanh chóng làm nổi bật các ô thỏa mãn một điều kiện nhất định:

2.2. Top/Bottom Rules (Quy tắc Top/Bottom)

Cho phép xác định các giá trị nằm trong top hoặc bottom của một tập hợp:

• Top 10 Items / Bottom 10 Items: Đánh dấu 10 mục cao nhất hoặc thấp nhất.
• Top 10% / Bottom 10%: Đánh dấu phần trăm phần tử cao nhất hoặc thấp nhất.
• Above Average / Below Average: Đánh dấu các giá trị trên hoặc dưới trung bình.

2.3. Data Bars (Thanh dữ liệu)

Data Bars hiển thị một thanh màu trong ô, độ dài tương ứng với giá trị của ô so với tập dữ liệu. Thích hợp cho việc so sánh nhanh các giá trị số mà không cần nhìn vào biểu đồ.

2.4. Color Scales (Thang màu)

Color Scales áp dụng một dải màu cho một phạm vi ô, màu sắc thay đổi từ thấp tới cao (ví dụ: xanh dương tới đỏ). Có các mẫu sẵn (2 màu, 3 màu) hoặc tùy chỉnh màu.

2.5. Icon Sets (Bộ biểu tượng)

Icon Sets cho phép gán các biểu tượng (mũi tên, hình tròn, dấu chấm, v.v.) dựa trên giá trị. Thường dùng để biểu diễn xu hướng (tăng/giảm) hoặc trạng thái (đạt/không đạt).

Có thể bạn quan tâm: Compatibility Checker Excel Là Gì? – Tìm Hiểu Chi Tiết, Cách Sử Dụng Và Lợi Ích Cho Doanh Nghiệp

3. Bước tạo Conditional Formatting cơ bản

3.1. Chọn phạm vi dữ liệu

1. Bôi đen ô hoặc kéo chuột để chọn toàn bộ phạm vi cần áp dụng định dạng.
2. Nếu muốn áp dụng cho toàn bộ cột, chỉ cần click vào tiêu đề cột (ví dụ: “C”).

3.2. Vào menu Conditional Formatting

• Excel 2026/2026/365: Home → Conditional Formatting.
• Các phiên bản cũ hơn: Home → Format → Conditional Formatting.

3.3. Chọn loại quy tắc

• Đối với Highlight Cells Rules, chọn “Greater Than”, nhập giá trị, chọn định dạng (màu nền, màu chữ, v.v.).
• Đối với Data Bars, chọn “Data Bars” → chọn kiểu màu.
• Đối với Icon Sets, chọn “Icon Sets” → chọn bộ biểu tượng mong muốn.

3.4. Xác nhận và hoàn tất

Nhấn OK để áp dụng. Excel sẽ tự động đánh dấu các ô thỏa mãn điều kiện.

4. Sử dụng công thức tùy chỉnh (Use a Formula)

4.1. Khi nào cần công thức tùy chỉnh?

• Khi điều kiện phức tạp không có trong các mẫu sẵn.
• Khi cần dựa trên nhiều ô (ví dụ: so sánh giá trị trong ô A với ô B).
• Khi áp dụng định dạng cho một dải ô dựa trên một ô tham chiếu duy nhất.

4.2. Cú pháp cơ bản

1. Chọn phạm vi muốn định dạng.
2. Vào Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Nhập công thức trả về TRUE hoặc FALSE. Nếu TRUE, định dạng sẽ được áp dụng.

Ví dụ 1: Đánh dấu các đơn hàng có doanh thu > 20% trung bình

= $C2 > (AVERAGE($C$2:$C$100) 1.20)

• $C2 là ô hiện tại trong cột doanh thu.
• $C$2:$C$100 là phạm vi tính trung bình.
• Khi công thức trả về TRUE, ô sẽ được định dạng.

Ví dụ 2: Đánh dấu hàng có ngày giao hàng trễ hơn ngày dự kiến

= $E2 > $F2

• $E2 là ngày giao thực tế.
• $F2 là ngày giao dự kiến.

4.3. Lưu ý về tham chiếu tuyệt đối và tương đối

• Tham chiếu tuyệt đối ($A$1): Không thay đổi khi sao chép công thức.
• Tham chiếu cột tuyệt đối ($A1): Chỉ cố định cột, hàng thay đổi.
• Tham chiếu hàng tuyệt đối (A$1): Chỉ cố định hàng, cột thay đổi.
• Đảm bảo công thức phù hợp với phạm vi áp dụng để tránh lỗi.

5. Quản lý và chỉnh sửa các quy tắc

5.1. Mở Conditional Formatting Rules Manager

• Home → Conditional Formatting → Manage Rules.
• Chọn Current Selection hoặc This Worksheet để xem tất cả quy tắc.

5.2. Các thao tác cơ bản

5.3. Xử lý xung đột

Nếu nhiều quy tắc áp dụng cho cùng một ô, Excel sẽ áp dụng quy tắc có thứ tự ưu tiên cao hơn (được sắp xếp ở trên trong Rules Manager). Sử dụng Stop If True để ngăn các quy tắc phía dưới ảnh hưởng.

6. Ứng dụng nâng cao

Có thể bạn quan tâm: Cách Nối (concat) Nhiều Ô Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

6.1. Conditional Formatting cho bảng (Table)

Khi dữ liệu được chuyển thành Table (Insert → Table), các quy tắc định dạng có thể tự động mở rộng khi thêm dòng mới. Đặc biệt:

• Sử dụng Structured References trong công thức (ví dụ: =@Revenue > AVERAGE(Table1Revenue)).
• Các quy tắc sẽ tự động áp dụng cho toàn bộ cột trong Table mà không cần điều chỉnh phạm vi.

6.2. Định dạng dựa trên giá trị của ô bên ngoài

Bạn có thể dùng một ô “điều khiển” để thay đổi điều kiện định dạng:

• Bước 1: Đặt một ô (ví dụ: G1) chứa giá trị ngưỡng.
• Bước 2: Tạo quy tắc với công thức: = $C2 > $G$1.
• Khi thay đổi giá trị trong G1, mọi ô trong cột C sẽ tự động cập nhật màu sắc.

6.3. Định dạng dựa trên nhiều điều kiện (AND/OR)

Sử dụng hàm AND hoặc OR trong công thức:

• AND: =AND($C2>1000, $D2="Completed") → Đánh dấu khi doanh thu > 1000 và trạng thái hoàn thành.
• OR: =OR($C2<500, $D2="Pending") → Đánh dấu khi doanh thu < 500 hoặc trạng thái đang chờ.

6.4. Conditional Formatting với hàm ISERROR và ISNUMBER

• Đánh dấu các ô có lỗi: =ISERROR(A1).
• Đánh dấu các ô chứa số: =ISNUMBER(A1).

6.5. Định dạng cho các ô trống hoặc không trống

• Trống: =ISBLANK(A1).
• Không trống: =NOT(ISBLANK(A1)).

6.6. Định dạng theo ngày động (Dynamic Dates)

Sử dụng các hàm ngày như TODAY(), NOW(), EDATE():

• Đánh dấu các ngày quá hạn: =AND($E2<>"" , $E2 < TODAY()).
• Đánh dấu các ngày trong 7 ngày tới: =AND($E2>=TODAY(), $E2<=TODAY()+7).

7. Tối ưu hoá hiệu suất khi sử dụng Conditional Formatting

Khi áp dụng quy tắc trên hàng ngàn hoặc hàng triệu ô, Excel có thể chậm lại. Dưới đây là các mẹo để tối ưu:

1. Giới hạn phạm vi: Tránh áp dụng cho toàn bộ cột nếu không cần. Chỉ chọn phạm vi thực tế.
2. Sử dụng công thức đơn giản: Tránh lồng quá nhiều hàm trong một công thức.
3. Kết hợp các quy tắc: Thay vì tạo nhiều quy tắc riêng biệt, cố gắng gộp lại trong một công thức.
4. Sử dụng Table: Khi dữ liệu thay đổi, Table tự động mở rộng mà không cần cập nhật phạm vi.
5. Tắt tính năng “Automatic Calculation” tạm thời khi thiết lập nhiều quy tắc, rồi bật lại sau khi hoàn thành.

8. Một số ví dụ thực tế

8.1. Quản lý KPI nhân viên

• Data Bar cho cột “Doanh thu” để so sánh nhanh.
• Color Scale cho cột “% Mục tiêu” (xanh → đỏ) để thấy ai đạt và ai chưa.
• Icon Set (mũi tên lên/dưới) cho % Mục tiêu > 100% (↑) và < 100% (↓).

8.2. Theo dõi tồn kho

Quy tắc: Đánh dấu màu đỏ nếu tồn kho < ngưỡng tối thiểu.

Công thức: =B2 < C2 (Áp dụng cho phạm vi B2:B100).

8.3. Đánh dấu ngày lễ trong lịch công việc

Sử dụng danh sách ngày lễ trong một sheet riêng (Holiday!A2:A20). Công thức:

=COUNTIF(Holiday!$A$2:$A$20, $D2) > 0

Áp dụng cho cột ngày D. Khi ngày trong D trùng với danh sách lễ, ô sẽ được tô màu vàng.

8.4. Phân loại khách hàng dựa trên mức chi tiêu

• Top 10%: Đánh dấu màu vàng (khách hàng VIP).
• Bottom 10%: Đánh dấu màu xanh (khách hàng cần chú ý tăng cường).

8.5. Kiểm tra dữ liệu nhập sai

• Duplicate Values: Tìm mã khách hàng trùng.
• Data Validation + Conditional Formatting: Khi nhập ngày sinh không hợp lệ (<1900 hoặc >TODAY()), ô sẽ chuyển sang màu đỏ.

9. Thực hành: Tạo một Dashboard đơn giản với Conditional Formatting

9.1. Bước 1: Chuẩn bị dữ liệu

9.2. Bước 2: Áp dụng Data Bars cho “Doanh thu”

• Chọn cột B, Conditional Formatting → Data Bars → Chọn màu xanh.

9.3. Bước 3: Color Scale cho “Lợi nhuận”

• Chọn cột D, Conditional Formatting → Color Scales → 3 màu (xanh → vàng → đỏ).

9.4. Bước 4: Icon Set cho “Chi phí”

• Conditional Formatting → Icon Sets → Chọn 3 biểu tượng (↑, →, ↓) dựa trên so sánh với trung bình chi phí.

9.5. Bước 5: Highlight Cells Rules – “Doanh thu < 140,000”

• Conditional Formatting → Highlight Cells Rules → Less Than → Nhập 140,000 → Định dạng nền màu hồng.

Kết quả: Dashboard hiển thị ngay lập tức các xu hướng, giúp người quản lý nhanh chóng đưa ra quyết định.

10. Các lỗi thường gặp và cách khắc phục

11. Kết luận

Conditional Formatting là một công cụ không thể thiếu cho bất kỳ người dùng Excel nào muốn biến dữ liệu thô thành thông tin trực quan, dễ hiểu. Từ những quy tắc đơn giản như Highlight Cells cho đến các công thức tùy chỉnh phức tạp, bạn có thể:

• Phát hiện nhanh các xu hướng, ngoại lệ trong dữ liệu.
• Tự động cập nhật khi dữ liệu thay đổi mà không cần thao tác thủ công.
• Tạo các báo cáo, dashboard hấp dẫn mà không cần phần mềm chuyên dụng.

Bằng cách nắm vững các nguyên tắc cơ bản, quản lý quy tắc hiệu quả, và áp dụng các kỹ thuật nâng cao, bạn sẽ nâng cao năng suất làm việc và đưa ra quyết định nhanh chóng, chính xác hơn. Hãy thử áp dụng những kiến thức vừa học vào dự án thực tế của bạn và khám phá sức mạnh thực sự của Conditional Formatting trong Excel!

Tiến sĩ Lê Thống Nhất

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ẻ.