엑셀 VALUE 오류 뜨는 이유와 해결 순서, 공백 때문일까요?

어제까지 멀쩡하던 합계 셀이 갑자기 엑셀 VALUE 오류로 바뀌어 보고서 제출 직전에 당황했던 적이 있습니다. 매출 집계표를 손보다 겪은 일인데, 원인은 의외로 작은 셀 하나에 섞여 있던 보이지 않는 공백이었습니다.

그날 표는 거래처 32곳의 월별 매출과 수량을 더하는 단순한 표였는데, 마지막 거래처의 수량 셀 하나에만 스페이스바가 눌려 있었습니다.

화면만 봐서는 ‘120’과 ‘120 ‘을 구분할 방법이 없어서, 결국 셀을 하나씩 눌러가며 원인을 찾아야 했습니다. 이런 일은 ERP 시스템이나 외부 웹사이트에서 데이터를 복사해 붙여넣을 때 특히 자주 생깁니다.

이 글은 숫자 계산 수식에서 갑자기 VALUE 오류를 만난 사무직 직장인 기준으로, 원인을 좁혀가는 순서대로 정리했습니다.

엑셀 VALUE 오류는 대부분 수식이 참조하는 셀에 숫자 대신 텍스트나 공백이 섞여 있어서 생기므로, 오류 셀을 선택해 수식 입력줄에서 참조 범위부터 확인하는 것이 가장 빠른 해결책입니다.

엑셀 VALUE 오류, 왜 자꾸 뜨는 걸까요

엑셀 VALUE 오류는 수식이 기대하는 데이터 형식과 실제 셀 값이 서로 맞지 않을 때 나타나는 오류입니다.

예를 들어 B2에 숫자 대신 텍스트 한 글자라도 섞여 있으면 =B2+C2+D2 같은 덧셈 수식은 바로 VALUE 오류를 반환합니다.

반대로 같은 데이터라도 =SUM(B2:D2)처럼 합계 함수를 쓰면 텍스트 셀을 그냥 건너뛰고 숫자만 더하기 때문에 오류가 안 뜨는 경우도 많습니다.

저도 처음엔 이 차이를 몰라서 수식을 여러 번 지웠다 다시 썼는데, 알고 보니 함수 종류 자체를 바꾸는 게 더 빠른 해결책이었습니다.

텍스트를 합치는 & 연산자나 CONCATENATE, TEXTJOIN 함수에서도 이 오류가 자주 나타납니다. 참조한 셀 중 하나가 이미 다른 오류 값을 담고 있으면, 그 오류가 연결된 수식 전체로 그대로 번지기 때문입니다.

VLOOKUP이나 INDEX, MATCH처럼 범위를 비교하는 함수에서도 찾는 범위와 반환 범위의 행·열 개수가 서로 다르면 같은 VALUE 오류가 뜰 수 있습니다.

엑셀 VALUE 오류 해결하는 단계별 방법

가장 먼저 오류 셀을 클릭해 수식 입력줄에서 어떤 셀을 참조하는지부터 눈으로 확인하는 것이 순서입니다. 아래 순서대로 따라 하면 스크린샷 없이도 메뉴를 찾을 수 있습니다.

순서를 건너뛰고 바로 TRIM이나 IFERROR부터 씌우면 오류 자체는 안 보이게 되어도 원인이 된 셀은 그대로 남아 다음 수식에서 또 같은 문제가 생길 수 있습니다. 그래서 아래 순서는 되도록 건너뛰지 않고 차례로 확인하는 것을 권장합니다.

1. 오류가 난 셀을 클릭한 뒤 상단 수식 입력줄에서 참조된 셀 범위를 확인한다.
2. 엑셀 상단 수식 탭 > 오류 검사 메뉴를 눌러 시트 안의 오류를 하나씩 순서대로 훑는다.
3. 오류 셀만 따로 모아 보려면 홈 탭 > 찾기 및 선택 > 이동 옵션을 누르고, 대화상자에서 ‘수식’을 고른 뒤 그 아래 ‘오류’ 항목만 체크하고 확인을 누른다.
4. 의심되는 셀을 선택해 =LEN(셀주소) 수식으로 글자 수를 확인한다. 숫자만 들어 있어야 할 셀의 글자 수가 이상하게 많다면 공백이 숨어 있다는 뜻이다.
5. 텍스트 쪽 공백은 =TRIM(셀주소) 함수로, 숫자 열에만 공백이 섞였다면 Ctrl+H 찾기 및 바꾸기로 스페이스를 모두 지운다.
6. 날짜가 원인으로 의심되면 =DATEVALUE(셀주소) 로 텍스트 날짜를 실제 날짜 값으로 바꿔 계산한다.

실제로 적용해 보니 4번 단계에서 LEN 값이 기대보다 1~2 더 크게 나오는 셀을 찾으면 거의 그 자리가 범인이었습니다. 눈으로 봐서는 구분이 안 되는 공백이라 이 단계를 건너뛰면 한참을 헤매게 됩니다.

참고로 Ctrl와 물결표(`) 키를 함께 누르면 시트 전체가 수식 보기 모드로 바뀌어, 어떤 셀이 직접 입력한 숫자이고 어떤 셀이 수식 결과인지 한눈에 비교할 수 있습니다.

TRIM이나 SUBSTITUTE로 값을 정리했다면 그 결과를 복사해 ‘값으로 붙여넣기’ 해두는 것이 좋습니다. 그래야 나중에 원본 수식이 있던 셀을 지우거나 옮기더라도 정리된 숫자가 그대로 남아 참조가 끊기지 않습니다.

단계대로 해도 안 될 때 원인별로 뭘 봐야 하나요

단계를 따라 했는데도 엑셀 VALUE 오류가 그대로라면 수식 구조 자체나 셀 서식 문제일 가능성이 큽니다. 아래 표로 증상별 점검 포인트를 정리했습니다.

LEN 함수로 잰 글자 수가 눈에 보이는 자릿수보다 1~2 많이 나오는데도 TRIM이 통하지 않는다면 대부분 이 CHAR(160) 공백이 원인이었습니다.

특히 웹페이지나 PDF 표에서 복사해온 숫자에 자주 섞여 있어서, 일반 TRIM만 믿고 넘어가면 오류가 계속 남게 됩니다.

엑셀 수식 오류를 고치다 보면 결국 파일 자체가 꼬여 다른 오류 메시지까지 겹치는 경우도 있는데, 이때는 문서 파일이 안전모드로도 안 열릴 때 점검하는 순서를 참고하면 원인을 좁히는 데 도움이 됩니다.

엑셀 오류 코드별로 어떻게 다른가요

VALUE 오류 말고도 엑셀에는 여러 오류 코드가 있고, 코드마다 원인이 다릅니다. 헷갈리기 쉬운 코드만 추려 비교했습니다.

표에 정리한 여섯 가지 오류 코드는 생김새는 비슷해 보여도 고치는 방법이 전혀 다르므로, 수식을 통째로 다시 쓰기 전에 오류 메시지부터 정확히 읽는 것이 더 빠릅니다.

오류 메시지를 아예 다른 값으로 바꿔 보여주고 싶다면 =IFERROR(수식, 대체값) 형태로 감싸면 됩니다.

다만 IFERROR는 모든 오류를 똑같이 가려버리므로, 먼저 원인을 찾아 고친 뒤 마지막 안전장치로만 쓰는 게 순서에 맞습니다.

참고로 #N/A만 따로 구분해 처리하고 싶다면 IFNA 함수를 쓰면 다른 오류까지 묻히는 걸 막을 수 있습니다.

같이 알아두면 좋은 엑셀 오류 팁

VALUE 오류를 자주 겪는다면 평소 데이터 입력 습관을 조금만 바꿔도 재발을 줄일 수 있습니다.

저는 그 뒤로 외부에서 데이터를 받으면 바로 계산하지 않고 빈 열에 CLEAN과 TRIM을 한 번 씌워 정리부터 합니다.

• 외부에서 복사해온 표는 계산 전에 TRIM, CLEAN 함수로 한 번 정리한다.
• 날짜 열은 셀 서식에서 ‘날짜’로 지정되어 있는지 입력 전에 미리 확인한다.
• 숫자 열에 천 단위 쉼표나 단위(원, 개)를 직접 입력하지 말고 셀 서식의 표시 형식으로만 적용한다.
• 매달 똑같은 형식으로 받는 보고서라면, 데이터를 받는 즉시 빈 열 하나에 TRIM과 CLEAN을 걸어두고 그 결과만 복사해 쓰는 습관을 들이면 매번 오류 위치를 찾아다니지 않아도 됩니다.
• 데이터 탭의 ‘텍스트 나누기’ 기능으로도 텍스트로 인식된 숫자 열을 빠르게 일반 숫자로 되돌릴 수 있습니다. 20~30줄 정도의 작은 표라면 수식보다 이 기능 하나로 더 빨리 끝나는 경우가 많습니다.
• 오류가 반복되는 파일이라면 평소 프로그램 자체 설치 상태도 의심해볼 수 있는데, 이럴 땐 윈도우 프로그램 설치 오류를 원인별로 확인하는 순서를 참고해 재설치 여부를 판단하면 됩니다.

엑셀 수식 오류의 기본 개념과 코드별 설명은 마이크로소프트 공식 지원 문서에서도 확인할 수 있습니다. 더 자세한 설명이 필요하면 마이크로소프트 공식 지원 페이지를 참고하시기 바랍니다.

자주 묻는 질문

엑셀 VALUE 오류는 알고 보면 데이터 하나하나의 형식 문제인 경우가 대부분이라, 순서대로 좁혀가면 생각보다 빨리 해결됩니다. 여러분은 VALUE 오류를 겪을 때 주로 어떤 원인이었나요, 댓글로 경험을 나눠주시면 다음 글에서 더 자세히 다뤄보겠습니다.

출처: Editlab, https://editlab.luvpp.com