Conditional Formatting trong Excel hay Định dạng có điều kiện là một tính năng hữu ích và rất cần thiết trong hầu hết các báo cáo Dashboard. Tính năng này giúp các ô tính nổi bật hơn dựa theo các điều kiện cho trước. Hãy cùng Tinhocmos tìm hiểu ngay cách định dạng có điều kiện trong Excel để báo cáo của bạn trở nên khác biệt và ấn tượng nhé.
Mục lục
2 Cách bước sử dụng Conditional Formatting trong Excel3 Một số loại Conditional Formatting trong Excel phổ biến nhất4 Tóm lại về Conditional Formatting trong ExcelKhái niệm về Conditional Formatting trong Excel
Conditional Formatting trong Excel hay Định dạng có điều kiện sẽ giúp bạn định dạng ô tính theo nhiều tiêu chí khác nhau: màu sắc, font chữ, viền ô,… Định dạng có điều kiện có ưu điểm vượt trội linh hoạt hơn định dạng thông thường và sẽ tự động định dạng một khi ô tính đáp ứng được tiêu chí đã đặt ra.
Bạn đang xem: Định dạng có điều kiện trong excel 2010
Định dạng có điều kiện có thể được áp dụng cho một hoặc nhiều ô, hàng, cột hoặc toàn bộ trang tính tùy nhu cầu của bạn.
Cách bước sử dụng Conditional Formatting trong Excel
Bước 1: Chọn vùng giá trị
Bạn chọn vùng giá trị cần định dạng có điều kiện trong Excel
Bước 2: Mở New Formatting Rule để thêm định dạng có điều kiện
Sau đó, tại thẻ Home > Styles, bạn chọn biểu tượng Conditional Formatting và nhấp chọn New Rule.
Hộp thoại New Formatting Rule hiện ra.
Bước 3: Cài đặt điều kiện định dạng
Tiếp theo, bạn chọn kiểu quy tắc mình cần dùng. Ở đây, Tinhocmos chọn quy tắc Format only cells that contain để định dạng ô theo các điều kiện bên dưới.
Tại phần Format only cells with, bạn lần lượt chọn Cell Value, Greater than và điền giá trị 250. Điều kiện này nghĩa là giá trị của các ô sẽ >250.
Bước 4: Cài đặt Format Cells cho ô tính thỏa mãn điều kiện
Phần Preview, bạn chọn Format. Hộp thoại Format Cells hiện ra.
Trong bảng Format Cells, bạn chọn mục Fill > More Colors rồi chọn màu xanh và nhấn OK để xác nhận.
Bước 5: Xác nhận kết quả
Quay trở về bảng New Formatting Rule ban đầu, bạn kiểm tra lại và chọn OK để xác nhận.
Ta được kết quả định dạng như hình, tất cả các ô có giá trị >250 sẽ được tô màu xanh.
Một số loại Conditional Formatting trong Excel phổ biến nhất
Định dạng Highlight ô tính
Bạn chọn Conditional Formatting > Highlight Cells Rules. Các tùy chọn định dạng phổ biến bao gồm:
Greater than: Giá trị ô > 1 giá trị cụ thểLess than: Giá trị ô Between: Giá trị ô nằm giữa 1 khoảng giá trịEqual to: Giá trị ô = 1 giá trị cụ thểText that Contain: Văn bản chứa giá trị nào đó dạng textA Date Occurring: Định dạng mốc thời gianDuplicate Values: Giá trị trùng lặpBạn có thể chọn More Rule để tùy chỉnh định dạng có điều kiện.
Ví dụ, dưới đây, Tinhocmos đã thêm định dạng có điều kiện là các ô có giá trị
Định dạng Xếp hạng giá trị các ô
Bạn chọn Conditional Formatting > Top/Bottom Rules. Các tùy chọn định dạng phổ biến bao gồm:
Top 10 items: Top 10 ô có giá trị cao nhấtTop 10% items: Top 10% ô có giá trị cao nhấtBottom 10 items: Top 10 ô có giá trị thấp nhấtBottom 10% items: Top 10% ô có giá trị thấp nhấtAbove Average: Các ô có giá trị trên giá trị trung bìnhBạn chọn More Rule để có thể tùy chỉnh rule mong muốn.
Ví dụ, Tinhocmos đã cài định dạng có điều kiện là Top 3 ô có giá trị cao nhất sẽ được tô màu xanh. Ta được kết quả như sau:
Định dạng thêm thanh Data Bars
Đây là một định dạng rất thú vị giúp bạn có cái nhìn trực quan hơn. Bảng dữ liệu như được thêm một “biểu đồ phụ” vào trong đó.
Bạn chỉ cần chọn Conditional Formatting > Data Bars rồi chọn màu phù hợp là sẽ thu được kết quả như hình.
Tóm lại về Conditional Formatting trong Excel
Định dạng có điều kiện trong Excel luôn là một trong những tính năng quan trọng bậc nhất và có tính ứng dụng cao trong nhiều nghiệp vụ phân tích, báo cáo kinh doanh. Thành thạo kỹ năng này sẽ giúp nâng tầm báo cáo và cải thiện khả năng phân tích dữ liệu của bạn một cách đáng kể.
Các bước thêm Định dạng có điều kiện trong ExcelB1: Chọn vùng giá trị
B2: Mở hộp thoại New Formatting Rule để thêm định dạng có điều kiện
B3: Cài đặt điều kiện định dạng
B4: Cài đặt Format Cells cho ô tính thỏa mãn điều kiện
B5: Kiểm tra và xác nhận kết quả
Ngoài ra, nếu công việc của bạn gắn liền với Tin học văn phòng nhưng vì quá bận rộn nên bạn không có thời gian tham gia các lớp học trực tiếp ở trung tâm thì bạn có thể tham khảo khóa Luyện thi MOS online, và Excel AZ từ cơ bản đến nâng cao và ứng dụng của Trung tâm tin học văn phòng MOS nhé!
Thông tin liên hệ
Trung tâm tin học văn phòng MOS – Viện Đào tạo và Tư vấn Doanh nghiệp – Đại học Ngoại Thương
Ở bài viết này emcanbaove.edu.vn 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.
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 emcanbaove.edu.vn 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ọnHighlight 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à emcanbaove.edu.vn đư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ở.
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 emcanbaove.edu.vn 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ọnConditional 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.
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ơn60 ° 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àoConditional Formatting > Manage Rules…
Bước 2: Trong hộpConditional 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: Tổng hợp các hàm toán học trong excel thường được sử dụng, hàm excel: nhóm hàm toán học trong excel
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ậpthêm vớibộ bài tập Excel có đáp áncủa emcanbaove.edu.vn để nằm lòngtin học văn phòng nhé!
emcanbaove.edu.vnlà lựa chọn hàng đầu chokế 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.