Cách sử dụng định dạng có điều kiện trong Microsoft Excel 2016

Giới thiệu

Định dạng có điều kiện (Conditional Formatting) là một công cụ mạnh mẽ và có thể thay đổi “diện mạo” của một ô dựa trên giá trị của nó, giúp người xem xác định dữ liệu quan trọng nhanh chóng. Bạn có thể thêm màu sắc, icon, thanh dữ liệu và thang màu vào ô bằng cách tạo quy tắc định dạng có điều kiện.

Đánh dấu các giá trị trùng lặp bằng màu đỏ

Định dạng có điều kiện có thể được sử dụng để làm nổi bật các giá trị trùng lặp, giúp dễ dàng xác định và sửa lỗi trong dữ liệu.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là các ô từ A2: A9.

Bước 2. Chuyển đến Home > Conditional Formatting.

Bước 3.Click vào Highlight Cells Rules > Duplicate Values.

Bước 4. Click OK. Bạn cũng có thể chọn các màu ô khác từ danh sách thả xuống bên cạnh danh sách mặc định.

Sử dụng Data Bars để tạo Progress Bars (thanh tiến trình)

Data Bars có thể được sử dụng để thể hiện dữ liệu bằng đồ thị bên trong một ô. Thanh dài nhất thể hiện giá trị cao nhất và thanh ngắn nhất cho thấy giá trị nhỏ nhất. Data Bars này giúp người dùng phát hiện số lượng lớn và nhỏ dễ dàng trong bảng tính.

Trong ví dụ này, bạn sẽ xem nhanh tiến trình của các dự án.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: B6.

Bước 2. Đi tới Home > Conditional Formatting.

Bước 3. Click vào Data Bars > Blue Data Bar.

Bước 4. Bạn cũng có thể chọn các thanh màu khác nếu muốn.

Xem thêm: Cách tạo Progress bar bằng định dạng có điều kiện trong Excel 2013, 2010 và 2007

Sử dụng các quy tắc Top/Bottom Rules để làm nổi bật 3 sản phẩm hàng đầu

Một cách tuyệt vời khác để sử dụng định dạng có điều kiện là chọn tùy chọn Top/Bottom Rules. Bạn có thể dễ dàng đánh dấu 3 mục hàng đầu trong danh sách. Để xác định ba sản phẩm hàng đầu tạo ra doanh số bán hàng nhiều nhất trong cửa hàng, bạn có thể tạo một quy tắc hàng đầu trong định dạng có điều kiện.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: B8.

Bước 2. Vào Home > Conditional Formatting.

Bước 3. Click vào Top/Bottom Rules > Top 10 Items…

Bước 4. Mặc định là 10 mục nhưng bạn có thể nhập số 3 hoặc sử dụng nút thêm hoặc bớt.

Bước 5. Bạn cũng có thể chọn các màu ô khác từ danh sách thả xuống bên cạnh danh sách mặc định.

Sử dụng Color Scales để tạo ra một bản đồ nhiệt

Bản đồ nhiệt là gì?

Bản đồ nhiệt là bảng mà dữ liệu trong bảng tính được hiển thị bằng màu sắc. Bạn có thể hiển thị giá trị trong ô hoặc không nếu muốn. Ẩn giá trị bằng cách sử dụng định dạng ô.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là B2: D10.

Bước 2. Vào Home > Conditional Formatting.

Bước 3. Click vào Color Scales > More Rules…

Bước 4. Mặc định là 2-Color Scale nhưng bạn có thể chọn 3-Color Scale từ danh sách sổ xuống.

Bước 5. Đối với ví dụ này, màu xanh nhạt, màu xanh và màu xanh đậm được chọn. Khi chọn màu cho bản đồ nhiệt, bạn nên chọn tông màu khác nhau cho cùng một màu. Bởi vì nếu chọn nhiều màu khác nhau sẽ làm người xem nhầm lẫn, khó xác định ô nào có giá trị thấp nhất hoặc cao nhất. Tuy nhiên, khi chọn tông màu khác nhau cho cùng một màu, người xem có thể nhanh chóng nhận ra giá trị cao nhất và thấp nhất.

Ngoài ra, nếu muốn ẩn các giá trị trong bảng và chỉ hiển thị màu trong bản đồ nhiệt, bạn có thể click chuột phải vào bảng tính, click vào Format cells > Custom > Enter “;;;” trong kiểu hộp và click vào OK. Giá trị vẫn ở trong bảng nhưng được ẩn.

Bản đồ nhiệt sau khi ẩn giá trị:

Sử dụng Icon Sets để xác định dữ liệu bị thiếu trong Timesheet

Nhiều ý kiến cho rằng, định dạng có điều kiện chỉ có thể được sử dụng để định dạng ô dựa trên giá trị của nó. Tuy nhiên, bạn có thể sử dụng công thức để nhận giá trị dựa trên các ô trong cùng một hàng và sau đó sử dụng định dạng có điều kiện để nhận kết quả mong muốn.

Hãy xem ví dụ này để hiểu rõ hơn. Trong ví dụ này, chúng ta sẽ thực hiện công thức trong ô E4 để tính số ô đã được điền dữ liệu từ A5 đến D5. Vì tất cả các dữ liệu cần thiết đã được lấp đầy trong hàng 5, nên công thức hiển thị kết quả 4 ô đã được điền giá trị. Tuy nhiên trong hàng 7, chỉ có 1 ô có dữ liệu và 3 ô trống. Do đó, hàng này cần được làm nổi bật bằng cách sử dụng Icon Sets để xác định hàng cần điều chỉnh.

Các bước thực hiện:

Bước 1. Chọn các ô để định dạng. Trong ví dụ này là E5: E9.

Bước 2. Chuyển đến Home > Conditional Formatting.

Bước 3. Click vào Icon Sets > More Rules…

Bước 4. Tùy chọn mặc định ở đây là 3 đèn giao thông, sau đó chọn 3 biểu tượng (hình tròn) từ danh sách thả xuống.

Bước 5. Đánh dấu vào ô “Show Icon only”.

Bước 6. Click vào biểu tượng cuối cùng và thay đổi “Red Cross Symbol” thành “No Cell Icon”.

Bước 7. Click vào biểu tượng thứ 2, thay đổi “Yellow Exclamation Symbol” thành “Red Cross Symbol”, thay đổi giá trị thành 1 và thay đổi Type từ “Percent” thành “Number”.

Bước 8. Đối với biểu tượng đầu tiên là “Green Check Symbol”, thay đổi giá trị thành 4 và thay đổi Type từ “Percent” thành “Number”.

Sau khi hoàn thành các bước trên, nếu có dữ liệu chưa hoàn thành trong bất kỳ ô nào trong cột A-D của cùng một hàng, sẽ có một dấu gạch chéo trong cột E. Điều này sẽ cho phép người dùng nhanh chóng xác định dữ liệu chưa hoàn chỉnh và sửa chữa nó. Khi tất cả bốn ô từ cột A-D được nhập vào cùng hàng, dấu check màu xanh lá cây sẽ xuất hiện. Điều này sẽ cho phép người dùng biết rằng tất cả dữ liệu đã được nhập và không thiếu dữ liệu.

Xóa quy tắc

Xác định các ô có chứa định dạng có điều kiện

Trước khi xóa mọi định dạng có điều kiện, bạn cần xác định ô chứa định dạng có điều kiện. Để làm điều này, chọn tất cả các ô trên bảng tính sử dụng Ctrl + A, click vào Special > Conditional formats > OK.

Thao tác này sẽ cho bạn thấy các ô được đánh dấu từ E5 đến E9 chứa định dạng có điều kiện.

Xóa ô chứa định dạng có điều kiện

Các bước thực hiện:

Bước 1. Vào Home > Conditional Formatting.

Bước 2. Click vào Clear Rules > Clear Rules from Selected Cells.

Xóa nhiều quy tắc trong cùng một trang tính

Ngoài ra, cũng có một cách khác để xóa định dạng có điều kiện. Phương thức này sẽ hiển thị tất cả các ô sử dụng định dạng có điều kiện trên cùng một trang tính và cho phép bạn xóa nhiều quy tắc một cách dễ dàng.

Các bước thực hiện:

Bước 1. Vào Home > Conditional Formatting.

Bước 2. Click vào Manage Rules.

Bước 3. Thay đổi quy tắc Show formatting từ Current Selection thành This Worksheet. Điều này sẽ hiển thị tất cả các quy tắc trong bảng tính này.

Bước 4. Click vào quy tắc bạn muốn xóa → click vào Delete Rule → OK.

Bước 5. Lặp lại Bước 4 đối với các quy tắc mà bạn muốn xóa.

Chúc các bạn thực hiện thành công!

Xem thêm:

• Sử dụng định dạng điều kiện để định dạng hàng chẵn, lẻ
• Hướng dẫn toàn tập Excel 2016 (Phần 1): Làm quen với Microsoft Excel
• 5 ác mộng đối với Excel và cách khắc phục