Cách sử dụng định dạng có điều kiện trong Excel chi tiết từ A-z

Định dạng có điều kiện trong Excel là một tính năng hữu ích, cho phép người dùng có thể áp dụng các định dạng cụ thể cho ô mà mình chọn theo tiêu chí nhất định. Vậy trong Microsoft Excel bao gồm những kiểu định dạng có điều kiện nào? Quy tắc và cách sử dụng định dạng có điều kiện hiệu quả trong Excel? Để giải đáp cụ thể, người dùng hãy cùng HVN Group theo dõi chi tiết bài viết dưới đây.

Các kiểu định dạng có điều kiện phổ biến trong Excel

Trong Excel sẽ có nhiều kiểu định dạng có điều kiện mà người dùng có thể áp dụng để làm nổi bật và trực quan hóa dữ liệu của mình trong bảng tính. Ví dụ như:

Làm nổi bật các quy tắc ô

Kiểu định dạng này có thể áp dụng cho nhiều loại dữ liệu khác nhau, giúp người dùng dễ dàng làm nổi bật các ô cụ thể dựa trên tiêu chí mà mình đặt ra. Ví dụ, các giá trị lớn hơn hoặc nhỏ hơn một số nhất định, văn bản chứa từ khóa, hay ngày trong phạm vi cụ thể,…

Ngoài ra, quy tắc làm nổi bật ô trong Excel cũng rất hữu ích cho giá trị trùng lặp. Khi đó, bằng cách áp dụng quy tắc định dạng cho bảng dữ liệu, người dùng có thể nhanh chóng xác định các mục trùng lặp hoặc duy nhất trong bảng.

Quy tắc trên/dưới

Quy tắc thiết lập định dạng trên/dưới cho phép người dùng dễ dàng xác định các giá trị trên cùng hoặc dưới cùng của một phạm vi dữ liệu. Và dù giá trị mặc định hiển thị là 10, nhưng người dùng vẫn có thể linh hoạt tùy chỉnh giá trị này để hiển thị bất kỳ giá trị số trên cùng hoặc dưới cùng nào theo yêu cầu thực tế. 

Thang màu

Định dạng thang màu trong Excel giúp người dùng dễ dàng làm nổi bật phạm vi dữ liệu từ thấp đến cao hoặc ngược lại bằng cách sử dụng gradient màu linh hoạt theo lựa chọn của mình. Khi đó, với các giá trị trong phạm vi được định dạng màu dựa trên tỷ lệ hoặc khoảng cách cụ thể để có giá trị lớn nhất và nhỏ nhất, việc so sánh dữ liệu sẽ trực quan hơn rất nhiều.

Thanh dữ liệu

Kiểu định dạng này cho phép thêm thanh ngang vào các ô để biểu diễn trực quan giá trị tương đối của từng ô, giúp việc so sánh dữ liệu đơn giản và dễ dàng hơn rất nhiều. Thêm vào đó, thanh dữ liệu cũng có khá nhiều tùy chọn để có thể thay đổi giao diện và các hoạt động, bao gồm cả việc tùy chỉnh màu sắc, chỉ hiển thị thanh, hoặc giá trị số của ô.

Bộ biểu tượng

Biểu tượng cũng là một tùy chọn định dạng có điều kiện phổ biến trong Excel, giúp người dùng dễ dàng so sánh các giá trị trong một bảng hoặc một cột dữ liệu lớn mà không cần sử dụng biểu đồ hay đồ thị. Bên cạnh đó, tùy chọn cũng cho phép người dùng có thể thiết lập bộ biểu tượng cho riêng mình để duy trì sự nhất quán và trực quan trong bảng tính.

Định dạng có điều kiện theo công thức

Ngoài các định dạng có điều kiện cơ bản, Excel còn cho phép người dùng dễ dàng thiết lập các điều kiện định dạng dựa trên công thức phức tạp. Khi đó, định dạng sẽ được áp dụng cho các kết quả trả về đúng với công thức, không chỉ làm nổi bật dữ liệu, giúp xác định xu hướng, mà còn hỗ trợ kiểm tra điều kiện một cách trực quan. 

Cách sử dụng định dạng có điều kiện trong Excel đơn giản

Định dạng có điều kiện trong Excel có thể áp dụng với nhiều ô, cột, hàng, hoặc toàn bộ bảng tính Excel dựa trên ô hoặc giá trị của ô khác bằng cách thiết lập các quy tắc (điều kiện) phù hợp. Cụ thể cách sử dụng định dạng có điều kiện trong Excel đơn giản như sau:

Tạo quy tắc định dạng có điều kiện trên Excel

Để thiết lập các quy tắc định dạng có điều kiện trong Excel, người dùng có thể dễ dàng thao tác với các bước sau:

– Bước 1: Tùy chọn ô muốn áp dụng định dạng có điều kiện trong bảng tính. Ví dụ, nếu cần làm nổi bật ô ở cột Change, thì các ô cần chọn lúc này sẽ là D2:D9.

– Bước 2: Click chọn Conditional Formatting trên tab Home >> sau đó tùy chọn định dạng có điều kiện phù hợp. 

– Bước 3: Tại ví dụ này, người dùng chỉ cần đơn giản áp dụng định dạng có điều kiện nhỏ hơn 0 bằng cách click chọn Highlight Cells Rules >> chọn Less Than…

– Bước 4: Nhập điều kiện giá trị ô tương ứng (ví dụ này sẽ là 0) tại tab Format cells that are LESS THAN. Khi đó, Excel sẽ giúp làm nổi bật tất cả các ô trong dãy đáp ứng đủ điều kiện.

– Bước 5: Để tùy chọn định dạng mà mình muốn, người dùng có thể tùy chọn với các định dạng có sẵn, hoặc thiết lập định dạng theo yêu cầu riêng của mình bằng cách click chọn Custom Format….

– Bước 6: Tại cửa sổ Format Cells, người dùng lúc nãy sẽ thấy 3 tab với 3 chức năng riêng biệt đó là:

• Font: Định dạng font chữ.
• Border: Định dạng kiểu đường viền.
• Fill: Định dạng màu nền.

– Bước 7: Sau khi thiết lập xong, chỉ cần nhấn OK là hoàn tất.

Thiết lập quy tắc định dạng trên Excel từ đầu

Trường hợp các quy tắc định dạng có điều kiện có sẵn trong Excel không phù hợp với yêu cầu, người dùng cũng có thể dễ dàng tạo ra một quy tắc mới từ đầu với các bước đơn giản sau:

– Bước 1: Tùy chọn ô mà người dùng cần thiết lập định dạng có điều kiện >> chọn Conditional Formatting >> chọn New Rule…

– Bước 2: Khi hộp thoại New Rule Formatting xuất hiện, hãy tùy chọn kiểu quy tắc mà mình muốn:

• Format all cells based on their values: Định dạng tất cả các ô dựa trên giá trị của chúng.
• Format only cells that contain: Chỉ định dạng các ô có chứa.
• Format only top or bottom ranked values: Chỉ định dạng các giá trị được xếp hạng trên cùng hoặc dưới cùng.
• Format only values that are above or below average: Chỉ định dạng các giá trị trên hoặc dưới mức trung bình.
• Format only unique or duplicate values: Chỉ định dạng các giá trị duy nhất hoặc trùng lặp.
• Use a formula to determine which cells to format: Sử dụng công thức để xác định ô cần định dạng.

– Bước 3: Ví dụ, nếu muốn làm nổi bật các ô có giá trị nằm trong khoảng giữa 60 và 70, hãy click chọn Format only cells that contain.

– Bước 4: Tại tab Edit the Rule Description, hãy thiết lập các điều kiện tương ứng.

– Bước 5: Click chọn Format để thiết lập định dạng mà mình muốn >> nhấn OK để hoàn tất. 

Định dạng có điều kiện dựa trên giá trị ô

Với định dạng có điều kiện dựa trên giá trị ô, hiểu đơn giản thì là người dùng sẽ căn cứ vào giá trị của một ô bất kỳ để thiết lập định dạng. Khi đó, bất kể giá trị nào trong ô đó thay đổi, thì định dạng có điều kiện đều sẽ được tự động điều chỉnh và cập nhật lại sự thay đổi của dữ liệu. 

Ví dụ: Người dùng muốn làm nổi bật tất cả các ô có giá lớn hơn giá của tháng 1 trong cột Price, thao tác lúc này đơn giản như sau:

– Bước 1: Click chọn Conditional Formatting trên tab Home >> chọn Highlight Cells Rules và chọn Greater Than… 

– Bước 2: Khi hộp thoại Greater Than… xuất hiện, hãy click chọn ô C5 – ô giá của tháng 1 vào ô chọn phạm vi tại tab Format cells that are GREATER THAN.

– Bước 3: Để tùy chọn định dạng mà mình muốn, người dùng cũng sẽ click chọn Custom Format >> tùy chỉnh định dạng phù hợp.

– Bước 4: Cuối cùng, nhấn OK để hoàn tất thiết lập định dạng.

Áp dụng quy tắc định dạng có điều kiện cho ô hoặc bảng

Với các định dạng có điều kiện trong Excel, người dùng sẽ không bị giới hạn bởi bất kỳ quy tắc nào cho mỗi ô. Điều này đồng nghĩa, người dùng có thể áp dụng nhiều quy tắc logic dựa trên các yêu cầu công việc cụ thể của mình.

Ví dụ: Người dùng cần xử lý các công việc liên quan đến thời tiết, muốn thiết lập các quy tắc cụ thể cho bảng thời tiết để tiện cho việc theo dõi như tô nền vàng cho ô dữ liệu có nhiệt độ > 60° F, màu cam khi nhiệt độ > 70° F và màu đỏ khi nhiệt độ > 80° F. Lúc này, thao tác có thể thực hiện đơn giản như sau:

– Bước 1: Click chọn Conditional Formatting trên tab Home >> chọn Manage Rules… để mở hộp thoại Conditional Formatting Rules Manager.

– Bước 2: Lựa chọn quy tắc cần áp dụng đầu tiên >> di chuyển quy tắc bằng cách sử dụng upward arrow (mũi tên hướng lên hoặc hướng xuống).

– Bước 3: Thao tác tương tự cho quy tắc thứ hai và thứ ba.

– Bước 4: Tùy chọn hộp kiểm Stop If True ở hai quy tắc đầu tiên nếu người dùng 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.

– Bước 5: Cuối cùng, chỉ cần nhấn OK để hoàn tất thiết lập.

Chỉnh sửa quy tắc định dạng có điều kiện trong Excel

Nếu muốn thay đổi một quy tắc định dạng hiện có, người dùng cũng chỉ cần thao tác đơn giản với các bước hướng dẫn dưới đây:

– Bước 1: Chọn bất kỳ ô nào đang áp dụng quy tắc định dạng mà người dùng muốn >> click chọn Conditional Formatting trên tab Home.

– Bước 2: Tiếp tục với tùy chọn Manage Rules… >> chọn Edit Rule… để thực hiện việc chỉnh sửa quy tắc theo yêu cầu cụ thể ở cửa sổ Edit Formatting Rule.

– Bước 3: Cuối cùng, nhấn OK để lưu lại chỉnh sửa là hoàn tất.

*Lưu ý: Trường hợp không tìm thấy các quy tắc cần chỉnh sửa, người dùng có thể truy cập hộp thoại This Worksheet tại tab Show formatting rules for để hiển thị tất cả các quy tắc được thiết lập trên bảng tính hiện có của mình.

Sao chép định dạng có điều kiện trong Excel đơn giản

Sao chép định dạng có điều kiện trong Excel là một cách hiệu quả để người dùng có thể áp dụng nhanh định dạng quy tắc cho vùng dữ liệu khác. Khi đó, với việc sao chép định dạng, người dùng dễ dàng thực hiện với một trong các cách sau:

Sao chép định dạng có điều kiện với công cụ Format Painter

Công cụ Format Painter cho phép người dùng có thể sao chép định dạng của quy tắc mà không ảnh hưởng đến công thức hay dữ liệu với thao tác đơn giản sau:

– Bước 1: Tùy chọn ô hoặc vùng dữ liệu có định dạng điều kiện cần sao chép.

– Bước 2: Trong tab Home, hãy click chọn Format Painter ở mục Clipboard (tùy chọn với biểu tượng hình cây cọ).

– Bước 3: Di chuột và nhấp chọn vùng dữ liệu mà người dùng muốn áp dụng định dạng. 

Sao chép và áp dụng các định dạng quy tắc “phù hợp”

Với cách này, người dùng có thể sao chép các định dạng có điều kiện và áp dụng quy tắc phù hợp theo các bước hướng dẫn chi tiết sau::

– Bước 1: Tùy chọn ô hoặc vùng dữ liệu có định dạng điều kiện phù hợp.

– Bước 2: Click chuột phải >> chọn Copy, hoặc nhấn tổ hợp phím Ctrl + C để sao chép định dạng.

– Bước 3: Tại phạm vi dữ liệu cần áp dụng định dạng, hãy click phải chuột >> chọn Dán đặc biệt và chọn Định dạng. Hoặc nhấn tổ hợp phím Alt + E + S + T để thao tác nhanh.

Sao chép định dạng với trình quản lý quy tắc định dạng có điều kiện

Ngoài hai cách trên, người dùng cũng có thể dễ dàng sao chép định dạng có điều kiện với trình quản lý quy tắc định dạng. Thao tác cụ thể như sau:

– Bước 1: Click chọn Conditional Formatting trên tab Home >> chọn Manage Rules… để mở trình quản lý quy tắc định dạng.

– Bước 2: Chọn vùng dữ liệu gốc và nhấp chọn Chỉnh sửa quy tắc >> sao chép công thức hoặc quy tắc để áp dụng lại cho vùng dữ liệu đích.

**Lưu ý: Nếu quy tắc định dạng dựa trên công thức, hãy đảm bảo rằng công thức sẽ được kiểm tra và chỉnh sửa để phù hợp với vùng dữ liệu mới.

Cách xóa định dạng có điều kiện trong Excel

Trường hợp người dùng muốn xóa định dạng có điều kiện trong Excel, có thể thao tác nhanh với các bước dưới đây:

– Bước 1: Truy cập bảng tính cần xóa định dạng có điều kiện.

– Bước 2: Chọn dải ô áp dụng định dạng >> click chọn Conditional Formatting trên tab Home và chọn Clear Rules.

– Bước 3: Chọn một trong các tùy chọn có sẵn để xóa định dạng:

• Clear Rules from Selected Cells: Xóa quy tắc từ các ô đã chọn.
• Clear Rules from Entire Sheet: Xóa quy tắc khỏi toàn bộ trang tính
• Clear Rules from this Table: Xóa quy tắc từ bảng này.
• Clear Rules from this PivotTable: Xóa quy tắc từ PivotTable này.

– Bước 4: Sau khi thao tác, định dạng có điều kiện ở dải ô mà người dùng chọn sẽ được xóa bỏ.

Tại sao nên dùng định dạng có điều kiện trong Excel?

Định dạng có điều kiện trong Excel là một công cụ mạnh mẽ, có thể giúp thay đổi cách hiển thị dữ liệu tự động dựa trên quy tắc mà người dùng đã thiết lập trước đó. Theo đó, dưới đây là một số lý do tại sao nên áp dụng các quy tắc định dạng có điều kiện trong Excel mà người dùng không thể bỏ qua:

Làm nổi bật thông tin quan trọng

Trong một bảng tính lớn với lượng dữ liệu khổng lồ, việc tìm kiếm các thông tin quan trọng hoặc đặc biệt có thể gặp khó khăn. Do đó, việc người dùng sử dụng các quy tắc định dạng để làm nổi bật thông tin sẽ là một giải pháp hoàn hảo, có thể giúp tối ưu thời gian và công sức thay vì việc tìm kiếm thủ công phức tạp.

So sánh dữ liệu trực quan

Khi cần so sánh các giá trị trong cùng bảng dữ liệu, người dùng cũng có thể sử dụng định dạng có điều kiện để làm nổi bật dữ liệu. Khi đó, với các giá trị hiển thị rõ cao/thấp, việc so sánh cũng sẽ trở nên đơn giản và dễ dàng hơn rất nhiều, đặc biệt là trong các báo cáo hoặc tài liệu thuyết trình.

Dễ dàng phát hiện xu hướng dữ liệu

Với các thiết lập định dạng có điều kiện, người dùng cũng có thể dễ dàng làm nổi bật xu hướng tăng/giảm của dữ liệu theo thời gian. Từ đó, đơn giản hơn trong việc nhận diện các vấn đề tiềm ẩn, định hướng và đưa ra quyết định chiến lược phù hợp cho sự phát triển.

Thiết lập các cảnh báo tự động

Trong quản lý tồn kho, tính năng định dạng có điều kiện trong Excel cũng là một công cụ hữu ích để người dùng thiết lập các cảnh báo tự động cho mục đích nhắc nhở. Khi đó, với quy tắc định dạng chuyển ô sang màu vàng khi số lượng hàng tồn kho dưới ngưỡng tối thiểu, hoặc màu đỏ khi hết hàng, người dùng có thể hạn chế được việc gián đoạn hoạt động kinh doanh do thiếu hàng.

Tăng tính thẩm mỹ và chuyên nghiệp

Thay vì chỉ hiển thị các giá trị số khô khan, thì người dùng có thể làm nổi bật dữ liệu quan trọng bằng các định dạng màu sắc hoặc biểu tượng. Điều này không chỉ giúp tăng tính thẩm mỹ, chuyên nghiệp cho bảng tính, mà còn làm cho dữ liệu thêm trực quan, dễ đọc và dễ hiểu hơn rất nhiều.

Khả năng tùy chỉnh linh hoạt

Excel cho phép người dùng có thể thiết lập các định dạng có điều kiện cho nhiều trường hợp khác nhau, tùy chỉnh linh hoạt theo nhu cầu hoặc mục đích hiển thị. Khi đó, ngoài các định dạng ô dựa trên giá trị của ô khác, lớn hơn hoặc nhỏ hơn một giá trị cụ thể, thì người dùng còn có thể thiết lập các định dạng dựa trên công thức, giúp tạo sự liên kết chặt chẽ và logic cho tất cả dữ liệu trên bảng tính.

Lỗi thường gặp khi dùng định dạng có điều kiện trong Excel

Khi áp dụng các định dạng có điều kiện trong Excel, người dùng đôi khi có thể gặp phải một số lỗi ảnh hưởng đến việc hiển thị dữ liệu và thao tác. Chẳng hạn như:

1. Lỗi công thức

Các quy tắc định dạng có điều kiện dựa trên công thức sẽ phụ thuộc nhiều vào các cách tham chiếu tương đối hoặc tuyệt đối. Do đó, nếu tham chiếu không được xác định chính xác, lỗi này có thể khiến vùng dữ liệu bị sai lệch, định dạng không hoạt động, hoặc hiển thị không đúng điều kiện mà người dùng thiết lập.

2. Lỗi chồng chéo các quy tắc

Khi bảng tính có quá nhiều định dạng quy tắc tương tự được áp dụng lên một phạm vi, các quy tắc có thể xung đột và ảnh hưởng đến kết quả hiển thị. Lúc này, cách tốt nhất để tránh lỗi chồng chéo, là người dùng sẽ sắp xếp lại quy tắc theo thứ tự ưu tiên, đảm bảo tính chính xác của quy tắc.

3. Lỗi không cập nhật dữ liệu

Trường hợp người dùng thêm, xóa, hoặc sửa dữ liệu trong phạm vi áp dụng quy tắc, một số định dạng có điều kiện (áp dụng quy tắc tĩnh) sẽ không tự động cập nhật sự thay đổi. Do đó, người dùng vẫn nên sử dụng bảng thay vì phạm vi tĩnh để các định dạng có điều kiện có thể tự động mở rộng khi có dữ liệu mới được thêm.

4. Lỗi không tương thích

Khi chuyển đổi tệp giữa Excel và Google Trang tính, một số định dạng có điều kiện dựa trên công thức phức tạp hoặc các tính năng không tương thích có thể sẽ bị mất do sự khác biệt giữa các phiên bản. Vậy nên, việc kiểm tra khả năng tương thích khi làm việc giữa các phiên bản, phần mềm, hoặc nền tảng khác nhau là điều rất cần thiết để tránh lỗi không tương thích.

Các phím tắt hỗ trợ định dạng có điều kiện trong Excel

Bằng cách sử dụng các phím tắt trong Excel, người dùng có thể thao tác nhanh trên dữ liệu của mình để tối ưu thời gian và tăng hiệu suất làm việc trên bảng tính Excel. Theo đó, dưới đây là các phím tắt và thao tác nhanh để hỗ trợ định dạng có điều kiện trong Excel người dùng có thể tham khảo:

Mẹo sử dụng định dạng có điều kiện hiệu quả trong Excel

Việc sử dụng định dạng có điều kiện trong Excel không chỉ giúp người dùng phân tích, hiển thị dữ liệu trực quan, mà còn có thể cải thiện hiệu suất và chất lượng công việc. Tuy nhiên, để sử dụng quy tắc định dạng một cách hiệu quả, người dùng vẫn cần lưu ý những điểm sau:

• Bắt đầu với các định dạng cơ bản: Nếu mới làm quen với định dạng có điều kiện, người dùng có thể bắt đầu từ các quy tắc cơ bản để hiểu rõ trước khi triển khai với các công thức hoặc điều kiện phức tạp hơn.
• Sử dụng màu sắc nhất quán: Với các thiết lập định dạng màu sắc, người dùng có thể dễ dàng làm nổi bật các thông tin quan trọng. Tuy nhiên, màu sắc trong bảng tính cũng cần phải đảm bảo sự nhất quán để tránh nhầm lẫn và rối mắt.
• Định dạng dữ liệu dưới dạng bảng: Hãy sử dụng tên bảng và cột cho công thức và các quy tắc định dạng có điều kiện thay vì tham chiếu đến các chữ cái và số của hàng hoặc cột. Vì điều này có thể giúp các quy tắc định dạng có điều kiện trong bảng tính thích ứng tốt với sự thay đổi của dữ liệu, đảm bảo rằng tất cả các định dạng sẽ luôn phù hợp khi mọi thứ thay đổi.
• Tránh sử dụng quá mức: Mặc dù định dạng có điều kiện là một công cụ mạnh mẽ, nhưng nếu áp dụng quá nhiều quy tắc và định dạng, thì điều này có thể khiến bảng tính trông lộn xộn, khó đọc và dễ dẫn đến nhầm lẫn.

Tóm lại, định dạng có điều kiện là một công cụ tuyệt vời để tạo ra các quy tắc và làm nổi bật dữ liệu quan trọng trong bảng tính. Và bằng cách sử dụng tính năng này, người dùng có thể tối ưu hóa hiệu suất và nâng cao tính chuyên nghiệp cho các tài liệu của mình. 

Lời kết

Bài viết là tất tần tật những chia sẻ tổng quan của HVN Group – Hệ sinh thái kiến tạo 4.0 về định dạng có điều kiện trong Excel và một số lưu ý hữu ích. Nếu có thắc mắc, hoặc băn khoăn cần giải đáp, người dùng vui lòng để lại lời nhắn trực tiếp tại LiveChat phía dưới để có thể nhận được sự tư vấn và hỗ trợ tốt nhất từ chuyên gia.