Nếu bạn đang tìm kiếm những thủ thuật đặc biệt để sử dụng Định dạng có điều kiện trong Excel thì bạn đã đến đúng nơi. Đây là hướng dẫn hoàn chỉnh về Cách sử dụng/làm Định dạng có điều kiện trong Excel. Bạn cũng sẽ học cách thực hiện và quản lý nhiều định dạng có điều kiện trong Excel, cũng như định dạng có điều kiện dựa trên ô khác. Hãy đi vào phần chính của bài viết.
Bạn có thể tải workbook thực hành từ đây.
Định dạng có điều kiện Excel là gì?
Định dạng có điều kiện là phương pháp thay đổi định dạng ô dựa trên các điều kiện cho trước. Ví dụ, tôi cho bạn một trang tính Excel. Trang tính có một số số trong các ô. Nếu bạn muốn định dạng ô có màu nền Đỏ cho các giá trị nhỏ hơn không.
- Phần điều kiện là: Nếu số nhỏ hơn không.
- Phần định dạng là: Tô nền Đỏ cho những ô đó.
Cách làm Định dạng có điều kiện trong Excel
Trong phần này, tôi sẽ chỉ cho bạn tất cả các loại Định dạng có điều kiện trong Excel trên hệ điều hành Windows. Bạn sẽ tìm thấy giải thích chi tiết với minh họa rõ ràng cho từng mục. Tôi sử dụng phiên bản Microsoft 365 ở đây. Nhưng bạn có thể dùng bất kỳ phiên bản nào bạn có. Nếu điều gì không hoạt động trong phiên bản của bạn thì hãy để lại bình luận.
Để giải thích thủ tục tiếp theo, tôi sẽ dùng tập dữ liệu sau. Ở cột B là các ngày ngẫu nhiên, cột C là tên ngẫu nhiên, cột D chứa lợi nhuận kiếm được ngày đó. Chúng ta sẽ áp dụng tất cả điều kiện định dạng cho tập dữ liệu tùy theo tiêu chí.
1. Quy tắc làm nổi bật ô (Highlight Cells Rules)
Bạn có thể làm nổi bật ô dựa trên các quy tắc như lớn hơn giá trị cụ thể, nhỏ hơn, hoặc giữa một khoảng, v.v. Tôi sẽ chỉ tất cả các loại với các bước và minh họa đúng.
Tiêu chí 1: Giá trị ô Lớn hơn giá trị cụ thể
Định dạng ô dựa trên điều kiện lớn hơn. Đặt giá trị cụ thể và xem bao nhiêu ô lớn hơn giá trị đó.
📌 Các bước:
- Chọn phạm vi ô D5:D12.
- Tab Home → Conditional Formatting.
- Chọn Highlight Cells Rules → Greater Than.
- Hộp thoại Greater Than xuất hiện.
- Đặt giá trị so sánh: 4000.
- Các ô lớn hơn sẽ được làm nổi bật → OK.
- Kết quả cuối cùng:
Tiêu chí 2: Giá trị ô Nhỏ hơn giá trị cụ thể
Ngược lại với trên. Làm nổi bật ô có giá trị nhỏ hơn giá trị cụ thể.
📌 Các bước:
- Chọn phạm vi → Highlight Cells Rules → Less Than.
- Hộp thoại Less Than hiện ra.
- Đặt giá trị: 2500 → OK.
- Kết quả:
Tiêu chí 3: Giá trị ô Nằm giữa hai giá trị
Làm nổi bật ô có giá trị nằm giữa hai giá trị cho trước.
📌 Các bước:
- Chọn D5:D12 → Highlight Cells Rules → Between.
- Trong hộp thoại đặt 2 giá trị: 3000 và 4000 → OK.
- Kết quả:
Tiêu chí 4: Giá trị ô Bằng giá trị cụ thể
Làm nổi bật ô bằng đúng một giá trị.
📌 Các bước:
- Chọn D5:D12 → Highlight Cells Rules → Equal To.
- Đặt giá trị: 3150.
- Kết quả:
Tiêu chí 5: Ô chứa văn bản cụ thể
Tìm văn bản cụ thể bằng định dạng có điều kiện.
📌 Các bước:
- Chọn C5:C12 → Highlight Cells Rules → Text that Contains.
- Hộp thoại Text that Contains hiện ra.
- Nhập văn bản từ dữ liệu → văn bản được làm nổi bật → OK.
- Kết quả:
Tiêu chí 6: Ô chứa ngày cụ thể
Áp dụng điều kiện về ngày tháng.
📌 Các bước:
- Chọn phạm vi chứa ngày B5:B12 → Highlight Cells Rules → A Date Occurring.
- Trong hộp thoại đặt quy tắc làm nổi bật ngày tháng trước.
- Chỉ ngày tháng trước trong B5:B12 được làm nổi bật.
- Kết quả:
Tiêu chí 7: Giá trị ô trùng lặp
Tìm giá trị trùng lặp từ tập dữ liệu.
📌 Các bước
- Chọn phạm vi cần đặt điều kiện → Duplicate Values từ menu thả xuống.
- Giá trị trùng lặp được làm nổi bật tự động.
- Kết quả:
2. Quy tắc Top/Bottom (Top/Bottom Rules)
Top và Bottom Rules là loại thứ 2 của Định dạng có điều kiện trong Excel. Dùng khi muốn làm nổi bật giá trị cao nhất/thấp nhất hoặc top/bottom phần trăm dữ liệu.
Trường hợp 1: Bất kỳ số lượng giá trị Top nào từ tập dữ liệu
Hiển thị các giá trị top nhất trong dữ liệu.
📌 Các bước:
- Chọn D5:D12 → Home → Conditional Formatting → Top/Bottom Rules → Top 10.
- Hộp thoại hiện ra → nhập số lượng giá trị top.
- Dữ liệu có < 10 mục → làm nổi bật top 3 giá trị.
- Kết quả:
Trường hợp 2: Top 10% (hoặc bất kỳ % nào) giá trị từ tập dữ liệu
Làm nổi bật giá trị thuộc top 10% tổng dữ liệu.
📌 Các bước
- Chọn D5:D12 → Top/Bottom Rules → Top 10%.
- Đặt điều kiện top 10% tổng giá trị → ô D5 ($5000) thỏa mãn.
Trường hợp 3: Bottom 10 (hoặc bất kỳ số lượng) mục từ tập dữ liệu
Làm nổi bật giá trị bottom nhất.
📌 Các bước
- Chọn phạm vi → Bottom 10 Items từ menu thả xuống.
- Đặt số lượng giá trị bottom → 3 giá trị bottom được làm nổi bật.
Trường hợp 4: Bottom 10% (hoặc bất kỳ %) giá trị từ tập dữ liệu
Ngược lại với Top 10%.
📌 Các bước:
- Chọn dữ liệu → Top/Bottom Rules → Bottom 10%.
- Nhập % mong muốn → giá trị khớp được làm nổi bật.
- $2100 khớp bottom 10%.
Trường hợp 5: Giá trị Trên trung bình của tập dữ liệu
Làm nổi bật giá trị trên trung bình.
📌 Các bước:
- Chọn phạm vi → Above Average từ Top/Bottom Rules.
- Giá trị mong muốn được làm nổi bật tự động.
Trường hợp 6: Giá trị Dưới trung bình của tập dữ liệu
Tìm giá trị dưới trung bình - điều kiện cuối của Top/Bottom Rules.
📌 Các bước:
- Chọn dữ liệu → Below Average từ menu thả xuống.
- Dữ liệu thỏa mãn được làm nổi bật.
3. Thanh dữ liệu (Data Bars)
Loại thứ 3 trong 5 loại định dạng có điều kiện. Lý tưởng để so sánh giá trị số. Tạo thanh thể hiện giá trị dương/âm.
📌 Các bước:
- Conditional Formatting → Data Bars.
- Chọn thiết kế có sẵn.
- Phạm vi dữ liệu sẽ trông như sau: giá trị dương màu xanh lá, âm màu đỏ.
4. Thang màu (Color Scales)
Loại thứ 4. Hiển thị phân bố dữ liệu. Có thể trộn 2 hoặc 3 màu. Màu trên = giá trị lớn, giữa = trung bình, dưới = giá trị nhỏ.
📌 Các bước:
- Chọn dữ liệu → Color Scales từ Conditional Formatting.
- Chọn bộ màu có sẵn.
- Cột được định dạng màu từ đỏ → vàng → xanh lá theo giá trị tăng.
- Có thể chọn More Rules để tùy chỉnh Color Scale.
5. Bộ biểu tượng (Icon Sets)
Loại cuối cùng. Triển khai biểu tượng dựa trên giá trị ô.
📌 Các bước:
- Chọn dữ liệu → Icon Sets từ Conditional Formatting.
- Chọn thiết kế.
- Kết quả: biểu tượng đỏ = giá trị thấp, vàng = trung bình, xanh lá = cao.
6. Định dạng có điều kiện với Quy tắc mới đơn hoặc nhiều
Khi thêm nhiều quy tắc cho cùng tập dữ liệu, có thể xảy ra xung đột do quy tắc ghi đè nhau.
Ví dụ không xung đột:
- Xanh lá nếu giá trị > 4000
- Vàng nếu giá trị 2000 đến 4000
- Đỏ nếu giá trị < 2000
Ví dụ có xung đột:
- Xanh lá nếu > 4000
- Vàng nếu > 3000
- Đỏ nếu > 2000
Số > 4000 cũng > 2000 → xung đột. Cần sắp xếp thứ tự quy tắc và áp dụng "Stop if True" (Dừng nếu đúng).
✅ Quản lý nhiều quy tắc Định dạng có điều kiện trên cùng tập dữ liệu
Để tổ chức quy tắc, mở "Conditional Formatting Rules Manager":
- Chọn cột đã áp dụng điều kiện.
- Home → Conditional Formatting → Manage Rules.
- Cửa sổ Conditional Formatting Rules Manager hiển thị quy tắc theo thứ tự thực thi.
- Sắp xếp bằng mũi tên lên/xuống.
- Nếu áp dụng quy tắc theo thứ tự như ảnh chụp: quy tắc 1 chạy trước (giá trị > 2000 → tô Đỏ) → dừng nếu checkbox đánh dấu.
- Kết quả nếu checkbox được đánh dấu.
- Sắp xếp seri: trước kiểm tra > 4000 → tô xanh lá → dừng. Không thì kiểm tra tiếp.
- Sau khi sắp xếp, kết quả hoàn hảo như mong muốn.
7. Làm nổi bật Lỗi/Ô trống với Định dạng có điều kiện
Dễ dàng tìm ô lỗi và ô trống.
📌 Các bước:
- Chọn toàn bộ dữ liệu hoặc cột cần tìm lỗi hoặc trống.
- Home → Conditional Formatting → New Rule (hoặc phím tắt Alt + O + D).
- Chọn Rule type = "Format Only cells that contain".
- Chọn Blanks để làm nổi bật ô trống hoặc Errors cho ô lỗi.
- Các tùy chọn khác: Cell Value, Specific Text, Dates Occurring, No errors, No Blanks.
- Chọn Format → màu tô → OK cả hai cửa sổ.
- Kết quả: dữ liệu làm nổi bật Trống hoặc Lỗi.
Định dạng có điều kiện nâng cao với Công thức
Bạn có thể tạo công thức để áp dụng định dạng có điều kiện. Ở đây tôi chỉ 3 ví dụ: làm nổi bật hàng lẻ/chẵn, mỗi hàng thứ n, và tìm kiếm động.
1. Làm nổi bật hàng Lẻ hoặc Chẵn
Làm nổi bật từng hàng lẻ hoặc chẵn.
📌 Các bước:
- New Rule trong Conditional Formatting.
- Chọn "Use a Formula to determine which cells to format".
- Dán công thức:
=ISODD(ROW()) - Chọn màu tô → OK.
- Kết quả: hàng lẻ thành màu vàng.
- Để làm nổi bật hàng chẵn:
=ISEVEN(ROW())
2. Làm nổi bật mỗi hàng thứ n
Làm nổi bật hàng thứ n (ví dụ hàng 3, 6, 9...).
📌 Các bước:
- New Rule → Use a Formula to determine which cells to format.
- Công thức:
=MOD(ROW(),3)=0(mỗi hàng thứ 3). - Chọn màu → OK.
- Kết quả: hàng 6, 9, 12 được tô màu.
3. Tìm kiếm động giá trị và làm nổi bật ô tương ứng
Làm nổi bật ô theo giá trị ô tìm kiếm.
📌 Các bước:
- Gán ô nhập tìm kiếm: ô F5.
- New Rule → Use a Formula to determine which cells to format.
- Công thức:
=AND($F$5<>"",$F$5=B5) - Chọn màu → OK.
- Nhập giá trị vào F5 → ô chứa giá trị đó được làm nổi bật.
4. Định dạng có điều kiện dựa trên ô khác
Định dạng phạm vi ô dựa trên ô/phạm vi khác.
📌 Các bước:
- Áp dụng định dạng cho cột Salesrep dựa trên giá trị Profit.
- Chọn ô cột Salesrep → New Rule.
- Chọn Use a Formula to determine which cells to format.
- Công thức:
=D5>4000 - Chọn màu → OK.
- Đã làm nổi bật Salesrep có Profit > $4000.
- Tạo thêm 2 quy tắc: Profit 2000-4000 và < 2000.
- Công thức 2000-4000:
=AND(D5>=2000,D5<=4000) - Công thức < 2000:
=D5<2000
Cách xóa Định dạng có điều kiện trong Excel
Để xóa định dạng có điều kiện:
- Chọn phạm vi cần xóa.
- Home → Conditional Formatting → Clear Rules.
- Hai tùy chọn: xóa từ ô đã chọn hoặc xóa toàn bộ trang tính.
- Định dạng đã bị xóa.
Kết luận
Trong bài viết này, bạn đã tìm hiểu cách dùng Định dạng có điều kiện trong Excel. Hy vọng hữu ích. Truy cập ExcelDemy để học thêm. Hãy để lại bình luận, góp ý hoặc thắc mắc bên dưới.