Học Excel - Bài 13: Sử 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 phát hiện và phân tích dữ liệu, nhận diện các lỗi quan trọng, và hiểu rõ hơn về mẫu và xu hướng.

Định dạng có điều kiện của Excel còn giúp bạn nhanh chóng nhận biết và làm nổi bật các ô hoặc phạm vi ô quan trọng, làm nổi bật các giá trị ngoại lệ và trực quan hóa dữ liệu bằng cách sử dụng thanh Data Bars, thang màu và biểu tượng tùy chỉnh tương ứng với các điều kiện cụ thể trong dữ liệu đó.

Một định dạng có điều kiện sẽ thay đổi cách mà các ô được hiển thị dựa trên các điều kiện bạn thiết lập. Nếu điều kiện được đáp ứng, Excel sẽ áp dụng định dạng cho phạm vi ô đó. Nếu điều kiện không được đáp ứng, phạm vi ô đó sẽ không được định dạng. Microsoft Excel cung cấp sẵn nhiều điều kiện khác nhau. Tuy nhiên, bạn cũng có thể tạo ra các điều kiện tùy chỉnh bằng cách sử dụng các 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 ô (chẳng hạn một lựa chọn hoặc một phạm vi được đặt tên), một bảng tính Excel và, trong Excel cho Windows, thậm chí cả một báo cáo PivotTable. Lưu ý rằng, Excel cung cấp một số cân nhắc thêm về định dạng có điều kiện trong báo cáo dạng PivotTable - xem mục Áp dụng định dạng có điều kiện trong báo cáo PivotTable dưới tab Windows.

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

• Đị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
  • Sử dụng Quick Analysis để áp dụng định dạng có điều kiện
  • Định dạng ô bằng cách sử dụng thang màu 2 mức
  • Định dạng ô bằng cách sử dụng thang màu 3 mức
  • Định dạng ô bằng các thanh dữ liệu
  • Định dạng ô bằng một bộ biểu tượng
  • Định dạng ô chứa văn bản, số, ngày hoặc giá trị thời gian
  • Chỉ định dạng các giá trị được xếp hạng đầu hoặc cuối cùng
  • Chỉ định dạng các giá trị trên hoặc dưới trung bình
  • Chỉ định dạng các giá trị duy nhất hoặc trùng lặp
  • Thiết lập quy tắc định dạng có điều kiện của riêng bạn
  • Sử dụng công thức để xác định ô cần định dạng
  • Sao chép và dán định dạng có điều kiện
  • Tìm ô chứa định dạng có điều kiện
  • Quản lý các quy tắc định dạng có điều kiện
  • Chỉnh sửa thứ tự trong các quy tắc định dạng có điều kiện được đánh giá
  • Xóa định dạng có điều kiện
• Định dạng có điều kiện trong Microsoft Excel Online
  • Định dạng ô bằng thang màu
  • Định dạng ô bằng thanh dữ liệu
  • Định dạng ô theo bộ biểu tượng
  • Chỉ định dạng ô chứa văn bản, số, ngày hoặc thời gian
  • Chỉ định dạng các giá trị được xếp hạng đầu hoặc cuối cùng
  • Chỉ định dạng các giá trị trên hoặc dưới trung bình
  • Chỉ định dạng các giá trị duy nhất hoặc trùng lặp
  • Sao chép và 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
  • Xóa định dạng có điều kiện

Đị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 hoạt động tương tự như trong phạm vi ô, bảng Excel hoặc báo cáo PivotTable. Tuy nhiên, bạn cần xem xét một số điểm khi sử 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 Giá trị 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 hoặc duy nhất. Những hạn chế được đề 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 cấu trúc bố cục của 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 Giá trị 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ả các mục con hiển thị trên hàng của một hoặc nhiều cột hoặc cột có một hoặc nhiều hàng.

Phương pháp mặc định để chọn các trường trong Giá trị là theo lựa chọn. Bạn có thể thay đổi nó sang trường hoặc giá trị tương ứng bằng cách nhấn nút Áp dụng quy tắc định dạng cho, hộp Quy tắc định dạng mới hoặc Chỉnh sửa quy tắc định dạng.

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

Lưu ý: Quick Analysis không khả dụng trong Excel 2010 hoặc phiên bản cũ hơn.

1. Lựa chọn dữ liệu mà bạn muốn áp dụng định dạng có điều kiện. Nút Phân tích nhanh hiện ở góc dưới bên phải của màn hình.

2. Nhấn Phân tích nhanh hoặc ấn Ctrl+Q.

Trong hộp thoại pop-up xuất hiện, trên tab Định dạng, di chuột qua các tùy chọn để xem Trước trực tiếp trên dữ liệu của bạn, sau đó, chọn định dạng mà bạn muốn.

4. Nếu Văn bản chứa xuất hiện, nhập tùy chọn định dạng mà bạn muốn áp dụng và nhấn OK.

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

Thang màu là một hướng dẫn trực quan giúp bạn hiểu rõ cách phân bổ và biến thiên dữ liệu. Thang màu 2 giúp bạn so sánh một phạm vi ô bằng cách chuyển đổi dần dần giữa 2 màu. Sắc độ biểu 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 dưới đây, bạn có thể dễ dàng nhận biết các ô có giá trị cao hơn bằng màu xanh ngày càng đậm, trong khi các ô có giá trị thấp hơn sẽ thiên về màu vàng.

Định dạng nhanh

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

2. Trên tab Trang chính, trong nhóm Styles, nhấn mũi tên bên cạnh Định dạng có điều kiện > nhấn Thang màu.

3. Chọn thang màu 2

Di chuột qua biểu tượng thang màu để xem biểu tượng thang màu 2. Màu ở phía trên đại diện cho giá trị cao hơn, màu ở phía dưới tượng trưng cho giá trị thấp hơn.

Bạn có thể thay đổi cách khoanh vùng các trường trong Giá trị của báo cáo PivotTable bằng cách sử dụng nút Tùy chọn Định dạng bên cạnh trường PivotTable đã áp dụng định dạng có điều kiện.

Định dạng nâng cao

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

2. Trên tab Trang chính, trong nhóm Styles, nhấn mũi tên cạnh Định dạng có điều kiện > nhấn Quản lý Quy tắc. Hộp thoại Bảng Quản lý Quy tắc Định dạng có điều kiện sẽ hiện ra.

3. Tiến hành một trong những công việc sau đây:

• Để thêm định dạng có điều kiện, nhấn Quy tắc mới > Quy tắc định dạng mới sẽ hiện ra.
• Để thay đổi định dạng có điều kiện, làm như sau:
  • Đảm bảo chọn bảng tính, bảng hoặc PivotTable phù hợp trong hộp danh sách Hiển thị quy tắc định dạng cho.
  • Hoặc thay đổi phạm vi ô bằng cách nhấn Rút gọn Hộp thoại trong Áp dụng cho để tạm thời ẩn hộp thoại này, sau đó chọn phạm vi ô mới trên bảng tính, sau đó nhấnMở rộng Hộp thoại.
  • Chọn quy tắc > nhấn Chỉnh sửa quy tắc. Hộp thoại Chỉnh sửa quy tắc định dạng sẽ xuất hiện.

4. Trong Áp dụng Quy tắc đến, để thay đổi phạm vi các trường trong Giá trị của báo cáo PivotTable như sau:

• Vùng chọn: Nhấn Các ô được chọn.
• Toàn bộ ô cho một nhãn Giá trị: Nhấn Tất cả các ô hiển thị giá trị của .
• Toàn bộ ô cho một nhãn Giá trị, loại bỏ các tổng phụ và chính: Toàn bộ Tất cả các ô hiển thị giá trị của cho .

5. Trong Chọn Loại Quy tắc, nhấn Định dạng tất cả các ô dựa trên giá trị của chúng (mặc định).

6. Trong Chỉnh sửa Mô tả Quy tắc, trong ô Loại định dạng, chọn Thang màu 2.

7. Để chọn một loại trong ô Loại cho Giá trị Tối thiểu và Tối đa, thực hiện như sau:

• Định dạng giá trị cao và thấp nhất: Chọn Giá trị thấp nhất và Giá trị cao nhất. Trong trường hợp này, bạn không cần nhập Tối thiểu và Tối đa.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Số và 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. Giá trị hợp lệ từ 0 đến 100. Không nhập ký tự phần trăm. Sử dụng nó khi muốn hiểu toàn bộ giá trị dưới dạng tỷ lệ phân bổ cân đối các giá trị.
• Định dạng phân vị: Chọn Phân vị > nhập giá trị tối thiểu và tối đa. Phân vị hợp lệ từ 0 đến 100.
• Sử dụng phân vị khi muốn hiển thị một nhóm giá trị cao (ví dụ: phân vị thứ 20 từ trên xuống) trong một tỷ lệ màu và các giá trị thấp (ví dụ: phân vị thứ 20 từ dưới lên) 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ủa một công thức: Chọn Công thức, sau đó nhập giá trị Tối thiểu và Tối đa.
  • 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 ký hiệu =.
  • Kết quả của các công thức không hợp lệ sẽ không được áp dụng định dạng.
  • Hãy kiểm tra công thức để đảm bảo không trả về giá trị lỗi.

8. Để chọn thang màu Tối thiểu và Tối đa, nhấn Màu sắc cho mỗi giá trị, sau đó chọn màu.

Nếu muốn chọn màu bổ sung hoặc tạo màu tùy chỉnh, nhấn Màu khác. Thang màu bạn chọn sẽ hiển thị trong ô Xem trước.

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

Thang màu 3 giúp bạn phân biệt các ô dữ liệu bằng cách chuyển từ một màu sang một màu khác dần dần. Màu sắc biểu thị các giá trị cao, trung bình và thấp. Ví dụ, trong thang màu xanh lá cây, vàng và đỏ, bạn có thể nhận biết các ô giá trị cao bằng màu xanh lá cây, các ô giá trị trung bình bằng màu vàng, và các ô giá trị thấp bằng màu đỏ.

Mẹo: Bạn có thể phân loại các ô theo màu của chúng khi sử dụng định dạng này - 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 bên cạnh Conditional Formatting > click Color Scales.
3. Chọn thang màu 3. 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. Di chuột qua biểu tượng thang màu để xem biểu tượng thang màu 3.

Bạn có thể thay đổi cách chọn các trường trong Giá trị của báo cáo PivotTable bằng cách sử dụng Cài đặt Định dạng bên cạnh trường PivotTable đã áp dụng định dạng có điều kiện.

Định dạng tiên tiến

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 > click Manage Rules. Cửa sổ Quản lý Quy tắc Định dạng Có điều kiện sẽ xuất hiện.

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

• Để thêm định dạng có điều kiện, nhấn vào Quy tắc Mới để mở Quy tắc Định dạng Mới.
• Thay đổi định dạng có điều kiện như sau:
  • Chắc chắn chọn bảng tính, bảng hoặc báo cáo PivotTable phù hợp trong hộp Hiển thị quy tắc định dạng cho.
  • Hoặc thay đổi phạm vi ô bằng cách nhấp vào Thu gọn Hộp thoại trong hộp Áp dụng cho để tạm thời ẩn hộp thoại, sau đó chọn phạm vi ô mới trên bảng tính, và nhấn vào Mở rộng Hộp thoại.
  • Chọn giá trị này, sau đó nhấn vào Chỉnh sửa quy tắc.

4. Trong Áp dụng quy tắc cho, thay đổi phạm vi các trường trong Giá trị của báo cáo PivotTable như sau:

• Lựa chọn: Click vào Chỉ các ô này.
• Trường tương ứng: Click vào Tất cả các ô cùng trường với các trường giống nhau.
• Trường giá trị: Click vào Tất cả các ô .

5. Trong Chọn loại Quy tắc, click vào Định dạng tất cả các ô dựa trên giá trị của chúng.

6. Trong Chỉnh sửa Mô tả Quy tắc > hộp Phong cách Định dạng, chọn Thang 3 Màu.

7. Chọn một kiểu cho Giá trị Tối thiểu, Điểm giữa và Giá trị Tối đa. Thực hiện một trong những công việc sau đây:

• Định dạng giá trị cao và thấp nhất: Chọn Điểm giữa. Trong trường hợp này, bạn không cần nhập giá trị tối thiểu và tối đa.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Số, sau đó nhập giá trị cho Tối thiểu, Điểm giữa và Tối đa.
• Định dạng phần trăm: Chọn Phần trăm, sau đó nhập giá trị cho Tối thiểu, Điểm giữa và Tối đa. Giá trị hợp lệ từ 0 đến 100. Không nhập dấu %. Sử dụng % khi bạn muốn hình dung toàn bộ giá trị theo tỷ lệ bằng cách sử 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 Phân vị, sau đó nhập giá trị cho Tối thiểu, Điểm giữa và Tối đa. Phân vị hợp lệ từ 0 đến 100. Sử 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 Công thức, sau đó nhập giá trị cho Tối thiểu, Điểm giữa và Tối đa. 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ủa 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 Tối thiểu, Điểm giữa và Tối đa, click vào Màu cho mỗi giá trị, sau đó chọn một màu.

• Để chọn màu bổ sung hoặc tạo màu tùy biến, click vào Màu khác.
• Thang màu bạn chọn sẽ được hiển thị trong hộp Xem trước.

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

Thanh dữ liệu giúp bạn nhận biết giá trị của ô so với ô khác. Chiều dài của thanh dữ liệu thể hiện giá trị của ô đó. Thanh dài hơn đại diện cho giá trị cao hơn, còn thanh ngắn hơn thể hiện giá trị thấp hơn. Thanh dữ liệu hữu ích để phân biệt giữa các số lớn và nhỏ, đặc biệt là với lượng dữ liệu lớn như doanh số bán hàng của các mặt hàng cao và thấp nhất trong báo cáo.

Ví dụ dưới đây sử dụng thanh dữ liệu để làm nổi bật giá trị âm và dương một cách rõ ràng nhất. Bạn có thể định dạng thanh dữ liệu để nó bắt đầu từ giữa ô và kéo về phía bên trái cho các giá trị âm.

Mẹo: Nếu bất kỳ ô nào 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 ô đó. Để đảm bảo áp dụng định dạng có điều kiện cho những ô đó, sử 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, sau đó chọn biểu tượng thanh dữ liệu.

Bạn có thể thay đổi cách khoanh vùng cho các trường trong Values của báo cáo PivotTable thông 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 hoặc báo cáo PivotTable.

2. Trên Home > Styles > click mũi tên cạnh Conditional Formatting, sau đó click Manage Rules > Conditional Formatting Rules Manager sẽ hiển thị.

3. Thực hiện một trong những hành động sau đây:

• Để thêm định dạng có điều kiện, nhấp vào New Rule để mở New Formatting Rule.
• Để thay đổi định dạng có điều kiện, làm như sau:
  • Đảm bảo đã chọn worksheet, bảng hoặc 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 nhấp vào Collapse Dialog trong ô Applies to để tạm thời ẩn hộp thoại này, sau đó chọn phạm vi ô mới trên worksheet, và cuối cùng nhấp vào Expand Dialog.
  • Chọn quy tắc này, sau đó chỉnh sửa Edit Rule. Hộp thoại Edit Formatting Rule sẽ 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: Nhấp vào Just these cells.
• Corresponding field: Nhấp vào All cells with the same fields.
• Value field: Nhấp vào All cells.

5. Trong Select a Rule Type, nhấp vào Format all cells based on their values.

6. Trong Chỉnh sửa Mô tả Quy tắc > hộp Kiểu Định dạng, chọn Dữ liệu thanh.

7. Chọn Loại Tối thiểu và Tối đa. Thực hiện một trong những hành động sau đây:

• Định dạng giá trị thấp nhất và cao nhất: Chọn Giá trị thấp nhất và Giá trị cao nhất. Trong trường hợp này, bạn không cần nhập giá trị cho Tối thiểu và Tối đa.
• Định dạng số, ngày hoặc giá trị thời gian: Chọn Số, sau đó nhập giá trị cho Tối thiểu và Tối đa.
• Định dạng phần trăm: Chọn Phần trăm và nhập giá trị cho Tối thiểu và Tối đa.
• Định dạng phân vị: Chọn Phân vị, sau đó nhập giá trị cho Tối thiểu và Tối đa.
• Định dạng kết quả công thức: Chọn Công thức, sau đó nhập giá trị cho Tối thiểu và Tối đa.

8. Để chọn màu cho Tối thiểu và Tối đa, nhấp vào Màu thanh. Nếu muốn chọn màu bổ sung hoặc tạo màu tùy chỉnh, nhấp vào Màu Thêm. Thanh màu bạn chọn sẽ hiển thị trong ô Xem trước.

9. Để chỉ hiện thanh dữ liệu mà không hiển thị giá trị trong ô, chọn Chỉ Hiện Thanh.

10. Để áp dụng đường viền đậm liền nét cho thanh dữ liệu, chọn Đường viền Đậm trong hộp Viền 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 Fill Đồng Màu hoặc Fill Gradient trong hộp Tô.

Để định dạng thanh phủ định, nhấp vào Giá Trị Phủ Định và Trục. Trong Cài Đặt Giá Trị Phủ Định và Trục, 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, nhấp vào OK.

Bạn có thể đổi hướng của thanh bằng cách chọn một cài đặt trong hộp Hướng Thanh. Bài viết chọn giá trị mặc định là Ngữ Cảnh, 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ộ biểu tượng

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

Bạn có thể phân loại ô sử dụng định dạng này theo biểu tượng - chỉ cần sử dụng menu ngữ cảnh.

Bạn có thể chọn chỉ hiển thị biểu tượng đáp ứng điều kiện. Ví dụ, hiển thị biểu tượng cảnh báo cho các ô có giá trị thấp hơn ngưỡng và không có biểu tượng cho các ô vượt quá ngưỡng này. Để làm điều đó, hãy ẩn biểu tượng bằng cách chọn No Cell Icon từ danh sách thả xuống bên cạnh biểu tượng khi bạn thiết lập điều kiện. Bạn cũng có thể tạo sự kết hợp của bộ biểu tượng riêng. Ví dụ: kết hợp 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 ô bạn muốn áp dụng đị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, sau đó chọn một bộ biểu tượng.

Định dạng nâng cao

1. Chọn ô bạn muốn áp dụng đị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. Thực hiện một trong những bước sau:

• Click New Rule để thêm điều kiện định dạng.
• Thay đổi điều kiện định dạng theo ý muốn (tương tự như trên).
• Trong Apply Rule To, bạn có thể 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ộ biểu tượng. Mặc định là 3 Traffic Lights (Unrimmed). Số biểu tượng, toán tử so sánh mặc định và ngưỡng giá trị cho mỗi biểu tượng 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 biểu tượng 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 của bạn. Đảm bảo ngưỡng đó được sắp xếp logic từ cao đến thấp 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 đến 100.
  • Định dạng kết quả công thức: Chọn Formula và nhập công thức vào ô Value.
• Để biểu tượng đầu tiên đại diện cho giá trị thấp hơn và biểu tượng cuối cùng là giá trị cao hơn, chọn Reverse Icon Order.
• Để chỉ hiện biểu tượng, không có giá trị trong ô, chọn Show Icon Only.

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

Để dễ tìm ô cụ thể hơn, bạn có thể áp dụng định dạ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ể làm nổi bật những sản phẩm có ít hơn 10 mục bằng màu vàng. Ngược lại, trong bảng tổng kết doanh thu của cửa hàng bán lẻ, bạn có thể đánh dấu những cửa hàng có lợi nhuận lớn hơn 10%, doanh số bán hàng ít hơn 100.000USD và nằm ở khu vực Đông Nam.

Ví dụ dưới đây áp dụng các tiêu chí định dạng có điều kiện cho dữ liệu như Lớn Hơn, Top%. Cụ thể, các thành phố có dân số lớn hơn 2.000.000 sẽ được tô màu xanh, và nhiệt độ trung bình cao hơn 30% sẽ được tô 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, sau đó 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, sau đó click Manage Rules.

3. Thực hiện một trong những việc sau tại bảng Quản lý quy tắc định dạng có điều kiện.

• 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 hoặc báo cáo PivotTable phù hợp trong box Hiển thị quy tắc định dạng cho.
  • Thay đổi phạm vi ô bằng cách click Rút gọn Hộp thoại trong Áp dụng cho để tạm thời ẩn hộp thoại này, sau đó chọn phạm vi ô mới trên worksheet hoặc bảng tính khác, rồi click Mở rộng Hộp thoại.
  • Chọn quy tắc, sau đó click Chỉnh sửa Quy tắc.

4. Trong Áp dụng Quy tắc đến, lựa chọn thay đổi phạm vi các trường trong khu vực Giá trị của một báo cáo PivotTable.

5. Trong Chọn một Loại Quy tắc, click Định dạng chỉ các ô chứa.

6. Tại Chỉnh sửa Mô tả Quy tắc > Định dạng chỉ các ô với, 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 Giá trị Ô, chọn toán tử so sánh, rồi nhập số, ngày hoặc thời gian. Ví dụ, chọn Trong khoảng, rồi nhập 100 và 200, hoặc chọn Bằng, rồi nhập 1/1/2009.
• Định dạng theo text: Chọn Text Cụ thể, chọn toán tử so sánh, rồi nhập text. Ví dụ, chọn Chứa > nhập Bạc hoặc chọn Bắt đầu với > 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 Ngày Xảy ra > chọn so sánh ngày/tháng. Ví dụ: chọn Hôm qua hoặc Tuần sau.
• Định dạng các ô có khoảng trống hoặc không: Chọn Trống hoặc Không có Trống. 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 Lỗi hoặc Không có Lỗi. Các giá trị lỗi bao gồm: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, và #NULL!.

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

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

Chỉ định định dạng cho các giá trị được xếp hạng cao nhất hoặc thấp nhất

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

Định dạng nhanh

1. Lựa 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 Style, click mũi tên gần 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 sử dụng, sau đó chọn định dạng.

Định dạng nâng cao

1. Lựa 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 vào mũi tên bên cạnh Conditional Formatting > click vào Manage Rules. Hộp thoại Conditional Formatting Rules Manager sẽ hiển thị.

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

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

4. Lựa 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 vào Format chỉ các giá trị được xếp hạng cao nhất hoặc thấp nhất.

6. Trong Edit the Rule Description > đến Format các giá trị được xếp hạng trong hộp list, 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ố, sau đó xóa box % của phạm vi được lựa chọn. 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 % của phạm vi được lựa chọn. Giá trị hợp lệ từ 1 tới 100.

8. Lựa 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 vùng 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ì sử 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 mỗi nhóm Cột.
• Một hàng và trường cột chính của nó, bằng cách chọn mỗi nhóm Hàng.

9. Để xác định định dạng, click vào Format để mở Hộp thoại Định dạng ô.

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, sau đó click vào OK.

Bạn có thể chọn nhiều hơn một kiểu định dạng. Các kiểu định dạng bạn chọn sẽ được hiển thị trong ô Xem trước.

Chỉ định định dạng cho 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ị cao hơn hoặc thấp hơn 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ó điểm cao hơ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 có độ lệch chuẩn dưới hai 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 vào mũi tên cạnh Conditional Formatting > click Top/Bottom Rules.
3. Chọn lệnh bạn muốn, ví dụ như Above Average hoặc Below Average.
4. Nhập giá trị bạn muốn sử dụng, sau đó chọn định dạng.

Bạn có thể thay đổi cách áp dụng quy tắc định dạng cho các trường trong Giá trị của báo cáo PivotTable bằng cách sử dụng nút tùy chọn Áp dụng quy tắc định dạng cho.

Đị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 vào mũi tên bên cạnh Conditional Formatting, sau đó click vào Quản lý Quy tắc để mở hộp thoại Quản lý Quy tắc Định dạng Có điều kiện.

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

• Để thêm quy tắc định dạng mới, nhấp vào Quy tắc Mới.
• Để thay đổi quy tắc định dạng có điều kiện:
  • Chọn worksheet, bảng hoặc báo cáo PivotTable phù hợp trong ô Hiển thị quy tắc định dạng cho.
  • Tùy chọn, thay đổi phạm vi các ô bằng cách nhấp vào Thu gọn Hộp thoại trong Áp dụng cho để tạm thời ẩn hộp thoại đó, sau đó chọn phạm vi mới trên worksheet, và cuối cùng nhấp vào Mở rộng Hộp thoại.
  • Chọn quy tắc, sau đó nhấp vào Chỉnh sửa Quy tắc.

4. Trong Áp dụng quy tắc đến, tích vào ô lựa chọn thay đổi phạm vi các trường trong khu vực Giá trị của báo cáo PivotTable.

5. Trong Chọn Loại Quy tắc, nhấp vào Định dạng chỉ các giá trị cao hoặc thấp hơn trung bình.

6. Trong Chỉnh sửa Mô tả Quy tắc, tại ô Định dạng các giá trị là, hãy thực hiện một trong những điều sau đây:

• Để định dạng các ô cao hơn hoặc thấp hơn trung bình cho toàn bộ ô trong phạm vi, chọn Trên hoặc Dưới.
• Để định dạng các ô cao hơn hoặc thấp hơn 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 áp dụng định 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ì sử 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 mỗi nhóm Cột.
• Một hàng và trường cột chính của nó, bằng cách chọn mỗi nhóm Hàng.

8. Click vào Định dạng để mở hộp thoại Định dạng Ô.

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

Bạn có thể lựa chọn nhiều định dạng. Các định dạng bạn chọn sẽ xuất hiện trong ô Xem trước.

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

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

Định dạng nhanh chóng

1. Chọn ô mà bạn muốn áp dụng điều kiện định dạng cho.
2. Trên tab Trang chính > Kiểu, nhấn vào mũi tên gần Định dạng có điều kiện > nhấn vào Luật đánh dấu ô nền.
3. Chọn Giá trị Trùng lặp.
4. Nhập giá trị mà bạn muốn sử dụng, sau đó chọn định dạng.

Định dạng tiên tiến

1. Chọn ô mà bạn muốn áp dụng điều kiện định dạng cho.

2. Trên tab Trang chính > Kiểu, nhấn vào mũi tên gần Định dạng có điều kiện, sau đó nhấn vào Quản lý Luật.

3. Thực hiện một trong những hành động sau đây:

• Thêm điều kiện định dạng mới: nhấn vào Luật mới.
• Thay đổi điều kiện định dạng như sau:
  • Chọn bảng hoặc trang tính phù hợp trong ô Hiển thị quy tắc định dạng cho.
  • Thay đổi phạm vi ô bằng cách nhấn vào Thu gọn Hộp thoại trong ô Áp dụng cho. Sau đó, chọn phạm vi ô mới trên trang tính, sau đó chọn Mở rộng Hộp thoại.
  • Chọn quy tắc, sau đó nhấn vào Chỉnh sửa Luật.

4. Trong Chọn Loại Luật, nhấn vào Định dạng chỉ giữ các giá trị duy nhất hoặc trùng lặp.

5. Trong Chỉnh sửa Mô tả Luật > Định dạng tất cả danh sách > chọn duy nhất hoặc trùng lặp.

6. Nhấn vào Định dạng để hiện thị Định dạng Ô.

7. Chọn số, font chữ, đường viền hoặc điều kiện lấp đầy mà bạn muốn áp dụng khi ô đáp ứng điều kiện, sau đó nhấn vào Đồng ý.

Thiết đặt quy tắc định dạng theo ý của bạn

Nếu không có tùy chọn nào phù hợp với nhu cầu của bạn, bạn có thể tạo quy tắc định dạng theo ý muốn như sau:

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

2. Trên tab Trang chính, nhấn vào Định dạng có điều kiện > Quy tắc mới.

3. Tạo quy tắc và chỉ định các tùy chọn định dạng của nó, sau đó nhấn vào Đồng ý.

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

Sử 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ể sử 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 ở trang tính khác trong cùng một bảng tính). 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ể sử dụng logic có điều kiện để kết hợp 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 Trang chính > Kiểu, nhấn vào mũi tên gần Định dạng có điều kiện > nhấn vào Quản lý Quy tắc.

2. Chọn một trong những hành động sau đây:

• Thêm điều kiện định dạng mới, nhấn vào Luật mới.
• Thay đổi điều kiện định dạng:
  • Chọn trang tính, bảng hoặc báo cáo Bảng Tổng hợp phù hợp trong hộp liệt kê Hiển thị quy tắc định dạng cho.
  • Thay đổi phạm vi ô bằng cách nhấn vào Thu gọn Hộp thoại trong hộp Áp dụng cho để ẩn tạm thời hộp đó. Chọn phạm vi ô mới trên trang tính bạn muốn, sau đó nhấn vào Mở rộng Hộp thoại.
  • Chọn quy tắc, sau đó nhấn vào Chỉnh sửa Luật.

3. Trong Áp dụng quy tắc cho, chọn cách thay đổi phạm vi cho các trường trong Giá trị của báo cáo Bảng Tổng hợp.

• Khoanh vùng theo lựa chọn: Nhấn vào Các ô đã chọn.
• Khoanh vùng theo trường tương ứng: Nhấn vào Tất cả ô hiển thị giá trị .
• Khoanh vùng theo trường giá trị: Nhấn vào Tất cả ô hiển thị giá trị cho .

4. Trong Chọn Loại Luật, nhấn vào Sử dụng một công thức để xác định các ô cần định dạng.

• Trong Chỉnh sửa Mô tả Luật, tại ô Định dạng giá trị nếu công thức này đúng, 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).
• Nhấn vào Định dạng để hiện thị Định dạng Ô.
• Chọn số, font chữ, đường viền hoặc điều kiện lấp đầy mà bạn muốn áp dụng khi giá trị ô đáp ứng điều kiện, sau đó nhấn vào Đồng ý. 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 Xem trước.

Ví dụ 1: Sử dụng hai quy tắc đị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ụ dưới đây mô tả cách sử dụng hai quy tắc định dạng có điều kiện. Nếu không áp dụng quy tắc 1, quy tắc 2 sẽ được thực hiện.

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

Quy tắc 2: Nếu trả trước hoặc 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 đánh dấu màu đỏ. Thay đổi một số giá trị như lãi suất, điều khoản vay, trả trước và giá mua để xem ô định dạng có điều kiện sẽ thay đổi như thế nào.

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

Định dạng có điều kiện được áp dụng cho từng ô trong trang tính 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 trang tính bằng cách click vào hình vuông ở trên cùng của cột A và bên trái của hàng 1. Hàm MOD trả về số dư sau khi chia một số (đối số đầu tiên) cho một số nguyên (đố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 và 1 cho số lẻ. Do 0 là False và 1 là TRUE, Excel sẽ định dạng mỗi hàng với số dư là 1. Quy tắc này sử dụng công thức: =MOD(ROW(),2)=1.

Lưu ý: Bạn có thể chọn trực tiếp các ô để nhập tham chiếu trong công thức trên bảng tính. Điều này sẽ tạo ra 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 sử dụng các tham chiếu ô tương ứng.

Dưới đây là một video hướng dẫn về việc sử 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 bạn 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 bảng tính, bạn có thể sử dụng Bộ sơn định dạng để sao chép kiểu định dạng có điều kiện đó cho dữ liệu mong muốn.

1. Nhấn vào ô bạn muốn áp dụng định dạng có điều kiện để sao chép.

2. Nhấn vào Trang chủ > Bộ sơn định dạng.

Di chuyển con trỏ sang Cọ sơn.

3. Để dán định dạng có điều kiện, kéo cọ sơn qua các ô hoặc phạm vi ô bạn muốn định dạng.

4. Nhấn phím Esc để ngừng sử dụng cọ sơn định dạng.

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

Nếu bảng tính chứa định dạng có điều kiện, bạn có thể nhanh chóng xác định ô để sao chép, thay đổi hoặc xóa định dạng đó. Sử dụng lệnh Đi đến Đặc biệt để tìm ô chứa một loại định dạng cụ thể hoặc tìm tất cả các ô có định dạng theo điều kiện nào đó.

Tìm tất cả các ô được định dạng theo điều kiện

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

2. Trên tab Trang chủ > Chỉnh sửa > nhấn mũi tên cạnh Tìm & Chọn, sau đó nhấn Định dạng có điều kiện.

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

1. Nhấn vào ô bất kỳ chứa định dạng có điều kiện bạn muốn tìm.
2. Trên tab Trang chủ, trong nhóm Chỉnh sửa, nhấn mũi tên cạnh Tìm & Chọn, sau đó nhấn Đi đến Đặc biệt.
3. Nhấn vào Định dạng có điều kiện.
4. Nhấn vào Cùng dưới dữ liệu xác nhận.

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

Khi sử dụng định dạng theo điều kiện, bạn đặt các quy tắc cho Excel để xác định khi nào áp dụng định dạng này. Để quản lý những quy tắc này, bạn cần hiểu thứ tự ưu tiên của chúng, xử lý xung đột giữa các quy tắc, ảnh hưởng của sao chép & dán đối với đánh giá quy tắc, cách thay đổi thứ tự ưu tiên và khi nào nên dừng đánh giá quy tắc.

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

Bạn có thể 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 bảng tính bằng cách sử dụng hộp thoại Quản lý Quy tắc Định dạng có điều kiện. (Trên tab Trang chủ, nhấn Định dạng có điều kiện > nhấn Quản lý Quy tắc.)

Hộp thoại Quản lý Quy tắc Định dạng có điều kiện sẽ xuất hiện.

Khi áp dụng 2 hoặc nhiều định dạng có điều kiện, các quy tắc này được đánh giá theo thứ tự ưu tiên (từ 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à một ví dụ về ngày hết hạn của 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 hết hạn bằng màu nền vàng và đã hết hạn bằng màu nền đỏ.

Trong ví dụ này, các ô chứa số ID của 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 của 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 hì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. 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. 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 có mức độ ưu tiên cao hơn sẽ được áp dụng trước hơn so với quy tắc có mức độ ưu tiên 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ì vậy, bạn cần để ý đến thứ tự của chúng. Bạn có thể thay đổi thứ tự ưu tiên bằng cách sử dụng các nút Move Up và Move Down trong hộp thoại này.

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

• Khi không có 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 có 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.

Tác động của cách dán, lấp đầy và Format Painter đối với quy tắc định dạng có điều kiện

Trong quá trình chỉnh sửa bảng tính, 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 sử dụng Format Painter. Những thao tác này có thể ảnh hưởng đến mức độ ưu tiên của các 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 bạn sao chép & dán giá trị ô chứa định dạng có điều kiện từ một bảng tính khác trong Excel, không có quy tắc định dạng có điều kiện nào được tạo trong bảng tính đó và định dạng này không được sao chép sang bảng tính đó.

Hiệu ứng khi có xung đột giữa một định dạng có điều kiện và định dạng thủ công

Nếu một 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 nào đã được thiết lập cho cùng một lựa chọn đó. Điều này có nghĩa là nếu có 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, các định dạng thủ công trước đó 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 Quản lý Quy tắc Định dạng Có Điều Kiện và không được sử dụng để xác định mức độ ưu tiên.

Lựa chọn Dừng Nếu Đúng (Stop If True)

Để tương thích ngược với các phiên bản Excel trước năm 2007, bạn có thể chọn Dừng Nếu Đúng (Stop If True) trong Quản lý Quy tắc để mô phỏng cách định dạng có điều kiện có thể xuất hiện trong các 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 bạn muốn áp dụng hơn 3 quy tắc định dạng cho một phạm vi ô và đang sử dụng phiên bản Excel trước năm 2007, trong Excel này:

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

Bảng dưới đây tóm tắt mỗi điều kiện có thể xảy ra 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 bỏ Stop If True để thay đổi hành vi mặc định:

• Để chỉ xem xét quy tắc đầu tiên, hãy chọn Dừng Nếu Đúng cho quy tắc đầu tiên.
• Để chỉ xem xét quy tắc đầu tiên và thứ hai, hãy chọn Dừng Nếu Đúng cho quy tắc thứ hai.

Bạn không thể chọn hoặc bỏ chọn Dừng Nếu Đúng nếu các quy tắc định dạng bằng thanh dữ liệu, bảng màu hoặc bộ biểu tượng.

Thay đổi thứ tự trong các quy tắc định dạng có điều kiện được xem xét

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

1. Trên tab Home, Style > nhấn mũi tên gần Conditional Formatting, sau đó nhấn Quản Lý Quy Tắc. Hộp thoại Quản Lý Quy Tắc Định Dạng Có Điều Kiện sẽ hiện 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 loại quy tắc, định dạng, phạm vi ô được áp dụng và cài đặt Dừng Nếu Đúng.

Nếu không thấy quy tắc mà bạn muốn, trong ô Hiển Thị Quy Tắc Định Dạng Cho, hãy đảm bảo bạn đã chọn đúng phạm vi ô, bảng tính, 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 được chọn lên trên theo thứ tự ưu tiên, nhấn Di Chuyển Lên. Để di chuyển quy tắc được chọn xuống dưới trong mức độ ưu tiên, nhấn Di Chuyển Xuống.

4. Nếu bạn muốn dừng đánh giá một quy tắc cụ thể, hãy chọn Dừng Nếu Đúng.

Loại bỏ định dạng có điều kiện

Xóa định dạng có điều kiện trên bảng tính

• Trên tab Home, nhấp vào Định Dạng Có Điều Kiện > Xóa Quy Tắc > Xóa Quy Tắc Từ Toàn Bộ Bảng Tính.

Thực hiện các bước sau nếu bạn đã áp dụng định dạng có điều kiện trong bảng tính và muốn loại bỏ nó.

Đối với toàn bộ bảng tính

• Trên tab Trang Chủ, nhấp vào Định Dạng Có Điều Kiện > Xóa Quy Tắc > Xóa Quy Tắc Từ Toàn Bộ Bảng.

Trong Phạm Vi Ô

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

3. Nhấp vào Xóa Định Dạng.

Tìm và loại bỏ định dạng có điều kiện giống nhau trong bảng tính

1. Chọn ô chứa định dạng có điều kiện mà bạn muốn xóa khỏi bảng tính.
2. Trên tab Trang Chủ, nhấp vào mũi tên gần Tìm & Chọn > nhấp vào Chọn Đặc Biệt.
3. Nhấp vào Định dạng có điều kiện.
4. Nhấp vào Giống nhau dưới Định dạng dữ liệu để 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 Trang Chủ, nhấp vào Định Dạng Có Điều Kiện > Xóa Quy Tắc > Xóa Quy Tắc từ Các Ô Được Chọn.

Định dạng có điều kiện trong Microsoft Excel Trực Tuyến

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

Thang màu giúp bạn dễ dàng nhận biết sự phân bổ và biến thiên của dữ liệu. Excel cung cấp hai loại thang màu: thang hai màu và thang ba màu. Hai loại này giúp bạn so sánh phạm vi ô bằng cách sử dụng hai hoặc ba mức độ màu khác nhau.

Trong thang hai màu, màu sắc biểu thị giá trị cao và thấp hơn. Ví dụ, trong thang màu xanh lá và vàng, màu xanh lá đại diện cho giá trị cao hơn, và màu vàng đại diện cho giá trị thấp hơn.

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

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

1. Chọn ô mà bạn muốn áp dụng định dạng có điều kiện.
2. Nhấn vào Trang Chủ > Định Dạng Có Điều Kiện > Thang Màu, sau đó chọn một trong các thang màu có sẵn.

Tùy chỉnh ô bằng thanh dữ liệu

Thanh dữ liệu giúp hiểu rõ hơn giá trị của ô so với các ô khác. Chiều dài của thanh dữ liệu thể hiện giá trị của ô. 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 muốn tìm hiểu các giá trị cao và thấp hơn, đặc biệt là trong các báo cáo số 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 ô cần định dạng điều kiện
2. Bấm Home > Định dạng điều kiện > Thanh dữ liệu, sau đó chọn thanh dữ liệu.

Định dạng ô theo bộ biểu tượng

Sử dụng bộ biểu tượng để ghi chú và phân loại dữ liệu thành 3 đến 5 danh mục dựa trên giá trị ngưỡng. Mỗi biểu tượng đại diện cho một phạm vi dữ liệu. Ví dụ, trong bộ biểu tượng 3 mũi tên, mũi tên màu xanh trên biểu thị giá trị cao hơn, mũi tên màu vàng ngang biểu thị giá trị trung bình và mũi tên màu đỏ xuống biểu thị giá trị thấp hơn.

1. Chọn ô cần định dạng điều kiện.
2. Bấm Home > Định dạng điều kiện > Bộ biểu tượng và chọn bộ biểu tượng.

Chỉnh sửa định dạng ô chứa văn bản, số, ngày hoặc thời gian

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

1. Chọn ô bạn muốn định dạng có điều kiện.
2. Trên tab Home > Bảng > click Luật tô sáng ô.
3. Chọn lệnh bạn muốn sử dụng, ví dụ: Giữa, Bằng hoặc Chứa Văn bản hoặc Một Ngày Xảy ra.
4. Nhập giá trị mong muốn, sau đó chọn định dạng (fill, text hoặc border color). Bấm OK.

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

Có thể dễ dàng xác định các giá trị cao và thấp nhất trong phạm vi ô dựa trên giá trị ngưỡng đã chọn. Ví dụ, bạn có thể xác định top 5 sản phẩm bán chạy nhất trong báo cáo theo khu vực, các 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 phòng.

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

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

Có thể dễ dàng 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 có kết quả đánh giá cao hơn trung bình trong đánh giá hàng năm hoặc xác định vật liệu sản xuất dưới hai độ lệch chuẩn trong xếp hạng.

• Chọn ô bạn muốn định dạng điều kiện.
• Trên tab Home, trong nhóm Bảng, click Luật top/dưới.
• Chọn lệnh bạn muốn như Trên trung bình hoặc Dưới trung bình.
• Nhập giá trị bạn muốn sử dụng, sau đó chọn một định dạng (fill, text, hoặc màu đường viền), sau đó click OK.

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

Dễ dàng 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 điều kiện.
• Trên tab Home, trong nhóm Bảng, click Luật tô sáng ô, sau đó chọn Giá trị trùng lặp.
• Nhập giá trị bạn muốn sử dụng, sau đó chọn định dạng và click OK.

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

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 bảng tính, bạn có thể sử dụng Bộ sao chép định dạng để sao chép kiểu định dạng có điều kiện cho dữ liệu đó.

1. Nhấp vào ô có định dạng điều kiện mà bạn muốn sao chép.

2. Nhấp vào Home > Bộ sao chép định dạng.

Con trỏ sẽ chuyển sang hình cọ vẽ.

Mẹo: Bạn có thể nhấp đúp vào Bộ sao chép định dạng 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 kiểu đị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 việc sử dụng cọ vẽ, nhấn Esc.

Lưu ý: Nếu bạn đã sử 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 sử dụng định dạng có điều kiện, bạn thiết lập các quy tắc Excel để 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 là bạn hiểu về thứ tự đánh giá quy tắc, những gì xảy ra khi có xung đột từ 2 quy tắc trở lên và cách sao chép & dán có thể ảnh hưởng đến việc đánh giá quy tắc.

Bạn có thể kiểm tra lại các quy tắc định dạng có điều kiện trong Excel cho web bằng cách sử dụng bảng Định dạng có điều kiện.

1. Trên tab Home, nhấp vào Định dạng có điều kiện > nhấp vào Quản lý quy tắc.

2. Bảng Định dạng có điều kiện sẽ hiện ra ở phía bên phải của dữ liệu của bạn. Dưới đây là những lưu ý giúp bạn hiểu về cách các quy tắc được liệt kê tác động như thế nào.

• Cách Excel xác đị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 ô, các quy tắc này sẽ được đánh giá theo thứ tự ưu tiên (từ 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 cao hơn so với 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, vì vậy nó có độ ưu tiên cao hơn. Vì vậy, bạn cần chú ý đến 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ể chứa nhiều hơn một quy tắc định dạng có điều kiện cho kết quả True. Dưới đây là cách xử lý chúng:

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

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

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

Trong quá trình chỉnh sửa bảng tính, bạn có thể sao chép & dán các giá trị từ một phạm vi ô vào một phạm vi ô khác bằng cách sử dụ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 đến độ ưu tiên của các quy tắc đị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 được tạo cho ô đích.

• Hiệu ứng khi có xung đột giữa đị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ó sẽ được ưu tiên hơn so với định dạng thông thường hiện tại. Bạn có thể áp dụng định dạng thô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 bạn xóa quy tắc định dạng có điều kiện, định dạng thông thường cho phạm vi ô đó vẫn được giữ nguyên.

Định dạng thông thường không được hiển thị trong bảng Định dạng có điều kiện hoặc được sử dụng để xác định thứ tự ưu tiên.

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

Bạn có thể xóa các quy tắc định dạng có điều kiện trong ô đã chọn hoặc trong toàn bộ bảng tính như sau:

• Xóa các quy tắc định dạng có điều kiện trong các ô đã chọn:
  • Chọn các ô đó trong bảng tính.
  • Nhấp vào tab Home > Định dạng có điều kiện > Xóa quy tắc > Xóa quy tắc từ các ô đã chọn.
• Để xóa các quy tắc định dạng có điều kiện trong toàn bộ bảng tính, nhấp vào Home > Định dạng có điều kiện > Xóa quy tắc > Xóa quy tắc từ toàn bộ bảng tính.