Khi bảng có nhiều dòng, bạn khó nhìn ra ngay nhân viên nào chưa đạt chỉ tiêu hoặc con số nào cao nhất. Tô màu có điều kiện trong Excel giúp bạn làm việc này tự động, và màu sẽ tự đổi theo khi số liệu thay đổi. Tính năng này có tên tiếng Anh là Conditional Formatting, còn được gọi là định dạng có điều kiện.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Trong ảnh, nút Conditional Formatting nằm ở đầu tab Home, còn Excel của bạn có thể đặt nút này ở nhóm Styles phía bên phải. Ở các ảnh có thao tác, con trỏ chuột chỉ vào chỗ bạn cần bấm hoặc nằm ngay cạnh ô bạn cần gõ.
Tải file thực hành: [Dữ liệu mẫu] - Bảng doanh số thực hành tô màu có điều kiện (file Excel trên OneDrive)
File thực hành có 12 nhân viên trong vùng A1:E13. 5 dòng đầu của bảng như sau:
Nhân viên | Khu vực | Doanh số (triệu) | Chỉ tiêu (triệu) | Kết quả |
|---|---|---|---|---|
An | Miền Bắc | 132 | 120 | Đạt |
Bình | Miền Nam | 96 | 120 | Chưa đạt |
Cường | Miền Trung | 88 | 100 | Chưa đạt |
Dung | Miền Bắc | 145 | 120 | Đạt |
Hùng | Miền Nam | 110 | 110 | Đạt |
Bạn nên xóa quy tắc của cách trước khi thử cách tiếp theo, để các màu không chồng lên nhau. Phần cuối bài có hướng dẫn xóa quy tắc.
Cách 1: Bạn tô các ô lớn hơn một con số
Yêu cầu của cách này là tô các ô có doanh số lớn hơn 120 triệu.
Đầu tiên, bạn quét chọn vùng C2:C13. Sau đó, bạn mở tab Home và chọn Conditional Formatting, Highlight Cells Rules, Greater Than.

Hộp thoại Greater Than hiện ra với một con số gợi ý. Bạn gõ 120 để thay con số đó, giữ màu mặc định rồi bấm OK.

Excel tô 4 ô có doanh số lớn hơn 120, gồm 132, 145, 121 và 139.

Menu Highlight Cells Rules còn có Less Than, Between và Equal To cho các điều kiện tương tự.
Cách 2: Bạn tô các ô chứa một đoạn chữ
Yêu cầu của cách này là tô các ô có kết quả Chưa đạt để nhìn ra ngay những người cần theo dõi.
Bạn quét chọn vùng E2:E13, rồi chọn Conditional Formatting, Highlight Cells Rules, Text that Contains. Trong hộp thoại, bạn gõ Chưa đạt và bấm OK.

Excel tô 6 ô có chữ Chưa đạt.

Bạn cần chọn đoạn chữ cẩn thận, vì quy tắc này tìm đoạn chữ nằm ở bất kỳ đâu trong ô và không phân biệt chữ hoa, chữ thường. Nếu bạn gõ Đạt, Excel sẽ tô cả ô Chưa đạt.
Cách 3: Bạn tô các giá trị cao nhất
Yêu cầu của cách này là tô 3 doanh số cao nhất.
Bạn quét chọn vùng C2:C13, rồi chọn Conditional Formatting, Top/Bottom Rules, Top 10 Items.

Hộp thoại có sẵn số 10. Bạn đổi số này thành 3 và bấm OK.

Excel tô 3 ô là 145, 139 và 132.

Cách 4: Bạn vẽ thanh dữ liệu trong ô bằng Data Bars
Data Bars vẽ một thanh màu trong mỗi ô, và giá trị càng lớn thì thanh càng dài. Cách này giúp bạn so sánh nhanh mà không cần vẽ biểu đồ.
Bạn quét chọn vùng C2:C13, rồi chọn Conditional Formatting, Data Bars và bấm một màu trong nhóm Gradient Fill hoặc Solid Fill.

Mỗi ô ở cột Doanh số sẽ có một thanh màu theo độ lớn của con số.

Cách 5: Bạn tô cả dòng bằng công thức
Các cách trên chỉ tô đúng ô chứa giá trị. Khi muốn tô cả dòng, bạn cần dùng công thức. Yêu cầu của cách này là tô cả dòng của những nhân viên có doanh số thấp hơn chỉ tiêu.
Đầu tiên, bạn quét chọn vùng A2:E13 từ ô A2. Sau đó, bạn chọn Conditional Formatting, New Rule.

Hộp thoại New Formatting Rule hiện ra. Bạn chọn dòng Use a formula to determine which cells to format, rồi gõ công thức sau vào ô bên dưới:
=$C2<$D2

Dấu $ trước C và D giữ cố định cột, còn số 2 không có dấu $ nên tự đổi theo từng dòng. Đây là điểm quyết định để Excel tô cả dòng. Bạn viết công thức theo dòng đầu tiên của vùng đã chọn, tức là dòng 2.
Tiếp theo, bạn bấm nút Format, mở thẻ Fill, chọn một màu rồi bấm OK hai lần.

Excel tô cả dòng của 6 nhân viên chưa đạt chỉ tiêu, gồm Bình, Cường, Hoa, Lan, Minh và Quỳnh. Dòng của Hùng không được tô vì doanh số bằng chỉ tiêu, và công thức chỉ xét trường hợp thấp hơn.

Màu sẽ tự đổi khi số liệu thay đổi. Ví dụ, khi bạn sửa doanh số của Bình ở ô C3 từ 96 thành 130, dòng của Bình không còn màu. Cột Kết quả trong file là chữ gõ sẵn, nên ô E3 vẫn ghi Chưa đạt sau khi bạn sửa số. Nếu muốn cột này tự đổi theo, bạn có thể dùng hàm IF trong Excel.

Bạn sửa và xóa quy tắc tô màu như thế nào?
Để xem hoặc sửa quy tắc, bạn chọn vùng có màu rồi chọn Conditional Formatting, Manage Rules. Hộp thoại này liệt kê các quy tắc, công thức, định dạng và vùng áp dụng. Bạn bấm vào dòng quy tắc rồi bấm Edit Rule để sửa công thức hoặc màu.

Để xóa quy tắc, bạn chọn Conditional Formatting, Clear Rules, rồi chọn Clear Rules from Selected Cells cho vùng đang chọn hoặc Clear Rules from Entire Sheet cho cả sheet.

Người mới thường hỏi gì về tô màu có điều kiện trong Excel?
Vì sao tôi không xóa được màu bằng nút Fill Color?
Màu do quy tắc tạo ra không phải màu tô thông thường, nên nút Fill Color không xóa được. Bạn cần dùng Clear Rules như phần trên.
Vì sao công thức tô sai dòng?
Lỗi này thường xảy ra vì công thức không viết theo dòng đầu tiên của vùng chọn. Ví dụ, với vùng A2:E13, công thức =$C3<$D3 sẽ tô lệch sang dòng khác. Bạn cũng không được thêm dấu $ trước số dòng, vì công thức =$C$2<$D$2 chỉ xét dòng 2 và không tô đúng từng dòng.
Tôi có thể lọc theo màu vừa tô không?
Bạn có thể lọc được, vì Filter nhận cả màu do Conditional Formatting tạo ra. Bạn bấm nút Filter trên tab Data, bấm mũi tên ở cột có màu, chọn Filter by Color rồi bấm vào màu cần xem.
Bạn muốn dùng Excel thành thạo cho công việc văn phòng?
Tô màu có điều kiện giúp người đọc báo cáo nhìn ra ngay điểm cần chú ý, và bạn không phải tô tay lại mỗi khi số liệu đổi.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.