Định dạng có điều kiện với công thức trong Excel

Định dạng có điều kiện hiện là một phần không thể thiếu của trải nghiệm Microsoft Excel. Điều này giúp chúng tôi xác định các giá trị cụ thể trong một nhóm dữ liệu ngẫu nhiên hoặc lớn. Chúng tôi không chỉ có thể xác định các giá trị nhất định mà còn có thể xác định những giá trị nào thuộc về một phạm vi nhất định hoặc không thuộc phạm vi nhất định trong đó, giá trị chuỗi, giá trị số, lỗi, v.v. với định dạng có điều kiện. Như tên cho thấy, Excel định dạng một ô dựa trên điều kiện có đúng hay không. Trong hướng dẫn này, chúng ta sẽ thảo luận về định dạng có điều kiện với công thức trong Excel.

Video Hướng dẫn Sử dụng Định dạng có Điều kiện với Công thức trong Excel

Trong video bài giảng này:

• Bạn sẽ học cách định dạng các ô bằng cách sử dụng định dạng có điều kiện dựa trên công thức.
• Bạn sẽ tìm hiểu về hàm ISTEXT .
• Và bạn sẽ học lại cách sử dụng Tham chiếu tuyệt đối trong các công thức.

Bạn có thể tải xuống sổ làm việc được sử dụng cho phần trình diễn từ liên kết tải xuống bên dưới.

Cách sử dụng định dạng có điều kiện với công thức trong Excel

Chủ đề chính của việc sử dụng định dạng có điều kiện với một công thức trong Excel khá đơn giản. Chúng ta cần chọn tính năng định dạng có điều kiện. Sau đó, thay vì chọn bất kỳ tùy chọn định trước nào, chúng ta cần chọn một quy tắc mới và sau đó chọn công thức để nhập nó.

Hãy nhớ rằng theo công thức, chúng tôi thường nhập một điều kiện ở đây trong hộp định dạng có điều kiện. Nếu điều kiện là đúng, thì định dạng sẽ được áp dụng và ngược lại.

Dưới đây là minh họa chi tiết về định dạng có điều kiện với công thức trong Excel.

Các bước:

• Trước tiên, hãy chọn phạm vi ô bạn muốn định dạng bằng công thức.
• Bây giờ, hãy chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Sau đó, chọn Quy tắc mới từ menu thả xuống.

• Do đó, Quy tắc định dạng mới hộp bây giờ sẽ bật lên.
• Sau đó, bạn có thể nhập công thức vào trường dưới Định dạng giá trị mà công thức này đúng:

• Bạn cũng có thể chọn kiểu định dạng bằng cách nhấp vào Định dạng
• Khi bạn đã hoàn tất tất cả các bước ở trên và các kiểu định dạng của ô, hãy nhấp vào OK trên hộp này.

Ví dụ:chúng tôi nhập công thức sau được hiển thị trong hình sau khi làm theo tất cả các bước này.

Phạm vi trên bảng tính là B3:B6 . Bây giờ nó sẽ giống như thế này.

Ở đây, chúng tôi đã nhập điều kiện rằng giá trị ô cao hơn 3. Vì vậy, trong lựa chọn này khi các giá trị cao hơn 3 tồn tại, các định dạng sẽ được áp dụng.

21 Ví dụ về Sử dụng Định dạng có Điều kiện với Công thức trong Excel

Bây giờ trong phần này, chúng tôi sẽ trình bày các ví dụ khác nhau về việc sử dụng công thức trong tính năng định dạng có điều kiện trong Excel. Khả năng sử dụng tính năng này và kết hợp từng tính năng này với tính năng khác là rất lớn. Vì vậy, chúng tôi đã cố gắng đề cập đến một số công thức chính mà chúng tôi nghĩ rằng bạn có thể thấy nhiều thông tin. Nếu bạn chỉ cần một số cụ thể, bạn có thể tìm thấy chúng trong bảng trên.

1. Định dạng giá trị văn bản

Trước tiên, hãy xem xét tập dữ liệu này chứa cả giá trị số và chuỗi trong đó.

Với sự trợ giúp của định dạng có điều kiện với công thức, chúng ta có thể dễ dàng chọn ra các giá trị chuỗi trong Excel. Để làm được điều đó, chúng tôi cần sử dụng hàm ISTEXT . Thực hiện theo các bước sau để xem cách chúng tôi có thể định dạng bằng cách sử dụng hàm này ở định dạng có điều kiện trong Excel.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=ISTEXT(B5)

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Bạn sẽ nhận thấy rằng Excel sẽ định dạng tất cả các văn bản trong tập dữ liệu sau khi sử dụng công thức này ở định dạng có điều kiện.

2. Đánh dấu các ô ngang bằng với một ô khác

Bây giờ, hãy xem một ví dụ khác về các giá trị phù hợp.

Có một bộ sưu tập lớn dữ liệu trong tập dữ liệu này. Bây giờ chúng tôi sẽ định dạng những cái chỉ khớp với giá trị ô của E5 .

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=B5=$E$5

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Đây là cách chúng ta có thể đánh dấu các ô bằng với một ô khác bằng cách sử dụng định dạng có điều kiện với sự trợ giúp của công thức trong Excel.

3. Định dạng có điều kiện trong Excel dựa trên một ô khác

Bây giờ chúng ta hãy thay đổi một chút so với bình đẳng. Bây giờ chúng tôi sẽ định dạng tập dữ liệu dựa trên việc nó lớn hơn hay nhỏ hơn giá trị của một ô khác trong phần này. Và chúng tôi sẽ đánh dấu chúng bằng cách sử dụng định dạng có điều kiện bằng phương pháp công thức trong Excel.

Chúng tôi sẽ thực hiện việc này trên tập dữ liệu sau.

Chúng tôi sẽ định dạng dải ô C5:C9 dựa trên giá trị của ô E5 .

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=C5>$E$5

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK . Đây sẽ là tập dữ liệu bây giờ.

• Tương tự, bạn có thể lặp lại quy trình tương tự cho các giá trị thấp hơn và kết thúc bằng tập dữ liệu như thế này.

Đây là cách chúng tôi có thể sử dụng định dạng có điều kiện với công thức dựa trên giá trị của ô khác trong Excel.

4. Định dạng có điều kiện bằng công thức IF trong Excel

Bây giờ chúng ta sẽ xem cách chúng ta có thể sử dụng các công thức có chứa hàm IF làm công thức định dạng có điều kiện trong Excel. Chúng ta không thể sử dụng trực tiếp hàm trong hộp định dạng có điều kiện. Nhưng chúng ta có thể sử dụng hàm để nhận một giá trị mà chúng ta có thể sử dụng để định dạng thêm các ô bằng cách sử dụng định dạng có điều kiện trong Excel.

Trước tiên, hãy lấy một tập dữ liệu cho ví dụ này. Đây là công cụ chúng tôi đang thực hiện nhiệm vụ này.

Các bước:

• Đầu tiên, chọn ô E5 và viết ra công thức sau.

=IF(D5>C5,"Profit","Loss")

• Sau đó nhấn Enter trên bàn phím của bạn.

• Bây giờ, hãy nhấp và kéo biểu tượng chốt điền vào cuối cột để sao chép công thức cho các ô còn lại.

• Tiếp theo, chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=E5="Profit"

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Đây sẽ là tập dữ liệu bây giờ.

Đây là cách chúng tôi có thể sử dụng IF hàm cho công thức định dạng có điều kiện trong Excel.

5. Đánh dấu các ô sử dụng nhiều điều kiện

Bây giờ chúng ta hãy quay lại tập dữ liệu đầu tiên.

Bây giờ chúng ta sẽ đánh dấu tất cả các ô là 5,6 hoặc chứa văn bản “cat”.

Để làm điều đó trong hộp công thức định dạng có điều kiện trong Excel, chúng ta cần hàm OR .

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=OR(B5=5,B5=6,B5="cat")

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Bây giờ hãy xem tập dữ liệu.

Đây là cách chúng tôi có thể sử dụng HOẶC trong công thức định dạng có điều kiện trong Excel cho nhiều điều kiện.

6. Đánh dấu các hàng trùng lặp

Tiếp tục, bây giờ chúng ta sẽ đánh dấu các ô trong toàn bộ hàng nơi toàn bộ hàng khớp với một hàng khác. Nó là một chút khó khăn. Chúng tôi sẽ cần COUNTIF và CONCATENATE các chức năng cho việc này.

Hãy lấy một tập dữ liệu mẫu để minh họa.

Ở đây, chúng ta có thể thấy hàng thứ ba và thứ sáu hoàn toàn trùng khớp. Thực hiện theo các bước sau để xem cách chúng tôi có thể sử dụng các hàm này để đánh dấu các giá trị trùng lặp trong một cột trong Excel.

Các bước:

• Đầu tiên, chọn ô F5 và viết ra công thức sau.

=CONCATENATE(B5,C5,D5,E5)

• Sau đó nhấn Enter .

• Bây giờ, hãy chọn lại ô, nhấp và kéo biểu tượng chốt điền để lấp đầy các ô còn lại bằng công thức cho các tham chiếu của chúng.

• Nó trông hơi lộn xộn, nhưng hãy bỏ qua nó ngay bây giờ. Bây giờ hãy chọn phạm vi B5:B10 .
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=COUNTIF($F$5:$F$10,$F5)>1

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK . Đây sẽ là tập dữ liệu bây giờ.

• Bây giờ, hãy nhấp chuột phải vào tiêu đề cột của F và chọn Ẩn từ menu ngữ cảnh.

Đây sẽ là chế độ xem bảng tính cuối cùng sau đó.

7. Đánh dấu giá trị ô chứa công thức

Bây giờ chúng ta hãy xem tập dữ liệu có chứa các công thức cần hoàn thành.

Chúng tôi sẽ sử dụng định dạng có điều kiện bằng cách sử dụng công thức để đánh dấu các ô này. Vì lý do này, chúng tôi cần sử dụng hàm ISFORMULA .

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=ISFORMULA(B5)

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Đây sẽ là tập dữ liệu bây giờ.

8. Làm nổi bật doanh số bán hàng từ một khu vực cụ thể

Trong ví dụ này, chúng tôi sẽ làm nổi bật doanh số bán hàng từ tập dữ liệu thuộc một khu vực cụ thể. Hãy xem tập dữ liệu sau.

Bây giờ, giả sử chúng ta cần đánh dấu những điểm đến từ Arizona. Để làm điều đó, hãy làm theo các bước sau.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=$D5="Arizona"

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Bây giờ Excel sẽ đánh dấu doanh số bán hàng từ Arizona là kết quả của các bước này.

9. Đánh dấu sự khác biệt của cột

Chúng tôi cũng có thể đánh dấu các hàng có các cột khác với các cột liền kề của chúng. Ví dụ:hãy xem ví dụ sau.

Để đánh dấu chúng bằng cách sử dụng định dạng có điều kiện với một công thức trong Excel, hãy làm theo các bước sau.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=$B5<>$C5

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Đây sẽ là sản phẩm cuối cùng của các bước này.

10. Sử dụng công thức để đánh dấu các giá trị còn thiếu

Trong phần này, chúng tôi sẽ sử dụng một công thức để đánh dấu các giá trị bị thiếu trong định dạng có điều kiện trong Excel. Hãy lấy tập dữ liệu sau làm ví dụ.

Có một số giá trị bị thiếu trong Danh sách 2 đã có trong Danh sách 1. Chúng tôi sẽ đánh dấu chúng. Làm theo các bước sau để xem cách thực hiện.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=COUNTIF($D$5:$D$10,$B5)=0

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Excel sẽ đánh dấu các ô cho công thức định dạng có điều kiện này.

11. Tạo Hộp Tìm kiếm Đơn giản để Đánh dấu các Ô

Bây giờ chúng ta hãy thử một trò chơi thú vị, với sự kết hợp của các biến thể khác nhau. Đây là tập dữ liệu cho điều đó.

Tại đây, chúng tôi sẽ đặt một giá trị vào ô E4 và Excel sẽ đánh dấu chúng trong phạm vi trước đó, tất cả đều có định dạng có điều kiện với phương thức công thức. Chúng tôi sẽ cần sự kết hợp của ISNUMBER và TÌM KIẾM các hàm cho phương pháp này.

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=ISNUMBER(SEARCH($E$4,B5))

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Bây giờ, hãy nhìn vào tập dữ liệu. Các văn bản chứa trò chơi chữ sẽ được đánh dấu.

Nếu chúng tôi thay đổi giá trị trong ô E4 , ví dụ:"sức khỏe", các điểm nổi bật sẽ thay đổi.

12. Làm nổi bật các giá trị thấp hơn mức trung bình

Bây giờ chúng ta hãy truy cập lại một trong các bộ dữ liệu trước đây.

Chúng tôi sẽ sử dụng một ứng dụng cơ bản của định dạng có điều kiện với một công thức ở đây trong Excel- chúng tôi sẽ làm nổi bật các giá trị ở đây nhỏ hơn trung bình trong cột E . Chúng tôi cần chức năng AVERAGE cho mục đích này.

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Tiếp theo, chọn Quy tắc mới từ menu thả xuống.

• Trong Quy tắc định dạng , trước tiên, hãy chọn hộp Sử dụng công thức để xác định ô cần định dạng trong Chọn loại quy tắc .
• Sau đó, chèn công thức sau vào Định dạng giá trị mà công thức này đúng .

=E5<AVERAGE($E$5:$E$10)

• Tiếp theo, chọn loại định dạng ưa thích của bạn.

• Cuối cùng, nhấp vào OK .

Điều này sẽ làm nổi bật các giá trị nhỏ hơn trung bình trong tập dữ liệu.

13. Làm nổi bật các giá trị lớn hơn mức trung bình

Tương tự, với phần trước, chúng ta sẽ đánh dấu các giá trị lớn hơn trung bình trong phần này bằng cách sử dụng phương pháp công thức của định dạng có điều kiện trong Excel. Chúng tôi sẽ phải sử dụng chức năng AVERAGE cho cả nhiệm vụ này. Tập dữ liệu sẽ giống nhau.

Làm theo các bước sau để xem công thức và cách bạn có thể sử dụng nó.

Các bước:

• Trước tiên, hãy chọn tất cả các ô trong tập dữ liệu không bao gồm tiêu đề.
• Sau đó, chuyển đến Trang chủ trên ruy-băng của bạn.
• Sau đó, chọn Định dạng có điều kiện từ Kiểu phần nhóm.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=E5>AVERAGE($E$5:$E$10)

• Next, select your preferred format type.

• Finally, click on OK .

Thus Excel will highlight all the values that are higher than average in column E .

14. Find Nearest Value Including Exact Match

Now let’s take another unique example that is practical in data cleaning and analysis. We are going to find the nearest value to a number in a random set of data.

This is the dataset of this example.

As you can see from the figure, we are going to find the value that is closest to the one in cell C13 . If there is the same value in the dataset, it will highlight that cell instead of the closest value. For this purpose, we will utilize the MIN , ABS , and OR functions.

Follow these steps to see how you can do that.

Các bước:

• First, we need to perform some calculations. We need to find the smallest difference from this value in that set of data. For that select cell C14 and write down the following formula.

=MIN(ABS(B5:D11-(C13)))

• Then press Enter .

• Now select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)

• Next, select your preferred format type.

• Finally, click on OK .

This will highlight the value that is closest to the value of 1, with conditional formatting formula in Excel.

15. Find Top 3 Values

Let’s go back to the random set of data.

This time we are going to find the top 3 values here using conditional formatting with a formula in Excel. The formula consists of the LARGE function .

Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.

Các bước:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=B5>=LARGE($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK .

As you can see Excel will thus mark the top 3 values from the range using conditional formatting formula.

16. Find Bottom 3 Values

Again, we will use the same dataset as before. But this time we will use that to highlight the bottom 3 values from the dataset. Just like before we are gonna need the SMALL function for the formula. To see how we can do that, follow these steps.

Các bước:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=B5<=SMALL($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK .

This time, Excel will mark the bottom 3 values because of the conditional formatting formula.

17. Show Temperature with Color Scale

In this next example, let’s try another interesting usage of formula and conditional formatting in Excel. Here is the dataset for that.

We have taken two columns because we will use a formula to change the color based on the input in cell E5 . Also, there is a blank row at the start of the dataset. We will need that to utilize the formula we are going to use. The formula consists of IF and AND functions. To see how you can change the color scheme of these temperatures based on the current temperature, follow these steps.

Các bước:

• First, select cell C6 and write down the following formula in it.

=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))

• Then press Enter .

• Now select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.

• Next, go to Conditional Formatting from the Styles group of the Home
• Then hover your mouse over the Color Scales .
• After that, select your preferred color scale.

• The temperature scale for conditional formatting based on the formula for current temperature will now be complete.

• If we change the value of the current temperature in cell E5 , the temperature scale will change accordingly.

This is how we can show the temperature with color scale utilizing formula and conditional formatting in Excel.

18. Highlight Alternate Rows with Conditional Formatting

Now let’s go back to one of the previous datasets of random numbers.

With this dataset, we will highlight alternate rows with a color using the conditional formatting formula in Excel. For the formula, we will need the INT and MOD functions.

The steps to do that are below.

Các bước:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=INT(MOD(ROW(),2))

• Next, select your preferred format type.

• Finally, click on OK .

This will highlight the alternate rows using the conditional formatting formula in Excel.

19. Highlight Cells with Error

Next up, we will be using the conditional formatting formula in Excel to highlight cells with errors. This is particularly helpful with a large number of data in a sheet and where finding errors manually is tiresome. For demonstration, let’s take a look at the following dataset.

There contains two errors in the dataset. We are going to use the ISERROR function in the conditional formatting formula to detect them. The formula used below will help us identify them using conditional formatting in Excel.

Các bước:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=ISERROR(B5)

• Next, select your preferred format type.

• Finally, click on OK .

Excel will thus highlight all the errors as a result of using the conditional formatting formula.

20. Create Checklist with Conditional Formatting

Now let’s try another fun application of conditional formatting with a formula in Excel. This time we will make a checklist beside a set of data. With the options checked, the original data will change its format. We will use the following dataset.

This is a standard to-do list. Once we check a checkbox in the status column, the task beside it will change the format, let’s say a strikethrough. The basic idea is to use the boolean value of the checkbox to format in column B . Also, we will need the Developer tab to insert checkboxes. If you don’t have one in your ribbon, click here to display the Developer tab on your ribbon .

Follow these steps to see how we can use the Excel conditional formatting formula to create a checklist in detail.

Các bước:

• First, go to the Developer trên ruy-băng của bạn.
• Then select Insert from the Controls group section.

• After that, select the Check box (Form Control) từ menu thả xuống.
• Then place the box in its appropriate place.

• Next, right-click on the box and select Format Control từ menu ngữ cảnh.

• Now go to the Control tab on the box and select cell C5 as its linked cell.

• Sau đó nhấp vào OK .
• Now let’s remove the alt text and place it in the middle of the cell to make it look good.

• Now repeat the process for all of the tasks.

• Once checked, there will be a TRUE/FALSE value on the cells depending on it.

• So let’s use a white font color to make them invisible.

• Now select the task range you want to format.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=C5=TRUE

• Next, select your preferred format type.

• Finally, click on OK .

Now once we check a check box beside a task, it will format the task beside it. We did all of that using the help of the conditional formatting formula in Excel.

21. Highlight Weekends in a Week

Let’s try another example where we will use a formula to highlight weekends in calender week in Excel. We will use the OR and WEEKDAY functions to create this formula. As for the dataset, we will use the following.

Now follow these steps to see how we can highlight weekends in Excel using conditional formatting using the formula.

Các bước:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home trên ruy-băng của bạn.
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule từ menu thả xuống.

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Then insert the following formula in the Format values where this formula is true .

=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)

• Next, select your preferred format type.

• Finally, click on OK .

Thus it will highlight the weekends from the calender week, all with the help of conditional formatting in Excel.

Kết luận

So these were the methods and different examples of using a formula for conditional formatting in Excel. I hope you have grasped the idea of the usage of the feature and can now use it accordingly. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

For more guides like this, visit Exceldemy.com .