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)

Bạn dùng tài liệu này mà bị vướng ở đâu? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp

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.

Lệnh Greater Than trong menu Highlight Cells Rules
Con trỏ chuột chỉ vào lệnh Greater Than trong menu Highlight Cells Rules.

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.

Ô giá trị trong hộp thoại Greater Than chứa số 120
Excel xem trước màu tô ngay khi bạn gõ xong con số 120.

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

Bốn ô doanh số lớn hơn 120 được tô màu
Bốn ô doanh số lớn hơn 120 được tô màu đỏ nhạt.

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.

Hộp thoại Text That Contains chứa chữ Chưa đạt
Ô điều kiện chứa đoạn chữ Chưa đạt.

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

Các ô Chưa đạt ở cột Kết quả được tô màu
Sáu ô Chưa đạt ở cột Kết quả được tô màu đỏ nhạ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.

Lệnh Top 10 Items trong menu Top/Bottom Rules
Con trỏ chuột chỉ vào lệnh Top 10 Items trong menu Top/Bottom Rules.

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

Hộp thoại Top 10 Items đổi thành số 3
Bạn đổi số 10 thành số 3 để Excel chỉ tô 3 giá trị cao nhất.

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

Ba doanh số cao nhất được tô màu
Ba doanh số cao nhất là 145, 139 và 132 được tô màu.

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.

Lựa chọn màu trong menu Data Bars
Con trỏ chuột chỉ vào thanh màu xanh lá trong nhóm Gradient Fill.

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

Cột Doanh số có thanh màu xanh trong từng ô
Ô có doanh số cao nhất có thanh màu dài nhất.

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.

Lệnh New Rule trong menu Conditional Formatting
Con trỏ chuột chỉ vào lệnh New Rule sau khi bạn đã chọn vùng A2:E13.

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

Công thức trong hộp thoại New Formatting Rule
Ô công thức chứa điều kiện so sánh cột C với cột D.

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.

Thẻ Fill trong hộp thoại Format Cells với màu cam nhạt
Ô Sample bên dưới hiện màu mà bạn vừa chọ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.

Các dòng có doanh số thấp hơn chỉ tiêu được tô màu cam nhạt
Sáu dòng có doanh số thấp hơn chỉ tiêu được tô màu cam nhạt.

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.

Dòng của Bình hết màu sau khi sửa doanh số thành 130
Dòng 3 không còn màu sau khi doanh số được sửa thành 130.

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.

Hộp thoại Conditional Formatting Rules Manager
Con trỏ chuột chỉ vào nút Edit Rule, và hộp thoại đang hiện quy tắc với công thức =$C2<$D2.

Để 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.

Lệnh Clear Rules from Entire Sheet trong menu Clear Rules
Con trỏ chuột chỉ vào lệnh xóa quy tắc trên toàn bộ 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.