Bài viết dưới đây, chúng ta sẽ cùng tìm hiểu về các hướng dẫn sử dụng Conditional Formatting trong Excel 2013, 2010 và 2016, hay còn gọi là định dạng có điều kiện. Cùng đón đọc nhé!
Các khái niệm cơ bản của định dạng có điều kiện của Excel
Cũng giống như các định dạng ô thông thường khác, bạn dùng định dạng có điều kiện để thực hiện việc định dạng dữ liệu với nhiều cách khác nhau như: thay đổi font màu, mà sắc hay các đường viền ô,… Sự khác biệt là định dạng có điều kiện linh hoạt hơn, từ đó cho phép bạn định dạng dữ liệu khi đáp ứng những tiêu chí hoặc điều kiện nhất định.
Bạn cũng có thể áp dụng định dạng có điều kiện cho một hoặc là nhiều ô, hàng, cột hoặc là toàn bộ bảng dựa trên nội dung ô, cũng như 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, như thế nào thì những ô được chọn phải được định dạng.
Trong toàn bộ các phiên bản của Excel, định dạng có điều kiện sẽ nằm ở cùng một vị trí, trên tab Home > Styles.
Định dạng có điều kiện ở Excel 2007
Định dạng có điều kiện ở Excel 2010
Định dạng có điều kiện ở 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 hết khả năng của định dạng có điều kiện trong Excel, bạn cần phải học cách tạo ra những loại quy tắc khác nhau.
Sẽ có hai điều quan trọng trong định dạng có điều kiện, đó là:
- Những ô nào cần được định dạng có điều kiện, và
- Cần đáp ứng các điều kiện nào.
Bài viết này sẽ chỉ cho bạn cách làm thế nào để áp dụng định dạng có điều kiện trong Excel 2010- phiên bản phổ biến nhất hiện nay. Tuy nhiên, những tùy chọn về cơ bản giống nhau trong Excel 2007 và 2013,nên bạn yên tâm là 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.
Bước 1: Chọn những ô mà bạn muốn định dạng.
Ví dụ, bạn tạo một bảng nhỏ liệt kê giá dầu thô hàng tháng. Điều bạn muốn làm là cần 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 hãy chọn các ô C2: C9.
Bước 2: Chuyển đến tab Home> Styles rồi nhấp vào Conditional Formatting. Bạn sẽ thấy một số quy tắc định dạng khác, bao gồm data bars, color scales and icon sets.
Bước 3: Vì bạn chỉ cần áp dụng định dạng có điều kiện cho những số nhỏ hơn 0, nên hãy chọn Highlight Cells Rules > Less Than …
Tất nhiên, bạn cũng 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 bảng dữ liệu của bạn, chẳng hạn như:
- Định dạng những giá trị lớn hơn, nhỏ hơn hoặc bằng
- Đánh dấu vào văn bản có chứa từ hoặc ký tự được chỉ định
- Làm nổi bật những bản copy
- Định dạng ngày tháng cụ thể
Bước 4: Tiến hành nhập giá trị trong hộp tại phần bên phải của cửa sổ dưới “Format cells that are LESS THAN“, đối với trường hợp này, bạn gõ 0. Ngay khi đã nhập giá trị, Microsoft Excel sẽ thực hiện làm nổi bật các ô trong dãy đã chọn để đáp ứng điều kiện của bạn.
Bước 5: Tiếp tục chọn định dạng mà bạn muốn từ danh sách. Lúc này, 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…để tiến hành thiết lập định dạng của riêng bạn.
Bước 6: Trong cửa sổ Format Cells, tiến hành 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. Tại các tab Font và Fill, bạn sẽ nhìn thấy ngay mẫu xem trước định dạng tùy chỉnh của mình.
Bước 7: Khi hoàn thành, hãy nhấp vào nút OKở cuối cửa sổ.
Mẹo:
- Nếu như bạn muốn thêm nhiều màu nền hoặc là các phông chữ hơn bảng màu chuẩn, hãy thực hiện nhấp vào nút More Colors …trên tab Fill hoặc Font.
- Nếu như bạn muốn áp dụng một màu nền gradient, hãy nhấp nút Fill Effects trên tab Fill và chọn các tùy chọn mong muốn.
- Nhấp OK để tiến hành đóng cửa sổ “Less Than“ và thực hiện 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.
Như bạn đã thấy trong ảnh bên dưới, quy tắc định dạng có điều kiện mới tạo đã hoạt động đúng – nó đổ màu cho tất cả các ô có giá trị âm.
Xem thêm: Hướng dẫn cách tách cột họ và tên trong Excel
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 để dùng cho phù hợp với nhu cầu của bạn, thì bạn hoàn toàn cũng có thể tạo một quy tắc mới từ đầu.
Bước 1: Tiến hành chọn các ô mà bạn muốn áp dụng định dạng có điều kiện, rồi nhấp vào Conditional Formatting > New Rule…
Bước 2: Tại hộp Rule New Formatting sẽ mở ra, 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 những giá trị nằm giữa 60 và 70.
Bước 3: Nhấp vào nút Format và thực hiện thiết lập định dạng của bạn chính xác như bạn đã làm trong ví dụ trước.
Bước 4: Tiếp đó, nhấp OK hai lần để đóng các cửa sổ đang mở, vậy là đị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 ô
Ở hai ví dụ trước, bạn đã thực hiện tạo ra các quy tắc định dạng bằng việc nhập các số. Tuy nhiên, trong một vài trường hợp, tốt hơn hết là nên căn cứ vào giá trị của ô để định dạng. Lợi thế của phương pháp này đó chính là bất kể giá trị của ô đó thay đổi như thế nào trong tương lai, thì định dạng có điều kiện của bạn cũng sẽ tự động điều chỉnh và phản ánh sự thay đổi dữ liệu.
Hãy dùng ví dụ “Oil price”, nhưng với lần này là 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 thực hiện tạo quy tắc theo cách tương tự bằng việc chọn Conditional formatting > Highlight Cells Rules > Greater Than….. Nhưng thay vì tiến hành gõ một số trong bước 4, thì bạn chọn ô B6 bằng việc nhấp vào biểu tượng chọn phạm vi như bạn vẫn làm trong Excel. Vì thế, 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 dùng định dạng có điều kiện trong Excel, bạn sẽ không bị giới hạn ở một quy tắc nào cho mỗi ô. Bạn cũng có thể áp dụng được nhiều quy tắc logic mà công việc cụ thể của bạn đã yêu cầu.
Ví dụ như hãy tạo ra 3 quy tắc cho bảng thời tiết với điều kiện 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, màu đỏ khi cao hơn 80 ° F.
Bạn đã biết cách tạo những quy tắc định dạng có điều kiện của Excel theo kiểu này – chỉ cần nhấp vào Conditional Formatting > Highlight Cells rules > Greater than. Tuy vậy, để quy tắc hoạt động chính xác nhất, bạn cũng cần phải đặt mức ưu tiên theo các bước sau:
Bước 1: Nhấp vào Conditional Formatting > Manage Rules…. để đi tới Rules Manager.
Bước 2: Tiếp đó, nhấp vào quy tắc cần được áp dụng trước tiên để chọn, rồi di chuyển lên phía trên bằng cách dùng upward arrow. Làm tương tự cho quy tắc thứ hai.
Bước 3: Nhấp 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 những quy tắc khác được áp dụng khi điều kiện đầu tiên được đáp ứng.
Sử dụng “Stop If True” trong các quy tắc định dạng có điều kiện
Bạn đãdùng tùy chọn Stop If True trong ví dụ trên để ngưng xử lý các quy tắc khác khi điều kiện đầu tiên được đáp ứng. Cách dùng đó rất rõ ràng và dễ hiểu. Bây giờ bạn hãy xem xét thêm hai ví dụ nữa bên dưới khi việc 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
Ví dụ như bạn đã thêm biểu tượng sau đây vào sales report.
Thoạt đầu nhìn có vẻ đẹp, nhưng có hơi rối. Vì vậy, mục tiêu của ta là chỉ giữ các mũi tên màu đỏ đi xuống để thu hút sự chú ý đến những sản phẩm hoạt động dưới mức trung bình và bỏ các biểu tượng khác. Thực hiện các bước như sau:
Bước 1: Tạo một quy tắc định dạng có điều kiện mới bằng việc nhấp vào Conditional formatting > New Rule > Format only Cells that contain.
Bước 2: Bây giờ bạn cần định hình quy tắc theo cách là 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 việc dùng công thức = AVERAGE (), như thể hiện trong hình bên dưới.
Mẹo. Bạn cũng luôn có thể chọn một dãy ô trong Excel bằng việc dùng biểu tượng chọn phạm vi tiêu chuẩn hoặc là gõ phạm vi bên trong những dấu ngoặc. Nếu bạn chọn áp dụng cách thứ hai, hãy nhớ dùng tham chiếu ô tuyệt đối với dấu $.
Bước 3: Tiếp theo, nhấp OK mà không thiết lập bất kỳ định dạng nào.
Bước 4: Sau đó, nhấp vào Conditional Formatting > Manage Rules…rồi đá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. Cuối cùng hãy xem kết quả.
Ví dụ 2. Loại bỏ định dạng có điều kiện ở các ô rỗng
Ví dụ bạn đã tạo ra quy tắc “Between” để đánh những ô có giá trị từ $0 đến $1000, như bạn thấy trong ảnh dưới đây. Nhưng vấn đề là các ô trống cũng được đánh dấu.
Để khắc phục được điều này, bạn tiến hành tạo một quy tắc nữa ở ô “Format only cells that contain“. Trong hộp thoại New Formatting rule, nhấp chọn Blanks từ danh sách.
Và một lần nữa, bạn cũng 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, hãy mở Conditional Formatting Rule Manager và nhấn chọn hộp kiểm Stop if true bên cạnh quy tắc “Blanks“.
Kết quả sẽ hiển thị 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 ở trên, bạn có thể thấy rằng nút Edit Rule… ở đó. Vì vậy, nếu như 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ác bước sau:
Bước 1: Chọn bất kỳ ô nào áp dụng quy tắc, rồinhấp vào Conditional Formatting > Manage Rules…
Bước 2: Trong Conditional Manager Rules Manager, hãy nhấp vào quy tắc bạn muốn chỉnh sửa, và tiếp tục nhấp nút Edit Rule….
Bước 3: Thực hiện những thay đổi cần thiết trong cửa sổ Edit Formatting Rule và nhấp vào OK để tiến hành lưu các chỉnh sửa.
Của sổ Formatting Rule giống với New Rule Formatting mà bạn dùng khi tạo các quy tắc, do vậy bạn sẽ không gặp khó khăn gì trong quá trình thực hiện.
Mẹo. Nếu như bạn không thấy quy tắc bạn đang muốn chỉnh sửa, hãy chọn This Worksheet từ danh sách “Show formatting rules for” để có thể 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
Bạn đang muốn áp dụng định dạng có điều kiện mà bạn đã tạo trước đó cho những dữ liệu khác trong bảng tính? Vậy thì bạn sẽ không cần tạo lại quy tắc từ đầu. Đơn giản chỉ cần dùng Format Painter để tiến hành sao chép các định dạng có điều kiện hiện có vào bộ dữ liệu mới.
Bước 1: Nhấp vào bất kỳ ô nào có định dạng có điều kiện bạn đang muốn sao chép.
Bước 2: Nhấp Home > Format Painter. Điều này cũng sẽ thay đổi con trỏ chuột thành một cây cọ.
Mẹo. Bạn có thể nhấn đúp vào Format Painter nếu bạn đang muốn dán định dạng có điều kiện trong một số dải ô khác nhau.
Bước 3: Để dán định dạng có điều kiện, hãy 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 đang muốn định dạng.
Bước 4: Khi hoàn tất, nhấn Escđể dừng dùng cọ.
Chú Ý. Nếu bạn đã tạo quy tắc định dạng có điều kiện bằng việc dùng công thức, bạn cũng có thể cần phải điều chỉnh những 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ũng có thể làm theo các bước sau:
Bước 1: Mở Conditional Manager Rules Manager (thông quaConditional Formatting > Manage Rules…), nhấn chọn quy tắc và nhấp vào nút Delete Rule.
Bước 2: Chọn dải ô, nhấp Conditional Formatting > Clear Rules và nhấn chọn một trong các tùy chọn có sẵn.
Xem thêm: Hướng dẫn cách vẽ biểu đồ cột trong Excel
