엑셀 #REF!, #VALUE! 오류 1초 만에 숨기고 해결하는 법
엑셀 파일을 열었는데 갑자기 셀에 #REF!, #VALUE! 오류가 떠 있으면 당황스럽죠?
특히 보고서 제출 직전이거나, 상사에게 파일을 보내야 하는 상황이면 더 난감해요.
“어제까지만 해도 멀쩡했는데 왜 깨졌지?”
“수식은 맞는 것 같은데 왜 오류가 나지?”
“일단 화면에서 오류만 안 보이게 할 수 없을까?”
이럴 때 가장 빠르게 쓸 수 있는 방법이 바로 IFERROR 함수예요.
다만 중요한 점이 하나 있어요.★
오류를 숨기는 것과 오류를 해결하는 것은 다릅니다.
그래서 이번 글에서는 엑셀에서 #REF!, #VALUE! 오류가 생겼을 때 1초 만에 숨기는 방법과, 실제로 원인을 찾아 해결하는 방법까지 같이 정리해볼게요!
1. 엑셀 #REF! 오류는 왜 생길까?
#REF! 오류는 엑셀에서 참조하던 셀이나 범위가 사라졌을 때 생기는 오류예요.
쉽게 말하면 수식이 원래 바라보던 주소를 잃어버린 상태죠.
예를 들어 이런 상황에서 자주 나와요.
수식이 참조하던 행이나 열을 삭제했을 때
다른 시트의 셀을 참조했는데 그 시트를 삭제했을 때
복사한 수식이 잘못된 위치로 이동했을 때
VLOOKUP, INDEX, OFFSET 같은 함수의 참조 범위가 깨졌을 때
예를 들어 원래 수식이 이렇게 되어 있었다고 해볼게요.
=A2+B2
그런데 B열을 삭제하면 엑셀이 더 이상 B2를 찾을 수 없겠죠?
이때 수식이 깨지면서 #REF! 오류가 나올 수 있어요.
즉, #REF!는 “수식이 참조하던 위치가 없어졌다”는 신호로 보면 됩니다.
2. 엑셀 #VALUE! 오류는 왜 생길까?
#VALUE! 오류는 계산해야 하는 값의 형식이 맞지 않을 때 나오는 오류예요.
예를 들어 숫자를 계산해야 하는데 셀 안에 글자가 들어 있거나, 날짜처럼 보여도 실제로는 텍스트로 저장되어 있으면 문제가 생길 수 있죠.
자주 나오는 원인은 이런 것들이에요.
숫자처럼 보이지만 실제로는 텍스트인 경우
셀 안에 보이지 않는 공백이 있는 경우
날짜 형식이 깨진 경우
더하기, 빼기, 곱하기 계산에 글자가 섞인 경우
함수에 들어가야 할 값의 형식이 잘못된 경우
예를 들어 아래처럼 계산하면 오류가 날 수 있어요.
=A2+B2
A2에는 숫자 100이 있는데, B2에 확인중이라는 글자가 들어 있다면 엑셀은 계산을 못 하겠죠?
그래서 #VALUE! 오류가 뜨는 거예요.
3. 오류를 1초 만에 숨기는 IFERROR 함수
보고서나 업무표에서 일단 오류 표시를 안 보이게 만들고 싶다면 IFERROR를 쓰면 돼요.
기본 구조는 아주 간단합니다.
=IFERROR(기존수식, 오류일 때 보여줄 값)
예를 들어 기존 수식이 이거라면:
=A2/B2
이렇게 감싸주면 됩니다.
=IFERROR(A2/B2,"")
그러면 오류가 날 때 빈칸으로 보이게 돼요.
만약 빈칸 대신 “확인 필요”라고 표시하고 싶다면 이렇게 쓰면 되고요.
=IFERROR(A2/B2,"확인 필요")
실무에서는 개인적으로 빈칸보다 **“확인 필요”**를 더 추천해요.
빈칸으로 숨기면 오류가 사라진 것처럼 보여서 나중에 문제를 놓칠 수 있거든요.
4. VLOOKUP 오류도 IFERROR로 정리 가능
엑셀에서 오류가 자주 나는 대표 함수가 VLOOKUP이에요.
예를 들어 거래처명이나 제품명을 찾는 수식에서 값이 없으면 오류가 뜰 수 있죠.
기존 수식이 이렇게 되어 있다면:
=VLOOKUP(A2,$F$2:$G$20,2,FALSE)
이렇게 바꿔줄 수 있어요.
=IFERROR(VLOOKUP(A2,$F$2:$G$20,2,FALSE),"확인 필요")
이렇게 하면 찾는 값이 없거나 수식에 문제가 있을 때 #N/A, #VALUE! 같은 오류 대신 “확인 필요”가 표시돼요.
다만 여기서도 주의할 점!
IFERROR는 오류를 안 보이게 만드는 함수이지, 데이터 자체를 고쳐주는 함수는 아니에요.
그래서 중요한 파일이라면 “확인 필요”가 뜬 셀을 나중에 꼭 다시 봐야 해요.
5. #REF! 오류는 숨기기보다 원인 수정이 먼저
#VALUE! 오류는 데이터 형식을 고치면 해결되는 경우가 많아요.
그런데 #REF! 오류는 조금 더 조심해야 합니다.
왜냐하면 #REF!는 참조하던 셀 자체가 사라진 상태일 수 있기 때문이에요.
예를 들어 수식이 이렇게 깨져 있다면:
=SUM(A2:#REF!)
이건 그냥 IFERROR로 숨기기보다, 원래 어떤 범위를 더해야 했는지 먼저 확인해야 해요.
해결 순서는 이렇게 가면 됩니다.
오류가 난 셀을 클릭하기
수식 입력줄에서
#REF!가 들어간 위치 확인하기원래 참조해야 하는 셀이나 범위 찾기
삭제된 행·열·시트가 있는지 확인하기
올바른 범위로 다시 수정하기
예를 들어 원래 A2부터 C2까지 더해야 했다면 이렇게 고쳐야 해요.
=SUM(A2:C2)
즉, #REF!는 무조건 숨기기 전에 원래 수식 구조를 확인하는 것!
6. #VALUE! 오류는 공백과 텍스트 숫자부터 확인하기
#VALUE! 오류가 나면 가장 먼저 확인할 것은 공백과 숫자 형식이에요.
특히 다른 사람이 준 엑셀 파일이나, 시스템에서 내려받은 자료는 숫자처럼 보여도 실제로는 텍스트인 경우가 많아요.
이럴 때는 아래 방법을 써볼 수 있어요.
숫자 앞뒤 공백 제거
=TRIM(A2)
텍스트 숫자를 진짜 숫자로 변환
=VALUE(A2)
공백 제거 후 숫자로 변환
=VALUE(TRIM(A2))
예를 들어 A2에 100처럼 보이는 값이 있는데 계산이 안 된다면, 실제로는 앞뒤에 공백이 들어간 텍스트일 수 있어요.
그럴 때는 VALUE(TRIM(A2))로 정리한 뒤 계산하면 해결되는 경우가 많습니다.
7. 실무에서는 이렇게 쓰면 편해요
엑셀 오류를 처리할 때는 상황에 따라 다르게 접근하는 게 좋아요.
단순히 보기 싫은 오류라면:
=IFERROR(기존수식,"")
확인이 필요한 업무표라면:
=IFERROR(기존수식,"확인 필요")
계산 결과가 없을 때 0으로 처리해야 한다면:
=IFERROR(기존수식,0)
하지만 보고서나 정산표에서는 무조건 0으로 바꾸는 건 조심해야 해요.
오류가 0으로 바뀌면 실제 숫자인 것처럼 보여서 금액이나 수량이 틀어질 수 있거든요.
그래서 실무 기준으로는 이렇게 추천해요.
임시 확인용 파일: 빈칸 처리 가능
제출용 보고서: “확인 필요” 표시 추천
계산표·정산표: 오류 원인 수정 후 사용
금액 자료: 무조건 0 처리 금지
8. 엑셀 오류 처리 체크리스트
엑셀에서 #REF!, #VALUE! 오류가 나면 아래 순서대로 확인해보세요.
마무리
엑셀에서 #REF!, #VALUE! 오류가 뜨면 일단 당황할 수밖에 없어요.
하지만 원리만 알면 해결 순서는 생각보다 간단합니다.
정리하면 이렇게예요.
#REF!는 참조하던 셀이나 범위가 사라진 오류.#VALUE!는 계산할 값의 형식이 맞지 않는 오류.IFERROR는 오류를 빠르게 숨길 수 있는 함수.
하지만 중요한 파일에서는 오류를 숨기기보다 원인을 확인하는 것이 먼저!
빠르게 정리하고 싶을 때는 아래 수식만 기억해두세요.
=IFERROR(기존수식,"확인 필요")
이 수식 하나만 알아도 엑셀 오류 때문에 보고서 화면이 지저분해지는 일은 꽤 줄일 수 있어요.
다만 최종 제출 전에는 “왜 오류가 났는지” 한 번은 꼭 확인하기!
그게 진짜 엑셀 업무단축의 핵심이에요.★
◆ 같이 보면 좋은 글
