Lỗi #VALUE! trong Excel: nguyên nhân và cách sửa từng trường hợp

#VALUE! là một trong những lỗi công thức gặp nhiều nhất trong Excel và cũng khó đoán nhất, vì cùng một thông báo có thể xuất phát từ phép cộng đơn giản, ngày tháng nhập sai hay một hàm như SUMIFS, VLOOKUP, FIND. Bài này chia lỗi theo từng tình huống bạn hay gặp, kèm ví dụ và cách sửa cụ thể, để bạn tìm đúng nguyên nhân thay vì chỉ che lỗi đi.

Lỗi #VALUE! trong Excel là gì?

Excel trả về #VALUE! khi một đối số hoặc một ô trong công thức có kiểu dữ liệu không dùng được cho phép tính đó. Khi rê chuột vào biểu tượng cảnh báo cạnh ô, bạn sẽ thấy dòng “A value used in the formula is of the wrong data type” (giá trị dùng trong công thức sai kiểu dữ liệu).

Các trường hợp phổ biến nhất:

• Cộng, trừ, nhân, chia một ô chứa chữ hoặc khoảng trắng.
• Số bị lưu dưới dạng văn bản vì dấu chấm, dấu phẩy không khớp với cài đặt vùng của máy.
• Ngày tháng thực chất là văn bản nên không trừ được.
• Hàm nhận đối số không hợp lệ: SUMIFS có vùng không cùng kích thước, FIND không tìm thấy chuỗi, VLOOKUP có số cột nhỏ hơn 1…
• Công thức tham chiếu tới một ô đang bị lỗi #VALUE! khác, lỗi “lan” sang ô mới.

Bước 1: Tìm đúng ô và bước gây lỗi

Đừng sửa mò. Với công thức dài, hãy để Excel chỉ ra chỗ sai trước.

Dùng Evaluate Formula

1. Chọn ô đang báo #VALUE!.
2. Vào tab Formulas, nhóm Formula Auditing, chọn Evaluate Formula.
3. Bấm Evaluate nhiều lần. Excel tính từng phần của công thức; phần gạch chân ngay trước khi kết quả biến thành #VALUE! chính là chỗ cần sửa.

Kiểm tra ô là số hay văn bản

• Nhìn căn lề: khi ô để căn lề mặc định, số nằm bên phải, văn bản nằm bên trái. Một “số” nằm sát lề trái là dấu hiệu nó đang là văn bản.
• Dùng hàm kiểm tra: ở ô trống, nhập =ISTEXT(A2). Kết quả TRUE nghĩa là A2 là văn bản. Ngược lại, =ISNUMBER(A2) trả về TRUE nếu A2 là số thật.
• Đếm ký tự: =LEN(A2) cho biết số ký tự. Nếu ô “trống” mà LEN lớn hơn 0, ô đó đang chứa khoảng trắng hoặc ký tự ẩn.

Lỗi #VALUE! khi cộng, trừ, nhân

Các toán tử +, -, *, / yêu cầu mọi giá trị đều là số. Chỉ cần một ô chứa chữ, ký hiệu tiền tệ gõ tay (như “500k”, “1.200đ”) hoặc một dấu cách, kết quả sẽ là #VALUE!.

Dòng cuối cho thấy SUM bỏ qua văn bản trong vùng tham chiếu nên không báo lỗi. Tương tự, PRODUCT cũng bỏ qua văn bản khi nhân một vùng ô. Đây là cách nhanh để hết lỗi, nhưng hãy cẩn thận: nếu “300k” thực ra là một khoản cần tính, SUM sẽ cho kết quả sai mà không cảnh báo. Cách đúng là đưa dữ liệu về dạng số.

Cách sửa

1. Dùng =ISTEXT() hoặc nhìn căn lề để tìm các ô văn bản trong vùng tính.
2. Xoá phần chữ, đơn vị gõ tay (k, đ, VNĐ, kg…) và chỉ để lại số. Muốn hiển thị đơn vị, hãy dùng định dạng số: nhấn Ctrl + 1, chọn Custom và nhập ví dụ #,##0 "đ". Ô vẫn là số nên tính được.
3. Với ô trông trống nhưng có dấu cách, chọn ô rồi nhấn Delete để xoá hẳn.
4. Khi cần cộng nhiều ô rời rạc, dùng =SUM(A2,C2,E2) thay cho =A2+C2+E2; khi nhân, dùng =PRODUCT(A2:B2), nhưng chỉ sau khi đã chắc dữ liệu đúng.

Số bị lưu dạng văn bản do dấu chấm, dấu phẩy

Đây là nguyên nhân rất hay gặp ở Việt Nam. Người Việt quen viết 1.000.000 (dấu chấm ngăn hàng nghìn), trong khi Windows cài định dạng tiếng Anh lại hiểu dấu chấm là dấu thập phân. Excel không đọc được “1.000.000” là số, nên giữ nguyên nó dưới dạng văn bản, và phép tính với ô đó báo #VALUE!. Trường hợp ngược lại cũng xảy ra khi bạn mở file từ máy dùng định dạng khác, hoặc dán số liệu từ web, email, phần mềm kế toán.

Để biết máy đang dùng dấu nào, xem dòng cài đặt File > Options > Advanced > Editing options: nếu tích Use system separators, Excel dùng dấu của Windows.

Cách 1: Hàm NUMBERVALUE (gọn nhất)

Hàm NUMBERVALUE (có từ Excel 2013) chuyển văn bản thành số và cho phép bạn tự khai báo dấu thập phân và dấu ngăn hàng nghìn của dữ liệu gốc:

• Dữ liệu kiểu Việt Nam “1.250.000,5” ở ô A2: =NUMBERVALUE(A2, ",", ".") cho kết quả 1250000,5 (hiển thị theo định dạng máy bạn).
• Dữ liệu kiểu Mỹ “1,250,000.5”: =NUMBERVALUE(A2, ".", ",").

Kéo công thức cho cả cột, sau đó sao chép cột kết quả và dán bằng Paste Special > Values nếu muốn thay hẳn dữ liệu cũ.

Cách 2: Text to Columns (không cần công thức)

1. Chọn cột chứa số bị lỗi.
2. Vào Data > Text to Columns, chọn Next hai lần.
3. Ở bước 3, bấm Advanced…, đặt Decimal separator và Thousands separator đúng như dữ liệu gốc (ví dụ dữ liệu “1.000.000”: thousands là dấu chấm).
4. Bấm OK rồi Finish. Các số sẽ chuyển sang căn phải, tức đã là số thật.

Với số không có dấu phân cách nhưng vẫn bị lưu dạng văn bản (thường có tam giác xanh ở góc ô), bạn chỉ cần chọn vùng ô, bấm biểu tượng cảnh báo và chọn Convert to Number.

Khoảng trắng và ký tự ẩn khi dán dữ liệu từ web

Dữ liệu sao chép từ trang web hoặc phần mềm khác thường kèm dấu cách ở đầu, cuối số, hoặc một loại dấu cách đặc biệt là khoảng trắng không ngắt (non-breaking space, mã ký tự 160). Mắt thường không phân biệt được, nhưng Excel coi cả ô là văn bản.

Xoá dấu cách thường bằng Find & Replace

1. Chọn cột số cần làm sạch (không chọn cột chứa họ tên hay địa chỉ, vì dấu cách giữa các từ là cần thiết).
2. Nhấn Ctrl + H.
3. Ô Find what: gõ một dấu cách. Ô Replace with: để trống.
4. Bấm Replace All.

Xoá khoảng trắng không ngắt (ký tự 160)

Hàm TRIM chỉ xoá dấu cách thường, không xoá ký tự 160. Hãy dùng công thức:

=TRIM(SUBSTITUTE(A2, CHAR(160), ""))

Nếu kết quả vẫn căn trái, bọc thêm VALUE hoặc NUMBERVALUE để chuyển thành số. Cách không dùng công thức: trong hộp Find what của Ctrl + H, giữ phím Alt và gõ 0160 trên bàn phím số bên phải, rồi Replace All. Laptop không có bàn phím số thì nên dùng công thức trên.

Ngoài ra, CLEAN giúp xoá các ký tự điều khiển không in được (như ký tự xuống dòng lạ) thường đi kèm dữ liệu xuất từ phần mềm khác.

Lỗi #VALUE! khi trừ ngày tháng

Excel lưu ngày tháng dưới dạng số, nên =B2-A2 sẽ ra số ngày chênh lệch. Lỗi xảy ra khi một trong hai ô là ngày “giả”, tức văn bản trông giống ngày. Nguyên nhân thường gặp là máy dùng định dạng tháng/ngày/năm (kiểu Mỹ) nhưng bạn nhập ngày/tháng/năm: “31/12/2026” không phải ngày hợp lệ theo kiểu Mỹ, nên Excel giữ nó là văn bản. Ngày bị căn trái là dấu hiệu dễ nhận ra.

Cách sửa

• Text to Columns: chọn cột ngày, vào Data > Text to Columns, bấm Next hai lần, ở bước 3 chọn Date và chọn đúng thứ tự của dữ liệu gốc (DMY cho ngày/tháng/năm), rồi Finish. Cách này cũng loại bỏ dấu cách ở đầu ô.
• Hàm DATE: nếu văn bản luôn có dạng dd/mm/yyyy đủ 10 ký tự ở ô A2, tạo ngày thật bằng =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)), sau đó định dạng lại ô theo kiểu bạn muốn bằng Ctrl + 1.
• Về lâu dài: đặt định dạng ngày của Windows trùng với cách bạn nhập (Settings > Time & language > Language & region > Regional format), hoặc thống nhất nhập ngày theo dạng yyyy-mm-dd, kiểu mà Excel nhận diện ổn định.

Lỗi #VALUE! trong các hàm thường dùng

Hàm SUM, AVERAGE

SUM và AVERAGE bỏ qua văn bản, nhưng nếu trong vùng có một ô đang bị lỗi #VALUE!, kết quả cũng sẽ là #VALUE!. Cách đúng là sửa ô lỗi gốc. Nếu tạm thời cần tổng bỏ qua các ô lỗi, dùng =AGGREGATE(9, 6, A2:A100): số 9 là phép tính SUM, số 6 là tuỳ chọn bỏ qua giá trị lỗi. Thay 9 bằng 1 để tính trung bình.

Hàm IF

Hàm IF thường không tự sinh #VALUE! mà “nhận” lỗi từ ô nó tham chiếu. Ví dụ =IF(C2>1000000, "Thưởng", "") sẽ báo #VALUE! nếu C2 đang lỗi. Hãy dùng Evaluate Formula để lần ngược về ô gốc. Nếu muốn IF trả kết quả thay thế khi gặp lỗi, có thể viết =IF(ISERROR(C2), "Kiểm tra dữ liệu", IF(C2>1000000, "Thưởng", "")).

Hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS

• Tham chiếu tới file khác đang đóng: các hàm này chỉ đọc được dữ liệu từ workbook khác khi workbook đó đang mở. Mở file nguồn rồi nhấn F9 để tính lại; nếu cần chạy khi file nguồn đóng, chuyển sang dùng SUMPRODUCT hoặc đưa dữ liệu về cùng file.
• Vùng không cùng kích thước (SUMIFS, COUNTIFS): vùng tính tổng và mọi vùng điều kiện phải có cùng số dòng, số cột. Ví dụ =SUMIFS(D2:D100, B2:B100, "Hà Nội", C2:C90, ">0") báo lỗi vì C2:C90 ngắn hơn.
• Điều kiện dài quá 255 ký tự: chuỗi điều kiện vượt giới hạn này sẽ gây #VALUE!. Hãy rút gọn điều kiện hoặc dùng ký tự đại diện *.

Hàm VLOOKUP

VLOOKUP thường báo #N/A khi không tìm thấy, nhưng báo #VALUE! khi: đối số số thứ tự cột (col_index_num) nhỏ hơn 1, hoặc giá trị cần tìm dài hơn 255 ký tự. Kiểm tra lại đối số thứ ba và nếu phải tra chuỗi dài, dùng INDEX kết hợp MATCH hoặc XLOOKUP (Excel 2021, Microsoft 365).

Hàm FIND, SEARCH

Khi không tìm thấy chuỗi cần tìm, FIND và SEARCH trả về #VALUE! chứ không trả về 0. Đây là hành vi bình thường. Nếu chỉ cần biết có chứa chuỗi hay không, dùng =ISNUMBER(SEARCH("kế toán", A2)) để nhận TRUE/FALSE. Lưu ý FIND phân biệt chữ hoa, chữ thường; SEARCH thì không.

Hàm VALUE, DATEVALUE

Hai hàm này báo #VALUE! khi văn bản đầu vào không khớp với định dạng số hoặc ngày của máy, ví dụ =DATEVALUE("31/12/2026") trên máy dùng kiểu tháng/ngày/năm. Dùng NUMBERVALUE cho số và hàm DATE như hướng dẫn ở phần ngày tháng.

Công thức mảng trên Excel 2019 trở về trước

Một số công thức xử lý cả vùng, như =SUM(IF(B2:B100="Hà Nội", D2:D100)), trên Excel 2019, 2016 trở về trước phải được nhập bằng Ctrl + Shift + Enter. Nhấn Enter thường có thể làm công thức báo #VALUE!. Từ Excel 2021 và Microsoft 365, công thức mảng động chạy bằng Enter bình thường. Nếu file được chia sẻ cho người dùng Excel cũ, hãy cân nhắc dùng SUMIFS để tránh lỗi.

Lỗi #VALUE! khi chèn ảnh bằng hàm IMAGE

Hàm IMAGE (Microsoft 365) chèn ảnh vào ô từ một đường dẫn web. Hàm này trả về #VALUE! khi tệp ảnh có định dạng không được hỗ trợ. Hãy kiểm tra đường dẫn trỏ thẳng tới tệp ảnh (thường kết thúc bằng .png, .jpg, .gif…), không phải trang web chứa ảnh.

Một trường hợp hiếm: phép trừ đơn giản cũng báo lỗi

Nếu ngay cả =B1-A1 với hai số vừa gõ tay cũng ra #VALUE!, hãy kiểm tra cài đặt vùng của Windows: Microsoft ghi nhận lỗi này xảy ra khi List separator (dấu ngăn cách danh sách) bị đặt thành dấu trừ. Mở Control Panel, vào Region > Additional settings, đổi List separator thành dấu phẩy hoặc dấu chấm phẩy, rồi mở lại file.

Có nên dùng IFERROR để ẩn lỗi #VALUE!?

=IFERROR(công_thức, "") giúp bảng tính gọn hơn, nhưng nó che mọi loại lỗi, kể cả lỗi do dữ liệu sai mà bạn cần biết. Chỉ nên dùng khi bạn đã hiểu vì sao lỗi xuất hiện và chấp nhận được, ví dụ ô dữ liệu chưa nhập hoặc FIND không tìm thấy chuỗi. Với bảng lương, báo cáo tài chính, hãy sửa dữ liệu gốc thay vì ẩn lỗi.

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

Tại sao ô trông là số mà vẫn báo #VALUE!?

Vì ô đó đang là văn bản: có dấu cách ẩn, ký tự 160, đơn vị gõ tay hoặc dấu chấm, dấu phẩy không khớp cài đặt vùng. Dùng =ISNUMBER(A2) để kiểm tra; FALSE nghĩa là cần làm sạch dữ liệu.

Cộng với ô trống có bị #VALUE! không?

Ô trống thật sự được tính là 0, nên không gây lỗi. Nếu vẫn lỗi, ô đó có thể chứa dấu cách hoặc công thức trả về chuỗi rỗng "". Kiểm tra bằng =LEN(A2) hoặc dùng SUM thay cho phép cộng.

#VALUE! khác #N/A thế nào?

#VALUE! báo dữ liệu hoặc đối số sai kiểu; #N/A báo hàm tra cứu (VLOOKUP, MATCH, XLOOKUP) không tìm thấy giá trị cần tìm. Hai lỗi cần cách sửa khác nhau.

Google Sheets có lỗi #VALUE! không?

Có. Google Sheets cũng hiển thị #VALUE! khi phép tính gặp giá trị sai kiểu, và khi rê chuột vào ô sẽ có mô tả lý do cụ thể. Phần lớn cách làm sạch dữ liệu trong bài (xoá khoảng trắng, đổi văn bản thành số) áp dụng được, dù tên menu khác Excel.

Kết luận

Phần lớn lỗi #VALUE! đến từ dữ liệu chứ không phải công thức: số lưu dạng văn bản, khoảng trắng ẩn, ngày tháng sai định dạng. Hãy xử lý theo thứ tự: Evaluate Formula để tìm chỗ lỗi → kiểm tra ô bằng ISTEXT/ISNUMBER → làm sạch dữ liệu bằng Ctrl + H, Text to Columns hoặc NUMBERVALUE → kiểm tra đối số của hàm. Chỉ dùng IFERROR khi đã hiểu nguyên nhân. Muốn luyện tay, hãy mở file thực hành và tự sửa từng ô lỗi. Nếu file nhiều công thức đến mức Excel bị treo mỗi khi tính lại, xem thêm hướng dẫn Excel bị đứng phải làm sao.

---

Phạm vi hướng dẫn: Excel for Microsoft 365, Excel 2024, 2021, 2019 và 2016 trên Windows; phần lớn thao tác tương tự trên Excel for Mac. Tên menu trong bài theo giao diện tiếng Anh. Nội dung được đối chiếu với tài liệu Microsoft Support, cập nhật tháng 10/2026.

Video hướng dẫn nhanh

Xem tóm tắt các bước trong bài qua video ngắn dưới đây.

Nguồn tham khảo

• Microsoft Support: How to correct a #VALUE! error
• Microsoft Support: NUMBERVALUE function
• Microsoft Support: IMAGE function