Cách tô màu ô trống và đếm số ô trống trong Excel với Conditional Formatting

Với bảng dữ liệu cần đảm bảo tính đầy đủ, muốn kiểm tra xem ô dữ liệu nào đang trống hay chứa giá trị rỗng. Ta cần tô màu ô trống để làm nổi bật lên trong bảng dữ liệu để quan sát, ngoài ra còn muốn tính tổng số ô trống để thống kê. Để làm điều này, ta có các cách sau:

1. Để tô màu các ô trống:
   • Cách 1 – sử dụng Conditional Formatting: Chọn vùng dữ liệu ➜ Home ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cells to format ➜ nhập công thức:
     • =LEN(TRIM($A2))=0 nếu muốn tô màu ô không chứa giá trị, hoặc chứ dấu cách.
     • =ISBLANK($A2) nếu chỉ muốn tô màu ô trống thật.
     • Sau khi nhập công thức chọn ➜ Format… chọn Fill ➜ OK ➜ OK
   • Cách 2 – sử dụng Find & Select: Chọn vùng dữ liệu ➜ Home ➜ Find & Select ➜ Go To Special ➜ Blanks ➜ OK ➜ tô màu Fill Color.
2. Để đếm các ô trống: sử dụng hàm COUNTBLANK với cấu trúc =COUNTBLANK(range)

Dưới đây là hướng dẫn chi tiết với hình ảnh minh họa để cho các cách tô màu và đếm ô trống trong Excel:

1. Tô màu các ô trống trong Excel

Cách 1: Sử dụng Conditional Formatting

Để tô màu các ô trống một cách tự động và nhanh nhất, thì ta nên sử dụng Conditional Formatting. Với cách này ta có thể tô các ô trống với nhiều điều kiện khác nhau, kể cả các các ô chứa dấu cách, chứa kết quả rỗng do công thức, hay ô trống thật sự. Dưới đây là các cách cụ thể để áp dụng:

1. Conditional Formatting với “Blanks”:

Áp dụng khi: Chỉ cần tô màu các ô trống cơ bản (không xét trường hợp ô có công thức trả về "" hay chứa khoảng trắng)

Ví dụ với bảng dữ liệu mẫu dưới đây:

Ta tô màu các ô trống không tính đến các điều kiện như sau:

• Bước 1: Chọn vùng dữ liệu (ở ví dụ: A1:E11).
• Bước 2: Vào Home ➜ Conditional Formatting ➜ New Rule.
• Bước 3: Chọn Format only cells that contain.
• Bước 4: Ở ô điều kiện, chọn Blanks.
• Bước 5: Nhấn Format… ➜ thẻ Fill ➜ chọn màu ➜ OK ➜ OK

2. Sử dụng Conditional Formatting với công thức:

Bằng cách sử dụng công thức trong Conditional Formatting, ta có thể phân biệt nhiều loại ô trống khác nhau. Ví dụ như ô trống chưa nhập dữ liệu, ô có công thức trả về chuỗi rỗng, ô chỉ chứa khoảng trắng,…

Công thức để tô màu các ô trống như sau:

Giả sử ta vẫn tiếp tục sử dụng bảng dữ liệu:

Giờ ta chỉ muốn tô màu các ô thực sự trống không chứa gì thì ta sẽ thực hiện như sau:

• B1: Chọn toàn bộ vùng dữ liệu bạn muốn kiểm tra (ví dụ A1:A11). Lưu ý nên chỉ chọn vùng theo cột hoặc hàng rồi làm tương tự với các hàng và cột khác.
• B2: Home ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cells to format.
• B3: Nhập công thức: =ISBLANK(A1) (Nhớ chọn A1 là ô đầu vùng chọn)
• B4: Format… ➜ Fill chọn màu ➜ OK ➜ OK.

3. Tô màu cả hàng nếu chứa ô trống

Ngoài ra ta cũng có thể sử dụng conditional Formatting với công thức để tô màu cả hàng khi chứa ô trống. Ví dụ vẫn sử dụng bảng dữ liệu trên ta làm như sau:

• B1: Chọn toàn bộ vùng dữ liệu bạn muốn kiểm tra (ví dụ A1:E11).
• B2: Home ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cells to format.
• B3: Nhập công thức: =COUNTBLANK($A1:$E1)>0 (Nhớ chọn A1 là ô đầu vùng chọn)
• B4: Format… ➜ Fill chọn màu ➜ OK ➜ OK.

Cách 2: Sử dụng Find & Select

Để tô màu ô trống trong Excel, ta cũng có thể sử dụng Find & Select như sau:

• B1: Chọn vùng dữ liệu.
• B2: Home ➜ Find & Select ➜ Go To Special ➜ chọn Blanks ➜ OK.
• B3: Tô màu bằng Fill Color.

Cụ thể, ta vẫn tiếp tục với bảng dữ liệu đang sử dụng:

Giờ ta sẽ sử dụng Go To Special thay vì sử dụng Conditional Formatting như sau:

• Bôi đen vùng dữ liệu từ A1:E11
• Vào thẻ Home => chọn Find & Select => tiếp tục chọn Go to Special => sau đó chọn Blanks => rồi nhấn Ok để hoàn thành.

2. Đếm số ô trống trong Excel

Ví dụ với bảng dữ liệu dưới đây, ta muốn kiểm soát xem có bao nhiêu ô trống?

Để đếm số lượng ô trống trong bảng dữ liệu trên, ta sử dụng hàm COUNTBLANK với công thức như sau:

=COUNTBLANK(range) trong đó Range là mảng muốn đếm các ô trống.

Áp dụng công thức hàm vào ví dụ:

=COUNTBLANK(C1:E11)

Trên đây là hướng dẫn cách tô màu và đếm các ô trống trong Excel. Trong bài có sử dụng hình ảnh minh họa bằng Excel online nên có thể giao diện làm việc không giống Excel offline nhưng các bước và cách làm đều giống hệt nhau. Mong rằng bài viết này hữu ích và chúc bạn áp dụng thành công!