Hướng dẫn sử dụng tính năng định dạng có điều kiện trong Excel 2010, 2013, và 2016

Blog Siêu Marketing thông qua bài viết này sẽ chia sẻ với bạn những kỹ thuật cơ bản liên quan đến định dạng có điều kiện. Bài học này sẽ bao gồm việc tạo ra các luật định dạng khác biệt, cách thức thực hiện định dạng dựa vào ô khác, và phương pháp chỉnh sửa hay nhân bản các luật định dạng trong các phiên bản Excel 2007, 2010, 2013 và 2016.

Hiểu về định dạng có điều kiện trong Excel

Định dạng có điều kiện được sử dụng để thay đổi định dạng ngăn chứa dữ liệu theo nhiều phong cách khác nhau, bao gồm việc thay thế màu sắc, màu chữ, và các kiểu đường biên của ô. Điểm khác biệt chính là định dạng có điều kiện cung cấp tính linh hoạt hơn khi bạn có thể thay đổi định dạng dựa vào tiêu chuẩn hoặc điều kiện cụ thể nào đó.

Định dạng có điều kiện có thể được áp dụng lên từng ô, dãy ô, cột, hoặc toàn bảng tính, theo nội dung ô hoặc theo giá trị trong ô khác bằng cách thiết lập các quy tắc xác định ngữ cảnh và cách thức việc định dạng sẽ diễn ra.

Vị trí của định dạng có điều kiện trong Excel

Trong mọi phiên bản Excel, bạn có thể tìm thấy định dạng có điều kiện tại cùng một nơi, đặt trong tab Home > Styles.

Ở Excel 2007, định dạng có điều kiện được tìm thấy tại

Conditional formatting in Excel 2007

Trong Excel 2010, nó nằm tại

Conditional formatting in Excel 2010

Định dạng có điều kiện trong Excel 2013 được hiển thị như sau

Conditional formatting in Excel 2013

Tạo quy tắc định dạng có điều kiện trong Excel như thế nào

Để khai thác tiềm năng của Excel trong việc định dạng có điều kiện, bạn cần biết cách tạo nên nhiều loại quy tắc định dạng.

Trong quá trình định dạng có điều kiện, hai yếu tố cần phải xem xét là:

  • Ô nào cần được áp đặt định dạng có điều kiện, và
  • Điều kiện cần để thực hiện việc định dạng đó.

Chúng tôi sẽ hướng dẫn bạn thực hiện định dạng có điều kiện trên Excel 2010, vì đây dường như là phiên bản được rất nhiều người sử dụng hiện nay. Tuy nhiên, các chức năng cơ bản rất tương đồng với Excel 2007 và 2013, do đó không gặp vấn đề nào sử dụng các phiên bản khác.

  1. Chọn ô bạn muốn định dạng theo điều kiện đặt ra.

Chẳng hạn, tôi tạo một bảng đơn giản mô tả giá dầu thô theo từng tháng. Mục tiêu của chúng tôi là làm nổi bật giá dầu giảm sút, đó là lý do tại sao chúng tôi sẽ chọn các ô từ C2 đến C9, nơi chứa các số âm trong cột Change.

Select the cells you want to format.

  1. Navigate to the tab Home> Styles và click vào Conditional Formatting. Tại đây, bạn có thể thấy nhiều quy tắc định dạng khác nhau như data bars, color scales và icon sets.
  2. Vì chúng ta mong muốn chỉ áp dụng điều kiện định dạng cho những giá trị thấp hơn 0, hãy chọn Highlight Cells Rules > Less Than …

Choose Highlight Cells Rules > Less Than...

Bạn hoàn toàn có thể áp dụng mọi quy tắc phù hợp khác với dữ liệu của mình, bao gồm:

  • Áp dụng định dạng cho giá trị cao hơn, thấp hơn hoặc ngang bằng
  • Nhấn mạnh các văn bản chứa từ hoặc ký tự cụ thể
  • Đánh dấu sự trùng lặp
  • Tùy chỉnh định dạng cho ngày mà bạn chỉ định
  1. Gõ giá trị vào ô tại góc bên phải của cửa sổ trong mục “Format cells that are LESS THAN“, ví dụ ở đây là nhập 0. Excel sẽ tự động làm nổi bật những ô thỏa mãn tiêu chuẩn bạn đã đặt.
  2. Lựa chọn định dạng mong muốn từ danh sách, hoặc tạo định dạng cá nhân bằng cách nhấp vào Custom Format….

Select the format you want to apply.

  1. Sử dụng Format Cells, bạn có thể chọn qua các tab Font, BorderFill để thiết lập loại phông chữ, viền và màu nền. Trên tab FontFill, bạn có thể xem trước kiểu định dạng tùy chọn.
  2. Hoàn tất việc thiết lập, bạn chỉ cần bấm nút OK ở cuối cửa sổ.

Choose the background color.Mẹo:

  • Để sử dụng thêm màu sắc phong phú hơn ngoài bộ màu chuẩn, hãy chọn More Colors … trong tab Fill hoặc Font.
  • Để áp dụng hiệu ứng màu nền gradient, bạn có thể chọn Fill Effects trong tab Fill và điều chỉnh theo ý muốn.
  • Bấm OK để đóng cửa sổ “Less Than” sau đó kiểm tra liệu định dạng có điều kiện đã đúng với dữ liệu hay không.

Như bạn thấy trên hình dưới đây, quy tắc định dạng có điều kiện mới đã được tạo hoạt động chính xác – làm nổi bật tất cả các ô có giá trị âm.

The newly created conditional formatting rule is applied.

Thực hiện tạo một quy tắc định dạng có điều kiện mới trong Excel

Khi quy tắc định dạng có sẵn không thích hợp với yêu cầu của bạn, bạn có thể thiết lập một quy tắc mới từ đầu.

  1. Mark các ô cần áp dụng định dạng và nhấn vào Conditional Formatting > New Rule

Creating a new conditional formatting rule in Excel

  1. Trong hộp thoại Rule New Formatting, bạn sẽ chọn loại quy tắc cần tạo, như là “Format only cells that contain” và đặt định dạng cho giá trị nằm trong khoảng từ 60 đến 70.


3.Chọn vàoClick vào nút Format và thực hiện các cài đặt định dạng một cách chính xác như chúng ta đã thực hiện trong ví dụ trước đó.

4. Nhấn vào OK hai lần để đóng hộp thoại mở và hoàn tất quá trình định dạng có điều kiện của bạn.

The values are conditionally formatted.

Định dạng có điều kiện trong Excel dựa vào giá trị của một ô

Trong cả hai ví dụ đã nêu, việc tạo quy tắc định dạng được thực hiện bằng cách nhập giá trị số cụ thể. Tuy nhiên, đôi khi việc định dạng nên dựa vào giá trị của ô sẽ hiệu quả hơn. Cách này giúp cho định dạng có điều kiện của bạn có thể tự động cập nhật theo mọi thay đổi của ô trong tương lai.

Xét ví dụ về “Giá dầu”, lần này chúng ta sẽ làm nổi bật tất cả các giá trị trong cột B cao hơn giá dầu của tháng hai.

Tạo quy tắc tương tự như trước bằng cách đi tới Conditional formatting > Highlight Cells Rules > Greater Than… Tuy nhiên, thay vì nhập số tại bước 4, hãy chọn ô B6 bằng cách nhấp vào biểu tượng chọn dải ô như bạn thường làm trong Excel. Kết quả, bạn sẽ thấy giá trị đã được định dạng như trong hình chụp màn hình dưới đây.

Áp dụng nhiều quy tắc định dạng có điều kiện lên một ô hoặc bảng

Việc sử dụng định dạng có điều kiện trong Excel không bị hạn chế bởi việc chỉ áp dụng một quy tắc cho mỗi ô. Bạn có thể áp dụng nhiều quy tắc khác nhau tùy theo yêu cầu của công việc cụ thể.

Lấy ví dụ, bạn muốn tạo ra 3 quy tắc cho bảng dự báo thời tiết để ô sẽ chuyển sang màu vàng khi nhiệt độ vượt trên 60 ° F, chuyển sang màu cam khi trên 70 ° F và chuyển sang màu đỏ khi trên 80 ° F.

Bạn đã quen với việc tạo các quy tắc định dạng có điều kiện trong Excel bằng cách đi tới Conditional Formatting > Highlight Cells rules > Greater than. Nhưng để các quy tắc này hoạt động một cách chính xác, bạn cũng cần thiết lập ưu tiên cho chúng theo cách sau:

  1. Nhấn vào Conditional Formatting > Manage Rules… để truy cập vào Rules Manager.
  2. Chọn quy tắc mà bạn muốn áp dụng trước, sau đó di chuyển nó lên trên bằng cách sử dụng nút upward arrow. Làm tương tự với quy tắc thứ hai.
  3. Tích vào hộp kiểm Stop If True bên cạnh hai quy tắc đầu tiên nếu bạn không muốn các quy tắc sau đó được áp dụng khi điều kiện trước đã được thỏa mãn.

Sử dụng “Stop If True” trong việc áp dụng các quy tắc định dạng có điều kiện

Tính năng Stop If True đã được áp dụng trong ví dụ trước, giúp ngăn không cho các quy tắc khác phát huy tác dụng một khi điều kiện đầu tiên đã đạt được. Cách sử dụng này khá trực quan và dễ hiểu. Bây giờ, hãy cùng xem xét thêm hai ví dụ khác mà việc áp dụng Stop If True cũng mang lại lợi ích ngay cả khi không hẳn rõ ràng.

Ví dụ 1. Chỉ hiển thị một số ít biểu tượng cụ thể

Giả sử bạn đã thêm các biểu tượng sau vào báo cáo doanh số.

A sales report with icon setsNhững biểu tượng này có vẻ bắt mắt, nhưng hơi rối rắm. Mục tiêu của chúng ta là chỉ giữ lại những mũi tên màu đỏ hướng xuống khi muốn tập trung sự chú ý vào những sản phẩm kém hiệu quả và loại bỏ các biểu tượng còn lại. Cùng xem cách thực hiện:

  1. Tạo một quy tắc định dạng có điều kiện mới bằng cách nhấp chọn
    1. Conditional formatting > New Rule > Format only Cells that contain bạn cần tạo quy tắc này để áp dụng cho giá trị cao hơn giá trị trung bình. Việc định dạng sẽ dựa theo công thức = AVERAGE (), mà bạn có thể nhìn thấy ở hình dưới đây.

    Tư vấn. Bạn có thể chọn một khoảng các ô trong Excel bằng cách dùng biểu tượng chọn vùng phạm vi hoặc nhập phạm vi vào giữa các dấu ngoặc. Nếu bạn dùng cách sau, hãy nhớ đến việc dùng tham chiếu ô cố định với ký hiệu $.

    1. Ấn OK mà không cần thiết lập định dạng.
    2. Click vào Conditional Formatting > Manage Rules… và tích vào Stop if True ngay cạnh quy tắc mà bạn vừa tạo. Xem xét kết quả sau đó.

    Ví dụ 2. Loại trừ định dạng có điều kiện cho ô trống

    Hãy giả sử bạn tạo một quy tắc “Between” để đánh dấu các ô có giá trị từ $0 đến $1000, như thấy trong hình dưới. Nhưng rồi bạn nhận ra rằng cả những ô không có thông tin cũng bị áp đặt quy tắc này.

    Empty cells are also highlighted with conditional formatting.

    Để xử lý vấn đề này, tạo một quy tắc mới trong mục “Format only cells that contain“. Trong cửa sổ New Formatting rule, chọn “Blanks“.

    Create a conditional formatting rule for blank cells.

    Bạn chỉ cần nhấp vào OK mà không cần áp đặt bất cứ định dạng nào.

    Mở Conditional Formatting Rule Manager và chọn Stop if true cạnh quy tắc “Blanks“.

    Bạn sẽ nhận được kết quả đúng như ý muốn.

    Hướng dẫn chỉnh sửa quy tắc định dạng có điều kiện trong Excel

    Nếu bạn quan sát kỹ hình ảnh phía trên, bạn có thể thấy có nút Edit Rule…. Nếu bạn muốn thay đổi một quy tắc định dạng đã có, hãy thực hiện các bước sau:

    1. Chọn ô nào đó chứa quy tắc và nhấp vào Conditional Formatting > Manage Rules…
    2. Trong Conditional Formatting Rules Manager, chọn quy tắc bạn muốn sửa đổi rồi nhấp vào Edit Rule…

    1. Sửa đổi thông tin cần thiết trong cửa sổ Edit Formatting Rule và bấm OK để lưu lại những thay đổi.

    Cửa sổ Formatting Rule rất giống với New Rule Formatting mà bạn đã dùng để tạo ra quy tắc, vì thế bạn sẽ dễ dàng thực hiện.

    Tư vấn. Nếu không tìm thấy quy tắc bạn muốn chỉnh sửa, hãy chọn This Worksheet từ menu “Show formatting rules for” để xem danh sách đầy đủ các quy tắc.

    Không cần phải tạo mới các quy định nếu muốn áp dụng định dạng có điều kiện mà bạn đã cài đặt sẵn trong bảng tính Excel của mình cho dữ liệu mới. Chỉ cần sử dụng công cụ Format Painter để chép định dạng có điều kiện này sang nhóm dữ liệu khác một cách dễ dàng.

    1. Chọn ô chứa định dạng có điều kiện mà bạn mong muốn sao chép.
    2. Kích vào Home > Format Painter để biến con trỏ chuột thành hình cây cọ.

    Cách sao chép định dạng có điều kiện trong Excel

    Copy conditional formatting in Excel.

    Mẹo: Hãy nhấp đúp vào Format Painter nếu bạn muốn áp dụng định dạng đã sao chép cho nhiều nhóm ô khác nhau.

    1. Để dán định dạng, bạn chỉ cần nhấp chuột vào ô bạn muốn và kéo cây cọ tới ô cuối cùng của dãy cần định dạng.

    Paste the conditional formatting to another range of cells.

    1. Bấm Esc để thoát chế độ sử dụng cây cọ khi đã xong.

    Lưu ý: Nếu bạn dùng công thức để tạo ra quy tắc định dạng có điều kiện, bạn cần chỉnh sửa lại tham chiếu tới các ô trong công thức sau khi sao chép.

    Cách loại bỏ các quy tắc định dạng có điều kiện

    Để xóa đi bất kỳ quy tắc nào, bạn có thể:

    • Mở Conditional Formatting Rules Manager (từ Conditional Formatting > Manage Rules…), chọn quy tắc cần xóa và nhấn Delete Rule.
    • Chọn dãy ô cần xóa quy tắc, rồi kích vào Conditional Formatting > Clear Rules và chọn 1 trong các lựa chọn hiện có.

    Delete conditional formatting rules in Excel.

    Để tìm hiểu thêm về các hàm Excel nâng cao và cách sử dụng Excel hiệu quả hơn, bạn có thể tham gia khóa học SM90 – Excel từ cơ bản đến nâng cao của Siêu Marketing. Khóa học này rất chi tiết, giúp bạn xây dựng và phát triển kiến thức một cách có hệ thống. Điều đặc biệt là khóa học không giới hạn thời gian học nên bạn có khả năng học mọi lúc mọi nơi.

    Hiện nay, có rất nhiều khuyến mãi đặc biệt khi bạn đăng ký khóa học này. Xem thông tin chi tiết tại: Khoá học Excel từ Siêu Marketing

    Xem thêm: Cách tô màu dòng theo điều kiện trong excel, giúp giải quyết các vấn đề phức tạp

    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop