Conditional Formatting là một tính năng trong Excel giúp bạn định dạng chuỗi dữ liệu trong các ô, các giá trị về màu sắc, kiểu mẫu nền, khung... Khi áp dụng tính năng này sẽ tạo các Rule định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số. Tuy nhiên khi chúng ta xóa bỏ Conditional Formatting thì định dạng mặc định của các ô sẽ được phục hồi.
Tìm hiểu công thức tô màu trong Excel
Để sử dụng tính năng này, tại tab Home , mục Styles bạn nhấn Conditional Formatting .
Tại Conditional Formatting, bạn sẽ có các lựa chọn sau:
- Highlight Cells Rules: Tô màu các ô có giá trị đặc biệt như: lớn nhất, nhỏ nhất, bằng nhau hoặc giữa một khoảng...
- Top/Bottom Rules: Tô màu các ô có trong top 10, top 10% lớn nhất, nhỏ nhất hay nhóm trung bình...
- Data Bars: Tạo các cột đồ thị tương ứng với giá trị trong ô.
- Color Scales: Tô màu tương ứng giá với giá trị trong ô.
- Icon Sets: Tạo các icon tương ứng với giá trị trong ô.
- New Rules: Cho phép tạo các Rule mới theo yêu cầu của bạn.
- Clear Rules: Xóa các Rule trong các ô đang chọn hoặc cả bảng(Entire Sheet)
- Manage Rules: Mở cửa sổ quản lý Conditional Formatting Rules Manager, cho phép tạo, xóa hoặc chỉnh sửa các Rule.
Trên đây là các lựa chọn trong Conditional Formatting, nhưng trong bài viết nãy sẽ chỉ tập trung vào các lựa chọn tô màu là Highlight Cells Rules, Top/Bottom Rules và New Rules.
1. Higlight Cells Rules
Tại đây bạn có các lựa chọn sau:
- Greater Than...: Tô màu giá trị lớn hơn ...
- Less Than...: Tô màu giá trị nhỏ hơn ...
- Between...: Tô màu giá trị ở khoảng từ ... đến ...
- Equal To...: Tô màu giá trị bằng ...
- Text that Contains...: Tô màu ô có chứa ký tự ...
- A Date Occurring...: Tô màu giá trịđược nhập tại một mốc thời gian nào đó.
- Duplicate Values...: Tô màu các ô có giá trị bằng nhau.
2. Top/Bottom Rules
Tại đây bạn có các lựa chọn sau:
- Top 10 Items...: Tô màu 10 ô có giá trị lớn nhất.
- Top 10 %...: Tô màu 10% các ô có giá trị lớn nhất.
- Bottom 10 Items...: Tô màu 10 ô có giá trị nhỏ nhất.
- Bottom 10 %...: Tô màu 10% các ô có giá trị nhỏ nhất.
- Above Average: Tô màu các ô có giá trị lớn hơn trung bình cộng của dãy.
- Below Average: Tô màu các ô có giá trị nhỏ hơn trung bình cộng của dãy.
3. New Rule
Khi chọn New Rule, cửa sổ New Formatting Rule hiện ra sẽ cho bạn những công thức nhằm tạo định dạng Rule mới như sau:
- Format all cells based on their values: Định dạng toàn bộ các theo kết quả của chúng
- Format only cells that contain: Chỉ định dạng ô có chứa các giá trị mà bạn thiết lập.
- Format only top or bottom ranked values: Chỉ định dạng theo giá trị cao nhất hoặc thấp nhất
- Format only values that are above or below average: Chỉ định dạng các giá trị trên hoặc dưới trung bình.
- Format only unique or duplicate values: Định dạng các giá trị giống nhau hoặc duy nhất.
- Use a formula to determine which cells to format: Định dạng các ô theo công thức của thức của bạn.
Để làm rõ hơn, bạn theo dõi phần hướng dẫn chi tiết dưới đây:
Format all cells based on their values
Tại phần Edit the Rule Description, ta có thể tùy chỉnh như sau:
- Phần Format Style , bạn có 2 lựa chọn tô màu theo 2 màu hoặc 3 màu.
- Phần Type bạn có thể lựa chọn theo dạng:
+ Định dạng theo giá trị nhỏ nhất và lớn nhất: Chọn Lowest Value và Highest Value.
+ Định dạng theo số, ngày tháng hoặc giá trị thời gian: Chọn Number và nhập giá trị của bạn.
+ Định dạng theo tỷ lện phần trăm: Chọn giá trị từ 0 đến 100
+ Định dạng bằng công thức: công thức bạn chọn bắt buộc phải trả về một kết quả rõ ràng như số, ngày tháng hoặc giá trị thời gian.
+ Định dạng nhóm phần trăm: Giá trị hợp lý là từ 10 đến 90. Các giá trị cùng nhóm phần trăm sẽ có mầu giống nhau.
- Phần Color bạn chọn màu cho giá trị nhỏ nhất và lớn nhất.
Sau khi chọn xong bạn nhấn OK để áp dụng công thức tô màu mới cho dữ liệu của bạn như ví dụ của mình bên dưới.
Format only cells that contain
- Cell Value: Tô màu theo giá trị của ô.
+ between: tô màu các giá trị giữa một khoảng nhất định.
+ not between: tô màu các giá trị ngoài một khoảng nhất định.
+ equal to: tô màu các ô có giá trị bằng một giá trị nhất định.
+ not equal to: tô màu các ô có giá trị khác một giá trị nhất định.
+ greater than: tô màu các ô có giá trị lớn hơn một giá trị nhất định.
+ less than: tô màu các ô có giá trị nhỏ hơn một giá trị nhất định.
+ greater than or equal to: tô màu các ô có giá trị lớn hơn hoặc bằng một giá trị nhất định.
+ less than or equal to: tô màu các ô có giá trị nhỏ hơn hoặc bằng một giá trị nhất định.
- Specific Text: Tô màu theo ký tự đặc biệt.
+ containing: tô màu ô chứa ký tự đặc biệt.
+ not containing: tô màu ô không chứa ký tự đặc biệt.
+ beginning with: tô màu ô bắt đầu với đặc biệt.
+ ending with: tô màu ô kết thúc với đặc biệt.
- Dates Occurring: Tô màu theo thời gian giá trị xuất hiện trong trang tính.
- Blanks: Tô màu ô trống.
- No Blanks: Tô màu ô không trống.
- Errors: Tô màu ô bị lỗi.
- No Errors: Tô màu ô không bị lỗi.
Format only top or bottom ranked values
- Top: Tô màu số giá trị đứng đầu.
- Bottom: Tô màu số giá trị đứng dưới.
Tại ô trống bên cạnh bạn điền số giá trị mà bạn muốn tô màu.
% of the selected range: tô màu số phần trăm trong khoảng mà bạn chọn tô màu.
Format only values that are above or below average
Tô màu các ô theo giá trị trung bình của khoảng được chọn:
- above: tô màu ô có giá trị trên trung bình.
- below: tô màu ô có giá trị dưới trung bình.
- equal or above: tô màu ô có giá trị bằng hoặc trên trung bình.
- equal or below: tô màu ô có giá trị bằng hoặc dưới trung bình.
- 1st dev above: tô màu ô có giá trị trên trung bình lớn nhất.
- 1st dev below: tô màu ô có giá trị dưới trung bình nhỏ nhất.
Format only unique or duplicate values:
Tô màu các ô theo giá trị:
- duplicate: trùng nhau trong khoảng được chọn.
- unique: không trùng trong khoảng được chọn.
Use a formula to determine which cells to format:
Sử dụng một công thức để định dạng ô mà bạn muốn tô màu. Ví dụ như công thức tô màu xen kẽ các dòng trong Excel, việc tô màu xen kẽ nhau sẽ giúp dữ liệu được hiển thị theo đúng ý mà bài tập hoặc công việc được giao.
Ngoài định dạng Format all cells based on their values, để chọn màu sắc cho các định dạng Rule còn lại, bạn làm như sau:
Bước 1: Tại Preview bạn mở Format .
Bước 2: Mở tab Fill . Tại đây chọn màu sắc mà bạn muốn tô cho ô, rồi nhấn OK .
Lựa chọn định dạng và màu sắc cho Rule xong, bạn nhấn OK để áp dụng định dạng. Như trong hình dưới mình chọn tô màu 3 ô có giá trị lớn nhất trên trung bình.
Mình được kết quả như hình dưới.
Chỉnh sửa thuộc tính Rule đã áp dụng
Để thay đổi Rule đã áp dụng, bạn thực hiện như sau:
Bước 1: Mở Conditional Formatting , chọn Manage Rules .
Bước 2: Tại cửa sổ Conditional Formatting Rules Manager, chọn Rule mà bạn muốn sửa, rồi nhấn vào Edit Rule .
Bước 3: Trong cửa sổ Edit Formatting Rule, bạn tùy chỉnh lại định dạng tô màu theo nhu cầu rồi nhấn OK để lưu thay đổi.
Bước 4: Nhấn OK để áp dụng Rule đã thay đổi.
Và đây bảng tính sau khi thay đổi Rule.
Xóa Rule đã tạo trong trang tính
Để xóa Rule đã tạo, bạn mở Conditional Formatting , chọn Clear Rules , sẽ thấy 2 lựa chọn sau:
- Clear Rules from Selected Cells : Xóa Rule tại các ô bạn chọn.
- Clear Rules from Entire Sheet : Xóa toàn bộ Rule trong trang tính.
Chọn lựa chọn phù hợp với bạn, Rule sẽ được xóa ngay lập tức tùy theo lựa chọn của bạn. Như trong hình dưới mình lựa chọn xóa toàn Rule trong trang tính.
Trên đây là bài viết công thức tô màu trong Excel của Taimienphi.vn. Mong rằng sau bài viết này, bạn có thể dễ dàng định dạng Rule nhằm tạo ra các công thức tô màu cần thiết cho công việc của bạn.