Cách tự động tô màu các ô có công thức trong Excel

Trong nhiều trường hợp, bạn có thể muốn đánh dấu tô màu nhanh tất cả các ô có công thức trong Excel.

Hoặc bạn có thể muốn nhanh chóng đánh dấu các ô chứa công thức để có thể loại bỏ bất kỳ công thức bổ sung nào mà bạn không cần trong sổ làm việc Excel của mình.

Trong hướng dẫn ngắn này, tôi sẽ chỉ cho bạn ba cách đơn giản mà bạn có thể sử dụng để nhanh chóng tô màu các ô có chứa công thức trong Excel.

Xem thêm:

Cách tự động co giãn độ rộng cột trong Excel đơn giản

Cách gộp 2 cột Họ và Tên trên Excel không mất nội dung

Nắm vững các phím tắt Excel: Tăng hiệu quả công suất làm việc

Sử dụng định dạng có điều kiện để tô màu các ô có công thức

Để tô màu các ô chứa công thức doanh số bán hàng ta sẽ sử dụng định dạng có điều kiện.

Định dạng có điều kiện có thể được thiết lập để kiểm tra từng ô trong một phạm vi nhất định (hoặc toàn bộ bảng tính) và chỉ đánh dấu bằng cách tô màu các ô có công thức trong đó.

Để dễ hiểu hơn, hãy cùng xem qua một ví dụ đơn giản:

Ví dụ: Tôi có một bảng dữ liệu mẫu, trong đó tôi có tên người bán hàng ở cột A, doanh số bán hàng họ đã thực hiện ở cột B và hoa hồng họ đã kiếm được ở cột C.

Cột hoa hồng (tức là cột C) được tính toán dựa trên giá trị bán hàng và chứa công thức như ảnh bên dưới.

Dưới đây là các bước để sử dụng định dạng có điều kiện để tô màu tất cả các ô có chứa công thức:

• Bước 1: Chọn toàn bộ bảng dữ liệu hoặc toàn bộ bảng tính mà bạn muốn tô màu các ô bằng công thức.
• Bước 2: Nhấp vào tab Home.

• Bước 3: Tìm đến nhóm Styles, nhấp vào mũi tên thả xuống của Condition Formatting, sau đó nhấn vào New Rule.

• Bước 4: Trong hộp thoại New Formatting Rule, nhấp vào tùy chọn “Use a formula to determine which cells to format”.

• Bước 5: Nhập công thức dưới đây:

=ISFORMULA(A1)

• Bước 6: Bấm vào nút Format.

• Bước 7: Trong hộp thoại Format Cells, chọn tab Fill, sau đó màu bạn muốn đánh dấu các ô có công thức. Trong ví dụ này, tôi sẽ chọn màu xanh lá cây.

• Bước 8: Nhấn OK, sau đó tiếp tục nhấn OK.

Ngay sau khi bạn thực hiện các bước trên, định dạng có điều kiện sẽ ngay lập tức đánh dấu tô màu tất cả các ô có chứa công thức trong bảng dữ liệu Excel (như hiển thị bên dưới).

Giái thích: Tôi đã sử dụng hàm ISFORMULA trong định dạng có điều kiện để kiểm tra xem một ô có công thức hay không. Nếu ô đó có chứa công thức, hàm ISFORMULA trả về TRUE và ô sẽ được đánh dấu bằng cách tô màu sắc vào ô đã chỉ định.

Một điều hay về định dạng điều kiện là việc tô sáng này là động, có nghĩa là trong trường hợp bạn xóa công thức khỏi một ô, ô đó sẽ ngừng được tô sáng. Mặt khác, nếu bạn thêm công thức vào bất kỳ ô nào (trong phạm vi có định dạng có điều kiện được áp dụng cho ô đó), ô đó sẽ ngay lập tức được đánh dấu.

Sử dụng lệnh “Go to Special” để tô màu các ô có chứa công thức

Một cách nhanh chóng khác để đánh dấu tô màu vào các ô chứa công thức là sử dụng lệnh “Go to Special” trong Excel.

Với lệnh “Go to Special”, bạn có thể chọn tất cả các ô có chứa công thức trong bảng tính Excel và sau khi đã chọn chúng, bạn có thể đánh dấu chúng theo cách thủ công.

Bên dưới, tôi có cùng một bảng dữ liệu trong đó tôi có tên ở cột A, giá trị bán hàng ở cột B và giá trị hoa hồng được tính bằng công thức trong cột C.

Dưới đây là các bước để đánh dấu tô màu tất cả các ô có chứa công thức:

• Bước 1: Chọn phạm vi vùng dữ liệu. Nếu muốn chọn toàn bộ bảng tính, bạn có thể nhấp vào hình tam giác màu xám ở phần trên cùng bên trái của bảng tính hoặc sử dụng phím tắt Ctrl + A + A

• Bước 2: Nhấn phím F5 trên bàn phím của bạn. Thao tác này sẽ mở hộp thoại Go-To
• Bước 3: Bấm vào nút Special. Thao tác này sẽ mở hộp thoại Go To Special.

Bạn cũng có thể mở hộp thoại Go To Special bằng cách nhấp vào tab Home, sau đó nhấp vào tùy chọn Find and Select (trong nhóm Editing), sau đó nhấp vào tùy chọn Go To Special.

• Bước 4: Chọn Formula trong hộp thoại Go To Special.

• Bước 5: Nhấn OK.

Các bước trên sẽ chọn tất cả các ô chứa công thức trong phạm vi vùng dữ liệu đã chọn (vùng dữ liệu mà chúng ta đã chọn ở Bước 1).

• Bước 6: Tô màu vào các ô đã chọn. Bạn có thể thực hiện việc này bằng cách nhấp vào tab Home và sau đó sử dụng công cụ Fill Color.

Không giống như phương pháp Định dạng có điều kiện, phương pháp dùng  lệnh “Go to Special” sẽ không tự động tô màu các ô chứa công thức trong bảng tính excel. Điều này có nghĩa là trong trường hợp bạn xóa công thức khỏi bất kỳ ô nào, phần đánh dấu tô màu sẽ không tự động cập nhật vào vùng dữ liệu

Sử dụng VBA để tô màu các ô có công thức

Nếu đây là việc bạn cần làm thường xuyên, bạn có thể cân nhắc sử dụng mã để thực hiện việc này.

Một lợi ích của việc sử dụng VBA là bạn có thể thiết lập nó một lần và sau đó sử dụng lại nó một cách dễ dàng trong cùng một sổ làm việc. Bạn cũng có thể lưu mã VBA trong Sổ làm việc Macro cá nhân để có thể sử dụng nó trên bất kỳ cửa sổ làm việc Excel nào trên hệ thống của mình.

Bên dưới có cùng một bảng dữ liệu trong đó tôi có các công thức ở cột C mà tôi muốn tô màu:

Đây là các bước để đánh dấu tô màu các ô có công thức bằng VBA:

• Bước 1: Chọn phạm vi ô mà bạn muốn tô màu các ô.
• Bước 2: Nhấp vào tab Developer, sau đó nhấp vào biểu tượng Visual Basic. Thao tác này sẽ mở trình chỉnh sửa Visual Basic.

• Bước 3: Nhấp vào Insert trong menu.
• Bước 4: Bấm vào Module. Thao tác này sẽ chèn một module mới cho cửa sổ làm việc.

• Bước 5: Sao chép và dán mã VBA bên dưới vào cửa sổ mã module.

Sub HighlightCellsWithFormulas()

Dim rng As Range
Dim cell As Range

Set rng = Selection
' Loop through each cell in the range
For Each cell In rng
  ' Check if the cell contains a formula
If cell.HasFormula Then

' Highlight the cell with yellow background color, change RGB values as desired
cell.Interior.Color = RGB(255, 255, 0)

End If
Next cell
End Sub

• Bước 6: Đặt con trỏ ở bất cứ đâu trong mã và nhấn phím F5 (để chạy macro). Hoặc nhấn vào nút Run như ảnh dưới.

• Bước 7: Bấm vào biểu tượng View Microsoft Excel trong menu (hoặc nhấn ALT + F11) để quay lại bảng tính.

Bạn sẽ thấy các ô có công thức đã được đánh dấu màu vàng.

Mã VBA ở trên đi qua từng ô trong vùng chọn và sau đó xác định những ô có công thức. Khi nó đã xác định được các ô này, nó sẽ áp dụng màu tô được chỉ định cho ô đó (trong trường hợp này là màu vàng).

Hãy nhớ rằng những thay đổi được thực hiện bởi mã VBA là không thể đảo ngược được. Vì vậy, bạn nên tạo bản sao lưu tập dữ liệu trước khi chạy mã VBA .

Lưu ý : Nếu bạn muốn sử dụng lại mã này trong cửa sổ làm việc, bạn sẽ phải lưu tệp của mình dưới dạng sổ làm việc Excel hỗ trợ macro có phần đuôi mở rộng là .xlsm

Kết luận

Bên trên là ba phương pháp đơn giản mà bạn có thể sử dụng để nhanh chóng đánh dấu tô màu tất cả các ô chứa công thức trong Excel.

Cá nhân tôi thích sử dụng phương pháp định dạng có điều kiện hơn vì nó tự động xác định các ô có công thức trong khi tôi tạo mô hình dữ liệu trong Excel.

Nhiều người cũng thích phương pháp VBA hơn vì nó chỉ thiết lập một lần và có thể sử dụng lại dễ dàng.