엑셀 #REF! 오류 해결 — 참조가 깨지는 4가지 상황과 방지법

Posted on by

누군가 “안 쓰는 열”이라며 C열을 지웠는데, 합계 열이 #REF!로 퍼지면 참조 주소가 깨진 겁니다. 쉽게 말하면 “수식이 가리키던 셀이 사라졌다”는 신호예요.

#VALUE!나 #DIV/0!는 데이터 형식이나 분모 문제라 원인을 좁히기 쉬운데, #REF!는 이미 참조가 사라진 뒤에 발견되는 경우가 많습니다. 누가 열을 지웠는지, 어떤 시트가 삭제됐는지, 어떤 범위를 복사해 덮었는지 다시 추적해야 하거든요.

Microsoft도 #REF!를 “수식이 유효하지 않은 셀을 참조할 때 표시되는 오류”로 설명합니다. 가장 흔한 원인은 수식이 참조하던 셀을 삭제하거나 다른 값으로 덮어쓴 경우입니다.

저도 재무팀 시절 분기 결산 파일에서 이 오류를 한 번 크게 맞은 적이 있어요. 담당자가 중간에 “안 쓰는 열”이라며 C열을 지웠는데, 합계 열 수식이 =SUM(B2,C2,D2) 형태였거든요. 저장하고 나서야 E열 전체가 #REF!로 무너진 걸 발견했습니다. 그때 배운 게, 오류 셀 개수보다 누가·언제·무엇을 지웠는지를 먼저 추적하는 게 빠르다는 거예요.

먼저 30초 진단표부터 보세요

증상 흔한 원인 바로 볼 곳 해결 방향
=SUM(B2,#REF!,D2) 참조 열/행 삭제 수식 입력줄 범위 참조로 수정
VLOOKUP에서 #REF! 열 번호 초과 세 번째 인수 범위 열 수와 번호 확인
INDEX에서 #REF! 행/열 번호 초과 row_num, column_num 범위 크기 안으로 조정
INDIRECT에서 #REF! 닫힌 외부 파일/잘못된 주소 참조 텍스트 파일 열기 또는 직접 참조
복붙 후 오류 수식 참조 대상 덮어씀 붙여넣기 위치 값 붙여넣기 사용
시트 삭제 후 오류 삭제된 시트 참조 수식의 시트명 백업에서 복구 또는 수식 재작성

#REF!는 “표시만 숨기기”보다 “깨진 참조를 복구하기”가 먼저입니다. IFERROR로 덮으면 보기에는 조용해지지만, 실제 계산 구조는 계속 깨진 상태일 수 있습니다.

따라 할 예제 데이터

아래 표를 입력해 보세요.

A열: 지점 B열: 1월 C열: 2월 D열: 3월 E열: 합계
강남 100 120 130 =SUM(B2,C2,D2)
종로 80 90 100 =SUM(B3,C3,D3)

이 상태에서 C열을 삭제하면 E열 수식이 깨질 수 있습니다.

=SUM(B2,#REF!,C2)

이게 #REF!입니다. 수식 안에 있던 C2가 삭제되면서 엑셀이 더 이상 어디를 참조해야 할지 모르는 상태가 된 겁니다.

상황 1. 행이나 열을 삭제해서 참조가 깨짐

가장 흔한 원인입니다.

아래처럼 개별 셀을 하나씩 더하는 수식은 열 삭제에 약합니다.

=SUM(B2,C2,D2)

중간 열을 삭제하면 수식 안에 #REF!가 끼어듭니다.

가능하면 연속 범위로 쓰는 편이 안전합니다.

=SUM(B2:D2)

Microsoft 공식 문서도 개별 셀 참조보다 범위 참조를 쓰면 열 삭제 시 수식이 더 잘 조정된다고 안내합니다.

물론 범위 참조도 무조건 안전한 건 아닙니다. 합계 범위 자체를 통째로 삭제하면 깨집니다. 그래도 =SUM(B2,C2,D2)보다는 =SUM(B2:D2)가 유지보수에 훨씬 낫습니다.

상황 2. VLOOKUP 열 번호가 범위보다 큼

VLOOKUP에서 #REF!가 나면 세 번째 인수인 열 번호를 먼저 보세요.

=VLOOKUP(A2,A:D,5,FALSE)

위 수식은 위험합니다. 범위는 A:D, 즉 4열인데 5번째 열을 가져오라고 했습니다. 없는 열을 요청했으니 #REF!가 납니다.

수정하려면 범위를 늘리거나 열 번호를 줄여야 합니다.

=VLOOKUP(A2,A:E,5,FALSE)

또는

=VLOOKUP(A2,A:D,4,FALSE)

실무에서는 열을 중간에 삽입/삭제하면서 이 오류가 자주 납니다. 그래서 중요한 파일에서는 XLOOKUP이나 INDEX/MATCH로 바꾸는 것도 방법입니다.

=XLOOKUP(A2,A:A,E:E)

XLOOKUP은 “몇 번째 열”이 아니라 “어느 열에서 반환할지”를 직접 지정하므로 열 번호 밀림에 덜 취약합니다.

상황 3. INDEX의 행/열 번호가 범위를 벗어남

INDEX도 범위보다 큰 행/열 번호를 요청하면 #REF!가 날 수 있습니다.

=INDEX(B2:E5,5,5)

B2:E5는 4행 x 4열입니다. 그런데 수식은 5행 5열을 달라고 합니다. 범위 밖을 요청했으니 오류가 납니다.

수정 예:

=INDEX(B2:E5,4,4)

또는 범위를 넓힙니다.

=INDEX(B2:F6,5,5)

INDEX를 쓸 때는 범위 크기와 행/열 번호가 맞는지 같이 확인하세요.

상황 4. 붙여넣기로 수식 참조 대상을 덮어씀

보고서 작업에서 은근히 많이 생깁니다.

예를 들어 다른 시트에서 계산 결과를 가져와 붙여넣는다고 해볼게요. 이때 수식이 들어 있는 셀 위에 값을 덮어쓰거나, 수식이 참조하던 원본 범위를 잘못 덮으면 참조가 깨질 수 있습니다.

복사한 결과만 보관하려면 일반 붙여넣기 대신 값 붙여넣기를 쓰세요.

Windows 기준:

Ctrl + Alt + V → V → Enter

또는 리본 메뉴에서:

홈 → 붙여넣기 → 값

Microsoft의 “깨진 수식 방지” 문서도 수식 결과를 다른 위치로 옮길 때는 값만 붙여넣는 방식을 안내합니다.

상황 5. INDIRECT가 닫힌 파일이나 잘못된 주소를 참조함

INDIRECT는 텍스트를 참조 주소로 바꾸는 함수입니다.

=INDIRECT("'1월'!B2")

시트 이름이 맞고 파일이 열려 있으면 동작합니다. 그런데 외부 파일을 INDIRECT로 참조할 때 원본 통합문서가 닫혀 있으면 #REF!가 날 수 있습니다.

이런 수식은 편하지만 관리가 어렵습니다.

=INDIRECT("'[매출.xlsx]1월'!B2")

파일명이 바뀌거나 닫혀 있거나 경로가 달라지면 문제가 생깁니다. 자주 쓰는 보고서라면 INDIRECT 대신 직접 참조, Power Query, 또는 통합 파일 구조로 바꾸는 편이 안전합니다.

깨진 참조를 찾는 방법

1. 수식 입력줄에서 #REF! 검색

오류 셀을 클릭하고 수식 입력줄을 보세요. #REF!가 어디에 들어갔는지 확인합니다.

=SUM(B2,#REF!,D2)

이 경우 중간 참조가 사라졌다는 뜻입니다. 원래 어떤 열이었는지 표 구조나 백업 파일에서 확인해야 합니다.

2. 찾기 기능 사용

시트 전체에서 #REF!를 찾을 수 있습니다.

Ctrl + F → #REF! 검색

범위를 “통합 문서”로 바꾸면 여러 시트의 오류를 한 번에 찾을 수 있습니다.

3. 오류 검사와 수식 계산 과정 보기

엑셀의 수식 탭에는 오류 검사와 수식 계산 과정 보기 기능이 있습니다.

수식 → 오류 검사
수식 → 수식 계산

복잡한 수식에서는 이 기능이 꽤 유용합니다. 어느 단계에서 참조가 깨지는지 순서대로 볼 수 있습니다.

복구 순서

  1. 먼저 Ctrl + Z가 가능한지 봅니다.

방금 열이나 시트를 삭제했다면 가장 빠른 해결은 실행 취소입니다.

Ctrl + Z
  1. 백업 파일과 비교합니다.

이미 저장하고 닫았다면 백업이나 이전 버전을 열어 원래 참조가 무엇이었는지 확인합니다.

  1. 수식을 직접 재작성합니다.

원래 참조가 확인되면 수식에서 #REF!를 지우고 올바른 범위를 넣습니다.

=SUM(B2:D2)
  1. 같은 패턴의 수식을 아래로 복사합니다.

한 셀만 고치면 끝이 아닐 수 있습니다. 같은 열의 다른 행도 같은 문제가 있는지 확인하세요.

  1. 원인을 막는 구조로 바꿉니다.

반복되는 파일이라면 수식만 고치지 말고 구조도 바꿔야 합니다. 개별 셀 참조를 범위 참조로 바꾸고, 열 번호에 의존하는 VLOOKUP을 줄이는 식입니다.

예방 체크리스트

  • 삭제 전, 해당 열/행을 참조하는 수식이 있는지 확인합니다.
  • 중요한 파일은 저장 전 복사본을 만듭니다.
  • =SUM(B2,C2,D2)보다 =SUM(B2:D2)를 우선 사용합니다.
  • VLOOKUP의 열 번호가 범위를 넘지 않는지 확인합니다.
  • 반복 보고서는 XLOOKUP, INDEX/MATCH, 표(Table), Power Query를 검토합니다.
  • 결과만 옮길 때는 값 붙여넣기를 사용합니다.
  • INDIRECT로 외부 파일을 참조하는 구조는 최소화합니다.

자주 하는 실수

1. #REF!를 IFERROR로 숨김

IFERROR(수식,"")로 덮으면 화면은 깨끗해집니다. 하지만 참조는 여전히 깨져 있습니다. #REF!는 원인 복구가 먼저입니다.

2. 삭제 후 저장해버림

열 삭제 직후에는 Ctrl + Z로 되돌릴 수 있습니다. 그런데 저장하고 닫은 뒤에는 복구가 어려워집니다. 중요한 파일은 삭제 전 복사본을 남기세요.

3. VLOOKUP 열 번호를 고정해놓고 표 구조를 바꿈

=VLOOKUP(A2,A:D,4,FALSE)는 표 구조가 유지될 때는 괜찮습니다. 중간 열을 추가하거나 삭제하면 가져오는 값이 달라지거나 오류가 날 수 있습니다.

4. INDIRECT를 만능 참조처럼 씀

INDIRECT는 편하지만 파일명, 시트명, 열 주소가 바뀌면 약합니다. 외부 파일 참조에서는 특히 조심해야 합니다.

5. 오류 셀 하나만 고침

같은 수식이 아래로 복사되어 있다면 오류가 여러 행에 퍼져 있을 수 있습니다. 한 셀을 고친 뒤 같은 열 전체를 확인하세요.

강의에서 자주 나오는 추가 질문

수강생이 #REF!를 보면 가장 먼저 하는 실수는, 수식을 고치기 전에 원본 구조가 어떻게 바뀌었는지를 확인하지 않는 겁니다. “어제까지 됐는데 오늘 안 돼요”라고 하면 저는 먼저 Ctrl + Z가 가능한지, 백업 파일이 있는지, 최근에 열·시트·붙여넣기 작업이 있었는지부터 물어봐요. 원인 이벤트를 못 찾으면 같은 수식을 열 번 고쳐도 다시 깨집니다.

또 하나는 VLOOKUP 열 번호만 보고 범위는 안 보는 경우예요. =VLOOKUP(A2,A:D,5,FALSE)에서 5가 문제인 건 한눈에 보이는데, 범위가 A:D인지 A:E인지 헷갈리면 수정 방향이 갈립니다. 저는 수식 입력줄에서 범위를 드래그해 보며 “지금 몇 열까지 잡혀 있지?”를 먼저 확인하라고 말해요. 엑셀 INDEX·MATCH·XLOOKUP 실무 글처럼 열 번호에 덜 의존하는 구조로 바꾸면 재발이 확 줄어요.

마지막으로, 여러 시트를 묶은 통합 보고서에서는 시트 이름 변경도 #REF!를 부릅니다. ='1월'!B2가 있는데 시트명이 1월_실적으로 바뀌면 참조가 깨져요. 시트명을 바꿀 때는 “이 시트를 참조하는 수식이 있는지”를 먼저 검색하는 습관이 필요합니다. Ctrl + F로 시트명 일부를 통합 문서 범위에서 찾아보면 1분 안에 감이 옵니다.

검증과 보안

#REF! 파일은 회사 데이터가 섞인 경우가 많습니다. 백업에서 복구할 때 원본 통합문서 전체를 외부에 올리지 마세요. 특히 AI 챗봇에 수식과 시트 구조를 붙여넣어 “고쳐줘”라고 하면, 거래처명·금액·내부 코드가 그대로 노출될 수 있어요. 저는 강의 때도 가명 데이터로만 복구 순서를 보여줍니다.

복구 후에는 반드시 합계·교차 검증을 한 번 더 돌리세요. #REF!가 사라졌다고 끝이 아닙니다. 열 삭제 과정에서 다른 수식의 참조 범위가 조용히 줄었을 수도 있거든요. 예를 들어 =SUM(B2:D10)이 =SUM(B2:C10)으로 바뀌었는데 오류 표시 없이 합계만 틀어지는 경우가 있습니다. 복구 직후에는 이전 백업과 합계 행을 나란히 놓고 숫자가 같은지 비교하는 게 안전해요.

오류별 관련 글

FAQ

#REF! 오류는 무슨 뜻인가요?

수식이 더 이상 유효하지 않은 셀이나 범위를 참조한다는 뜻입니다. 참조하던 행, 열, 시트, 외부 파일이 삭제되거나 바뀌었을 때 자주 발생합니다.

#REF! 오류는 IFERROR로 처리해도 되나요?

표시만 숨기는 목적이라면 가능하지만 권장하지 않습니다. 참조가 깨진 상태라 계산 결과를 신뢰하기 어렵습니다. 먼저 수식 안의 #REF!를 올바른 참조로 고쳐야 합니다.

삭제한 열 때문에 #REF!가 났어요. 바로 복구할 수 있나요?

방금 삭제했다면 Ctrl + Z가 가장 빠릅니다. 이미 저장하고 닫았다면 백업 파일이나 이전 버전에서 원래 참조를 확인해야 합니다.

VLOOKUP에서 #REF!가 나는 이유는 뭔가요?

세 번째 인수인 열 번호가 지정한 범위보다 클 때 자주 납니다. 예를 들어 범위가 A:D인데 5번째 열을 가져오라고 하면 #REF!가 납니다.

INDEX에서도 #REF!가 나나요?

네. 지정한 범위보다 큰 행 번호나 열 번호를 요청하면 #REF!가 날 수 있습니다.

INDIRECT는 왜 #REF!가 자주 나나요?

텍스트로 만든 참조 주소가 실제로 존재하지 않거나, 외부 원본 파일이 닫혀 있거나, 시트명이 바뀌면 참조가 깨질 수 있습니다.

#REF!를 예방하는 가장 좋은 습관은 뭔가요?

삭제 전 백업, 개별 셀보다 범위 참조 사용, 값 붙여넣기, VLOOKUP 열 번호 점검, 외부 참조 최소화입니다. 반복 보고서는 표(Table)나 Power Query로 구조를 안정화하는 것도 좋습니다.

한 줄 요약

#REF!는 수식이 가리키던 주소가 사라졌다는 뜻입니다. 행·열 삭제, 붙여넣기, VLOOKUP 열 번호 초과, INDEX 범위 초과, INDIRECT 외부 참조를 먼저 확인하세요. IFERROR로 숨기기보다 원래 참조를 복구하고, 반복 파일은 범위 참조·값 붙여넣기·XLOOKUP·Power Query처럼 덜 깨지는 구조로 바꾸는 게 안전합니다.

참고 문서

작성자 소개

최지원

대기업 재무팀 8년, 직무교육 강사 4년. 엑셀·VBA·파이썬·AI 업무 자동화를 실무와 강의에서 검증해 정리합니다. 책에서 본 것이 아니라 직접 써본 것만 씁니다.

WP 게시물 작성자의 글 보기