Cách Sử Dụng định Dạng Có điều Kiện Trong Excel 2010, 2013 Và 2016

Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn các bạn các tính năng cơ bản của định dạng có điều kiện. Bạn sẽ học cách tạo các quy tắc định dạng khác nhau, cách định dạng có điều kiện dựa trên một ô khác, cách chỉnh sửa và sao chép các quy tắc định dạng trong Excel 2007, 2010 2013 và 2016.

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

Giống như định dạng ô thông thường, bạn sử dụng định dạng có điều kiện để định dạng dữ liệu theo nhiều cách khác nhau: thay đổi màu sắc, màu font và các kiểu đường viền ô. Sự khác biệt là định dạng có điều kiện linh hoạt hơn, cho phép bạn định dạng dữ liệu khi đáp ứng các tiêu chí hoặc điều kiện nhất định.

Bạn có thể áp dụng định dạng có điều kiện cho một hoặc nhiều ô, hàng, cột hoặc toàn bộ bảng dựa trên nội dung ô hoặc dựa vào giá trị của một ô khác bằng cách tạo ra các quy tắc (điều kiện) xác định khi nào và như thế nào thì các ô được chọn phải được định dạng.

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

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

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

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

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

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

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

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

• Những ô nào cần phải được định dạng có điều kiện, và
• Cần đáp ứng những điều kiện nào.

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

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

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

2. Chuyển đến tab Home> Styles  và nhấp vào Conditional Formatting. Bạn cũng sẽ thấy một số quy tắc định dạng khác, gồm  data bars, color scales and icon sets.
3. Vì chúng ta chỉ cần áp dụng định dạng có điều kiện cho các số nhỏ hơn 0, chúng ta chọn Highlight Cells Rules > Less Than …

Khoá học Excel cơ bản

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

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

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

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

Mẹo:

• Nếu bạn muốn thêm nhiều màu nền hoặc phông chữ hơn bảng màu chuẩn, hãy nhấp vào nút More Colors …trên tab Fill hoặc Font.
• Nếu bạn muốn áp dụng một màu nền gradient, hãy nhấp vào nút Fill Effects trên tab Fill và chọn các tùy chọn mong muốn.
• Nhấp OKđể đóng cửa sổ “Less Than“ và kiểm tra xem việc định dạng có điều kiện có được áp dụng chính xác cho dữ liệu của bạn hay không.

Xem thêm: Thủ thuật Excel nâng cao

Như bạn thấy trong ảnh chụp màn hình bên dưới, quy tắc định dạng có điều kiện mới tạo của chúng ta hoạt động đúng – nó đổ bóng tất cả các ô có giá trị âm.

Tạo một quy tắc định dạng có điều kiện Excel từ đầu

Nếu không có quy tắc định dạng sẵn để sử dụng phù hợp với nhu cầu của bạn, bạn có thể tạo một quy tắc mới từ đầu.

1. Chọn các ô mà bạn muốn áp dụng định dạng có điều kiện và nhấp vào Conditional Formatting > New Rule…

2. Hộp Rule New Formattingsẽ mở ra và bạn chọn kiểu quy tắc cần thiết. Ví dụ: “Format only cells that contain” và chọn định dạng các giá trị nằm giữa 60 và 70.

3.Nhấp vào nút Format và thiết lập định dạng của bạn chính xác như chúng ta đã làm trong ví dụ trước.

4.Nhấp OK hai lần để đóng các cửa sổ đang mở và định dạng có điều kiện của bạn đã xong.

Định dạng có điều kiện Excel dựa trên giá trị của ô

Trong cả hai ví dụ trước, chúng ta đã tạo ra các quy tắc định dạng bằng cách nhập các số. Tuy nhiên, trong một số trường hợp, tốt hơn nên căn cứ vào giá trị của ô để định dạng. Lợi thế của cách này là bất kể giá trị của ô đó thay đổi như thế nào trong tương lai, định dạng có điều kiện của bạn sẽ tự động điều chỉnh và phản ánh sự thay đổi dữ liệu.

Hãy sử dụng ví dụ “Oil price”, nhưng lần này làm nổi bật tất cả giá trong cột B lớn hơn giá của tháng hai.

Bạn tạo quy tắc theo cách tương tự bằng cách chọn Conditional formatting > Highlight Cells Rules > Greater Than….. Nhưng thay vì gõ một số trong bước 4, bạn chọn ô B6 bằng cách nhấp vào biểu tượng chọn phạm vi như bạn thường làm trong Excel. Do đó, giá được định dạng như bạn thấy trong ảnh chụp màn hình bên dưới.

Áp dụng một số quy tắc định dạng có điều kiện cho một ô/bảng

Khi sử dụng định dạng có điều kiện trong Excel, bạn không bị giới hạn ở một quy tắc cho mỗi ô. Bạn có thể áp dụng nhiều quy tắc logic mà công việc cụ thể của bạn yêu cầu.

Ví dụ, hãy tạo ra 3 quy tắc cho bảng thời tiết sẽ tô nền màu vàng khi nhiệt độ cao hơn 60 ° F với, màu da cam khi cao hơn 70 ° F và màu đỏ khi cao hơn 80 ° F.

Bạn đã biết cách tạo các quy tắc định dạng có điều kiện của Excel theo kiểu này – bằng cách nhấp vào Conditional Formatting > Highlight Cells rules > Greater than. Tuy nhiên, để các quy tắc hoạt động chính xác, bạn cũng cần phải đặt mức ưu tiên theo cách này:

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

Xem thêm: hướng dẫn sử dụng excel 2020

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

Chúng ta đã sử dụng tùy chọn Stop If True trong ví dụ trên để dừng xử lý các quy tắc khác khi điều kiện đầu tiên được đáp ứng. Cách sử dụng đó rất rõ ràng và dễ hiểu. Bây giờ chúng ta hãy xem xét thêm hai ví dụ nữa khi việc sử dụng Stop If True không rõ ràng nhưng cũng rất hữu ích.

Ví dụ 1. Chỉ hiển thị một số mục của biểu tượng thiết lập

Giả sử bạn đã thêm biểu tượng sau đây vào sales report.

Nhìn có vẻ đẹp, nhưng có hơi rối. Vì vậy, mục tiêu của chúng ta là chỉ giữ các mũi tên màu đỏ đi xuống để thu hút sự chú ý đến các sản phẩm hoạt động dưới mức trung bình và bỏ các biểu tượng khác. Hãy xem làm điều này như thế nào:

1. Tạo một quy tắc định dạng có điều kiện mới bằng cách nhấp vào Conditional formatting > New Rule > Format only Cells that contain.
2. Bây giờ bạn cần phải định hình quy tắc theo cách nó chỉ được áp dụng cho các giá trị lớn hơn mức trung bình. Bạn thực hiện việc này bằng cách sử dụng công thức = AVERAGE (), như thể hiện trong hình bên dưới.

Mẹo. Bạn luôn có thể chọn một dãy ô trong Excel bằng cách sử dụng biểu tượng chọn phạm vi tiêu chuẩn  hoặc gõ phạm vi bên trong các dấu ngoặc. Nếu bạn chọn cách thứ hai, hãy nhớ sử dụng tham chiếu ô tuyệt đối với dấu $.

3. Nhấp OK mà không thiết lập bất kỳ định dạng nào.
4. Nhấp vào Conditional Formatting > Manage Rules…và đánh dấu vào hộp kiểm Stop if True  bên cạnh quy tắc bạn vừa tạo. Xem kết quả.

Tìm hiểu về: khóa học excel nâng cao Hà Nội

Ví dụ 2. Loại bỏ định dạng có điều kiện ở các ô rỗng

Giả sử bạn đã tạo ra quy tắc “Between” để đánh các ô có giá trị từ $0 đến $1000, như bạn thấy trong ảnh chụp màn hình dưới đây. Nhưng vấn đề là các ô trống cũng được đánh dấu.

Để khắc phục điều này, bạn tạo một quy tắc nữa ở ô “Format only cells that contain“. Trong hộp thoại New Formatting rule, chọn Blanks từ danh sách.

Và một lần nữa, bạn chỉ cần nhấp vào OK mà không cần cài đặt bất kỳ định dạng nào.

Cuối cùng, mở  Conditional Formatting Rule Manager và chọn hộp kiểm Stop if true bên cạnh quy tắc “Blanks“.

Kết quả chính xác như bạn mong đợi.

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

Nếu bạn nhìn kỹ vào ảnh chụp màn hình ở trên, bạn có thể thấy nút Edit Rule… ở đó. Vì vậy, nếu bạn muốn thay đổi một quy tắc định dạng hiện có, hãy tiếp tục theo cách này:

1. Chọn bất kỳ ô nào áp dụng quy tắc và nhấp vào Conditional Formatting > Manage Rules…
2. Trong Conditional Manager Rules Manager, nhấp vào quy tắc bạn muốn chỉnh sửa, và sau đó nhấp nút Edit Rule….

3. Thực hiện các thay đổi cần thiết trong cửa sổ Edit Formatting Rulevà nhấp vào OK để lưu các chỉnh sửa.

Của sổ Formatting Rule rất giống với New Rule Formatting mà bạn sử dụng khi tạo các quy tắc, do đó bạn sẽ không gặp khó khăn gì.

Mẹo. Nếu bạn không thấy quy tắc bạn muốn chỉnh sửa, hãy chọn This Worksheet  từ danh sách “Show formatting rules for” để hiển thị danh sách tất cả các quy tắc trong bảng tính của bạn.

Làm thế nào để sao chép định dạng có điều kiện trong Excel

Nếu bạn muốn áp dụng định dạng có điều kiện mà bạn đã tạo trước đó cho các dữ liệu khác trong bảng tính, bạn sẽ không cần tạo lại quy tắc từ đầu. Đơn giản chỉ cần sử dụng Format Painter để sao chép các định dạng có điều kiện hiện có vào bộ dữ liệu mới.

1. Nhấp vào bất kỳ ô nào có định dạng có điều kiện bạn muốn sao chép.
2. Nhấp Home > Format Painter. Điều này sẽ thay đổi con trỏ chuột thành một cây cọ.

Mẹo. Bạn có thể nhấp đúp vào Format Painter nếu bạn muốn dán định dạng có điều kiện trong một số dải ô khác nhau.

3. Để dán định dạng có điều kiện, nhấp vào ô đầu tiên và kéo cây cọ xuống đến ô cuối cùng trong dải ô bạn muốn định dạng.

4. Khi hoàn tất, nhấn Escđể dừng sử dụng cọ.

Chú Ý. Nếu bạn đã tạo quy tắc định dạng có điều kiện bằng cách sử dụng công thức, bạn có thể cần phải điều chỉnh các tham chiếu ô trong công thức sau khi sao chép định dạng có điều kiện.

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

Để xóa một quy tắc, bạn có thể:

• Mở Conditional Manager Rules Manager (thông quaConditional Formatting > Manage Rules…), chọn quy tắc và nhấp vào nút Delete Rule.
• Chọn dải ô, nhấp Conditional Formatting > Clear Rulesvà chọn một trong các tùy chọn có sẵn.

Để có thể biết thêm nhiều hàm nâng cao trong Excel cũng như sử dụng tốt hơn các công cụ của Excel, các bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới nâng cao của Học Excel Online. Đây là khóa học chi tiết và đầy đủ nhất, giúp bạn phát triển kiến thức một cách đầy đủ, có hệ thống. Đặc biệt khóa học này không giới hạn thời gian học tập, nên bạn có thể học thoải mái và xem lại bất cứ khi nào.

Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký khóa học này. Chi tiết xem tại: Khoá học Excel từ Học Excel Online

Xem thêm: Tô màu dòng có điều kiện trong excel, giải quyết những bài toán khó