Cách sử dụng Conditional Formatting để định dạng với điều kiện trong Excel

Conditional Formatting là tính năng trong Excel giúp tự động áp dụng định dạng (ví dụ: màu nền, font chữ, màu chữ, thanh dữ liệu, biểu tượng) cho các ô dựa trên điều kiện đặt ra để dễ dàng nhìn thấy các mẫu hoặc xu hướng trong dữ liệu.

1. Tổng quan về Conditional Formatting

• Mục đích: Conditional Formatting được sử dụng để highlight các dữ liệu theo điều kiện mong muốn nhằm dễ quan sát.
• Nguyên lý hoạt động: Conditional Formatting hoạt động dựa trên các quy tắc (Rules) để xác định định dạng (Format) của các ô dựa trên giá trị (Values) của chúng.

• Lưu ý về PivotTable report:
  • Một số loại Conditional Formatting không hoạt động với các fields trong khu vực Values của PivotTable Report.
  • Khi thay đổi bố cục của PivotTable report (như lọc, ẩn/hiện, thu gọn/mở rông, di chuyển trường) thì Conditional Formatting vẫn được áp dụng miễn là các trường trong dữ liệu cơ sở không bị xóa.
  • Phạm vi áp dụng Conditional Formatting cho các trường trong vùng Values có thể dựa trên thứ bậc dữ liệu (data hierarchy) và được xác định bởi tất cả các visible children (cấp độ thấp hơn) của một parent (cấp độ cao hơn) trên các hàng cho một hoặc nhiều cột, hoặc các cột cho một hoặc nhiều hàng.
  • Trong thứ bậc dữ liệu, children không kế thừa Conditional Formatting từ parent, và parent cũng không kế thừa từ children.
  • Có ba phương pháp để xác định phạm vi (scoping) Conditional Format cho các trường trong khu vực Values: theo lựa chọn (by selection), theo trường tương ứng (by corresponding field), và theo trường giá trị (by value field).
    • Scoping by selection: Sử dụng cho một tập hợp fields liền kề hoặc không liền kề trong khu vực Values.
    • Scoping by value field: Tránh nhiều lựa chọn không liền kề, định dạng có điều kiện một tập hợp fields trong khu vực Values cho tất cả các cấp độ trong thứ bậc dữ liệu, bao gồm cả subtotals và grand totals.
    • Scoping by corresponding field: Tránh nhiều lựa chọn không liền kề, định dạng có điều kiện một tập hợp fields trong khu vực Values cho một cấp độ trong thứ bậc dữ liệu, loại trừ subtotals.

2. Các bước sử dụng Conditional Formatting

Để sử dụng định dạng có điều kiện cho vùng dữ liệu ta thực hiện như sau:

• Bước 1: Chọn vùng dữ liệu (có thể 1 hoặc nhiều ô trong phạm vi, table hoặc PivotTable) muốn định dạng có điều kiện.
• Bước 2: Vào tab Home > trong nhóm Styles, nhấp vào mũi tên bên cạnh Conditional Formatting > chọn Rule muốn áp dụng ( ví dụ: Color Scales, Data Bars, Icon Sets, Highlight Cells Rules, Top/Bottom Rules) hoặc chọn tùy chọn định dạng cụ thể (chọn New Rule > Use a formula to determine which cells to format > nhập công thức > nhấn Format > Ok)

Ngoài ra, ta có thể sử dụng nút Quick Analysis để vài Conditional Formatting:

• Bước 1: Chọn vùng dữ liệu cần định dạng
• Bước 2: Nhấp vào nút Quick Analysis (Nút này sẽ tự động xuất hiện ở góc dưới bên phải vùng chọn của bạn) hoặc nhấn Ctrl + Q.
• Bước 3: Tại cửa sổ hiện lên, nhấn tab Formatting > di chuyển đến các tùy chọn để xem Live Preview trên dữ liệu > sau đó chọn định dạng muốn.
• Lưu ý: Các tùy chọn định dạng hiển thị phụ thuộc vào dữ liệu đã chọn:
  • Nếu chỉ có văn bản (text) thì sẽ có tùy chọn Text, Duplicate, Unique, Equal To, Clear.
  • Nếu có Numbers hoặc cả Text và Number thì sẽ có các tùy chọn Data Bars, Colors, Icon Sets, Greater, Top 10%, Clear.

3. Các loại Conditional Formatting thường sử dụng

Để sử dụng Advanced Formatting (định dạng nâng cao) ta sử dụng hộp thoại Conditional Formatting Rules Manager với các bước thực hiện như sau:

• Bước 1: Chọn vùng dữ liệu cần định dạng
• Bước 2: vào Tab Home > trong nhóm Styles nhấp mũi tên bên cạnh Conditional Formatting > nhấp Manage Rules > hộp thoại Conditional Formatting Rules Manager xuất hiện.
• Bước 3: Thực hiện một trong các thao tác sau:
  • Thêm Rule mới: Nhấp vào New Rule để mở hộp thoại New Formatting Rule
  • Chỉnh sửa Rule hiện có: chọn Rule > nhấp Edit Rule để mở Edit Formatting Rule.
  • Thay đổi phạm vi ô: Nhấp Collapse Dialog trong hộp Applies to, chọn phạm vi ô mới, sau đó nhấp Expand Dialog.
  • Chọn kiểu Rule (Select a Rule Type) và chỉnh sửa mô tả Rule (Edit The Rule Description) theo các loại bên dưới.
• Bước 4: Nhấp Format để mở hộp thoại Format Cells để chọn định dạng ( number, font, border, fill,…) > nhấp Ok.

Dưới đây là các loại Conditional Formatting được sử dụng phổ biến nhất:

3.1. Color Scales (Thang màu)

Color Scales hỗ trợ định dạng phân bố và biến thiên của dữ liệu bằng cách sử dụng các sắc độ màu, với 2 lựa chọn phổ biến:

1. Two-color Scale: So sánh một vùng ô bằng cách chuyển màu giữa 2 màu. Sắc độ màu thể hiện giá trị cao hơn hoặc thấp hơn (ví dụ: xanh lá cây cho giá trị cao, vàng cho giá trị thấp).
2. Three-color Sacle: So sánh một vùng ô bằng chuyển màu giữa 3 màu. Sắc độ màu thể hiện giá trị cao hơn, trung bình hoặc thấp hơn (ví dụ: xanh lá cây cho giá trị cao, vàng cho giá trị trung bình và đỏ cho thấp).

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

1. Trong hộp thoại New Formatting Rule hoặc Edit Formatting Rule, chọn Select a Rule Type là Format all cells based on their values.
2. Trong phần Edit the Rule Description, trong danh sách Format Style, chọn 2-Color Scale hoặc 3-Color Scale.
3. Thiết lập giá trị và màu sắc:
   • Với 2-Color Scale: Chọn Type cho Minimum và Maximum (ví dụ: Lowest Value, Highest Value, Number, Percent, Percentile, Formula). Sau đó, nhấp vào Color cho từng mức và chọn màu.
   • Với 3-Color Scale: Chọn Type cho Minimum, Midpoint và Maximum (tương tự như trên). Sau đó, nhấp vào Color cho từng mức và chọn màu.
   • Ta có thể chọn More Colors để tạo màu tùy chỉnh.
4. Nhấp OK để áp dụng định dạng.

3.2. Data Bars (Thanh dữ liệu)

Data Bars giúp định dạng để dễ dàng so sánh giá trị của một ô với các ô khác bằng cách hiển thị một thanh (dài hơn cho giá trị cao hơn hoặc ngắn hơn cho giá trị thấp hơn). Loại định dạng này trong Conditional Formatting rất hữu ích để nhanh chóng phân biệt số cao và thấp, đặc biệt với lượng dữ liệu lớn.

1. Các bước sử dụng Data Bars trong Conditional Formatting:
2. Mở hộp thoại New Formatting Rule hoặc Edit Formating Rule > chọn Select a Rule Type > Format all cells based on their values.
3. Trong phần Edit the Rule Description > trong Format Style > chọn Data Bar
4. Thiết lập giá trị màu bằng cách chọn Type cho Minimum và Maximum (tương tự như Color Scale)
5. Thiết lập tùy chỉnh thanh:
   • Nhấp Bar Color để chọn màu thanh.
   • Chọn Show Bar Only để ẩn giá trị số trong ô.
   • Chọn Solid Border hoặc Gradient Fill cho thanh,.
   • Định dạng thanh âm bằng cách nhấp vào Negative Value and Axis
   • Đổi hướng thanh bằng Bar Direction
6. Nhấp Ok để áp dụng định dạng.

3.3. Icon Sets (Bộ biểu tượng)

Icon Sets trong Conditional Formatting dùng để chú thích và phân loại dữ liệu thành các danh mục ( từ 3 – 5 danh mục) và được phân tách bằng thresshold value (giá trị ngưỡng). Mỗi icon đại diện cho một range of values.

Cách định dạng Icon Sets trong Conditonal Formatting:

Các bước sử dụng Data Bars trong Conditional Formatting:

1. Mở hộp thoại New Formatting Rule hoặc Edit Formating Rule > chọn Select a Rule Type > Format all cells based on their values.
2. Trong Edit the Rule Description, trong danh sách Format Style, chọn Icon Set.
3. Ta chọn một Icon Set sau đó có thể tùy chỉnh:
   1. Cài đặt comparison operators và threshold values cho mỗi icon cũng như Type cho các ngưỡng giá trị (ví dụ: Number, Percent, Percentile, Formula).
   2. Chọn Reverse Icon Order để đảo ngược thứ tự biểu tượng hoặc Show Icon Only để chỉ hiển thị biểu tượng.
4. Nhấp Ok để áp dụng định dạng.

3.4. Highlight Cells Rules (làm nổi bật ô)

Highlight Cells Rules trong conditional Formatting giúp tô nổi bật các ô chứa giá trị trong vùng dữ liệu dựa trên giá trị trong ô. (Ví dụ: lớn hơn, nhỏ hơn một giá trị, chứa text,…)

Cách định dạng Highlight Cells Rules trong Conditional Formatting:

1. Mở hộp thoại New Formatting Rule hoặc Edit Formating Rule > chọn Select a Rule Type > Format all cells based on their values.
2. Trong Edit the Rule Description, trong danh sách Format only cells with chọn kiểu định dạng muốn:
   • Cell Value: Định dạng theo number, date, hoặc time value (chọn comparison operator và nhập giá trị hoặc formula).
   • Specific Text: Định dạng theo text (chọn comparison operator và nhập text hoặc formula).
   • Dates Occurring: Định dạng theo ngày tháng (chọn một so sánh ngày cụ thể, ví dụ: Yesterday, Next week).
   • Blanks hoặc No Blanks: Định dạng các cells rỗng hoặc không rỗng.
   • Errors hoặc No Errors: Định dạng các cells có lỗi hoặc không có lỗi.
3. Nhấp Format để chọn định dạng (số, phông chữ, viền, tô màu).
4. Nhấp OK để áp dụng định dạng.

3.5. Top/Bottom Rules (tìm giá trị cao nhất và thấp nhất)

Top/Bottom Rules trong Conditional Formatting được sử dụng để định dạng làm nổi bật top các giá trị cao nhất và thấp nhất trong một vùng dữ liệu dựa trên một cutoff value chỉ định (ví dụ: Top 5 sản phẩm bán chạy nhất, 15% sản phẩm thấp nhất).

Các sử dụng định dạng Top/Bottom Rules trong Conditional Formatting:

1. Tại hộp thoại New Formatting Rule hoặc Edit Formatting Rule, chọn Select a Rule Type là Format only top or bottom ranked values.
2. Trong Edit the Rule Description, trong danh sách Format values that rank in the, chọn Top hoặc Bottom.
3. Sau đó, nhập một số và (tùy chọn) chọn hộp % of the selected range.
4. Nhấp Format để chọn định dạng.
5. Nhấp OK để áp dụng.

3.6. Above/Below Average Rules (Nổi bật giá trị trên/dưới mức trung bình)

Above/Below Average Rules trong Conditional Formatiing giúp nổi bật ô chứa giá trị trên hoặc dưới mức trung bình hoặc độ lệch chuẩn trong một vùng dữ liệu.

Cách định dạng ô theo Above/Below Average Rules như sau:

1. Trong  New Formatting Rule hoặc Edit Formatting Rule > chọn Select a Rule Type là Format only values that are above or below average.
2. Trong Edit the Rule Description, trong danh sách Format values that are, chọn Above hoặc Below, hoặc các tùy chọn độ lệch chuẩn.
3. Nhấp Format để chọn định dạng.
4. Nhấp OK để áp dụng định dạng.

3.7. Duplicate/Unique Values Rules (làm nổi bật giá trị trùng lặp/duy nhất)

Duplicate/Unique Values Rules trong Conditional Formatting giúp làm nổi bật các ô xuất hiện 1 lần (unique) hoặc nhiều lần (duplicate) trong một vùng dữ liệu.

Cách sử dụng định dạng Duplicate/Unique Values Rules như sau:

1. Trong hộp thoại New Formatting Rule hoặc Edit Formatting Rule, chọn Select a Rule Type là Format only unique or duplicate values.
2. Trong Edit the Rule Description, trong danh sách Format all, chọn unique hoặc duplicate.
3. Nhấp Format để chọn định dạng.
4. Nhấp OK để áp dụng định dạng.

Lưu ý: Các quy tắc định dạng “Unique” hoặc “Duplicate Values” sẽ coi “*” và “?” là wildcard characters ngay cả khi chúng được sử dụng trong một formula.

3.8. Custom Rules using Formulas (Định dạng tùy chỉnh bằng công thức)

Custom Rules using Formulas trong Conditional Formatting giúp định dạng các ô chứa giá trị theo điều kiện tùy chỉnh không có sẵn, ta sẽ tạo điều kiện bằng cách sử dụng một công thức logic. Trong đó công thức phải trả về giá trị TRUE (1) hoặc FALSE (0).

Cách định dạng theo công thức tùy chỉnh như sau:

1. Trong New Formatting Rule hoặc Edit Formatting Rule, chọn Select a Rule Type là Use a formula to determine which cells to format.
2. Trong Edit the Rule Description, trong danh sách Format values where this formula is true, nhập formula của bạn. Formula phải bắt đầu bằng dấu bằng (=).
3. Nhấp Format để chọn định dạng.
4. Nhấp OK để áp dụng.

Lưu ý:

• Ta có thể nhập cell references trong formula bằng cách chọn trực tiếp các ô trên worksheet. Excel sẽ chèn absolute cell references; sử dụng relative cell references nếu muốn Excel điều chỉnh các references cho từng ô trong phạm vi đã chọn.
• Nếu formula trả về lỗi, Conditional Formatting sẽ không được áp dụng. Bạn nên sử dụng các hàm IS hoặc IFERROR để xử lý lỗi.
• Ta có thể sử dụng các hàm AND và OR để kết hợp nhiều kiểm tra logic. Ví dụ: =AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1)).
• Một ví dụ khác để tô màu xen kẽ các hàng: =MOD(ROW(),2)=1.

4. Cách quản lý Conditional Formatting Rules

Để xem vùng dữ liệu đang có những định dạng như thế nào? Ta sẽ sử dụng Conditional Formatting Rules Manager bằng cách:

Vào tab Home > nhấp Conditional Formatting > nhấp Manage Rules > trong hộp thoại Formatting Rules Manager sẽ hiển thị tất cả các rules trong workbook hoặc worksheet hiện tại.

Một số lưu ý trong Conditional Formatting Rules Manager:

• Thứ tự ưu tiên (Precedence): Các rules được đánh giá theo thứ tự từ trên xuống dưới trong danh sách. Rule ở trên có ưu tiên cao hơn.
  • Thay đổi thứ tự: Sử dụng các mũi tên Move Up và Move Down trong hộp thoại để thay đổi thứ tự ưu tiên của rule.
  • Khi rules không xung đột: Nếu các rules không xung đột về định dạng (ví dụ: một rule tô đậm, một rule tô màu đỏ), cả hai sẽ được áp dụng.
  • Khi rules xung đột: Nếu các rules xung đột (ví dụ: một rule tô màu đỏ, một rule tô màu xanh lá cây), chỉ rule có ưu tiên cao hơn (ở trên trong danh sách) được áp dụng.
• Ngừng đánh giá nếu đúng (Stop If True):
  • Ta sẽ chọn hộp kiểm Stop If True cho một rule để dừng đánh giá các rules tiếp theo nếu rule hiện tại đúng.
  • Ta không thể chọn tùy chọn này nếu rule định dạng bằng data bar, color scale, hoặc icon set.
• Sao chép, dán và Format Painter: Khi sao chép/dán, điền hoặc sử dụng Format Painter, một Conditional Formatting rule mới dựa trên các source cells sẽ được tạo cho các destination cells.
• Xung đột với định dạng thủ công: Conditional Formatting (nếu đúng) sẽ ưu tiên hơn bất kỳ định dạng thủ công nào hiện có cho cùng một vùng chọn.

5. Cách xóa Conditional Formatting

Khi muốn xóa bất định dạng nào không mong muốn thì ta có thể thực hiện như sau:

5.1. Xóa định dạng trên toàn bộ trang tính hiện tại:

Cách thực hiện: Vào tab Home > chọn Conditional Formatting > chọn Clear Rules > chọn Clear Rules from Entire Sheet.

5.2. Xóa định dạng trên phạm vi ô được chọn:

Cách thực hiện: Bôi đen vùng cần xóa định dạng > tab Home > chọn Conditional Formatting > chọn Clear Rules > chọn Clear Rules from Selected Cells. (Hoặc dùng Quick Analysis Lens và nhấp Clear Format).

5.3. Tìm và xóa các ô định dạng giống nhau:

Cách thực hiện: chọn ô có định dạng cần xóa > tab Home >  nhấp mũi tên bên cạnh Find & Select > Go To Special > Conditional formats > Same để chọn tất cả các ô có cùng định dạng > tab Home, nhấp Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Trên đây là hướng dẫn chi tiết về cách sử dụng conditional Formatting trong Excel. Mong rằng bài viết này đã giúp các bạn làm chủ được cách định dạng có điều kiện để làm nổi bật các thông tin theo nhu cầu và quá trình làm việc với Excel!