Học Excel - Bài 13: Cách dùng định dạng có điều kiện trong Microsoft Excel

Định dạng có điều kiện trong Microsoft Excel giúp bạn khám phá & phân tích dữ liệu, phát hiện lỗi quan trọng, nhận diện mẫu và xu hướng dễ dàng.

Định dạng có điều kiện của Excel còn hỗ trợ highlight dễ dàng ô hoặc phạm vi ô quan tâm, nhấn mạnh các giá trị bất thường và trực quan hóa dữ liệu bằng thanh Data, thang màu và bộ icon tương ứng với biến cụ thể trong dữ liệu đó.

Một định dạng có điều kiện thay đổi diện mạo các ô trên điều kiện bạn chọn. Nếu điều kiện đúng, Excel sẽ định dạng phạm vi ô đó. Nếu điều kiện sai, phạm vi ô đó không được định dạng. Microsoft Excel có sẵn nhiều điều kiện. Tuy nhiên, bạn vẫn có thể tạo điều kiện riêng bằng cách dùng công thức đánh giá đúng hoặc sai.

Microsoft Excel Microsoft Excel cho Android Microsoft Excel cho iOS Microsoft Excel Online

Bạn có thể áp dụng định dạng có điều kiện cho một phạm vi ô (lựa chọn hoặc một phạm vi được đặt tên), một bảng Excel, và trong Excel cho Windows, thậm chí một báo cáo PivotTable. Lưu ý, Excel cung cấp thêm một vài cân nhắc định dạng có điều kiện trong báo cáo dạng PivotTable - xem mục Apply conditional formatting in a PivotTable report bên trước tab Windows.

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

Áp dụng định dạng có điều kiện trong báo cáo PivotTable

Về cơ bản, định dạng có điều kiện có cách hoạt động như trong phạm vi ô, bảng Excel hoặc báo cáo PivotTable. Tuy nhiên, bạn có thêm một số điều cần cân nhắc khi dùng định dạng có điều kiện trong báo cáo PivotTable:

• Một số định dạng có điều kiện không hoạt động với các trường trong vùng Value của báo cáo PivotTable. Ví dụ, bạn không thể định dạng những trường như vậy dù chúng chứa giá trị trùng lặp hay duy nhất. Những hạn chế đề cập trong các phần còn lại của bài đều có thể được áp dụng.
• Nếu thay đổi bố cục báo cáo PivotTable bằng cách lọc, ẩn cấp độ, thu gọn và mở rộng cấp độ hoặc di chuyển một trường, Excel vẫn duy trì định dạng có điều kiện miễn là các trường trong dữ liệu bên dưới không bị xóa.
• Phạm vi định dạng có điều kiện cho các trường trong vùng Values có thể dựa trên cấu trúc phân cấp dữ liệu và được xác định bằng tất cả mục con hiển thị trên hàng của một hoặc nhiều cột hay cột có một hay nhiều hàng.

Phương pháp mặc định để khoanh vùng các trường trong Values là theo lựa chọn. Bạn có thể thay đổi nó sang trường tương ứng hoặc giá trị bằng cách nhấn nút Apply formatting rule to, box New Formatting Rule hay Edit Formatting Rule.

Phương pháp phân phạm vi	Mục đích sử dụng
Theo lựa chọn	Một tập hợp các trường liền kề trong vùng Values như toàn bộ tổng sản phẩm cho một vùng. Một tập hợp các trường không liền kề trong Values như tổng sản phẩm cho các vùng khác nhau trên khắp cấp độ ở phân cấp dữ liệu.
Theo trường giá trị	Tránh tạo 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 các trường trong Values cho toàn bộ cấp độ tại cấu trúc phân cấp dữ liệu. Bao gồm tổng phụ và tổng chính.
Theo trường tương ứng	Tránh tạo 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 các trường trong Values cho một cấp độ ở hệ thống phân cấp dữ liệu. Loại bỏ tổng phụ. Khi định dạng có điều kiện các trường trong Values cho giá trị trên, dưới, trên hoặc dưới trung bình, quy tắc mặc định thường dựa trên tất cả giá trị hiển thị. Tuy nhiên, khi lựa chọn cách phân phạm vi này, thay vì dùng toàn bộ giá trị hiển thị, bạn có thể áp dụng định dạng có điều kiện cho từng kết hợp: Một cột và trường hàng chính của nó. Một hàng và cột chính của nó.

Dùng Quick Analysis để áp dụng định dạng có điều kiện

Lưu ý: Quick Analysis không có sẵn trong Excel 2010 hoặc cũ hơn.

Nhấn nút Quick Analysisđể áp dụng định dạng có điều kiện cho dữ liệu được chọn. Nút Quick Analysis hiện tự động khi bạn chọn dữ liệu.

1. Chọn dữ liệu muốn định dạng có điều kiện. Nút Quick Analysis hiện ở góc bên phải phía dưới lựa chọn.

2. Click Quick Analysis hoặc nhấn Ctrl+Q.

Trong cửa sổ pop-up hiện ra, trên tab Formatting, chuyển trỏ chuột qua vị trí khác để thấy Live Preview trên dữ liệu của bạn, sau đó, click vào tùy chọn định dạng bạn muốn.

4. Nếu Text that Contains xuất hiện, nhập tùy chọn định dạng bạn muốn áp dụng và click OK.

Định dạng ô bằng cách dùng thang 2 màu

Thang màu là hướng dẫn trực quan giúp bạn hiểu rõ phân bổ và biến thiên dữ liệu. Thang 2 màu giúp bạn so sánh một phạm vi ô bằng cách chuyển tiếp dần dần giữa 2 màu. Sắc độ đại diện các giá trị cao hoặc thấp hơn. Ví dụ, trong thang màu xanh & vàng, như hình bên dưới, bạn dễ dàng xác định các ô có giá trị cao hơn thiên về màu xanh ngày càng đậm, còn ô có giá trị thấp thiên về màu vàng.

Định dạng nhanh

1. Chọn một hoặc nhiều ô trong phạm vi lựa chọn, bảng hay báo cáo PivotTable.

2. Trên tab Home, trong nhóm Styles, click mũi tên bên cạnh Conditional Formatting > click Color Scales.

3. Chọn thang 2 màu

Trỏ chuột qua icon thang màu để thấy icon thang 2 màu. Màu phía trên cùng đại diện cho giá trị cao hơn, màu dưới cùng tượng trưng cho giá trị thấp hơn.

Bạn có thể thay đổi phương pháp khoanh vùng các trường trong Values của báo cáo PivotTable bằng cách dùng nút Formatting Options bên cạnh trường PivotTable được áp dụng định dạng có điều kiện.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hay báo cáo PivotTable nào đó.

2. Trên tab Home, trong nhóm Styles, click mũi tên cạnh Conditional Formatting > click Manage Rules. Hộp thoại Conditional Formatting Rules Manager xuất hiện.

3. Thực hiện một trong những tác vụ sau:

• Để thêm định dạng có điều kiện, click New Rule > New Formatting Rule hiện ra.
• Để thay đổi định dạng có điều kiện, hãy làm như sau:
  • Đảm bảo chọn worksheet, bảng hoặc PivotTable phù hợp trong box list Show formatting rules for.
  • Hoặc thay đổi phạm vi ô bằng cách click Collapse Dialog trong Applies to để tạm thời ẩn box hộp thoại này, bằng cách chọn phạm vi ô mới trên worksheet, sau đó chọn Expand Dialog.
  • Chọn quy tắc > click Edit rule. Hộp thoại Edit Formatting Rule xuất hiện.

4. Trong Apply Rule To, để thay đổi phạm vi các trường trong Values của báo cáo PivotTable như sau:

• Vùng chọn: Click Selected cells.
• Toàn bộ ô cho một nhãn Value: Click All cells showing <Value label> values.
• Toàn bộ ô cho một nhãn Value, loại bỏ các tổng phụ và chính: Toàn bộ All cells showing <Value label> values for <Row Label>.

5. Trong Select a Rule Type, click Format all cells based on their values (default).

6. Trong Edit the Rule Description, trong box Format Style, chọn 2-Color Scale.

7. Để chọn một loại trong box Type cho Minimum và Maximum, hãy làm như sau:

• Định dạng giá trị cao và thấp nhất: Chọn Lowest Value và Highest Value. Trong trường hợp này, bạn không cần nhập Minimum và MaximumValue.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Number rồi nhập giá trị tối thiểu và tối đa.
• Định dạng phần trăm: Nhập giá trị tối thiểu và tối đa. Các giá trị hợp lệ từ 0 tới 100. Đừng nhập dấu phần trăm. Chỉ dùng nó khi bạn muốn hình dung toàn bộ giá trị theo tỉ lệ do phân bổ cân xứng các giá trị đó.
• Định dạng phân vị: Chọn Percentile > nhập giá trị tối thiểu và tối đa. Phân vị hợp lệ từ 0 tới 100.
• Dùng phân vị khi bạn muốn trực quan hóa một nhóm giá trị cao (như phân vị thứ 20 từ trên xuống) trong một tỷ lệ cấp màu và các giá trị thấp (như phân vị thứ 20 từ dưới lên) do chúng đại diện cho các giá trị cực có thể làm sai lệch dữ liệu của bạn.
• Định dạng kết quả của một công thức: Chọn Formula, rồi nhập giá trị Minimum và Maximum.
  • Công thức phải trả về số, ngày hoặc giá trị thời gian.
  • Bắt đầu công thức bằng dấu =.
  • Kết quả các công thức không hợp lệ sẽ không có định dạng nào được áp dụng.
  • Bạn nên kiểm tra công thức để đảm bảo nó không trả về giá trị lỗi.

8. Để chọn thang màu Minimum và Maximum, click Color cho mỗi giá trị, rồi chọn màu.

Nếu muốn chọn màu bổ sung hoặc tạo màu tùy biến, click More Colors. Thang màu bạn chọn hiện trong box Preview.

Định dạng ô bằng cách dùng thang 3 màu

Thang 3 màu giúp bạn so sánh một phạm vi các ô bằng cách chuyển tiếp dần dần giữa 3 màu. Sắc độ màu đại diện cho các giá trị cao hơn, trung bình hoặc thấp. Ví dụ, trong thang màu xanh lá, vàng và đỏ, bạn có thể xác định các ô giá trị cao thiên về màu xanh lá, ô giá trị trung bình có màu vàng, ô giá trị thấp là màu đỏ.

Mẹo: Bạn có thể phân loại ô ở định dạng này theo màu của chúng - chỉ cần sử dụng menu ngữ cảnh.

Định dạng nhanh

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hoặc báo cáo PivotTable.
2. Trên tab Home > nhóm Styles > click mũi tên cạnh Conditional Formatting > click Color Scales.
3. Chọn thang 3 màu. Màu trên cùng đại diện cho các giá trị cao, màu ở giữa đại diện cho giá trị trung bình, màu ở dưới đại diện cho giá trị thấp. Trỏ chuột qua icon thang màu để thấy biểu tượng thang 3 màu.

Bạn có thể thay đổi cách khoanh vùng các trường trong Values của báo cáo PivotTable bằng cách dùng Formatting Options bên cạnh trường PivotTable được định dạng có điều kiện.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hoặc báo cáo PivotTable.

2. Trên tab Home > Styles > click mũi tên cạnh Conditional Formatting > click Manage Rules. Conditional Formatting Rules Manager xuất hiện.

3. Làm một trong những việc sau:

• Để thêm định dạng có điều kiện, click New Rule để mở New Formatting Rule.
• Thay đổi định dạng có điều kiện như sau:
  • Đảm bảo chọn worksheet, bảng hay báo cáo PivotTable phù hợp trong box Show formatting rules for.
  • Hoặc thay đổi phạm vi ô bằng cách click Collapse Dialog trong box Applies to để tạm thời ẩn hộp thoại, chọn phạm vi ô mới trên worksheet, rồi chọn Expand Dialog.
  • Chọn giá trị này, rồi click Edit rule.

4. Trong Apply Rule To, thay đổi phạm vi các trường trong Values của báo cáo PivotTable như sau:

• Selection: Click Just these cells.
• Corresponding field: Click All <value field> cells with the same fields.
• Value field: Click All <value field> cells.

5. Trong Select a Rule Type, click Format all cells based on their values.

6. Trong Edit the Rule Description > box Format Style, chọn 3-Color Scale.

7. Chọn một kiểu cho Minimum, Midpoint và Maximum. Thực hiện một trong những việc sau:

• Định dạng giá trị cao và thấp nhất: Chọn Midpoint. Trong trường hợp này, bạn không cần nhập giá trị cao và thấp nhất.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Number, rồi nhập giá trị cho Minimum, Midpoint và Maximum.
• Định dạng phần trăm: Chọn Percent, rồi nhập giá trị cho Minimum, Midpoint và Maximum. Giá trị hợp lệ từ 0 tới 100. Không nhập dấu %. Dùng % khi bạn muốn hình dung toàn bộ giá trị theo tỷ lệ bởi dùng giá trị phần trăm đảm bảo phân bổ giá trị đồng đều.
• Định dạng phân vị: Chọn Percentile, rồi nhập giá trị cho Minimum, Midpoint và Maximum. Phân vị hợp lệ từ 0 tới 100. Dùng phân vị khi bạn muốn trực quan hóa một nhóm giá trị cao bằng một tỷ lệ cấp màu và giá trị thấp bằng màu khác vì chúng đại diện cho các giá trị cực có thể làm sai lệch dữ liệu của bạn.
• Định dạng kết quả công thức: Chọn Formula, sau đó nhập giá trị cho Minimum, Midpoint, Maximum. Công thức này phải trả về số, ngày hoặc giá trị thời gian. Bắt đầu công thức bằng dấu =. Kết quả công thức không hợp lệ sẽ không có định dạng được áp dụng. Bạn nên kiểm tra công thức để đảm bảo nó không trả về giá trị lỗi.

8. Để chọn thang màu Minimum, Midpoint và Maximum, click Color cho mỗi giá trị, rồi chọn một màu.

• Để chọn màu bổ sung hoặc tạo màu tùy biến, click More Colors.
• Thang màu bạn chọn được hiển thị trong box Preview.

Định dạng ô bằng các thanh dữ liệu

Thanh dữ liệu giúp bạn thấy giá trị của ô liên quan tới ô khác. Chiều dài của thanh dữ liệu đại diện cho giá trị trong ô đó. Thanh dài hơn tượng trưng cho giá trị cao hơn, thanh ngắn hơn sẽ có giá trị thấp hơn. Thanh dữ liệu hữu ích trong việc xác định số lớn hoặc thấp hơn, nhất là với khối lượng dữ liệu lớn như doanh số bán hàng đồ chơi cao và thấp nhất trong báo cáo.

Ví dụ dưới đây dùng thanh dữ liệu để làm nổi bật giá trị âm và dương ấn tượng nhất. Bạn có thể định dạng thanh dữ liệu để nó bắt đầu ở giữa ô và kéo sang bên trái các giá trị âm.

Mẹo: Nếu ô bất kỳ trong phạm vi chứa công thức trả về lỗi, Excel sẽ không áp dụng định dạng có điều kiện cho những ô này. Để đảm bảo áp dụng định dạng có điều kiện cho những ô đó, dùng hàm IS hoặc IFERROR để trả về một giá trị () hoặc N/A thay vì giá trị lỗi.

Định dạng nhanh

1. Chọn một hoặc nhiều ô trong một phạm vi, bảng hoặc báo cáo PivotTable.
2. Trên tab Home > Style > click mũi tên cạnh Conditional Formatting > click Data Bars, rồi chọn icon thanh dữ liệu.

Bạn có thể thay đổi phương pháp khoanh vùng cho các trường trong Values của báo cáo PivotTable qua nút tùy chọn Apply formatting rule to.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hay báo cáo PivotTable.

2. Trên Home > Styles > click mũi tên cạnh Conditional Formatting, rồi click Manage Rules > Conditional Formatting Rules Manager xuất hiện.

3. Làm một trong những việc sau:

• Để thêm định dạng có điều kiện, click New Rule để mở New Formatting Rule.
• Để thay đổi định dạng có điều kiện, hãy làm như sau:
  • Đảm bảo đã chọn worksheet, bảng hoặc báo cáo PivotTable phù hợp trong box Show formatting rules for.
  • Tùy chọn, thay đổi phạm vi ô bằng cách click Collapse Dialog trong box Applies to để tạm thời ẩn hộp thoại này, bằng cách chọn phạm vi ô mới trên worksheet, rồi chọn Expand Dialog.
  • Chọn quy tắc này, rồi chỉnh sửa Edit Rule. Hộp thoại Edit Formatting Rule xuất hiện.

4. Trong Apply Rule To, bạn có 3 tùy chọn thay đổi phạm vi các trường ở Values của báo cáo PivotTable như sau:

• Selection: Click Just these cells.
• Corresponding field: Click All <value field> cells with the same fields.
• Value field: Click All <value field> cells.

5. Trong Select a Rule Type, click Format all cells based on their values.

6. Trong Edit the Rule Description > box Format Style, chọn Data Bar.

7. Chọn Minimum và MaximumType. Làm một trong những việc sau:

• Định dạng giá trị thấp và cao nhất: Chọn Lowest Value và Highest Value. Trong trường hợp này, bạn không nhập giá trị cho Minimum & Maximum.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Number, rồi nhập Minimum và MaximumValue.
• Định dạng phần trăm. Chọn Percent và nhập giá trị Minimum và Maximum.
• Định dạng phân vị: Chọn Percentile, nhập giá trị Minimum và Maximum.
• Định dạng kết quả công thức: Chọn Formula, rồi nhập giá trị Minimum và Maximum.

8. Để chọn thang màu Minimum và Maximum, click Bar Color. Nếu muốn chọn màu bổ sung hoặc tạo màu tùy biến, click More Colors. Thanh màu bạn chọn được hiện trong box Preview.

9. Để chỉ hiện thanh dữ liệu, không phải giá trị trong ô, chọn Show Bar Only.

10. Để áp dụng đường viền đậm liền nét cho thanh dữ liệu, chọn Solid Border trong box Border và chọn màu đường viền.

11. Để chọn giữa một thanh liền nét và thanh màu gradient, chọn Solid Fill hoặc Gradient Fill ở box Fill.

Để định dạng thanh phủ định, click Negative Value and Axis. Trong Negative Value and Axis Settings, chọn các tùy chọn màu cho thanh giá trị âm và màu đường viền. Bạn có thể chọn cài đặt vị trí và màu của trục đó. Khi đã hoàn tất lựa chọn, click OK.

Bạn có thể đổi hướng của thanh bằng cách chọn một cài đặt trong box Bar Direction. Bài viết chọn giá trị mặc định là Context, nhưng bạn có thể chọn hướng từ trái sang phải hoặc ngược lại, tùy vào cách bạn muốn hiển thị dữ liệu.

Định dạng ô bằng một bộ icon

Dùng icon để chú thích và phân loại dữ liệu thành 3 tới 5 danh mục được phân chia bằng ngưỡng giá trị. Mỗi icon đại diện cho một phạm vi giá trị. Ví dụ, trong bộ icon 3 mũi tên, mũi tên màu xanh đi lên chỉ giá trị tăng dần, mũi tên màu vàng sang ngang là giá trị trung bình, mũi tên đỏ đi xuống là giá trị giảm dần.

Bạn có thể phân loại ô dùng định dạng này theo icon - chỉ cần dùng menu ngữ cảnh.

Bạn có thể chọn chỉ hiện icon đáp ứng điều kiện. Ví dụ, hiện icon cảnh báo những ô thấp hơn giá trị tới hạn và không có icon cho những ô vượt quá ngưỡng này. Để làm việc đó, hãy ẩn icon bằng cách chọn No Cell Icon từ danh sách thả xuống bên cạnh icon khi bạn đang thiết lập điều kiện. Bạn cũng có thể tạo kết hợp bộ icon riêng. Ví dụ: tích biểu tượng màu xanh, đèn giao thông màu vàng và lá cờ màu đỏ.

Định dạng nhanh

1. Chọn ô muốn định dạng có điều kiện.
2. Trên tab Home > Style > click mũi tên cạnh Conditional Formatting, click Icon Set, rồi chọn một bộ icon.

Định dạng nâng cao

1. Chọn ô muốn định dạng có điều kiện.

2. Trên tab Home > nhóm Styles > click mũi tên cạnh Conditional Formatting > click Manage Rules > mở Conditional Formatting Rules Manager.

3. Làm một trong những việc sau:

• Click New Rule để thêm định dạng có điều kiện.
• Thay đổi định dạng có điều kiện như ý muốn (tương tự như trên).
• Trong Apply Rule To, tùy chọn thay đổi phạm vi các trường trong Values của báo cáo PivotTable.

4. Trong Select a Rule Type, click Format all cells based on their values.

5. Trong Edit the Rule Description > box Format Style > chọn Icon Set.

• Chọn bộ icon. Mặc định là 3 Traffic Lights (Unrimmed). Số icon, toán tử so sánh mặc định và giá trị tới hạn cho mỗi icon có thể khác nhau theo từng bộ.
• Bạn có thể điều chỉnh toán tử so sánh và giá trị ngưỡng. Phạm vi giá trị mặc định cho mỗi icon bằng kích thước, nhưng bạn có thể điều chỉnh nó phù hợp với nhu cầu riêng. Đảm bảo ngưỡng đó có thứ tự logic từ cao tới thấp nhất khi được sắp xếp từ trên xuống dưới.
• Làm một trong những việc sau:
  • Định dạng giá trị số, ngày hoặc thời gian: Chọn Number.
  • Định dạng %: Chọn Percent.
  • Định dạng phân vị từ 0 tới 100.
  • Định dạng kết quả công thức: Chọn Formula nhập công thức vào box Value.
• Để icon đầu tiên đại diện cho giá trị thấp hơn, icon cuối cùng là giá trị cao hơn, chọn Reverse Icon Order.
• Để chỉ hiện icon, không có giá trị trong ô đó, chọn Show Icon Only.

Định dạng ô chứa text, số, ngày hoặc giá trị thời gian

Để tìm ô cụ thể dễ hơn, bạn có thể định dạng chúng bằng một toán tử so sánh. Ví dụ, trong bảng kiểm kê hàng hóa được phân loại theo danh mục, bạn có thể highlight những sản phẩm chứa ít hơn 10 mục màu vàng. Mặt khác, trong worksheet tổng kết cửa hàng bán lẻ, bạn có thể xác định toàn bộ cửa hàng có lợi nhuận lớn hơn 10%, lượng tiêu thụ ít hơn 100.000USD và ở vùng Đông Nam.

Ví dụ bên dưới áp dụng các tiêu chí định dạng có điều kiện cho dữ liệu như Greater Than, Top%. Cụ thể, ở đây, các thành phố có số dân lớn hơn 2.000.000 sẽ được tô nền màu xanh, nhiệt độ cao trung bình ở mức 30% sẽ có màu cam.

Định dạng nhanh

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hoặc báo cáo PivotTable.
2. Trên tab Home > Styles, click mũi tên cạnh Conditional Formatting > click Highlight Cells Rules.
3. Chọn lệnh bạn muốn như Between, Equal To Text that Contains, hoặc A Date Occurring.
4. Nhập giá trị bạn muốn sử dụng, rồi chọn định dạng.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hoặc báo cáo PivotTable.

2. Trên tab Home > Styles, click mũi tên bên cạnh Conditional Formatting, rồi click Manage Rules.

3. Thực hiện một trong những việc sau ở bảng Conditional Formatting Rules Manager.

• Thêm định dạng có điều kiện: click New Rule.
• Thay đổi định dạng có điều kiện
  • Đảm bảo đã chọn worksheet, bảng hay báo cáo PivotTable phù hợp trong box Show formatting rules for.
  • Thay đổi phạm vi các ô bằng cách click Collapse Dialog trong Applies to để tạm thời ẩn box hộp thoại đó, bằng cách chọn phạm vi ô mới trên worksheet đó hoặc bảng tính khác, rồi chọn Expand Dialog.
  • Chọn quy tắc, rồi click Edit Rule.

4. Trong Apply Rule To, tùy chọn thay đổi phạm vi các trường trong khu vực Values của một báo cáo PivotTable.

5. Trong Select a Rule Type, click Format only cells that contain.

6. Tại Edit the Rule Description > Format only cells with, thực hiện một trong những việc sau:

• Định dạng theo số, ngày hoặc thời gian: Chọn Cell Value, chọn toán tử so sánh, rồi nhập số, ngày hoặc thời gian. Ví dụ, chọn Between, rồi nhập 100 và 200, hoặc chọn Equal to, rồi nhập 1/1/2009.
• Định dạng theo text: Chọn Specific Text, chọn toán tử so sánh, rồi nhập text. Ví dụ, chọn Contains > nhập Silver hoặc chọn Starting with > nhập Tri. Chuỗi tìm kiếm bao gồm trích dẫn và bạn có thể dùng các kí tự đại diện. Độ dài tối đa của chuỗi là 255 ký tự.
• Định dạng theo ngày: Chọn Dates Occurring > chọn so sánh ngày/tháng. Ví dụ: chọn Yesterday hoặc Next week.
• Định dạng các ô có khoảng trống hoặc không: Chọn Blanks hoặc No Blanks. Một giá trị trống là ô không chứa dữ liệu, khác ô có một hoặc nhiều dấu cách. (dấu cách cũng được xem là text).
• Định dạng ô có giá trị lỗi hoặc không lỗi: Chọn Errors hoặc No Errors. Các giá trị lỗi bao gồm: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.

7. Để chọn một định dạng, click Format để mở hộp thoại Format Cells.

8. Chọn số, font, đường viền hoặc định dạng lấp đầy bạn muốn áp dụng khi giá trị ô đáp ứng điều kiện, rồi click OK. Bạn có thể chọn một hoặc nhiều định dạng. Định dạng bạn chọn hiển thị trong box Preview.

Chỉ định dạng các giá trị được xếp hạng đầu hoặc dưới cùng

Bạn có thể tìm thấy các giá trị cao và thấp nhất trong phạm vi ô dựa trên một giá trị ngưỡng lựa chọn. Ví dụ, bạn có thể tìm thấy top 5 sản phẩm bán chạy trong báo cáo theo vùng, 15% sản phẩm bị đánh giá thấp trong khảo sát khách hàng hoặc top 25 người lương cao nhất trong bộ phận.

Định dạng nhanh

1. Chọn một hoặc nhiều ô hơn trong phạm vi, bảng hoặc báo cáo PivotTable.
2. Trên tab Home > nhóm Style, click mũi tên cạnh Conditional Formatting > click Top/Bottom Rules.
3. Chọn lệnh bạn muốn như Top 10 items hoặc Bottom 10 %.
4. Nhập giá trị bạn muốn dùng, rồi chọn định dạng.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hay báo cáo PivotTable.

2. Trên tab Home > nhóm Styles, click mũi tên cạnh Conditional Formatting > click Manage Rules. Hộp thoại Conditional Formatting Rules Manager hiện ra.

3. Thực hiện một trong những việc sau:

• Để thêm định dạng có điều kiện, click New Rule.
• Để thay đổi định dạng, hãy làm như sau:
  • Đảm bảo chọn worksheet, bảng hay báo cáo PivotTable phù hợp trong Show formatting rules for.
  • Tùy chọn thay đổi phạm vi ô bằng cách click Collapse Dialog trong Applies to để tạm thời ẩn hộp thoại, bằng cách chọn phạm vi ô mới trên worksheet, rồi chọn Expand Dialog.
  • Chọn quy tắc, rồi click Edit Rule.

4. Tùy chọn thay đổi các trường phạm vi trong Values của báo cáo PivotTable tại Apply Rule To.

5. Trong Select a Rule Type, click Format only top or bottom ranked values.

6. Trong Edit the Rule Description > tới Format values that rank in the list box, chọn Top hoặc Bottom.

7. Thực hiện một trong những việc sau:

• Để xác định số cao hoặc thấp nhất, nhập số, rồi xóa box % of the selected range. Giá trị hợp lệ từ 1 tới 100.
• Để xác định tỷ lệ phần trăm đầu hoặc cuối, nhập số và tích box % of the selected range. Giá trị hợp lệ từ 1 tới 100.

8. Tùy chọn thay đổi cách áp dụng định dạng cho các trường trong vùng Values của báo cáo PivotTable được khoanh phạm vi theo trường tương ứng.

Mặc định, định dạng có điều kiện dựa trên toàn bộ giá trị hiển thị. Tuy nhiên, khi bạn khoanh vùng theo trường tương ứng, thay vì dùng toàn bộ giá trị hiển thị, bạn có thể áp dụng định dạng có điều kiện cho từng kết hợp:

• Một cột và trường hàng chính của nó, bằng cách chọn each Column group.
• Một hàng và trường cột chính của nó, bằng cách chọn each Row group.

9. Để xác định định dạng, click Format để mở Format Cells.

10. Chọn số, font, đường viền hoặc định dạng lấp đầy bạn muốn áp dụng khi giá trị ô đáp ứng điều kiện, rồi click OK.

Bạn có thể chọn nhiều hơn một định dạng. Các định dạng bạn lựa chọn sẽ hiện trong box Preview.

Chỉ định dạng các giá trị trên hoặc dưới trung bình

Bạn có thể tìm thấy các giá trị trên/dưới trung bình hoặc có độ lệch chuẩn trong phạm vi ô. Ví dụ, bạn có thể tìm thấy những người có kết quả trên trung bình trong đánh giá hiệu quả hàng năm hoặc xác định vật liệu sản xuất nằm dưới hai độ lệch chuẩn trong đánh giá chất lượng.

Định dạng nhanh

1. Chọn một hoặc nhiều ô trong phạm vi hoặc báo cáo PivotTable.
2. Trên tab Home > Style, click mũi tên bên cạnh Conditional Formatting > click Top/Bottom Rules.
3. Chọn lệnh bạn muốn, ví dụ Above Average hoặc Below Average.
4. Nhập giá trị bạn muốn sử dụng, rồi chọn định dạng.

Bạn có thể thay đổi phương pháp khoanh vùng các trường trong Values của báo cáo PivotTable bằng cách dùng nút tùy chọn Apply formatting rule to.

Định dạng nâng cao

1. Chọn một hoặc nhiều ô trong phạm vi, bảng hay báo cáo PivotTable.

2. Trên tab Home > Styles, click mũi tên cạnh Conditional Formatting, rồi click Manage Rules để mở hộp thoại Conditional Formatting Rules Manager.

3. Thực hiện một trong những việc sau:

• Để thêm định dạng mới, click New Rule.
• Để thay đổi định dạng có điều kiện:
  • Chọn worksheet, bảng hay báo cáo PivotTable phù hợp trong box Show formatting rules for.
  • Tùy chọn, thay đổi phạm vi các ô bằng cách click Collapse Dialog trong Applies to để tạm thời ẩn box hộp thoại đó, chọn phạm vi mới trên worksheet, rồi chọn Expand Dialog.
  • Chọn quy tắc, rồi click Edit Rule.

4. Trong Apply Rule To, tích vào lựa chọn thay đổi phạm vi các trường trong khu vực Values của báo cáo PivotTable.

5. Trong Select a Rule Type, click Format only values that are above or below average.

6. Trong Edit the Rule Description, tại box Format values that are, hãy làm một trong những việc sau:

• Để định dạng ô trên hoặc dưới trung bình cho toàn bộ ô trong phạm vi, chọn Above hoặc Below.
• Để định dạng ô trên hoặc dưới một, hai hoặc ba độ lệch chuẩn cho toàn bộ ô trong phạm vi, chọn một độ lệch chuẩn.

7. Tùy chọn thay đổi cách định dạng áp dụng cho các trường trong vùng Values của báo cáo PivotTable được khoanh vùng theo trường tương ứng.

Mặc định, định dạng có điều kiện dựa trên các giá trị hiển thị. Tuy nhiên, khi bạn khoanh vùng theo trường tương ứng, thay vì dùng toàn bộ giá trị hiển thị, bạn có thể áp dụng định dạng có điều kiện cho mỗi kết hợp sau:

• Một cột và trường hàng chính của nó, bằng cách chọn each Column group.
• Một hàng và trường cột chính của nó, bằng cách chọn each Row group.

8. Click Format để hiện box Format Cells.

9. Chọn số, font, đường viền hoặc định dạng lấp đầy bạn muốn áp dụng khi giá trị ô đáp ứng điều kiện, click OK.

Bạn có thể chọn nhiều hơn một định dạng. Các định dạng bạn chọn sẽ hiện trong box Preview.

Chỉ định dạng các giá trị duy nhất hoặc trùng lặp

Ví dụ dưới đây dùng định dạng có điều kiện trên cột Instructor để tìm người dạy nhiều hơn một lớp (các tên trùng lặp được highlight bằng màu đỏ nhạt). Giá trị điểm chỉ xuất hiện một lần trong cột Grade (giá trị duy nhất) được highlight bằng màu xanh lá.

Định dạng nhanh

1. Chọn ô bạn muốn định dạng có điều kiện.
2. Trên tab Home > Style, click mũi tên cạnh Conditional Formatting > click Highlight Cells Rules.
3. Chọn Duplicate Values.
4. Nhập giá trị bạn muốn sử dụng, rồi chọn định dạng.

Định dạng nâng cao

1. Chọn ô bạn muốn định dạng có điều kiện.

2. Trên tab Home > Styles, click mũi tên cạnh Conditional Formatting, rồi click Manage Rules.

3. Làm một trong những việc sau:

• Thêm định dạng có điều kiện: click New Rule.
• Thay đổi định dạng có điều kiện, làm như sau:
  • Chọn worksheet hay bảng phù hợp trong box Show formatting rules for.
  • Thay đổi phạm vi ô bằng cách click Collapse Dialog trong box Applies to. Sau đó, chọn phạm vi ô mới trên worksheet, rồi chọn Expand Dialog.
  • Chọn quy tắc, rồi click Edit Rule.

4. Trong Select a Rule Type, click Format only unique or duplicate values.

5. Trong Edit the Rule Description > Format all list > chọn unique hoặc duplicate.

6. Click Format để hiện Format Cells.

7. Chọn số, font, đường viền hay định dạng lấp đầy muốn áp dụng khi giá trị ô đáp ứng điều kiện, rồi click OK.

Thiết lập quy tắc định dạng có điều kiện của riêng bạn

Nếu không có lựa chọn nào kể trên đáp ứng nhu cầu của bạn, bạn có thể tạo quy tắc định dạng có điều kiện như sau:

1. Chọn ô muốn định dạng.

2. Trên tab Home, click Conditional Formatting > New Rule.

3. Tạo quy tắc và xác định các tùy chọn định dạng của nó, rồi click OK.

Nếu không thấy các lựa chọn mong muốn, bạn có thể dùng một công thức để xác định ô cần định dạng.

Dùng công thức để xác định ô cần định dạng

Nếu không thấy lựa chọn chính xác bạn cần khi tạo quy tắc định dạng riêng, bạn có thể dùng công thức logic để xác định tiêu chí định dạng. Ví dụ, so sánh giá trị trong lựa chọn với kết quả một hàm trả về hoặc đánh giá dữ liệu trong ô ngoài phạm vi lựa chọn (có thể nằm ở worksheet khác trong cùng một workbook). Công thức của bạn phải trả về True hoặc False (1 hoặc 0) nhưng bạn có thể dùng logic có điều kiện để xâu chuỗi chúng lại với nhau thành một bộ định dạng có điều kiện tương ứng như các màu khác nhau cho từng giá trị của một tập hợp nhỏ (ví dụ: tên danh mục sản phẩm).

1. Trên tab Home > Styles, click mũi tên cạnh Conditional Formatting > click Manage Rules.

2. Làm một trong những việc sau:

• Thêm định dạng có điều kiện, click New Rule.
• Thay đổi định dạng có điều kiện:
  • Chọn worksheet, bảng hay báo cáo PivotTable phù hợp trong box liệt kê Show formatting rules for.
  • Thay đổi phạm vi ô bằng cách click Collapse Dialog trong box Applies to để tạm thời ẩn box đó. Chọn phạm vi ô mới trên worksheet bạn muốn, rồi click Expand Dialog.
  • Chọn quy tắc, rồi click Edit Rule.

3. Trong Apply Rule To, chọn cách thay đổi phạm vi cho các trường trong Values của báo cáo PivotTable.

• Khoanh phạm vi theo lựa chọn: Click Selected cells.
• Khoanh phạm vi theo trường tương ứng: Click All cells showing <Values field> values.
• Khoanh phạm vi theo trường giá trị: Click All cells showing <Values field> for <Row>.

4. Trong Select a Rule Type, click Use a formula to determine which cells to format.

• Trong Edit the Rule Description, tại box Format values where this formula is true, nhập công thức. Bạn phải bắt đầu công thức bằng dấu =. Công thức đó phải trả về một giá trị logic TRUE (1) hoặc FALSE (0).
• Click Format để hiện Format Cells.
• Chọn số, font, đường viền hoặc định dạng lấp đầy bạn muốn áp dụng khi giá trị ô đáp ứng điều kiện, rồi nhấn OK. Bạn có thể chọn nhiều hơn một định dạng. Định dạng được chọn sẽ hiện ở box Preview.

Ví dụ 1: Dùng hai định dạng có điều kiện với tiêu chí sử dụng kiểm tra AND hoặc OR.

Ví dụ sau hiện cách sử dụng hai quy tắc định dạng có điều kiện. Nếu không dùng quy tắc 1, quay tắc 2 sẽ được áp dụng.

Quy tắc 1: Người mua nhà đã lên ngân sách trả trước 75.000USD và vay thế chấp 1.500USD mỗi tháng. Nếu cả trả trước và thanh toán hàng tháng đáp ứng những yêu cầu này, ô B4 và B5 sẽ có màu xanh lá.

Quy tắc 2: Nếu trả trước hay thanh toán hàng tháng không đáp ứng ngân sách của người mua. Ô B4 và B5 sẽ có màu đỏ. Thay một số giá trị như APR, điều khoản vay, trả trước và giá mua để xem điều gì sẽ xảy ra với các ô định dạng có điều kiện.

Ví dụ 2: Đổi màu hàng bằng hàm MOD và ROW

Định dạng có điều kiện áp dụng cho mỗi ô worksheet này sẽ tô màu nền xen kẽ các hàng trong phạm vi ô bằng màu xanh dương. Bạn có thể chọn toàn bộ ô trong worksheet khi click vào hình vuông phía trên hàng 1 và bên trái cột A. Hàm MOD trả về số dư sau khi chia một số (đối số đầu tiên) cho ước số (đối số thứ hai). Hàm ROW trả về số hàng hiện tại. Khi chia số hàng hiện tại cho 2, bạn luôn nhận được số dư bằng 0 cho số chẵn hay 1 cho số lẻ. Do 0 là False và 1 là TRUE, Excel sẽ định dạng mỗi hàng được đánh số lẻ. Quy tắc này dùng công thức: =MOD(ROW(),2)=1.

Lưu ý: Bạn có thể nhập các tham chiếu ô trong công thức bằng cách chọn ô trực tiếp trên worksheet. Việc này sẽ chèn các tham chiếu ô tuyệt đối. Nếu muốn Excel điều chỉnh tham chiếu cho mỗi ô trong phạm vi lựa chọn, hãy dùng các tham chiếu ô tương ứng.

Dưới đây là video hướng dẫn dùng công thức với định dạng có điều kiện:

Sao chép và dán định dạng có điều kiện

Nếu muốn áp dụng kiểu định dạng hiện tại cho dữ liệu mới hoặc khác trên worksheet, bạn có thể dùng Format Painter để sao chép định dạng có điều kiện đó cho dữ liệu mong muốn.

1. Click ô áp dụng định dạng có điều kiện bạn muốn sao chép.

2. Click Home > Format Painter.

Chuyển con trỏ sang Paintbrush.

3. Để dán định dạng có điều kiện, kéo cọ vẽ qua các ô hay phạm vi ô muốn định dạng.

4. Nhấn Esc để dừng dùng định dạng cọ vẽ.

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

Nếu worksheet chứa định dạng có điều kiện, bạn có thể nhanh chóng xác định ô để có thể sao chép, thay đổi hoặc xóa định dạng có điều kiện đó. Dùng lệnh Go To Special để chỉ tìm ô chứa định dạng có điều kiện nào đó hoặc tìm toàn bộ ô định dạng theo điều kiện.

Tìm toàn bộ ô được định dạng theo điều kiện

1. Click ô bất kỳ không có định dạng theo điều kiện.

2. Trên tab Home > Editing > click mũi tên cạnh Find & Select, rồi click Conditional Formatting.

Chỉ tìm ô có cùng định dạng theo điều kiện

1. Click ô bất kỳ chứa định dạng có điều kiện bạn muốn tìm.
2. Trên tab Home, trong nhóm Editing, click mũi tên cạnh Find & Select, rồi click Go To Special.
3. Click Conditional formats.
4. Click Same under Data validation.

Quản lý các quy tắc định dạng có điều kiện

Khi dùng định dạng theo điều kiện, bạn thiết lập các quy tắc cho Excel dùng để xác định thời điểm áp dụng định dạng có điều kiện. Để quản lý những quy tắc này, bạn cần hiểu thứ tự đánh giá các quy tắc, điều gì sẽ xảy ra khi xung đột từ hai quy tắc trở lên, cách sao chép & dán có thể ảnh hưởng tới đánh giá quy tắc như thế nào, cách thay đổi thứ tự quy tắc được đánh giá và khi nào nên dừng đánh giá quy tắc.

Tìm hiểu mức độ ưu tiên quy tắc định dạng có điều kiện

Bạn tạo, chỉnh sửa, xóa và xem toàn bộ các quy tắc định dạng có điều kiện trong workbook bằng cách dùng hộp thoại Conditional Formatting Rules Manager. (Trên tab Home, click Conditional Formatting > click Manage Rules.)

Box Conditional Formatting Rules Manager xuất hiện.

Khi áp dụng 2 hay nhiều định dạng có điều kiện, những quy tắc này được đánh giá theo thứ tự ưu tiên (trên xuống dưới) bằng cách chúng được liệt kê trong hộp thoại.

Dưới đây là ví dụ ngày hết hạn cho ID. Ví dụ này muốn đánh dấu các huy hiệu sẽ hết hạn trong 60 ngày nhưng chưa tới hạn bằng màu nền vàng và đã hết hạn bằng màu nền đỏ.

Ở ví dụ này, các ô chứa số ID nhân viên có ngày hết hạn chứng chỉ trong 60 ngày được định dạng bằng màu vàng. Số ID nhân viên đã hết hạn chứng chỉ được tô màu đỏ. Các quy tắc được hiển thị trong ảnh sau:

Quy tắc đầu tiên (nếu là True thì sẽ đặt màu nền sang màu đỏ) kiểm tra giá trị ngày trong cột B dựa trên ngày hiện tại (kết quả được tính bằng hàm TODAY trong công thức). Gán công thức vào dữ liệu đầu tiên trong cột B2. Công thức cho quy tắc này là =B2<TODAY(). Công thức này kiểm tra các ô trong cột B (ô B2:B15). Nếu công thức này trong ô bất kỳ ở cột B được đánh giá True, ô tương ứng của nó trong cột A (ví dụ, A5 tương ứng B5, A11 tương ứng B11) sẽ được định dạng bằng màu nền đỏ. Sau khi đánh giá toàn bộ ô được xác định trong Applies to theo quy tắc đầu tiên, quy tắc thứ hai sẽ được kiểm tra. Công thức này kiểm tra giá trị trong cột B có ít hơn 60 ngày tính từ ngày hiện tại (ví dụ: hôm nay là ngày 11/8/2010). Ô trong B4 là 4/10/2010, ít hơn 60 ngày tính từ hôm nay. Do đó, quy tắc đánh giá cột này là True và định dạng nó bằng màu nền vàng. Công thức cho quy tắc này là =B2<TODAY()+60. Bất kỳ ô được định dạng màu đỏ đầu tiên theo quy tắc cao nhất sẽ được để riêng.

Quy tắc cao hơn có mức độ ưu tiên lớn hơn quy tắc thấp hơn trong danh sách này. Mặc định, các quy tắc mới luôn được thêm vào phần đầu danh sách nên có mức ưu tiên cao hơn. Vì thế, bạn sẽ muốn để mắt tới thứ tự của chúng. Bạn có thể thay đổi thứ tự ưu tiên bằng cách dùng mũi tên Move Up và Move Down trong box hộp thoại này.

Điều gì xảy ra khi nhiều quy tắc định dạng có điều kiện cho kết quả True?

• Khi không xung đột quy tắc: Ví dụ, nếu một quy tắc định dạng ô có font đậm và quy tắc khác định dạng cùng ô đó bằng màu đỏ, ô này được định dạng bằng kiểu font chữ đậm và màu đỏ. Do không có xung đột giữa hai định dạng này, cả hai quy tắc này đều được áp dụng.
• Khi xung đột quy tắc: Ví dụ, một quy tắc thiết lập màu đỏ cho font chữ trong ô, quy tắc khác đặt font màu xanh. Do hai quy tắc này xung đột nên chỉ một quy tắc được áp dụng. Quy tắc có mức ưu tiên cao hơn sẽ được áp dụng.

Cách dán, lấp đầy và Format Painter ảnh hưởng tới quy tắc định dạng có điều kiện

Trong khi chỉnh sửa worksheet, bạn có thể sao chép & dán giá trị ô chứa các định dạng theo điều kiện, lấp đầy một phạm vi ô bằng định dạng có điều kiện hoặc dùng Format Painter. Những tác vụ này có thể ảnh hưởng tới mức độ ưu tiên của quy tắc định dạng có điều kiện như sau: Một quy tắc định dạng có điều kiện mới dựa trên ô nguồn được tạo cho các ô đích.

Nếu sao chép & dán giá trị ô chứa định dạng có điều kiện cho một worksheet trong phiên bản Excel khác, không quy tắc định dạng có điều kiện nào được tạo trong phiên bản đó và định dạng này không được sao chép lên phiên bản đó.

Điều gì xảy ra khi xung đột một định dạng có điều kiện và định dạng thủ công

Nếu quy tắc định dạng có điều kiện cho kết quả True, nó sẽ được ưu tiên hơn bất kỳ định dạng thủ công hiện có nào cho cùng lựa chọn đó. Điều này nghĩa là nếu chúng xung đột, định dạng có điều kiện sẽ được áp dụng. Nếu bạn xóa quy tắc định dạng có điều kiện, định dạng thủ công cho phạm vi ô vẫn được giữ lại.

Định dạng thủ công không được liệt kê trong hộp thoại Conditional Formatting Rules Manager hay dùng để xác định mức độ ưu tiên.

Chọn thời điểm dừng quy tắc đánh giá bằng Stop If True

Để tương thích ngược với các phiên bản Excel trước 2007, bạn có thể chọn Stop If True trong Manage Rules để mô phỏng cách định dạng có điều kiện có thể xuất hiện trong những phiên bản trước đó của Excel mà không hỗ trợ nhiều hơn 3 quy tắc định dạng có điều kiện hoặc áp dụng nhiều quy tắc cho cùng một phạm vi.

Ví dụ, nếu muốn áp dụng trên 3 quy tắc định dạng cho một phạm vi ô và đang làm việc trên phiên bản Excel trước 2007 thì bản Excel này:

• Chỉ đánh giá 3 quy tắc đầu tiên.
• Áp dụng quy tắc đầu tiên theo thứ tự ưu tiên là True.
• Bỏ qua các quy tắc có thứ tự ưu tiên thấp hơn nếu chúng là True.

Bảng sau tóm tắt từng điều kiện có thể cho 3 quy tắc đầu tiên:

Quy tắc 1	Quy tắc 2	Quy tắc 3	Kết quả
True	True hoặc False	True hoặc False	Áp dụng quy tắc 1, bỏ qua quy tắc 2 & 3
False	True	True hoặc False	Áp dụng quy tắc 2 và bỏ qua quy tắc 3
False	False	True	Áp dụng quy tắc 3
False	False	False	Không áp dụng quy tắc nào

Bạn có thể chọn hoặc xóa Stop If True để thay đổi hành vi mặc định:

• Để chỉ đánh giá quy tắc đầu tiên, chọn Stop If True cho quy tắc đầu tiên.
• Để chỉ đánh giá quy tắc đầu tiên và thứ hai, chọn Stop If True cho quy tắc thứ hai.

Bạn không thể chọn hoặc bỏ tích Stop If True nếu các quy tắc định dạng bằng thanh dữ liệu, thang màu hoặc bộ icon.

Chỉnh sửa thứ tự trong các quy tắc định dạng có điều kiện được đánh giá

Thứ tự trong danh sách các quy tắc định dạng có điều kiện được đánh giá - theo mức độ ưu tiên - cũng phản ánh tầm quan trọng của chúng: ở vị trí càng cao, tầm quan trọng của quy tắc đó cũng cao hơn. Điều này có nghĩa trong hai quy tắc định dạng có điều kiện xung đột với nhau, quy tắc ở vị trí cao hơn trong danh sách sẽ được áp dụng.

1. Trên tab Home > Style > click mũi tên cạnh Conditional Formatting, rồi click Manage Rules. Box Conditional Formatting Rules Manager mở ra.

Excel hiển thị các quy tắc định dạng có điều kiện cho lựa chọn hiện tại, bao gồm kiểu quy tắc, định dạng, phạm vi ô được áp dụng và cài đặt Stop If True.

Nếu không thấy quy tắc bạn muốn, trong box Show formatting rules for, đảm bảo bạn đã chọn đúng phạm vi ô, worksheet, bảng hoặc báo cáo PivotTable.

2. Chọn quy tắc. Bạn chỉ có thể chọn một quy tắc một lần.

3. Để di chuyển quy tắc lựa chọn lên trên theo thứ tự ưu tiên, click Move Up. Để chuyển quy tắc lựa chọn xuống dưới trong mức độ ưu tiên, click Move Down.

4. Nếu muốn dừng đánh giá một quy tắc cụ thể, chọn Stop If True.

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

Xóa định dạng có điều kiện trên worksheet

• Trên tab Home, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Làm theo những bước sau nếu bạn áp dụng định dạng có điều kiện trong worksheet và cần loại bỏ nó.

Đối với toàn bộ worksheet

• Trên tab Home, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Trong phạm vi ô

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

2. Click Quick Analysis Lens hiện ở góc dưới, bên phải dữ liệu lựa chọn.

3. Click Clear Format.

Tìm và xóa định dạng có điều kiện giống nhau trong worksheet

1. Click vào ô chứa định dạng có điều kiện bạn muốn xóa khỏi worksheet.
2. Trên tab Home, click mũi tên cạnh Find & Select > click Go To Special.
3. Click Conditional formats.
4. Click Same under Data validation để chọn toàn bộ ô chứa các quy tắc định dạng có điều kiện giống nhau.
5. Trên tab Home, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

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

Định dạng ô bằng thang màu

Thang màu là những hướng dẫn trực quan, giúp bạn hiểu phân bổ và biến thiên dữ liệu. Excel có hai loại thang màu: thang hai màu và thang 3 màu. Hai thang màu này giúp bạn so sánh phạm vi ô bằng cách chuyển màu giữa hai hoặc ba sắc độ.

Ở thang 2 màu, sắc độ màu đại diện cho giá trị cao và thấp hơn. Ví dụ, trong thang màu xanh lá và vàng, bạn có thể xác định các ô giá trị cao hơn bằng màu xanh, ô giá trị thấp hơn là màu vàng.

Ở thang 3 màu, sắc độ màu đại diện cho các giá trị cao hơn, trung bình và thấp hơn. Ví dụ, thang màu xanh lá, vàng và đỏ, bạn có thể xác định giá trị cao hơn là màu xanh lá, trung bình là màu vàng và thấp hơn là màu đỏ.

Mẹo: Bạn có thể phân loại ô sử dụng một trong những định dạng này theo màu của chúng - chỉ cần sử dụng menu ngữ cảnh.

1. Chọn ô muốn định dạng có điều kiện.
2. Click Home > Conditional Formatting > Color Scales, rồi chọn thang màu.

Định dạng ô bằng thanh dữ liệu

Thanh dữ liệu giúp bạn thấy giá trị của một ô với các ô khác. Chiều dài thanh dữ liệu đại diện cho giá trị trong ô. Thanh dữ liệu càng dài thì giá trị của ô càng cao và ngược lại. Thanh dữ liệu hữu ích khi bạn muốn tìm số cao và thấp hơn, nhất là ở bảng tính dữ liệu lớn như doanh thu bán hàng đồ chơi cao và thấp nhất trong báo cáo doanh số trong mùa lễ hội.

1. Chọn ô bạn muốn định dạng có điều kiện
2. Click Home > Conditional Formatting > Data Bars, rồi chọn thanh dữ liệu.

Định dạng ô theo bộ icon

Dùng bộ icon để chú thích và phân loại dữ liệu thành 3 tới 5 danh mục theo giá trị ngưỡng. Mỗi icon đại diện cho một phạm vi dữ liệu. Ví dụ, trong bộ icon 3 mũi tên, mũi tên màu xanh trỏ lên đại diện cho giá trị cao hơn, mũi tên màu vàng sang ngang là giá trị trung bình và mũi tên màu đỏ đi xuống là giá trí thấp hơn.

1. Chọn ô muốn định dạng có điều kiện.
2. Click Home > Conditional Formatting > Icon Sets và chọn bộ icon.

Chỉ định dạng ô chứa text, số, ngày hoặc thời gian

Để dễ dàng xác định ô trong phạm vi, bạn có thể định dạng những ô cụ thể đó dựa trên phép so sánh. Ví dụ, trong worksheet hàng tồn kho được phân loại theo danh mục, bạn có thể tô màu vàng số lượng sản phẩm ít hơn 10. Hoặc trong worksheet tổng kết cửa hàng bán lẻ, bạn có thể chọn toàn bộ cửa hàng có lợi nhuận lớn hơn 10%, doanh số bán hàng thấp hơn 100.000USD và khu vực ở Đông Nam.

1. Chọn ô bạn muốn định dạng có điều kiện.
2. Trên tab Home > Tables > click Highlight Cell Rules.
3. Chọn lệnh bạn muốn, ví dụ: Between, Equal To Text that Contains hoặc A Date Occurring.
4. Nhập giá trị muốn dùng, rồi chọn định dạng (fill, text hoặc border color). Click OK.

Chỉ định dạng các giá trị được xếp hạng đầu hoặc cuối

Bạn có thể tìm thấy các giá trị cao và thấp nhất trong phạm vi ô dựa trên giá trị ngưỡng lựa chọn. Ví dụ, bạn có thể tìm thấy top 5 sản phẩm bán chạy trong báo cáo theo vùng, sản phẩm dưới 15% trong nghiên cứu khách hàng hoặc 25 người có mức lương cao nhất trong bộ phận.

1. Chọn các ô muốn định dạng có điều kiện.
2. Trên tab Home, trong nhóm Tables, click Top/Bottom Rules.
3. Chọn lệnh bạn muốn, ví dụ: Top 10 items or Bottom 10 %.
4. Nhập giá trị bạn muốn dùng, rồi chọn định dạng (màu tô, màu text hoặc màu đường viền), rồi click OK.

Chỉ định dạng các giá trị trên hoặc dưới trung bình

Bạn có thể tìm thấy các giá trị trên hoặc dưới trung bình hoặc độ lệch chuẩn trong một phạm vi ô. Ví dụ, bạn có thể tìm thấy những người đạt kết quả trên mức trung bình trong đánh giá thường niên hoặc xác định vật liệu sản xuất nằm dưới hai độ lệch chuẩn trong đánh giá xếp hạng.

• Chọn ô bạn muốn định dạng có điều kiện.
• Trên tab Home, trong nhóm Tables, click Top/Bottom Rules.
• Chọn lệnh bạn muốn như Above Average hoặc Below Average.
• Nhập giá trị bạn muốn sử dụng, rồi chọn một định dạng (lấp đầy, text, hoặc màu đường viền), rồi click OK.

Chỉ định dạng các giá trị duy nhất hoặc trùng lặp

Bạn có thể tìm thấy các giá trị duy nhất hoặc trùng lặp trong một phạm vi ô.

• Chọn các ô muốn định dạng có điều kiện.
• Trên tab Home, trong nhóm Tables, click Highlight Cell Rules, rồi chọn Duplicate Values.
• Nhập giá trị bạn muốn sử dụng, rồi chọn định dạng và click OK.

Sao chép và dán định dạng có điều kiện

Nếu muốn áp dụng kiểu định dạng hiện tại cho dữ liệu mới hoặc khác trên worksheet, bạn có thể dùng Format Painter để sao chép định dạng có điều kiện cho dữ liệu đó.

1. Click ô chứa định dạng có điều kiện bạn muốn sao chép.

2. Click Home > Format Painter.

Con trỏ lúc này sẽ thay đổi sang cọ vẽ.

Mẹo: Bạn có thể click đúp Format Painter nếu muốn tiếp tục sử dụng cọ vẽ để dán định dạng có điều kiện vào ô khác.

3. Dán định dạng có điều kiện, kéo cọ vẽ qua các ô hoặc phạm vi ô muốn định dạng.

4. Để dừng sử dụng cọ vẽ, nhấn Esc.

Lưu ý: Nếu đã dùng công thức trong quy tắc áp dụng định dạng có điều kiện, bạn có thể điều chỉnh các tham chiếu liên quan hoặc tuyệt đối trong công thức đó sau khi dán định dạng có điều kiện.

Quản lý các quy tắc định dạng có điều kiện

Khi dùng định dạng có điều kiện, bạn thiết lập các quy tắc Excel dùng cho việc xác định thời điểm áp dụng định dạng có điều kiện. Để quản lý những quy tắc này, điều quan trọng bạn cần hiểu thứ tự đánh giá quy tắc, điều gì xảy ra khi xung đột từ 2 quy tắc trở lên và cách sao chép & dán có thể ảnh hưởng tới đánh giá quy tắc như thế nào.

Bạn có thể xem lại các quy tắc định dạng có điều kiện trong Excel cho web bằng cách dùng bảng Conditional Formatting.

1. Trên tab Home, click Conditional Formatting > click Manage Rules.

2. Bảng Conditional Formatting xuất hiện ở bên phải dữ liệu của bạn. Những lưu ý sau sẽ giúp bạn quyết định tác động của các quy tắc được liệt kê.

• Cách Excel quyết định thứ tự áp dụng các quy tắc định dạng có điều kiện

Khi áp dụng từ hai quy tắc trở lên cho một phạm vi ô, những quy tắc này được đánh giá theo thứ tự ưu tiên (trên xuống dưới) bằng cách liệt kê chúng trong bảng này.

Quy tắc ở vị trí cao sẽ có thứ tự ưu tiên lớn hơn quy tắc ở vị trí thấp hơn trong danh sách. Mặc định, Excel luôn thêm quy tắc mới vào vị trí đầu tiên của danh sách, do đó, nó có độ ưu tiên cao hơn. Vì thế, bạn nên để mắt tới thứ tự của chúng.

• Điều gì xảy ra khi có nhiều hơn một quy tắc định dạng có điều kiện cho kết quả True

Một phạm vi ô có thể bao gồm nhiều hơn một quy tắc định dạng có điều kiện cho kết quả True. Đây là cách áp dụng chúng:

Khi không xung đột quy tắc - Ví dụ: một quy tắc định dạng ô bằng font chữ đậm, quy tắc khác định dạng cùng ô đó bằng màu đỏ. Do không có xung đột định dạng, hai quy tắc đều được áp dụng cho định dạng ô đó.

Khi xung đột quy tắc - Ví dụ: Một quy tắc thiết lập màu font ô đỏ, quy tắc khác lại đặt màu font ô là xanh. Do hai quy tắc xung đột, quy tắc có độ ưu tiên cao hơn trong danh sách sẽ được áp dụng.

• Cách dán, lấp đầy và Format Painter ảnh hưởng tới các quy tắc định dạng có điều kiện

Trong khi chỉnh sửa worksheet, bạn có thể sao chép & dán các giá trị ô lựa chọn vào một phạm vi ô bằng các định dạng có điều kiện hoặc dùng Format Painter. Những hoạt động này có thể ảnh hưởng tới độ ưu tiên quy tắc của định dạng có điều kiện như sau: quy tắc định dạng có điều kiện mới sẽ dựa trên các ô nguồn dược tạo cho ô đích.

• Điều gì xảy ra khi xung đột định dạng có điều kiện và định dạng thông thường

Đối với một phạm vi ô, nếu quy tắc định dạng có điều kiện cho kết quả True, nó được ưu tiên hơn định dạng bình thường hiện tại. Bạn áp dụng định dạng thường bằng các nút trong nhóm Font trên tab Home (Excel 2016/2013) hoặc nhóm Cell trên tab Home (Excel 2010/2007). Nếu xóa quy tắc định dạng có điều kiện, định dạng thường cho phạm vi ô đó vẫn được giữ nguyên.

Định dạng thường không được liệt kê trong bảng Conditional Formatting hoặc dùng để xác định mức độ ưu tiên.

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

Bạn có thể xóa định dạng có điều kiện trong ô lựa chọn hoặc toàn bộ worksheet như sau:

• Xóa định dạng có điều kiện trong các ô lựa chọn:
  • Chọn những ô đó trong worksheet.
  • Click tab Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
• Để xóa định dạng có điều kiện trong toàn bộ worksheet, click Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.