Hướng dẫn sử dụng Conditional Formatting

Khi bạn muốn làm nổi bật những ô có dữ liệu theo điều trong Excel, tuy nhiên bạn rất ngại việc phải làm thủ công vì mất thời gian và dễ gây sai sót. Để giải quyết vấn đề này, thì hôm nay, mình sẽ giới thiệu đến các bạn cách hướng dẫn sử dụng conditional formatting(định dạng có điều kiện) trong excel.

Với những tính năng đơn giản, nhưng vô cùng hữu ích, conditional formatting giúp người sử dụng excel có thể phân biệt các dữ liệu quan trọng, đặc biệt là với các bảng dữ liệu lớn, càng làm cho chúng ta thấy được việc ứng dụng conditional formatting là điều rất cần thiết.

Hướng dẫn sử dụng Conditional Formatting trong Excel

I.Giới thiệu Conditional Formatting.

Như đã nói ở trên, Conditional formatting trong Excel cho phép người dùng có thể thực hiện sự nổi bật cho các ô, dòng… dữ liệu chứa thông tin quan trọng, do vậy mà việc sử dụng đúng định dạng sẽ giúp bạn làm việc hiệu quả hơn, tiết kiệm thời gian thay vì cách làm thủ công. Điều đặc biệt, là khi các bạn định dạng theo điều kiện, thì nó không làm mất đi định dạng gốc, nó chỉ làm nổi bật ô dữ liệu mà bạn muốn, sau khi xóa định dạng Conditional formatting thì định dạng của ô sẽ được trả lại như lúc ban đầu.

Có 2 kiểu định dạng, đó là định dạng theo điều kiện có sẵn và định dạng theo điều kiện riêng. Hôm nay, chúng ta sẽ cùng nhau tìm hiểu cách định dạng theo điều kiện có sẵn trước nhé.

II.Định dạng theo điều kiện có sẵn.

Để mở bảng điều khiển của Định dạng có điều kiện bạn chọn như sau:

Chọn Home -> Conditional formatting ->với điều kiện có sẵn thì bạn sẽ có những tùy chọn như bên dưới.

• Highlight Cells Rules: Nổi bật các ô tính theo giá trị.
• Top/ Bottom, Rules: Xác định ô theo xếp hạng.
• Data Bars: Hiển thị ô theo mức độ lớn nhỏ của giá trị
• Color Scales: Tô màu giá trị lớn nhỏ hay là tạo ra bảng đồ nhiệt độ.
• Icon Sets: Thêm biểu tượng vào ô theo giá trị.

III.Tìm hiểu các định dạng có sẵn qua các ví dụ cụ thể.

1. Highlight Cells Rules:

• Greater than: Lớn hơn giá trị
• Less than: Nhỏ hơn giá trị cho sẵn
• Between: Nằm trong khoảng 2 giá trị
• Equal to: Bằng đúng giá trị đã cho
• Text that Contains…:Nội dung ô tính có chứa giá trị bằng chữ.
• A Date Occurring…:Giá trị có chứa ngày định sẵn
• Duplicate Values…:Xác định những giá trị trùng lặp

Ví dụ 1: Cho bảng dữ liệu như bên dưới, hãy làm nổi bật các ô có chứa số tiền ăn lớn hơn 60,000đ.

Bước 1: Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Highlight Cells Rules->Greater than…

Bước 2:  Sau đó xuất hiện hộp thoại Greater than, tại ô Format Cells that are Greater than chúng ta đánh số 60000, sau đó nhấn Ok.

Chúng ta, có thể thấy cột tiền ăn có số tiền lớn hơn 60,000 đã được tô màu.

Ví dụ 2: Cho bảng dữ liệu như bên dưới, hãy làm nổi bật các ô có chứa chữ Việt Nam trong cột Quốc tịch.

Bước 1: Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Highlight Cells Rules->Text that Contains…

Bước 2:  Sau đó xuất hiện hộp thoại Text that Contains, tại ô Format Cells that contain the text chúng ta gõ chữ Việt Nam, sau đó nhấn Ok.

Chúng ta, có thể thấy những ô có chứa chữ Việt Nam đã được tô màu.

Ví dụ 3: Cho bảng dữ liệu như bên dưới, hãy làm nổi bật các ô có chứa Đơn giá từ 120 đến 300.

Bước 1: Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Highlight Cells Rules->Between…

Bước 2:  Sau đó xuất hiện hộp thoại Between, tại ô Format Cells that are Between chúng ta đánh số 120 trước, tiếp theo là 300, sau đó nhấn Ok.

Qua các ví dụ trên, các bạn thấy cách thực hiện định dạng không có phức tạp tí nào đúng không, các bạn lưu ý thêm một điều, sau khi làm nổi bật các dữ liệu mà bạn muốn, các bạn có thể chọn theo màu, in đậm, kẻ khung theo ý muốn của mình bằng cách như sau.

Kết quả sau khi hoàn thành các bước trên.

2.Top / Bottom Rules – Quy tắc xác định ô tính theo xếp hạng:

Các mục này, dùng để xác định những giá trị theo thứ hạng dựa trên vùng ô mà bạn chọn xét điều kiện, có các tùy chọn như sau:

• 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, nhỏ 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 bộ vùng được chọn.

Ví dụ 1: Cho bảng dữ liệu như bên dưới, hãy lọc ra Top 3 học sinh có số điểm cao nhất.

Bước 1: Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Top/Bottom Rules->Top 10 Items…

Bước 2:  Sau đó xuất hiện hộp thoại Top 10 Items, tại ô Format Cells that rank in the Top chúng ta đánh số 3, sau đó nhấn Ok.

3.Data Bars

Data Bars được sử dụng để thể hiện dữ liệu, bằng đồ thị bên trong một ô. Nếu giá trị của ô trong bảng tính cao nhất hay nhiều nhất thì thanh biểu đồ sẽ dài nhất, hoặc ngược lại nếu giá trị nhỏ nhất thì thanh biểu đồ sẽ hiển thị thấp nhất. Với cách thể hiện biểu đồ đơn giản như vậy, sẽ làm cho người xem dễ dàng nhận thấy được sự khác biệt giữa các giá trị với nhau trong cùng 1 bảng tính.

Ví dụ:

Cách thực hiện :Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Data Bars->Gradient Fill-> chọn màu mà bạn muốn.

4.Color Scales

Color Scales dùng để tạo ra bản đồ mà dữ liệu trong bảng tính được hiển thị bằng màu sắc, các bạn có thể hiển thị giá trị trong ô hoặc không nếu muốn.

Ví dụ: Tô màu cho bảng dữ liệu sau.

Cách thực hiện: Bôi đen dữ liệu cần làm nổi bật, vào Tab Home->Conditional formatting ->Color Scales-> chọn màu mà bạn muốn.

Sau khi chọn xong ta được hình như sau, để chọn  thêm các định dạng khác nhau, các bạn có thể vào More Rules.

5.Icon Sets

Thay vì định dạng bằng màu sắc, chúng ta có thể định dạng các biểu tượng Icon đầy thú vị.

Ví dụ:chúng ta có bảng dữ liệu sau, dùng hàm Counta để đếm các dòng có dữ liệu đầy đủ, nếu muốn đếm từ cột số lượng đến thành tiền, ta làm công thức như sau, tại ô I3=COUNTA(D3:H3), sau đó kéo công thức xuống các ô còn lại.

Sau khi, thực hiện xong công thức trên, các bạn vào Tab Home->Conditional formatting ->Icon Sets->chọn More Rules..

Sau đó, xuất hiện hộp thoại New Formatting rule, các bạn thực hiện chọn như các bước trong hình, tại mục value nếu giá trị đầy đủ của bảng công thức mà chúng ta đã làm ra trước đó là 5, thì các bạn nhập số 5, tương ứng với Icon là màu xanh->sau đó bấm ok.

Kết quả như hình bên dưới.

Như vậy, mình đã hướng dẫn xong các kiểu định dạng có sẵn trong excel hay chúng ta thường gọi là Conditional Formatting. Trong bài viết sau, mình sẽ hướng dẫn các bạn cách sử dụng định dạng theo điều kiện riêng. Chúc các bạn thực hiện thành công.

Tham khảo thêm các bài viết.

–Các thủ thuật trong Excel-phần 2

–Hướng dẫn cách copy dán giá trị trong Excel

Các thủ thuật Excel cho dân văn phòng-phần 1