Để tô màu dữ liệu trùng trong Excel, chọn các ô rồi vào Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, sau đó nhấn OK. Nếu muốn tự quyết định thế nào là trùng (cả hàng, chỉ bản sao thứ hai, hai cột cùng lúc), hãy dùng quy tắc công thức: =COUNTIF($A$2:$A$9,A2)>1 tô màu mọi ô có giá trị xuất hiện nhiều hơn một lần trong A2:A9.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Ana và Ben được tô màu ở mọi hàng có tên họ: A2, A3, A5, A7 và A8. Quy tắc hiện ngay dưới lưới. Đổi A4 thành Eve và cả hai ô Eve cùng sáng lên.
Tô màu dữ liệu trùng bằng quy tắc Duplicate Values
- Chọn các ô cần kiểm tra, ví dụ A2:A9. Đừng chọn hàng tiêu đề.
- Vào Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Giữ Duplicate ở ô đầu tiên, hoặc đổi sang Unique để tô các giá trị chỉ xuất hiện một lần.
- Chọn định dạng (mặc định là Light Red Fill with Dark Red Text) rồi nhấn OK.
Quy tắc này không phân biệt chữ hoa chữ thường, nên ana và Ana được tô như dữ liệu trùng. Nó luôn tô mọi bản sao, kể cả bản đầu tiên, và chỉ so sánh từng ô riêng lẻ: nó không thể tô cả hàng hay kiểm tra hai cột cùng lúc. Những trường hợp đó cần quy tắc công thức.
Tô màu dữ liệu trùng bằng công thức
Quy tắc công thức tô một ô khi công thức trả về TRUE cho ô đó. Bạn viết công thức cho ô đầu tiên của vùng chọn, rồi Excel áp nó sang từng ô còn lại giống như khi bạn kéo công thức xuống.
- Chọn A2:A9, với A2 là ô hiện hành.
- Vào Home > Conditional Formatting > New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập
=COUNTIF($A$2:$A$9,A2)>1. - Bấm Format, chọn màu nền ở thẻ Fill rồi nhấn OK hai lần.
Vùng $A$2:$A$9 có dấu đô la, nên ô nào cũng đếm trong cùng một danh sách. A2 thì không có, nên ở hàng 5 quy tắc đọc thành A5. Trong các trang tính trên trang này, bạn có thể bấm vào một quy tắc dưới lưới, sửa nó và xem ô nào thay đổi. Các loại quy tắc khác có ở trang conditional formatting.
Chỉ tô lần xuất hiện thứ hai
Để giữ nguyên bản đầu tiên và chỉ tô các lần lặp lại, hãy cho vùng đếm kết thúc ở hàng hiện tại: =COUNTIF($A$2:A2,A2)>1. Ở A5, quy tắc thành =COUNTIF($A$2:A5,A5)>1, và Ana đã có hai lần trong A2:A5.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
A5, A7 và A8 được tô màu: đó là các hàng mà Remove Duplicates sẽ xóa. Đây là quy tắc nên dùng khi bạn muốn xem trước nút đó sẽ xóa gì trước khi bấm.
Tô cả hàng nếu một giá trị bị trùng
Chọn cả bảng, A2:C8, và khóa cột chứa khóa bằng dấu $ chỉ đặt trước chữ cái: =COUNTIF($B$2:$B$8,$B2)>1. Ở C5 quy tắc đọc thành $B5, nên mọi ô của hàng 5 đều hỏi cùng một câu về email ở cột B.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Signed up | |
| 2 | Ana | ana@mail.com | 2026-03-02 |
| 3 | Ben | ben@mail.com | 2026-03-04 |
| 4 | Ana P. | ana@mail.com | 2026-03-09 |
| 5 | Cara | cara@mail.com | 2026-03-11 |
| 6 | Dan | dan@mail.com | 2026-03-15 |
| 7 | Benjamin | ben@mail.com | 2026-03-20 |
| 8 | Eve | eve@mail.com | 2026-03-21 |
Các hàng 2, 3, 4 và 7 được tô màu: hai người đã đăng ký hai lần dưới tên khác nhau. Nếu thiếu $ trước B, tham chiếu sẽ dịch theo cột: các ô ở cột B sẽ kiểm tra ngày ở cột C, các ô ở cột C sẽ kiểm tra cột D trống, và chỉ cột A được tô màu.
Tô màu hàng trùng (hai cột phải khớp)
Khi một hàng chỉ được coi là trùng nếu cả hai cột đều lặp lại, hãy đếm bằng COUNTIFS. Ở đây một đơn hàng là trùng khi cùng một khách hàng đặt cùng một sản phẩm: =COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2)>1.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Product | Qty |
| 2 | Ana | Apple | 3 |
| 3 | Ben | Pear | 1 |
| 4 | Ana | Pear | 2 |
| 5 | Cara | Apple | 5 |
| 6 | Ana | Apple | 3 |
| 7 | Ben | Kiwi | 4 |
| 8 | Dan | Pear | 2 |
Chỉ hàng 2 và 6 được tô màu. Ana xuất hiện ba lần, nhưng chỉ hai đơn của cô là táo. Để chỉ tô bản thứ hai của cặp, ở đây cũng dùng vùng đếm mở rộng dần: =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1.
Tô màu dữ liệu trùng giữa hai cột
Để tô các tên trong một danh sách cũng có mặt ở danh sách kia, hãy đếm từng ô trong cột còn lại. Cần hai quy tắc: một cho A2:A7 tìm trong C, một cho C2:C7 tìm trong A. >0 nghĩa là "tìm thấy ít nhất một lần".
| A | B | C | |
|---|---|---|---|
| 1 | January | February | |
| 2 | Ana | Dan | |
| 3 | Ben | Fay | |
| 4 | Cara | Ana | |
| 5 | Dan | Gus | |
| 6 | Eve | Hal | |
| 7 | Fay | Ivy |
Ana, Dan và Fay có ở cả hai tháng, nên được tô màu ở cả hai cột. Quy tắc Duplicate Values khi chọn cả hai cột (giữ Ctrl, hoặc Cmd trên Mac, để chọn A2:A7 và C2:C7) cho cùng kết quả ở đây, nhưng nó cũng sẽ tô một tên lặp lại ngay trong một cột. Nếu muốn liệt kê các giá trị khớp thay vì tô màu, xem trang so sánh hai cột.
Đếm số ô trùng được tô màu
Conditional formatting tô màu ô nhưng không cho bạn con số nào. Cùng phép kiểm tra đó đặt trong SUMPRODUCT sẽ đếm được: COUNTIF(A2:A9,A2:A9) trả về số lần giá trị của mỗi ô xuất hiện, >1 biến kết quả thành TRUE hoặc FALSE, và -- biến chúng thành 1 và 0.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Highlighted | |
| 2 | Ana | ||
| 3 | Ben | ||
| 4 | Cara | ||
| 5 | Ana | ||
| 6 | Dan | ||
| 7 | Ben | ||
| 8 | Ana | ||
| 9 | Eve |
Đến lượt bạn: Trong C2, đếm xem quy tắc tô màu bao nhiêu ô trong A2:A9.
Đáp án là 5: ba Ana và hai Ben. Trong Excel 365 và 2021, =SUM(--(COUNTIF(A2:A9,A2:A9)>1)) cho cùng kết quả; trong Excel 2019, cách viết đó cần nhấn Ctrl+Shift+Enter (Cmd+Shift+Enter trên Mac), còn SUMPRODUCT thì không.
Vì sao quy tắc tô sai ô
Nguyên nhân thường gặp là thiếu $. Khi thiếu nó, vùng đếm dịch xuống theo từng ô: ở A8, quy tắc =COUNTIF(A2:A9,A2)>1 thành =COUNTIF(A8:A15,A8)>1, chỉ nhìn từ hàng 8 trở xuống và chỉ thấy một Ana.
| A | |
|---|---|
| 1 | Customer |
| 2 | Ana |
| 3 | Ben |
| 4 | Cara |
| 5 | Ana |
| 6 | Dan |
| 7 | Ben |
| 8 | Ana |
| 9 | Eve |
Chỉ A2, A3 và A5 được tô màu, còn A7 và A8 vẫn trơn dù chúng là dữ liệu trùng. Bấm vào quy tắc dưới lưới và sửa thành =COUNTIF($A$2:$A$9,A2)>1: cả năm ô cùng sáng lên. Các nguyên nhân thường gặp khác:
- Ô hiện hành không phải ô đầu tiên của vùng chọn khi viết quy tắc. Excel đọc công thức tương đối theo ô hiện hành, nên một quy tắc viết khi A9 đang được chọn sẽ lệch bảy hàng. Kiểm tra trong Home > Conditional Formatting > Manage Rules, nơi cột Applies to hiển thị vùng áp dụng.
- Các giá trị thực ra không bằng nhau:
Anacó dấu cách ở cuối là một giá trị khác. Hãy làm sạch dữ liệu trước bằng=TRIM(A2).
Câu hỏi thường gặp
Làm sao tô màu dữ liệu trùng trong Excel?
Chọn vùng dữ liệu, vào Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values rồi nhấn OK. Mọi ô có giá trị xuất hiện nhiều hơn một lần trong vùng chọn sẽ được tô nền đỏ nhạt.
Công thức nào tô màu dữ liệu trùng trong conditional formatting?
=COUNTIF($A$2:$A$9,A2)>1, nhập trong Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, khi đang chọn A2:A9. Vùng được khóa bằng $, còn tham chiếu ô A2 thì không.
Làm sao chỉ tô bản trùng thứ hai, không tô bản đầu tiên?
Dùng =COUNTIF($A$2:A2,A2)>1. Vùng đếm kết thúc ở hàng hiện tại, nên bản đầu tiên đếm được 1 và giữ nguyên, còn mọi bản sau đếm được từ 2 trở lên và được tô màu.
Làm sao tô cả hàng nếu một giá trị bị trùng?
Chọn cả bảng (ví dụ A2:C8) và dùng quy tắc khóa cột chứa khóa: =COUNTIF($B$2:$B$8,$B2)>1. Dấu $ trước B giúp mọi ô trong hàng đều nhìn vào cột B.
Làm sao bỏ màu đánh dấu dữ liệu trùng?
Chọn các ô rồi vào Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Nếu muốn sửa quy tắc, dùng Home > Conditional Formatting > Manage Rules.
