Làm sao để sử dụng định dạng có điều kiện trong Excel bản 2007, 2010, 2013, 2016

Ở bài viết này Gitiho.com sẽ hướng dẫn các bạn một cách chi tiết những ứng dụng thường thấy của định dạng có điều kiện trong Excel. Qua đây, bạn sẽ nắm rõ được các quy tắc định dạng khác nhau, cách định dạng có điều kiện trên một ô khác hay các chỉnh sửa và sao chép các quy tắc định dạng bao gồm trong Excel 2007, 2010, 2013 và 2016.

Khái niệm định dạng có điều kiện trong Excel

Để hiểu đơn giản, định dạng có điều kiện trên Excel cũng giống như định dạng ô thông thường với những tiện ích như: thay đổi màu sắc, màu font và các kiểu đường viền ô. Điểm cộng mà định dạng có điều kiện đem đến cho người sử dụng là sự linh hoạt, cho phép người dùng định dạng dữ liệu khi đáp ứng các yêu cầu và tiêu chí phụ thuộc theo công việc. 

Không chỉ tiện lợi về mặt ứng dụng, áp dụng định dạng có điều kiện có thể sử dụng cho một hoặc nhiều ô, hàng, cột hay thậm chí toàn bộ bảng tính Excel dựa trên nội dung ô hoặc dựa vào giá trị của ô 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.

Vị trí của định dạng có điều kiện trên màn hình Excel

Cho dù trên bất kì phiên bản Excel nào thì định dạng có điều kiện đều nằm ở tab Home > Styles,

Trên Excel 2007

 

Trên Excel 2010.

 

Trên Excel 2013 và 2016.

Xem thêm: Hướng dẫn cách ẩn #N/A trong Excel bằng tính năng Conditional Formatting

Tạo các quy tắc định dạng có điều kiện trên Excel

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

Trước tiên bạn phải hiểu được các quy tắc khác nhau của điều kiện trong Excel. 

Trong đó bạn phải hiểu có hai điều quan trọng trong định dạng có điều kiện:

Định dạng có điều kiện được xuất hiện lần đầu trên bản Excel 2007 và mới nhất là Excel 2016, nhưng trong bài viết này Gitiho.com sẽ hướng dẫn các bạn sử dụng tính năng này trên bản 2010 - bản Excel phổ biến ở hầu khắp các cơ quan doanh nghiệp. Bên cạnh đó, phiên bản 2010 cũng không khác gì các tuỳ chọn cơ bản trên Excel 2007, 2013.

Bước 1: Lựa chọn ô cần định dạng.

Ví dụ bảng Excel dưới đây là bảng liệt kê dầu thô hàng tháng. Nhiệm vụ của chúng ta là làm nổi bật ô Change (giảm giá) vì vậy chúng ta cần chọn các ô C2:C9.

Bước 2: Nhấn vào tab Home>Style và nhấn vào Conditional Formating. Tới đây Excel sẽ đưa ra cho bạn những lựa chọn quy tắc định dạng khác như data bars, color scales và icon sets.

Bước 3: Chúng ta chỉ cần đặt áp dụng định dạng có điều kiện nhỏ hơn 0 (>0), chúng ta chọn Highlight Cells Rules > Less Than …

 

Ngoài ra bạn có thể áp dụng bất kỳ loại quy tắc nào phù hợp với với loại dư liệu của bạn, sau đây là những ví dụ:

- Làm nổi bật các bản sao chép.

- Định dạng ngày tháng.

- Đánh dấu văn bản có ký tự được chỉ định.

Bước 4: Điền giá trị trong hôp thoại phía bên tay phải của cửa sổ dưới "Format cells that are LESS THAN“. Trong ví dụ mà Gitiho.com đưa ra chúng ta sẽ gõ "0". Tới đây Excel sẽ làm nổi bật những ô trong dãy đáp ứng yêu cầu của bạn.

Bước 5:  Để lựa chọn định dạng mà bạn muốn, bạn có thể chọn một trong nhiều các định dạng trước đó hoặc nhấn vào phần Custom Format... để đưa ra thiết lập định dạng theo yêu cầu của riêng bạn.

Bước 6: Trong cửa sổ Format Cells chứa 3 tab gồm Font, Border và Fill với 3 chức năng riêng biệt gồm:

- Font: kiểu phông chữ.

- Border: kiểu đường viền.

- Fill: màu nền.

Bước 7: Bạn chỉ cần bấm OK ở cuối phía bên phải cửa sổ.

Một vài mẹo nhỏ: 

- Nếu bạn là người yêu thích màu nền gradient nhẹ nhàng, bạn chỉ cần nhấn vào nút Fill Effects trên tab Fills và chọn các tuỳ chọn mong muốn.

- Ngoài ra Excel cũng cung cấp rất nhiều lựa chọn màu sắc cho người sử dụng, bạn chỉ cần nhấp vào phần More Color... trên cửa sổ Fill hoặc Font.

Sau khi hoàn thành những bước trên, quy tắc định dạng có điều kiện đã làm nổi bật tất cả các ô mang giá trị âm.

Tạo quy tắc định dạng Excel từ đầu

Sẽ có lúc người sử dụng sẽ gặp trường hợp không có quy tắc định dạng có sẵn phù hợp với nhu cầu công việc. Để xử lý tình huống này, chúng ta nên tạo một quy tắc mới từ đầu.

Bước 1: Chọn những ô mà bạn cần định dạng có điều kiện rồi chọn Conditional Formating > New Rule.

 

Bước 2: Khi mở Rule New Formating bạn chỉ cần chọn kiểu quy tắc mà bạn muốn. Như ví dụ đưới đây chúng ta nhấn vào “Format only cells that contain” rồi chọn định dạng các giá trị nằm giữa 60 và 70.

Bước 3: Nhấn vào Format và  thiết lập định dạng của bạn giống như ví dụ trên.

Bước 4: Nhấn OK hai lần để hoàn tất những tác vụ bạn đã làm và đóng các cửa sổ đã mở.

Xem thêm: Cách viết công thức Excel trong Conditional Formatting và Data Validation

Cách sử dụng "Stop if true" bằng quy tắc xác định có điều kiện

Có thể nói rằng cách sử dụng lệnh "Stop if true" này có phần nào đó không rõ ràng, nhưng chính điều này lại đem đến một lợi ích không ngờ cho người sử dụng Excel.

VD1: Chỉ hiển thị mục nào có biểu tượng thiết lập.

Theo như hình dưới đây, bản báo cáo doanh số (sales report) được điền một số biểu tượng nhất định.

Nhiệm vụ của bạn sẽ là loại bỏ bớt những ký tự không cần thiết và chỉ giữ lại mũi tên đỏ hướng xuống để bản báo cáo chú ý hơn vào những sản phẩm có mức hoạt động dưới trung bình. Vậy chúng ta cần làm thao tác này như thế nào?

Bước 1: Tạo một định dạng có điều kiện mới bằng cách chọn "Conditional formatting > New Rule > Format only Cells that contain."

Bước 2: Tới bước này bạn cần xác định cho Excel chỉ được áp dụng cho các giá trị lớn hơn mức trung bình. Để thực hiện tác vụ này bạn cần sử dụng công thức =AVERAGE(), 

Mẹo: Excel cho phép người sử dụng lựa chọn một dãy ô trong bằng cách sử dụng biểu tượng phạm vi tiêu chuẩn hoặc điền phạm vi bạn yêu cầu bên trong dấu ngoặc. Tuy nhiên nếu như bạn sử dụng cách điền phạm vi, chúng ta cần phải tham chiếu ô tuyệt đối với dấu $.

Bước 3: Nhấn OK mà không cần thiết lập bất kì định dạng nào.

Bước 4: Nhấn vào phần Conditional Formating > Manage Rules... Sau đó tích vào ô Stop if true bên cạnh quy tắc vừa tạo.

VD2: Bỏ đi những ô trống bắng định dạng có điều kiện.

Ở ví dụ này chúng ta đã tạo ra quy tắc "Between" với mục đích đánh các ô có giá trị từ $0 đến $1000. Tuy nhiên những ô rỗng cũng được đánh dấu đỏ.

Từ đây chúng ta tạo một quy tắc ở ô "Format only cells that contain".  Từ đó trong hộp thoại "New Formatting rule" ta sẽ chọn phần  "Blank". 

Tới đây bạn chỉ cần nhấn OK mà không cần phải thêm cài đặt bất kỳ nào.

'

Sử dụng định dạng có điều kiện trên Excel dựa vào giá trị của ô

Với cách sử dụng định dạng này chúng ta sẽ căn cứ vào giá trị của ô để định dạng để giúp cho việc trong tương lai, bất kể giá trị nào của ô đó thay đổi thì định dạng điều kiện của người sử dụng sẽ tự động điều chỉnh và báo cáo lại việc thay đổi dữ liệu.

Dưới đây Gitiho.com sẽ sử dụng giá dầu (Oil price) làm ví dụ và sẽ làm nổi bật tất cả giá trong cột B lớn hơn giá của tháng Hai. 

Vẫn giống như các quy tắc trên, chúng ta sẽ chọn Conditional formatting > Highlight Cells Rules > Greater Than….. Tới đây thay vì gõ số trong bước 4, ta chọn ngay ô B6 bằng cách nhấp vào biểu tượng chọn phạm vi . Sau đó  ta sẽ có được dịnh dạng như ảnh dưới đây.

Xem thêm: Công thức Conditional Formatting trong Excel để tô màu ngày chủ nhật

Sử dụng một số quy tắc định dạng có điều kiện cho một bảng hoặc ô bất kỳ

Microsoft Excel là một ứng dụng tuyệt vời khi bạn không bị giới hạn quy tắc ở mỗi ô và bạn hoàn toàn áp dụng vô số quy tắc logic mà công việc của bạn yêu cầu.

Chẳng hạn chúng ta cần phải xử lý công việc liên quan đến thời tiết, mỗi khi nhiệt độ lên cao hơn 60 ° F thì nền vàng sẽ xuất hiện, màu da cam đối với tiết trời 70° F trở lên và chuyển đỏ khi lớn hơn 80° F.

Cũng tương tự như những ví dụ đã nêu, chúng ta vào "Conditional Formatting > Highlight Cells rules > Greater than". Để hoạt động này chạy chính xác, chúng ta cần lưu ý mức ưu tiên khi sử dụng cách này:

Bước 1: Bấm vào "Conditional Formatting > Manage Rules…." rồi tiếp theo tới phần Rules Manager.

Bước 2: Lựa chọn quy tắc cần áp dụng đầu tiên, tiếp đến phía trên bằng cách sử dụng "Upward arrow"

Bước 3: Chọn hộp thoại "Stop if true" cùng quy tắc đầu tiên nếu như bạn không muốn quy tắc được áp dụng khi điều kiện đầu tiên đáp ứng được nhiệm vụ

 

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

Bảng Edit Rule trong hình ảnh dưới đây có mục đích thay đổi một quy tắc định dạng hiện có, tiếp theo bạn làm theo các bước sau.

Bước 1: Chọn bất kỳ ô nào áp dụng quy tắc, sau đó nhấp tiếp vào Conditional Formatting > Manage Rules…

Bước 2: Trong hộp Conditional Manager Rules Manager, lựa chọn quy tắc bạn chỉnh sửa và nhấp vào đó, tiếp theo bấm vào Edit Rule...

Bước 3: Thay đổi theo quy tắc theo ý muốn của bạn trong cửa sổ Edit Formatting Rule rồi chọn OK để lưu lại chỉnh sửa.

Hai cửa sổ Formatting Rule và New Rule Formatting không có gì quá khác nhau, vậy nên bạn cũng không cần lo lắng gì nhiều.

Mẹo:  Trong trường hợp bạn không tìm thấy được quy tắc cần chỉnh sửa, hãy sử dụng hộp This Worksheet trong nhóm "Show formatting rules for” để hiện thị hết tất cả các quy tắc trong bảng tính của bạn.

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

Nếu như bạn muốn sao chép tất cả những điều kiện đã sử dụng trước đó cho mọi dữ liệu khác trong bảng tính. Sẽ vô cùng tốn thời gian nếu như bạn copy toàn bộ từ đầu. Một cách hữu dụng và chính xác hơn mà chúng ta nên dùng là Format Painter để sao chép định dạng có điều kiện vào bộ dữ liệu mới.

Xem thêm: Cách tô màu trong Excel theo điều kiện bằng Conditional Formatting

Kết luận

Hy vọng qua bài viết này, bạn đã hiểu rõ hơn về cách sử dụng định dạng có điều kiện trong Excel 2007, 2010, 2013, 2016 và ứng dụng thành công vào công việc của mình nhé! Ngay bây giờ hãy cùng luyện tập thêm với bộ bài tập Excel có đáp án của Gitiho để nằm lòng tin học văn phòng nhé! 

Gitiho là lựa chọn hàng đầu cho kế toán, quản lý, người bán hàng, marketer phát triển bản thân, kĩ năng Excel hay tự học tin học văn phòng tại nhà tại nhà một cách chính xác ngay từ đầu mà không mất quá nhiều thời gian.