Trong bài này, bạn sẽ tìm thấy 2 cách để thay đổi màu nên ô tính dựa trên giá trị ô tính trong Excel 2016, 2013, và 2010. Và bạn cũng sẽ học được cách sử dụng công thức Excel để thay đổi màu của ô tính trống hoặc những ô tính bị lỗi công thức.
Mọi người biết cách thay đổi màu nền của một ô tính hoặc một vùng dữ liệu trong Excel một cách đơn giản bằng việc chọn nút “Fill color” 
. Nhưng làm thế nào nếu bạn muốn thay đổi màu nền của tất cả những ô tính với một giá trị cụ thể? Trong bài viết này bạn sẽ tìm thấy câu trả lời cho câu hỏi này và học được một số mẹo hữu ích giúp bạn chọn đúng phương pháp cho các trường hợp cụ thể.
- Thay đổi màu nền ô tính dựa vào giá trị (Động) – Màu nền sẽ thay đổi tự động khi giá trị ô tính thay đổi.
- Thay đổi màu nền ô tính dựa vào giá trị hiện tại của ô tính (Tĩnh) – Sau khi được cài đặt, màu nền sẽ không thay đổi cho dù giá trị ô tính thay đổi.
- Thay đổi màu nền của những ô tính đặc biệt (ô trống/ lỗi / chứa công thức)
Cách thay đổi màu nền ô tính dựa vào giá trị ô tính trong Excel
Màu nền sẽ thay đổi phụ thuộc vào giá trị của ô tính.
Nhiệm vụ: Bạn có một bảng hoặc vùng dữ liệu, và bạn muốn thay đổi màu nền của các ô tính dựa vào giá trị của ô tính. Bạn cũng muốn màu nền thay đổi linh hoạt phản ánh sự thay đổi của dữ liệu.
Giải pháp: Bạn cần sử dụng tính năng “Conditional formatting” của Excel để làm nổi bật các giá trị như lớn hơn X, nhỏ hơn Y hoặc trong khoảng X và Y.
Giả sử bạn có bảng giá gas tại các vùng khác nhau và bạn muốn những ô giá lớn hơn 3.7 USD sẽ có màu đỏ và bằng hoặc nhỏ hơn 3.45 USD sẽ có màu xanh dương.
Và đây là các bước thực hiện:
-
- Chọn bảng hoặc vùng dữ liệu mà bạn muốn thay đổi màu nền của ô tính. Trong ví dụ này, chúng ta đã chọn $B$2:$H$10.
- Chọn tab Home, nhóm Styles, và chọn Conditional Formatting > New Rule….
- Trong hộp thoại New Formatting Rule, chọn “Format only cells that contain” trong khung “Select a Rule Type” tại phần trên của hộp thoại.
- Tại phần dưới của hộp thoại “Format Only Cells with section“, chọn điều kiện. Chúng ta chọn để định dạng cho những ô tính có giá trị lớn hơn 3.7, ta chọn Cell Value – greater than – 3.7, như bạn nhìn thấy tại hình bên dưới.
Rồi chọn nút Format… để chọn màu nền cho ô tính khi thỏa mãn điều kiện bên trên.
5. Trong hộp thoại Format Cells, chọn tab Fill và chọn màu bạn muốn, trường hợp của chúng ta là màu đỏ nhạt, và chọn OK.
6. Bây giờ bạn sẽ quay lại cửa sổ New Formatting Rule và xem trước sự thay đổi tại khung Preview. Nếu mọi thứ ổn, nhấn nút OK.
Kết quả định dạng của bạn sẽ nhìn như hình bên dưới:
Tiếp theo chúng ta muốn áp dụng thêm một điều kiện, ví dụ thay đổi màu nền của ô tính với giá trị nhỏ hơn hoặc bằng 3.45 sang màu xanh dương, chọn lại New Rule và lặp lại từ bước số 3 – 6 để chọn điều kiện. Và kết quả sẽ như hình bên dưới:
Khi bạn hoàn thành, chọn OK. Cái bạn có bây giờ là một bảng tính được định dạng rất đẹp và bạn có thể thấy dễ dàng thấy giá trị lớn nhất và nhỏ nhất tại các vùng khác nhau. Texas là nơi tuyệt vời nhất 🙂
Các thay đổi vĩnh viễn màu nền ô tính dựa vào giá trị
Khi được cài đặt, màu nền sẽ không thay đổi khi giá trị ô tính có thể thay đổi trong tương lai.
Nhiệm vụ:Bạn muốn màu của ô tính dựa vào giá trị hiện tại của nó và muốn màu sẽ giữ nguyên khi giá trị ô tính bị thay đổi.
Giải pháp: Tìm tất cả những ô tính có giá trị cụ thể sử dụng chức năng Find All của Excel hoặc add-in Select Special Cells và sau đó thay đổi định dạng của những ô tìm được bằng tính năng Format Cells.
Đây là một trong số những tính năng hiếm hoi không có trong phần trợ giúp của Excel, các diễn đàn và blog không có giải pháp cho điều này. Cũng dễ hiểu thôi, vì tính năng này không phổ biến. Và nếu bạn vẫn muốn thay đổi màu nền của ô tính một cách cố định một lần và mãi mãi, bạn hãy làm theo các bước sau đây:
Tìm và chọn tất cả các ô tính thỏa mãn điều kiện cụ thể
Có thể có những trường hợp phụ thuộc vào giá trị bạn đang tìm kiếm.
Nếu bạn muốn tìm những ô có giá trị cụ thể, ví dụ: 50, 100 hoặc 3.4, chọn tab Home, group Editing, và chọn Find Select > Find….
Trong ví dụ trước, nếu chúng ta cần tìm tất cả giá gas giữa 3.7 và 3.799, chúng ta sẽ dùng điều kiện tìm kiếm sau đây:
Bây giờ chọn bất kì một mục đã tìm thấy tại phần dưới của hộp thoại Find and Replace sau đó nhấn Ctrl + A để chọn toàn bộ. Sau đó chọn nút Close.
Đây là cách để chọn tất cả các ô tính có giá trị cụ thể bằng cách dùng chức năng Find All trong Excel.
Tuy nhiên, cái chúng ta thực sự cần là tìm tất cả giá gas lớn hơn 3.7 và không may là tính năng Find and Replace không cho phép làm những điều như vậy.
May mắn thay, có một công cụ khác có thể thực hiện những điều kiện phức tạp. Add-in Select Special Cells giúp bạn tìm tất cả các giá trị trong một khoảng xác định, ví dụ: giữa -1 và 45, lấy giá tị lớn nhất/ nhỏ nhất trong một cột, hàng hoặc vùng, tìm những ô tính theo màu chữ, màu nền và nhiều tính năng khác.
Bạn chọn nút Select by Value trên thanh ribbon và chỉ ra điều kiện tìm kiến trong khung add-in’s, trong ví dụ này, chúng ta đang tìm giá trị lớn hơn 3.7. Chọn nút Select và trong giây lát bạn sẽ có kết quả như dưới đây:
Nếu bạn muốn thử add-in Select Special Cells, bạn có thể tải bản dùng thử tại đây.
Thay đổi màu nền của ô tính được chọn sử dụng hộp thoại “Format Cells”
Hiện tại, tất cả các ô tính với giá trị cụ thể đã được chọn (sử dụng Find and Replace hoặc add-in Select Special Cells) việc còn lại của bạn là chọn màu nền của những ô đã chọn để thay đổi.
Mở hộp thoại Format Cells bằng cách nhấn Ctrl + 1 (bạn cũng có thể nhấn chuột phải vào một ô bất kì trong các ô đã chọn sau đó chọn “Format Cells…” từ menu sổ xuống, hoặc chọn Home tab > Cells group > Format > Format Cells…) và thay đổi định dạng mà bạn muốn. Chúng ta sẽ thay đổi màu nền sang màu vàng tại thời điểm này.
Nếu bạn chỉ muốn thay đổi màu nền và không thay đổi các định dạng khác, thì bạn chỉ cần chọn nút Fill color và chọn màu bạn muốn.
Dưới đây là kết quả sau khi đã thay đổi:
Không giống như các làm sử dụng “Conditional Formatting“, màu nền được đặt theo cách này sẽ không bao giờ thay đổi ngay cả khi giá trị ô tính bị thay đổi.
Thay đổi màu nền cho những ô tính đặc biệt (trống, hoặc lỗi công thức)
Như ví dụ trước, bạn có thể thay đổi màu nền của những ô tính đặc biệt theo 2 cách: động hoặc tĩnh.
Dùng công thức Excel để thay đổi màu nền của ô tính đặc biệt
Màu nền ô tính sẽ thay đổi tự động dựa vào giá trị ô tính.
Phương pháp này cung cấp một giải pháp mà rất có thể bạn sẽ cần trong 99% trường hợp, tức là màu nền của các ô sẽ thay đổi theo các điều kiện bạn đặt.
Chúng ta sẽ sử dụng lại bảng giá gas, nhưng lần này sẽ có thêm một vài vùng miền và một số ô tính bị trống. Hãy xem cách bạn có thể phát hiện những ô tính trống và thay đổi màu nền của chúng.
- Tại tab Home, trong group Styles, chọn Conditional Formatting > New Rule… (Xem tại bước 2 ở phần Các thay đổi màu nền của ô tính dựa vào giá trị một cách linh hoạt).
- Tại hộp thoại “New Formatting Rule“, chọn tùy chọn “Use a formula to determine which cells to format“. Sau đó nhập một công thức dưới đây vào ô “Format values where this formula is true“:
- =IsBlank()– để thay đổi màu nền của những ô tính trống.
- =IsError() – để thay đổi màu nền của những ô tính chứa công thức trả về lỗi.
Chúng ta đang muốn thay đổi màu nền cho các ô tính trống, nhập công thức =IsBlank(), rồi đặt trỏ chuột vào giữa dấu đóng mở ngoặc đơn và chọn nút Collapse Dialog
ở phía bên tay phải của cửa sổ để chọn một vùng ô tính, hoặc bạn có thể gõ thủ công vùng ô tính, ví dụ: =IsBlank(B2:H12).
- Chọn nút Format… và chọn màu cần thay đổi ở tab Fill (chi tiết xem tại bước 5 của phần “Thay đổi màu nền ô tính dựa vào giá trị một cách linh hoạt”) và chọn OK. Bản xem trước của quy tắc định dạng có điều kiện sẽ như bên dưới:
- Nếu bạn thấy màu đã phù hợp, chọn nút OK và bạn sẽ thấy sự thay đổi ngay lập tức tại bảng tính.
Thay đổi màu nền của những ô tính đặc biệt theo cách tĩnh.
Khi đã thay đổi, màu nền sẽ được giữ nguyên cho dù giá trị của ô tính được thay đổi.
Nếu bạn muốn thay đổi vĩnh viễn màu của các ô tính trống hoặc ô tính bị lỗi công thức, hãy làm theo các bước sau đây.
- Chọn bảng tính hoặc vùng dữ liệu rồi nhấn F5 để mở hộp thoại “Go To“, và chọn nút “Special…“.
- Tại hộp thoại “Go to Special“, chọn tùy chọn Blanks để chọn tất cả các ô tính trống.
Nếu bạn muốn chọn các ô tính chứa công thức bị lỗi, chọn Formulas > Errors. Như bạn có thể thấy tại hình bên trên, sẽ có một số tùy chọn khác cho bạn.
- Và cuối cùng, change the background of selected cells, or make any other format customizations using the thay đổi màu nền của các ô tính được chọn, hoặc tùy chỉnh các định dạng khác sử dụng hộp thoại “Format Cells” như đã miêu tả tại phần Thay đổi màu nền của các ô tính đã chọn.
Hãy nhớ rằng, định dạng được thay đổi bằng cách này sẽ được giữ nguyên ngay cả khi những ô tính trống của bạn được điền dữ liệu hoặc những công thức bị lỗi đã được sửa đúng. Tất nhiên, không thể hiểu được tại sao một ai đó lại muốn có tính năng này, có thể cho mục đích lưu lại lịch sử 🙂
