Cách sửa lỗi #REF! trong Excel: 6 nguyên nhân và giải pháp từ Microsoft

Tóm tắt nhanh (TL;DR)

Lỗi #REF! xuất hiện khi Excel mất tham chiếu đến ô hoặc vùng dữ liệu — thường do xóa dòng/cột, dán đè, hoặc link file bị đứt. Cách sửa nhanh nhất: nhấn Ctrl+Z ngay sau khi xóa nhầm, hoặc dùng hàm IFERROR để ẩn lỗi tạm thời. 6 nguyên nhân chính: xóa cột/dòng, VLOOKUP sai vùng, INDEX sai tọa độ, INDIRECT link file đã đóng, OLE/DDE lỗi, tham chiếu cấu trúc không hỗ trợ. Phòng tránh: dùng tham chiếu vùng (range reference) thay vì tham chiếu từng ô, khóa trang tính, và chuyển dữ liệu quan trọng lên hệ thống có cấu trúc.

Lỗi #REF! là gì?

#REF! (viết tắt của Reference — tham chiếu) là lỗi Excel hiển thị khi một công thức tham chiếu đến ô hoặc vùng dữ liệu không còn hợp lệ. Nguyên nhân phổ biến nhất: các ô được công thức tham chiếu bị xóa hoặc bị dán đè. (Nguồn: Microsoft Support)

Nói dễ hiểu: Excel đang giơ hai tay lên trời và bảo "Tao mất dấu cái ô mày kêu tao tính rồi, tao chịu."

6 nguyên nhân gây lỗi #REF! (và cách sửa từng cái)

Dưới đây là 6 trường hợp mà Microsoft Support liệt kê là nguyên nhân gây ra lỗi #REF!. Mỗi mục đều có cách sửa cụ thể.

1. Xóa cột hoặc dòng đang được tham chiếu

Đây là nguyên nhân phổ biến nhất. Bạn có công thức =SUM(B2,C2,D2) ở cột E. Rồi ai đó (có thể là bạn, có thể là thằng cùng phòng) xóa cột C. Kết quả: công thức biến thành =SUM(B2,#REF!,C2). Excel không biết #REF! là cái gì nên nó báo lỗi. (Nguồn: Microsoft Support)

Cách sửa:

• Ngay lập tức: Nhấn Ctrl+Z (Undo) — Microsoft khuyên đây là cách nhanh nhất nếu bạn vừa xóa nhầm.
• Về lâu dài: Thay vì viết =SUM(B2,C2,D2), hãy viết =SUM(B2:D2) — tham chiếu vùng (range reference). Khi xóa bất kỳ cột nào trong khoảng B:D, Excel tự động điều chỉnh công thức. (Nguồn: Microsoft Support)

💡 Mẹo vặt: Nếu bạn thấy team mình cứ xoành xoạch dính lỗi này, đọc bài 7 dấu hiệu file Excel đang giết doanh nghiệp của bạn để biết còn bao nhiêu "quả bom" khác đang chờ phát nổ.

2. VLOOKUP với tham chiếu sai vùng

Công thức =VLOOKUP(A8, A2:D5, 5, FALSE) — bạn bảo Excel "lấy giá trị ở cột thứ 5", nhưng vùng tham chiếu A2:D5 chỉ có 4 cột (A, B, C, D). Excel bảo: "Cột thứ 5 đâu?", rồi hiện #REF!. (Nguồn: Microsoft Support)

Cách sửa:

• Mở rộng vùng tham chiếu: =VLOOKUP(A8, A2:E5, 5, FALSE) — thêm cột E vào vùng.
• Hoặc giảm số cột lookup: =VLOOKUP(A8, A2:D5, 4, FALSE).

3. Hàm INDEX với tọa độ hàng/cột sai

Công thức =INDEX(B2:E5, 5, 5) — INDEX range B2:E5 chỉ có 4 hàng và 4 cột, nhưng bạn lại yêu cầu lấy ô ở hàng 5, cột 5. Excel không có siêu năng lực để đọc hàng từ trên trời rơi xuống. (Nguồn: Microsoft Support)

Cách sửa:

• Điều chỉnh tham chiếu hàng hoặc cột nằm trong vùng INDEX: =INDEX(B2:E5, 4, 4) sẽ trả về kết quả hợp lệ.

4. Hàm INDIRECT tham chiếu file đã đóng

INDIRECT là hàm linh hoạt, nhưng nó không thích tham chiếu đến workbook đã đóng. Bạn viết công thức INDIRECT trỏ đến một file Excel khác — nếu file đó không được mở, Excel trả về #REF! ngay lập tức. (Nguồn: Microsoft Support)

Tương tự với dynamic array functions khi tham chiếu workbook đã đóng.

Cách sửa:

• Mở workbook được tham chiếu trước khi mở file chính.

5. Lỗi tham chiếu ô không hợp lệ (Invalid Cell Reference)

Khi bạn di chuyển hoặc xóa ô khiến tham chiếu bị đứt, hoặc một hàm trả về lỗi tham chiếu. Đây là trường hợp "tổng hợp" — nó có thể xảy ra từ nhiều thao tác khác nhau. (Nguồn: Microsoft Support)

Cách sửa:

• Kiểm tra công thức, xác định ô nào bị lỗi, và điều chỉnh lại vùng tham chiếu.
• Dùng IFERROR để bọc công thức nếu muốn ẩn lỗi tạm thời: =IFERROR(công_thức_gốc, "")

6. Sự cố OLE và DDE

Ít gặp hơn, nhưng nếu bạn dùng Object Linking and Embedding (OLE) hoặc Dynamic Data Exchange (DDE) để kết nối Excel với ứng dụng khác, các link này có thể trả về #REF! khi chương trình nguồn không chạy hoặc cài đặt Trust Center chặn nội dung bên ngoài. (Nguồn: Microsoft Support)

Cách sửa:

• Khởi động chương trình mà OLE link đang gọi.
• Kiểm tra cài đặt Trust Center trong File → Options → Trust Center → Trust Center Settings → External Content. (Nguồn: Microsoft Support)

Dấu hiệu nhận biết sớm: file Excel sắp "bể"

Lỗi #REF! thường không xuất hiện một mình. Nếu file bạn đang có những dấu hiệu sau, xác suất cao #REF! sẽ ghé thăm trong tương lai gần:

• Nhiều người cùng mở và sửa một file — ai cũng có quyền xóa dòng, cột.
• File chứa hàng tá công thức VLOOKUP/INDEX tham chiếu chéo giữa các sheet.
• Dữ liệu được copy từ file này sang file khác, link file bị đứt.
• Không ai nhớ đã xóa cái gì, lúc nào.

Nếu 3/4 dấu hiệu trên đúng với bạn, hãy đọc luôn bài Kịch bản rủi ro khi quản lý bằng Excel — bạn đang ở cảnh nào? trước khi quá muộn.

Mẹo phòng tránh #REF! (Microsoft khuyên gì)

Microsoft Support có một vài khuyến nghị đơn giản nhưng cực kỳ hiệu quả:

1. Dùng range reference, không dùng explicit cell reference. =SUM(B2:D2) thay vì =SUM(B2,C2,D2). Khi bạn xóa cột, Excel tự động co giãn range. (Nguồn: Microsoft Support)
2. Khóa trang tính. Vào Review → Protect Sheet, chỉ cho phép người dùng nhập liệu vào ô input, không cho xóa dòng/cột. Lưu ý: cái này chỉ ngăn thao tác trực tiếp, không ngăn người khác xóa dữ liệu ở file nguồn.
3. Dùng IFERROR che chắn. =IFERROR(VLOOKUP(...), "Đang cập nhật") — dân Excel gọi đây là "cái phao cứu sinh" mỗi khi không biết dữ liệu có tồn tại hay không.
4. Tách biệt dữ liệu thô và báo cáo. Dữ liệu gốc để một sheet, công thức và báo cáo để sheet khác. Không ai được động vào sheet dữ liệu gốc — chỉ được thêm dòng mới, không được xóa.

#REF! và vấn đề mở rộng

Đến đây có một thật thú vị (và hơi buồn): lỗi #REF! không phải là lỗi Excel tồi tệ nhất. Nó chỉ là triệu chứng của một vấn đề lớn hơn — cách bạn tổ chức dữ liệu.

Bạn có thể vá lỗi này, lỗi kia trong Excel. Nhưng khi công ty bạn có 50 nhân viên, mỗi người một file Excel riêng, mỗi tối đều copy-paste số liệu — thì #REF! chỉ là một trong hàng tá vấn đề. Số liệu lệch, nhập chồng chéo, mất lịch sử thay đổi... ngốn vài chục giờ mỗi tháng.

Bài toán này giống như sửa một cái bánh xe bị xẹp trên chiếc xe có 3 bánh kia cũng sắp xì hơi — ngồi vá từng cái cũng được, nhưng có khi nên thay luôn bộ lốp.

Đọc tiếp bài Chi phí thực sự của việc dùng Excel để quản lý doanh nghiệp để thấy mỗi tháng team bạn đang mất bao nhiêu thời gian và tiền bạc cho những việc không tên — mà không bao giờ được ghi trong báo cáo.

Câu hỏi thường gặp

Tóm lại

Lỗi #REF! không phải là tận thế. Nhấn Ctrl+Z được. Sửa lại công thức được. Nhưng nếu bạn thấy nó xuất hiện thường xuyên, đó là hồi chuông cảnh tỉnh — dữ liệu của bạn đang quản lý theo kiểu "chắp vá" và đã đến lúc nghĩ đến một hệ thống có tổ chức hơn.

Tác giả: Exceltoapp.vn