Data Analyst with Excel: Conditional Formatting

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Conditional Formatting trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Conditional Formatting

Conditional Formatting trong Excel cho phép bạn highlight các ô với một màu sắc nhất định. Màu sắc đó sẽ phụ thuộc vào giá trị của ô đó.

Highlight Cells Rules

Để highlight các ô có giá trị lớn hơn một giá trị nào đó khác, bạn hãy thực thi các bước sau:

1. Chọn phạm vi A1:A10.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Highlight Cells Rules => Great Than

4. Nhập giá trị 80 và chọn kiểu định dạng màu sắc

5. Click OK

Kết quả là Excel sẽ highlight những ô lớn hơn 80

6. Thay đổi giá trị của ô A1 thành 81, bạn sẽ thấy ô tự động thay đổi ngay lập tức

Ở bước ba, bạn hãy khám phá thêm các điều kiện khác để định dạng màu sắc nhé. Ví dụ như là: Less than, Between, Equal to, text that contains,...

Clear Rules

Để xóa các quy tắc của conditional formatting, bạn hãy thực thi các bước sau:

1. Chọn phạm vi A1:A10.

2.  Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Clear Rules => Clear Rules from Selected Cells.

Top/Bottom

Để highlight những ô chứa các giá trị trên trung bình, hãy thực thi những bước sau.

1. Click Top/Bottom Rules => Above Average.

2. Chọn một kiểu định dạng màu sắc

3. Click OK

Excel sẽ tự tính toán ra giá trị trung bình (42,5) và highlight các ô chứa giá trị trên mức này.

Ở bước 1, bạn cũng có thể khám phá thêm các điều kiện khác như: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%,...

Conditional Formatting với các công thức

Nâng trình Excel của bạn lên level tiếp theo bằng cách sử dụng công thức để xác định ô nào nên format. Các công thức áp dụng conditional formatting phải trả về TRUE hoặc FALSE.

1. Chọn phạm vi A1:E5

2.  Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click New Rule

4. Chọn 'Use a formula to determine which cells to format'

5. Nhập công thức =ISODD(A1)

6. Chọn kiểu formatting và click OK

Kết quả là Excel sẽ highlights tất cả các số lẻ

Một ví dụ tiếp theo

7. Chọn phạm vi A2:D7

8. Lặp lại từ bước 2-4 ở trên

9. Nhập công thức =$C2="USA"

10. Chọn một kiểu formating và click OK

Kết quả là Excel highlight tất cả các hàng USA

Manage Rules

Để xem tất cả các quy tắc conditional formatting trong một workbook, bạn hãy sử dụng Conditional Formatting Rules Manager.

1. Chọn ô A1.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Manage Rules

Hộp thoại Conditional Formatting Rules Manager sẽ xuất hiện

4. Từ danh sách thả xuống, thay đổi Current Selection thành This Worksheet để xem tất cả conditional formatting rules trong worksheet này.

Bạn có thể click New Rule, Edit Rule và Delete Rule để tạo, chỉnh sửa và xóa các rules.

Data Bars

Data bars trong Excel giúp trực quan hóa các giá trị một cách dễ dàng hơn. Một bar dài hơn thể hiện được một giá trị cao hơn.

Để thêm các Data Bars, bạn hãy thực thi các bước sau.

1. Chọn một phạm vi các giá trị

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Data Bars và chọn kiểu mà bạn muốn

Kết quả được thể hiện như sau:

Theo mặc định, ô có giá trị nhỏ nhất (là giá trị 0 nếu không có giá trị âm) sẽ không có data bar. Ô có giá trị tối đa (là 95 trong ví dụ trên) có data bars lấp đầy toàn bộ ô. Tất cả các ô khác được lấp đầy theo tỷ lệ tương ứng.

4. Thay đổi các giá trị

Khi bạn thay đổi các giá trị trong ô thì data bars sẽ tự động thay đổi theo. Đọc tiếp để biết thêm cách tùy chỉnh những data bars này.

5. Chọn phạm vi A1:A10

6. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

7. Click Edit Rule

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm về data bars (Show Bar Only, Minimum and Maximum, Bar Appearance, Negative Value và Axis, Bar Direction,...)

8. Chọn Number từ trường Minimum nhập vào giá trị 100. Chọn Number từ trường Maximum và nhập giá trị 150

9. Click OK hai lần.

Kết quả

Những ô chứa giá trị 100 (nếu có) sẽ không có data bar và những ô chứa giá trị 150 (nếu có) sẽ có data bar lấp đầy toàn bộ ô.

Color Scales

Những giá trị trong các ô sẽ trực quan hóa hơn nếu bạn dùng Color Scales. Sắc thái của màu sắc sẽ đại diện cho giá trị trong một ô.

Để thêm Color Scales, bạn hãy thực thi các bước sau.

1. Hãy chọn một phạm vi các giá trị.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Color Scales và chọn một kiểu mà bạn muốn.

kết quả sẽ như thế này.

Theo mặc định, Excel thực hiện phép tính trung vị cho các giá trị. Ô có giá trị nhỏ nhất (trong vd trên là 9) có màu đỏ. Ô có giá trị trung vị (36) có màu vàng. Ô có giá trị lớn nhất (80) có màu xanh lá cây. Tất cả các ô còn lại có màu sắc theo tỷ lệ tương ứng.

Đọc tiếp để biết cách tùy chỉnh thêm cho tính năng Color Scale này nhé!

4. Chọn phạm vi A1:A7.

5. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

6. Click Edit rule.

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm các tính năng color scale (Format Style, Minium, Midpoint và Maximum, Color,...).

7. Chọn 2-Color Scale từ trường Format Style và chọn màu trắng và xanh.

8. Click OK hai lần.

Kết quả sẽ như sau.

Icon Sets

Icon Sets trong Excel cũng là một tính năng giúp chúng ta trực quan hóa giá trị trong ô. Mỗi icon đại diện một phạm vi các giá trị.

Để thêm một icon set, bạn hãy thực thi các bước sau.

1. Chọn một phạm vi.

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Icon Sets và chọn kiểu mà bạn muốn.

Kết quả như thế này:

Theo mặc định, đối với 3 icons, Excel thực hiện tính toán phân vị thứ 67 và phân vị thứ 33.

Phân vị thứ 67 = min + 0.67 * (max-min) = 2 + 0.67 * (95-2) = 64.31

Phân vị thứ 33 = min + 0.33 * (max-min) = 2 + 0.33 * (95-2) = 32.69

Mũi tên màu xanh lá sẽ thể hiện các giá trị bằng hoặc lớn hơn 64.31. Mũi tên màu vàng sẽ thể hiện các giá trị nhỏ hơn 64.31 hoặc bằng hoặc lớn hơn 32.69. Mũi tên màu đỏ sẽ thể hiện các giá trị nhỏ hơn 32.69

4. Thay đổi các giá trị.

Khi bạn thay đổi các giá trị thì Excel sẽ tự động cập nhập lại các icon.

Một số tùy chỉnh khác cho tính năng icon set này.

5. Chọn phạm vị A1:A10

6. Ở tab Home, trong group Styles, click Conditional Formatting => Manage Rules.

7. Click Edit Rule

Excel sẽ xuất hiện hộp thoại Edit Formatting Rule. Tại đây bạn có thể tùy chỉnh thêm các tính năng khác của Icon set (Icon Style, Reverse Icon Order, Show Icon Only, Icon, Value, Type,...)

8. Chọn 3 icon từ trường Icon Style. Chọn No Cell Icon từ danh sách thả xuống của icon thứ 2. Ở trường Types thay đổi giá trị thành Number. Ở trường Value nhập giá trị 100 và 0. Chọn biểu tượng lớn hơn (>) bên cạnh giá trị 0.

Bạn có thể xem thêm hình bến dưới để rõ cách làm

9. Click OK hai lần.

Kết quả sẽ hiện thị như sau.

Shade Alternate Rows

Để phủ màu luân phiên các hàng trong Excel, bạn hãy sử dụng conditional formatting hoặc áp dụng một table style. Việc đổ màu cho từng hàng khác nhau sẽ giúp cho việc đọc dữ liệu của bạn dễ dàng hơn.

1. Chọn một phạm vi.

Để phủ màu cho toàn bộ worksheet, bạn hãy click vào nút Select All (mũi tên màu cam trên hình)

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click New Rule.

4. Chọn "Use a formula to determine which cells to format"

5. Nhập công thức sau =MOD(ROW(),2)=0

6. Chọn một kiểu định dạng rồi click OK.

Kết quả hiển thị như sau.

Hàm MOD trả về số dư của một phép chia. Hàm ROW trả về số hàng của một ô.

Ví dụ: Ở hàng thứ 7, MOD (7,2) = 1 vì 7 chia 2 bằng 3 và có số dư là 1. Ở hàng thứ 8, MOD (8,2) = 0 vì 8 chia 2 bằng 4 và có số dư là 0.

Như vậy tất cả các ô trong từng hàng ở ví dụ trên nếu trả về là 0 sẽ được phủ màu lên.

Nếu bạn không thích sử dụng công thức thì có thể áp dụng table style để phủ màu luân phiên các hàng trong Excel.

7. Chọn một ô bất kỳ bên trong phạm vi các giá trị

8. Ở tab Home, trong group Styles, click Format as Table.

9. Chọn một kiểu table với các hàng được luân phiên tô màu.

10. Click OK

Kết quả.

Để thay đổi màu từ hàng sang cột, bạn hãy thực thi các bước sau.

11. Đầu tiên, hãy chọn một ô bên trong table, tiếp theo, chọn tab Table Design. Trong group Table Styles Options, hãy uncheck Banded Rows và check Banded Columns.

Kết quả sẽ hiển thị như sau.

Để chuyển table này thành một phạm vi các ô bình thường, hãy thực thi các bước sau.

12. Đâu tiên, hãy chọn một ô bên trong table. Tiếp theo, ở tab Table Design, trong group Tools, click Convert to Range.

Kết quả hiển thị như sau.

So sánh hai danh sách

Phần này sẽ hướng dẫn bạn cách so sánh hai danh sách bằng cách sử dụng conditional formatting. Ví dụ, bạn có 2 danh sách các team NFL

Để hightlight các team có trong danh sách đầu tiên mà không có trong danh sách thứ hai, hãy thực thi các bước sau.

1. Đầu tiên, hãy chọn phạm vi A1:A18 và đặt tên cho nó là firstList, chọn phạm vi B1:B20 và đặt tên cho nó là secondList.

2. Tiếp theo, hãy chọn phạm vi A1:A18

3. Ở tab Home, trong group Styles, click Conditional Formatting.

4. Click New Rule

5. Chọn 'Use a formula to determine which cells to format'.

6. Nhập công thức =COUNTIF(secondList,A1)=0

7. Chọn một formatting style và click OK

Kết quả là Miami Dolphins và Tennessee Titans không có trong danh sách thứ hai.

COUNTIF(secondList,A1) đếm số teams trong secondList mà bằng với team trong ô A1.

Nếu COUNTIF(secondList,A1) = 0 thì team trong ô A1 không có trong secondList. Với kết quả này, Excel sẽ phủ màu xanh dương cho ô đó.

Luôn luôn lấy giá trị của ô ở trên cùng phía bên trên của phạm vi đã chọn (A1:A18) cho công thức. Excel sẽ tự động copy công thức đó cho các ô khác. Do đó, ở ô A2 sẽ có công thức =COUNTIF(secondList,A2)=0, ô A3 =COUNTIF(secondList,A3)=0,...

8. Để highlight các team có trong secondList mà không chứa trong firstList, hãy chọn phạm vị B1:B20, tạo một new rule bằng công thức =COUNTIF(firstList,B1)=0 rồi cài đặt định dạng fill màu cam.

Kết quả là các team :Denver Broncos, Arizona Cardinals, Minnesota Vikings và Pittsburgh Steelers không có trong danh sách đầu tiên.

Conflicting Rules

Thỉnh thoảng một vài conditional formatting rules trong Excel bị xung đột với nhau. Phần này sẽ minh họa cho bạn hai kết quả khác nhau.

1. Giá trị 95 thì lớn hơn 80 những cũng là giá trị cao nhất (top 1). Các định dạng (yellow fill vs green fill và yellow text color vs green text color) bị xung đột. Bạn hãy nhìn vào hình bên dưới, quy tắc nào được đặt cao hơn sẽ được thực thi. Do đó, giá trị 95 được tô màu vàng.

Kết quả.

2. Nếu di chuyển quy tắc thứ 2 lên trên cùng. Giá trị 95 sẽ được tô màu xanh lá cây.

Kết quả.

Heat Map

Để tạo heat map trong Excel, bạn có thể sử dụng conditional formatting. Heat map là một biểu diễn đồ họa của dữ liệu trong đó các giá trị riêng lẻ được biểu diễn dưới dạng màu sắc.

Để tạo một heat map, bạn hãy thực thi các bước sau.

1. Chọn phạm vi B3:M11

2. Ở tab Home, trong group Styles, click Conditional Formatting.

3. Click Color Scale và chọn kiểu mà bạn muốn.

Kết quả.

4. Chọn phạm vi B3:M11

5. Click chuột phải và sau đó chọn click Format Cells (hoặc nhấn tổ hợp phím CTRL + 1)

6. Chọn Custom category

7. Nhập ;;;

8. Click OK

Kết quả

Tài liệu của series

Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!

Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab!

Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp thì bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây.

Bài viết tiếp theo cùng series:

• Data Analysis with Excel: Chart