Định dạng có điều kiện trong Excel: tô màu tự động
Dùng Conditional Formatting trong Excel và Google Sheets để tô màu ô theo điều kiện, tô cả dòng bằng công thức, cảnh báo hạn sắp đến và dữ liệu trùng.
Một bảng theo dõi công nợ hay tiến độ có hàng trăm dòng, nhìn bằng mắt rất dễ sót. Conditional Formatting (Định dạng có điều kiện) cho phép Excel tự tô màu những ô cần chú ý: doanh số dưới chỉ tiêu, hợp đồng sắp hết hạn, mã hàng bị nhập trùng… Dữ liệu thay đổi thì màu cũng tự cập nhật.
1. Tô màu nhanh với quy tắc có sẵn
- Chọn vùng dữ liệu, ví dụ cột doanh số
C2:C200. - Home → Conditional Formatting → Highlight Cells Rules.
- Chọn kiểu điều kiện: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values.
- Nhập giá trị (ví dụ nhỏ hơn 50.000.000), chọn màu → OK.
Nhóm Top/Bottom Rules giúp tô 10 giá trị cao nhất hoặc các ô dưới mức trung bình. Còn Data Bars, Color Scales và Icon Sets biến cột số thành biểu đồ mini ngay trong ô — rất hợp với báo cáo gửi sếp.
2. Tô màu cả dòng bằng công thức
Quy tắc có sẵn chỉ tô đúng ô thoả điều kiện. Muốn cả dòng sáng lên (ví dụ dòng có trạng thái “Chưa thanh toán”), cần dùng công thức:
- Chọn toàn bộ vùng dữ liệu bắt đầu từ dòng 2, ví dụ
A2:F200(không chọn dòng tiêu đề). - Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Nhập công thức:
=$E2="Chưa thanh toán" - Bấm Format → thẻ Fill chọn màu nền → OK.
$ trước chữ E khoá cột, để ô nào trên dòng đó cũng nhìn vào cột E; số 2 không có $ nên Excel tự đổi thành 3, 4, 5… cho các dòng dưới. Viết =$E$2 là sai — cả bảng sẽ cùng tô hoặc cùng không.3. Cảnh báo hạn sắp đến và đã quá hạn
Giả sử cột D là ngày hết hạn hợp đồng. Tạo hai quy tắc trên vùng A2:F200:
| Mục đích | Công thức | Màu gợi ý |
|---|---|---|
| Đã quá hạn | =AND($D2<>"", $D2<TODAY()) | Đỏ nhạt |
| Còn 7 ngày trở xuống | =AND($D2>=TODAY(), $D2-TODAY()<=7) | Vàng |
| Đã hoàn thành (cột F ghi “Xong”) | =$F2="Xong" | Xám |
Phần $D2<>"" giúp bỏ qua dòng chưa nhập ngày (ô trống được Excel coi là số 0, luôn “quá hạn”). Nếu ngày trong cột D đang ở dạng chữ chứ không phải ngày thật, quy tắc sẽ không chạy — xem cách xử lý ngày tháng trong Excel.
4. Quản lý thứ tự và sửa quy tắc
Vào Conditional Formatting → Manage Rules, ở ô Show formatting rules for chọn This Worksheet để thấy tất cả quy tắc.
- Quy tắc nằm trên được ưu tiên. Dùng mũi tên lên/xuống để sắp xếp: “Quá hạn” nên đứng trên “Sắp đến hạn”.
- Tích Stop If True nếu không muốn quy tắc phía dưới áp thêm màu.
- Cột Applies to cho biết quy tắc áp dụng cho vùng nào; sửa trực tiếp tại đây khi bảng dài thêm.
- Xoá hết: Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet.
5. Làm trên Google Sheets
- Chọn vùng dữ liệu → Format → Conditional formatting.
- Ở Format cells if… chọn điều kiện có sẵn, hoặc chọn Custom formula is và nhập công thức giống Excel, ví dụ
=$E2="Chưa thanh toán". - Chọn màu ở Formatting style → Done. Thẻ Color scale dùng để tô dải màu.
Kết hợp định dạng có điều kiện với danh sách thả xuống cho cột trạng thái, bảng theo dõi của bạn sẽ vừa nhập nhanh vừa nhìn là hiểu.