Sử dụng định dạng có điều kiện conditional formatting có khó như bạn nghĩ?

Định dạng có điều kiện (Conditional Formatting) chính là một công cụ mạnh mẽ để thay đổi “diện mạo” của một ô dựa trên giá trị của nó, giúp cho người xem xác định được dữ liệu quan trọng nhanh chóng. 

Bạn có thể thêm màu sắc cùng với 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. Với các tính năng đơn giản mà hiệu quả, Conditional Formatting trong thực tế vô cùng cần thiết, giúp cho người dùng dễ dàng phân biệt các ô tính quan trọng và nhất là khi làm việc với số lượng dữ liệu lớn cần kiểm tra.

I. Các khái niệm cơ bản về định dạng có điều kiện của Excel

1. Định dạng có điều kiện trong thủ thuật Excel ở đâu?

Trong tất cả các phiên bản của thủ thuật Excel, định dạng có điều kiện nằm ở cùng một vị trí, trên tab Home > Styles.

Định dạng có điều kiện trong Excel 2007

Định dạng có điều kiện trong Excel 2010

Định dạng có điều kiện trong Excel 2013

2. Làm thế nào để tạo các quy tắc định dạng có điều kiện trong thủ thuật Excel

Để tận dụng những khả năng của định dạng có điều kiện trong thủ thuật Excel, thì bạn cần phải học cách tạo ra các loại quy tắc khác nhau.

Có hai điều quan trọng ở trong định dạng có điều kiện đó là:

Tôi sẽ chỉ cho bạn cách áp dụng định dạng có điều kiện trong Excel 2010 chính vì đây có vẻ là phiên bản phổ biến nhất hiện nay. Nhưng, các tùy chọn về cơ bản giống nhau trong Excel 2007 và 2013, vì vậy bạn cũng sẽ không gặp bất kỳ vấn đề nào cho dù có bạn sử dụng khác phiên bản.

Thí dụ, tôi tạo ra một bảng nhỏ liệt kê giá dầu thô hàng tháng. Điều chúng ta muốn thực hiện chính là làm nổi bật sự giảm giá, nghĩa là tất cả những ô có số âm trong cột Change, vì vậy chúng ta chọn các ô C2: C9.

Chuyển đến tab Home> Styles sau đó nhấp vào Conditional Formatting. Bạn cũng sẽ thấy một số quy tắc định dạng khác, bao gồm: data bars, color scales and icon sets.

Vì chúng ta chỉ cần phải áp dụng Conditional Formatting cho các số nhỏ hơn 0, thì chúng ta chọn Highlight Cells Rules > Less Than …

Tất nhiên, bạn cũng có thể tiếp tục với bất kỳ loại quy tắc nào khác nhau để thích hợp cho dữ liệu của bạn, chẳng hạn như là:

• Định dạng các giá trị lớn hơn, nhỏ hơn hoặc bằng
• Đánh dấu văn bản có chứa từ hoặc ký tự được chỉ định
• Làm nổi bật các bản copy
• Định dạng các ngày cụ thể

Nhập giá trị trong hộp ở phần bên phải của cửa sổ phía dưới “Format cells that are LESS THAN“, trong trường hợp này, thì chúng ta gõ 0. Ngay khi bạn nhập giá trị, Microsoft Excel cũng sẽ làm nổi bật các ô trong dãy đã chọn để đáp ứng điều kiện của bạn.
Chọn định dạng bạn muốn từ danh sách. Bạn có thể chọn một trong số các định dạng được xác định trước hay nhấp vào Custom Format…để có thể thiết lập định dạng của riêng bạn.

Trong cửa sổ Format Cells, chuyển đổi giữa các tab Font, Border và Fill để chọn kiểu phông chữ, và kiểu đường viền và màu nền. Trên các tab Font và Fill, bạn sẽ thấy ngay các mẫu xem trước định dạng tùy chỉnh của mình.

Khi hoàn thành, hãy nhấn vào nút OK ở cuối cửa sổ.

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

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

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

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

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 > chọn Conditional Formatting.
• Bước 3. Nhấn 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 đã được mặc định.

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

Data Bars có thể được dùng để thể hiện bảng dữ liệu bằng đồ thị bên trong một ô. Thanh dài nhất được thể hiện giá trị cao nhất và thanh ngắn nhất sẽ cho thấy giá trị nhỏ nhất. Data Bars này giúp cho người dùng phát hiện số lượng lớn và nhỏ dễ dàng hơn trong bảng tí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à B2: B6.
• Bước 2. Đi tới Home > sử dụng Conditional Formatting.
• Bước 3. Click vào Data Bars > Blue Data Bar.
• Bước 4. Bạn cũng có thể chọn những thanh màu khác nếu muốn.

3. 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 Conditional Formatting đó là chọn tùy chọn Top/Bottom Rules. Bạn có thể dễ dàng đánh dấu với 3 mục hàng đầu trong danh sách. Để xác định ba sản phẩm hàng đầu sẽ tạo ra doanh số bán hàng nhiều nhất trong cửa hàng, bạn có thể tạo ra một quy tắc hàng đầu trong Conditional Formatting. Các bước thực hiện như sau:

• Bước 1. Chọn các ô để định dạng. Trong ví dụ này sẽ là B2: B8.
• Bước 2. Vào Home > chọn Conditional Formatting.
• Bước 3. Click vào Top/Bottom Rules > Top 10 Items… Chọn Top/Bottom Rules
• Bước 4. Mặc định là 10 mục tuy nhiên bạn có thể nhập số 3 hay 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 nhau từ danh sách thả xuống bên cạnh danh sách đã mặc định.

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

Bản đồ nhiệt là gì? - Bản đồ nhiệt chính là bảng mà dữ liệu trong bảng tính được hiển thị qua màu sắc. Bạn có thể hiển thị giá trị có trong ô hoặc không nếu muốn. Ẩn giá trị bằng cách 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 sẽ là B2: D10.
• Bước 2. Vào Home > nhấn Conditional Formatting.
• Bước 3. Nhấn vào Color Scales > More Rules…
• Bước 4. Mặc định là 2-Color Scale tuy nhiên 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 đang đượ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 với cùng một màu. Bởi vì nếu chọn nhiều màu khác nhau thì sẽ làm người xem nhầm lẫn, khó xác định được ô nào có giá trị thấp nhất hay cao nhất. Tuy nhiên, khi chọn tông màu khác nhau cho cùng một màu, thì 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 những giá trị trong bảng và chỉ hiển thị màu trong bản đồ nhiệt, thì bạn có thể chọn chuột phải vào bảng tính, click vào Format cells > Custom > Enter “;;;” trong kiểu hộp và click nhấn nút OK. Giá trị vẫn ở trong bảng tuy nhiên vẫn được ẩn.

5. 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 dùng để định dạng ô dựa trên giá trị của nó. Nhưng, bạn có thể sử dụng công thức để nhận giá trị được dựa trên các ô trong cùng một hàng và sau đó sử dụng Conditional Formatting để 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, thì 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. Bởi vì tất cả các dữ liệu cần thiết đã được lấp đầy trong hàng 5, vậy nên công thức hiển thị kết quả 4 ô đã được điền giá trị. Nhưng trong hàng 7, chỉ có 1 ô có dữ liệu và 3 ô trống. Do vậy hàng này cần được làm nổi bật bằng cách sử dụng Icon Sets để có thể xác định hàng cần điều chỉnh.

III. Các thao tác với Conditional Formatting áp dụng trong bài thi MOS

1. Áp dụng màu ô và màu chữ dựa theo giá trị

• Tại thẻ Home, nhóm Styles, nhấn vào Conditional Formatting.
• Trong danh sách Conditional Formatting, hãy chọn Highlight Cell Rules hoặc Top/Bottom Rules, click vào loại điều kiện theo ý muốn của bạn.
• Trong hộp thoại, điền những điều kiện cần thiết và chọn vào màu ô, màu chữ trong danh sách.
• Click OK để có thể sử dụng Conditional Formatting.

2. Hiển thị data bars, color scale hoặc icon dựa vào giá trị dữ liệu

Trong danh sách Conditional Formatting, chọn Data Bars, Color Scales hay Icon Sets tương ứng, sau đó nhấn vào tùy chọn mong muốn.

3. Tạo quy tắc mới

• Trong danh sách Conditional Formatting hãy chọn New Rule.
• Trong hộp thoại New Formatting Rule, nhấn mục Select a Rule Type, chọn kiểu quy tắc theo ý muốn.
• Tại vùng Edit the Rule Description, cụ thể hóa những điều kiện.
• Click OK để áp dụng.

4. Hiệu chỉnh quy tắc được áp dụng

• Trong danh sách Conditional Formatting, nhấn chọn Manage Rules.
• Trong hộp thoại Conditional Formatting Rules Manager, chọn quy tắc bạn muốn sửa đổi sau đó ấn vào Edit Rule.
• Tạo thay đổi trong hộp thoại Edit Formatting Rule, rồi ấn OK để áp dụng.

5. Sử dụng Stop If True

Stop If True ngăn việc áp dụng quy tắc tiếp theo khi sử dụng vùng dữ liệu đã khớp với điều kiện được nêu ra. Để sử dụng chức năng này, hãy chọn vào ô vuông Stop If True trong hộp thoại Conditional Formatting Rules Manager.

6. Xóa quy tắc đã áp dụng

Xóa quy tắc đã được áp dụng định dạng có điều kiện 

Cách 1: Trong danh sách Conditional Formatting, chọn Clear Rules sau đó chọn Clear Rules from Selected Cells hay Clear Rules from Entire Sheet.

Cách 2: Mở hộp thoại sử dụng Conditional Formatting Rules Manager, chọn quy tắc và ấn Delete Rule rồi ấn OK để kết thúc.

IV. Xóa quy tắc 

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

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

2. 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 > chọn Conditional Formatting.
• Bước 2. Click vào Clear Rules > sau đó chọn Clear Rules from Selected Cells.

3. 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 Conditional Formatting. Phương thức này sẽ hiển thị tất cả những ô 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 nhất.

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

• Bước 1. Vào Home > sử dụng Conditional Formatting.
• Bước 2. Chọn vào Manage Rules.
• Bước 3. Thay đổi các quy tắc Show formatting từ Current Selection thành This Worksheet. Điều này sẽ hiển thị tất cả những quy tắc trong bảng tính này.
• Bước 4. Click vào quy tắc bạn đang muốn xóa → click vào Delete Rule → OK.
• Bước 5. Lặp lại Bước 4 đối với những quy tắc mà bạn muốn xóa.

V. Kết luận 

Qua bài viết này, các bạn sẽ hiểu rõ hơn về cách sử dụng định dạng có điều kiện (Conditional Formatting) trong thủ thuật Excel. Mong các bạn sẽ thực hiện thành công nhé!