Định dạng có điều kiện (conditional formatting) thay đổi cách một ô hiển thị (màu nền, màu chữ, đường viền) khi một điều kiện đúng. Chọn các ô, vào Home > Conditional Formatting, rồi chọn một quy tắc có sẵn, hoặc chọn New Rule > Use a formula to determine which cells to format và gõ một công thức như =$C2>100, công thức này tô màu mọi hàng có số tiền ở cột C lớn hơn 100.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid |
| 3 | 1002 | South | 85 | 2026-03-10 | Open |
| 4 | 1003 | North | 240 | 2026-03-12 | Open |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid |
| 6 | 1005 | South | 150 | 2026-03-25 | Open |
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Hàng 2, 4 và 6 được tô màu. Đổi C3 thành 100 thì không có gì xảy ra, vì quy tắc là lớn hơn 100; đổi thành 101 thì hàng 3 sáng lên. Quy tắc được hiển thị bên dưới lưới ô, và bạn có thể bấm vào để sửa.
Cách thêm một quy tắc định dạng có điều kiện
Menu có sẵn các quy tắc cho những trường hợp phổ biến:
- Highlight Cells Rules, các quy tắc tô màu ô: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values.
- Top/Bottom Rules, các quy tắc lấy nhóm đầu và cuối: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average (có thể đổi số 10).
- Data Bars, Color Scales và Icon Sets: chúng tô đậm nhạt từng ô theo độ lớn thay vì kiểm tra một điều kiện.
Với mọi trường hợp khác, hãy viết một quy tắc công thức:
- Chọn các ô cần định dạng, bắt đầu từ ô trên cùng bên trái để nó là ô đang hoạt động. Với trang tính phía trên, đó là A2:E7.
- Vào Home > Conditional Formatting > New Rule.
- Chọn Use a formula to determine which cells to format.
- Gõ công thức cho ô đang hoạt động. Công thức phải trả về TRUE hoặc FALSE:
=$C2>100. - Bấm Format, chọn màu nền hoặc màu chữ, rồi nhấn OK hai lần.
Excel áp công thức cho từng ô trong vùng giống như một công thức được điền xuống và sang ngang, nên các dấu đô la quyết định mỗi ô sẽ nhìn vào đâu. $C2 nghĩa là "cột C, hàng này": cột cố định, hàng dịch chuyển. Các quy tắc trên trang này hiển thị đúng những công thức bạn sẽ gõ vào ô đó.
Tô màu ô lớn hơn giá trị trong một ô khác
Trỏ quy tắc tới một ô thay vì gõ thẳng con số giúp bạn dễ dàng thay đổi ngưỡng. Quy tắc dưới đây chỉ tô màu các ô Amount, và so sánh từng ô với F2; các dấu $ trong $F$2 giữ cho mọi ô đều nhìn vào F2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Limit | ||
| 2 | 1001 | North | 120 | 100 | ||
| 3 | 1002 | South | 85 | |||
| 4 | 1003 | North | 240 | |||
| 5 | 1004 | East | 60 | |||
| 6 | 1005 | South | 150 | |||
| 7 | 1006 | East | 95 |
C2, C4 và C6 được tô màu. Gõ 90 vào F2 thì C7 cũng được tô; gõ 200 thì chỉ còn C4. Quy tắc có sẵn Highlight Cells Rules > Greater Than cũng chấp nhận tham chiếu ô trong ô nhập của nó (=$F$2).
Tô màu hàng theo văn bản
Điều kiện văn bản được đặt trong ngoặc kép. =$B2="North" tô màu mọi hàng có khu vực là North. Phép so sánh bằng = không phân biệt chữ hoa chữ thường, nên north cũng khớp. Để tìm văn bản chỉ chứa một từ, hãy dùng SEARCH bên trong ISNUMBER: SEARCH trả về vị trí của từ đó, hoặc báo lỗi khi không có, và ISNUMBER biến kết quả đó thành TRUE hoặc FALSE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Amount | Note |
| 2 | 1001 | North | 120 | Paid on time |
| 3 | 1002 | South | 85 | Late, called twice |
| 4 | 1003 | North | 240 | |
| 5 | 1004 | East | 60 | Paid late |
| 6 | 1005 | South | 150 | Waiting for invoice |
| 7 | 1006 | East | 95 |
Hàng 2 và hàng 4 được tô màu vì là North, còn D3 và D5 chứa "late" (một ô viết là Late). Đổi B7 thành North thì hàng 7 cũng được tô. Quy tắc có sẵn Highlight Cells Rules > Text that Contains làm được việc giống quy tắc SEARCH cho các ô đã chọn.
Tô màu ngày quá hạn
Một đơn hàng đang mở bị quá hạn khi ngày đến hạn nằm trước hôm nay. Trong Excel, quy tắc là =AND($E2="Open",$D2<TODAY()), và màu sẽ cập nhật mỗi ngày. Trang tính dưới đây dùng một ngày cố định trong G2 thay cho TODAY(), để ví dụ trông giống nhau bất cứ khi nào bạn đọc: =AND($E2="Open",$D2<$G$2).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status | Today | |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid | 2026-03-15 | |
| 3 | 1002 | South | 85 | 2026-03-10 | Open | ||
| 4 | 1003 | North | 240 | 2026-03-12 | Open | ||
| 5 | 1004 | East | 60 | 2026-03-20 | Paid | ||
| 6 | 1005 | South | 150 | 2026-03-25 | Open | ||
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Với 2026-03-15 trong G2, hàng 3, 4 và 7 bị quá hạn. Hàng 2 có ngày sớm hơn nhưng đã thanh toán, nên AND giữ nó không tô màu. Quy tắc thứ hai tô màu các ô D sắp đến hạn trong bảy ngày tới: hiện chưa có ô nào. Đổi G2 thành 2026-03-20 thì D6 (đến hạn ngày 2026-03-25) được quy tắc này tô màu. Đổi E3 thành Paid thì hàng 3 không còn được tô.
Với "đến hạn tuần này" hay "tháng trước" mà không cần công thức, quy tắc có sẵn Highlight Cells Rules > A Date Occurring có những lựa chọn đó.
Tô màu ô trống, hoặc hàng bị thiếu giá trị
=B2="" là TRUE với ô trống (và với công thức trả về văn bản rỗng). Đặt $ trước cột để tô màu cả hàng khi có một ô trong hàng bị trống: =$C2="". Quy tắc có sẵn tương ứng là New Rule > Format only cells that contain > Blanks.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Amount | Status |
| 2 | 1001 | North | 120 | Paid |
| 3 | 1002 | South | Open | |
| 4 | 1003 | North | 240 | Open |
| 5 | 1004 | East | Paid | |
| 6 | 1005 | South | 150 | Open |
| 7 | 1006 | East | 95 | Open |
Hàng 3 và hàng 5 được tô màu. Gõ một số tiền vào C3 thì hàng đó trở lại bình thường. =ISBLANK($C2) cũng dùng được ở đây, nhưng ISBLANK là FALSE với ô chứa công thức trả về "", còn =$C2="" là TRUE trong cả hai trường hợp.
Đếm những gì quy tắc tô màu
Quy tắc không bao giờ cho ra một con số, nhưng cùng điều kiện đó đặt trong COUNTIFS hoặc SUMPRODUCT thì có. Đếm các đơn hàng đang mở đã quá hạn ở phần trên, với ngày trong G2, cần hai điều kiện: Status là Open, và Due trước G2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status | Today | |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid | 2026-03-15 | |
| 3 | 1002 | South | 85 | 2026-03-10 | Open | ||
| 4 | 1003 | North | 240 | 2026-03-12 | Open | Overdue | |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid | ||
| 6 | 1005 | South | 150 | 2026-03-25 | Open | ||
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Đến lượt bạn: Trong G4, đếm số đơn hàng đang mở có ngày đến hạn trước ngày trong G2.
Đáp án là 3, đúng bằng số hàng được tô màu. Điều kiện "<"&G2 nối dấu nhỏ hơn với ngày trong G2; nếu viết "<G2" thì công thức sẽ so sánh với chữ G2. Xem thêm về kiểu điều kiện này ở trang COUNTIFS.
Thang màu, thanh dữ liệu và bộ biểu tượng
Ba kiểu này tô đậm nhạt mọi ô theo giá trị của nó thay vì bật tắt theo một điều kiện. Chọn các con số rồi chọn một kiểu trong Home > Conditional Formatting:
- Color Scales chuyển từ một màu cho giá trị thấp nhất sang một màu khác cho giá trị cao nhất (mẫu đầu tiên trong danh sách tô giá trị cao nhất màu xanh lá, giá trị giữa màu vàng và giá trị thấp nhất màu đỏ). Phù hợp để làm bản đồ nhiệt cho một bảng số.
- Data Bars vẽ một thanh bên trong mỗi ô, dài theo giá trị của ô so với các ô khác. Trong More Rules, bạn có thể chọn Show Bar Only để ẩn con số.
- Icon Sets đặt một mũi tên, lá cờ hoặc đèn giao thông trước giá trị, mặc định chia vùng giá trị thành ba phần bằng nhau.
Để đổi ngưỡng của bất kỳ kiểu nào, vào Home > Conditional Formatting > Manage Rules > Edit Rule, nơi mỗi màu hay biểu tượng có thể gắn với một con số, một phần trăm, một phân vị hoặc một công thức.
Lỗi thường gặp: đặt sai dấu đô la
Ba phiên bản của cùng một quy tắc, áp dụng cho A2:E7, cho ra ba kết quả khác nhau:
| Quy tắc | Mỗi ô kiểm tra gì | Kết quả |
|---|---|---|
=$C2>100 | cột C của chính hàng đó | cả hàng được tô màu theo số tiền |
=C2>100 | ô cách nó hai cột về bên phải: A2 kiểm tra C2, B2 kiểm tra D2 | chỉ cột A đi theo số tiền; B và C luôn bị tô màu (ngày tháng và văn bản đều được tính là lớn hơn 100), D và E không bao giờ được tô |
=$C$2>100 | luôn là C2 | mọi hàng đều được tô màu, hoặc không hàng nào |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid |
| 3 | 1002 | South | 85 | 2026-03-10 | Open |
| 4 | 1003 | North | 240 | 2026-03-12 | Open |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid |
| 6 | 1005 | South | 150 | 2026-03-25 | Open |
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Mọi ô đều được tô màu vì C2 là 120. Đổi C2 thành 50 thì mọi màu biến mất cùng lúc. Bấm vào quy tắc và xóa dấu $ trước số 2 để có =$C2>100, lúc đó màu lại đi theo từng hàng. Quy tắc về việc khóa phần nào này cũng áp dụng cho công thức bạn điền xuống, được giải thích ở trang tham chiếu tuyệt đối.
Câu hỏi thường gặp
Làm sao để định dạng có điều kiện dựa vào một ô khác?
Chọn các ô cần tô màu, vào Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, rồi viết công thức cho ô đầu tiên được chọn, trỏ tới ô kia: =$C2>100 tô màu cả hàng khi cột C của hàng đó lớn hơn 100.
Làm sao để tô màu cả hàng bằng định dạng có điều kiện?
Chọn cả bảng, không chỉ một cột, và đặt $ trước chữ cái cột của ô cần kiểm tra: =$E2="Open". Cột được giữ cố định còn số hàng thì dịch chuyển, nên mọi ô trong một hàng đều kiểm tra cùng một ô.
Làm sao để định dạng có điều kiện với nhiều điều kiện?
Kết hợp chúng bằng AND hoặc OR trong một quy tắc công thức: =AND($E2="Open",$D2<TODAY()) tô màu các đơn hàng chưa thanh toán đã quá hạn. Nhiều quy tắc riêng trên cùng một vùng đều được áp dụng; khi hai quy tắc cùng đặt một kiểu định dạng, chẳng hạn màu nền, quy tắc nằm cao hơn trong Home > Conditional Formatting > Manage Rules sẽ được ưu tiên.
Làm sao để tô màu các ô chứa một đoạn văn bản cụ thể?
Dùng Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, hoặc quy tắc công thức =ISNUMBER(SEARCH("late",A2)), trả về TRUE khi A2 chứa chữ late, viết hoa hay thường đều được.
Vì sao công thức định dạng có điều kiện không hoạt động?
Thường là do dấu đô la: =$C$2>100 chỉ kiểm tra C2 cho mọi ô, còn =C2>100 trên cả một hàng thì mỗi ô lại kiểm tra một cột khác nhau. Ngoài ra, hãy kiểm tra công thức đã được viết cho ô đầu tiên của vùng ghi trong Applies to ở Manage Rules chưa.
