Bạn muốn làm nổi bật một số ô tính có giá trị quan trọng nhưng ngại việc định dạng thủ công sẽ dễ gây nhầm lẫn và thiếu sót?Chức năng Conditional Formatting (Định dạng có điều kiện) trong Excel sẽ giúp bạn giải quyết vần đề này. Với những tính năng đơn giản mà hiệu quả, Conditional Formatting trong thực tế rất cần thiết, giúp người sử dụng dễ dàng phân biệt các ô tính quan trọng, nhất là khi phải làm việc với một lượng dữ liệu lớn và cần kiểm tra lại hoặc nhấn mạnh các giá trị quan trọng trong bảng tính.

Bạn đang xem: Cách sử dụng conditional formatting trong excel 2010

Bạn đang xem: Cách dùng conditional formatting trong excel 2010

Ngoài ra, bạn không cần lo lắng định dạng theo điều kiện sẽ làm mất định dạng gốc vì Excel vẫn giữ lại định dạng cũ, chỉ bổ sung quy tắc mới và chép đè định dạng mới lên trên. Khi xóa quy tắc của Conditional Formatting, định dạng cũ của ô tính sẽ được trả lại như ban đầu.Để bắt đầu sử dụng, bạn vào thẻ Home trên thanh Ribbon, nhấn Conditional Formatting trong mục Styles và chọn kiểu điều kiện bạn muốn:Greater Than: Lớn hơn một giá trị xác định.Less Than: Nhỏ hơn giá trị cho trước.Between: Nằm trong khoảng giá trị định sẵn.Equal To: Bằng đúng giá trị xác định.Text That Contains: Nội dung ô tính có chứa phần chữ cho trước.A Date Occurring: Giá trị ô tính chứa một ngày tháng định sẵn.Duplicate Values: Xác định những giá trị trùng lặp.


*

Excel xác định những giá trị theo thứ hạng dựa trên vùng ô tính chọn xét điều kiện và định dạng riêng các ô tính chứa các giá trị này.Top 10 Items: 10 giá trị lớn nhất.Top 10%: 10% số lượng ô tính có giá trị lớn nhất.Bottom 10 Items: 10 giá trị nhỏ nhất.Bottom 10%: 10% số lượng ô tính có giá trị lớn bé nhất.Above Average: Những giá trị lớn hơn giá trị trung bình của toàn vùng chọn.Below Average: Các giá trị nhỏ hơn giá trị trung bình của toàn vùng chọn.

Bạn có thể tùy chỉnh số lượng giá trị hoặc số phần trăm bất kỳ khác 10 với 4 kiểu định dạng đầu tiên.

Với cách định dạng này, các giá trị sẽ được đo lường rõ ràng ngay trong từng ô tính của vùng chọn định dạng điều kiện.Ngoài các thao tác định dạng sẵn có, bạn còn có thể tự tạo quy tắc riêng cho định dạng điều kiện của mình. Bạn chỉ cần thực hiện tương tự như định dạng kiểu có sẵn và chọn More Rules ở từng kiểu hoặc New Rule (bên dưới Icon Sets) để tùy chỉnh riêng.
*

Bạn có thể chọn Clear Rules để xóa theo vùng đang chọn (Selected Cells), theo toàn bộ bảng tính (Entire Sheet), theo bảng này (This Table), theo bảng phân tích dữ liệu (This Pivot
Table).Trong trường hợp cần quản lý các định dạng có điều kiện thì chọn Manage Rules để dễ dàng thay đổi, xóa và cập nhật các định dạng điều kiện mới.
*

Trong hình ví dụ trên, ta có thể thấy việc định dạng Duplicate Values giúp xác định lỗi đánh máy ở một trong hai mã hàng BC35. Sau khi chỉnh xong lỗi này, bạn có thể chọn quy tắc Duplicate Values và nhấn Delete Rule. Ngoài ra, giá tiền cũng thế hiện được mức độ cao thấp nhờ Data Bar.

Trong bài viết này, mua.edu.vn 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


*

Á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:

Nhấp vào Conditional Formatting > Manage Rules…. để đi tới Rules Manager.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.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.


*

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


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:

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.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 $.


Nhấp OK mà không thiết lập bất kỳ định dạng nào.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:

Chọn bất kỳ ô nào áp dụng quy tắc và nhấp vào Conditional Formatting > Manage Rules…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….

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.

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.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.

Để 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.


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

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 docongtuong.edu.vn 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 Excel

Khá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.

Đị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, docongtuong.edu.vn 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ặp

Bạn có thể chọn More Rule để tùy chỉnh định dạng có điều kiện.

Ví dụ, dưới đây, docongtuong.edu.vn đã 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ình

Bạn chọn More Rule để có thể tùy chỉnh rule mong muốn.

Ví dụ, docongtuong.edu.vn đã 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.

Xem thêm: 30+ Lời Chúc Mừng Giáng Sinh Bằng Tiếng Anh Hay Và Ý Nghĩa Nhất

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