엑셀 작업을 하다 보면 갑자기 셀에 #REF!라는 붉은 오류가 등장하면서 멘붕이 오는 경우가 많습니다. 당황할 필요 없습니다. #REF! 오류는 수식이 참조하던 셀, 행, 열, 시트가 더 이상 존재하지 않을 때 발생하는 엑셀의 정확한 신호입니다. 이 글에서는 참고 자료를 바탕으로 이 오류를 발견한 순간부터 3초 안에 원인을 찾고 바로 해결하는 실전 팁 5가지를 정리했습니다.
#REF! 오류, 왜 뜨는 걸까
#REF!는 "Reference Error", 즉 참조 오류를 뜻합니다. 수식 안에 들어 있는 셀 주소가 가리키는 대상을 엑셀이 찾을 수 없을 때 발생합니다. 가장 흔한 원인은 다음과 같습니다.
- 셀, 행, 열을 삭제한 경우: 수식에서 직접 참조하던 셀(예:
=SUM(B5,C5,D5))을 삭제하면 참조가 깨집니다. 이때 엑셀은=SUM(B5,#REF!,C5)처럼 자동으로#REF!를 표시합니다. - 붙여넣기 과정에서 참조가 깨진 경우: 복사한 수식을 다른 위치에 붙여넣을 때 상대 참조가 깨져서 대상 범위를 벗어나면 오류가 발생합니다.
- 함수 인수 범위를 벗어난 경우:
VLOOKUP이나INDEX함수에서 조회 범위보다 큰 열 번호나 행 번호를 지정하면#REF!오류가 나타납니다. 예를 들어,=VLOOKUP(A8, A2:D5, 5, FALSE)는 조회 범위가 A-D열(4개 열)인데 5번째 열 값을 요구하므로 오류가 납니다. - 닫힌 외부 통합문서를 참조한 경우:
INDIRECT함수나 동적 배열 함수로 닫혀 있는 다른 엑셀 파일을 참조하려 하면#REF!가 표시됩니다. - OLE 링크 문제: 다른 프로그램(워드, 파워포인트 등)과 연결된 개체의 링크가 끊긴 경우도 원인이 될 수 있습니다.
오류 난 셀, 3초 만에 진단하는 법
문제를 빨리 찾으려면 오류가 발생한 셀을 클릭한 다음 수식 입력줄을 바로 확인하는 게 가장 빠릅니다. 수식 입력줄에 #REF!라는 글자가 들어간 부분이 보인다면 그게 바로 깨진 참조입니다.
예를 들어 =SUM(B5, #REF!, C5)처럼 보이면, 원래 C5 자리에 있던 D5나 다른 셀이 삭제된 것입니다. 이렇게 수식 입력줄에서 #REF!의 위치를 찾으면 어떤 부분이 문제인지 즉시 파악할 수 있습니다.
만약 수식 자체는 멀쩡해 보이는데도 오류가 난다면, 함수 인수가 범위를 벗어난 것은 아닌지 확인해야 합니다. VLOOKUP이나 INDEX, MATCH 등이 포함된 수식이라면 참조 범위와 요청하는 값의 위치가 일치하는지 꼭 점검하세요.
삭제한 열이나 행을 되살리는 가장 쉬운 방법
실수로 참조되던 열이나 행을 삭제해서 오류가 발생했다면, 가장 직접적인 해결책은 삭제한 열이나 행을 다시 삽입하는 것입니다.
#REF!오류가 발생한 셀을 선택합니다.- 수식 입력줄을 보고 어떤 참조가
#REF!로 바뀌었는지 확인합니다. 예를 들어=SUM(B5, #REF!, C5)에서 가운데 참조가 깨졌다면, 원래C5와D5사이에 있던 열이 삭제된 것입니다. - 오류가 발생한 위치(이 경우 C열과 D열 사이)에 마우스를 두고 우클릭한 후 삽입을 선택합니다.
- 빈 열이 삽입되면 수식 입력줄에서
#REF!부분을 올바른 셀 주소(예:C5)로 직접 수정하고 Enter를 누릅니다.
이 방법이 가장 직관적이지만, 데이터 구조가 복잡하거나 삭제된 위치를 정확히 모를 때는 수동 수정이 더 빠를 수 있습니다.
수식 직접 고치기 명시적 참조보다 범위 참조가 안전한 이유
=SUM(B5, C5, D5)처럼 각 셀을 쉼표로 나열하는 명시적 참조는 셀 하나하나를 직접 가리키기 때문에, 그중 하나라도 삭제되면 바로 #REF!가 발생합니다. 반면 =SUM(B5:D5)처럼 범위 참조를 사용하면 셀을 삭제해도 엑셀이 자동으로 범위를 조정합니다.
따라서 수식을 새로 작성할 때는 되도록 범위로 참조하는 습관을 들이는 것이 좋습니다. 이미 #REF! 오류가 발생한 수식이라면, 수식 입력줄에서 #REF! 부분을 지우고 올바른 범위로 다시 입력하면 됩니다.
- 예시:
=SUM(B5, #REF!, C5)→=SUM(B5:C5)또는=SUM(B5, C5, D5)로 수정 - VLOOKUP 예시:
=VLOOKUP(A8, A2:D5, 5, FALSE)→=VLOOKUP(A8, A2:E5, 5, FALSE)로 조회 범위를 늘리거나,=VLOOKUP(A8, A2:D5, 4, FALSE)로 열 번호를 낮춤 - INDEX 예시:
=INDEX(B2:E5, 5, 5)→=INDEX(B2:E5, 4, 4)처럼 범위 안에 있도록 행·열 인덱스 조정
외부 참조와 OLE 링크, 블록 해제로 해결하는 경우
#REF! 오류가 외부 통합문서나 다른 프로그램과의 연결 문제에서 발생하는 경우도 있습니다. 이때는 다음과 같이 접근해야 합니다.
- 외부 통합문서 참조:
INDIRECT함수나 동적 배열로 닫힌 파일을 참조했다면, 해당 파일을 열어야 오류가 해결됩니다. 참조 대상 파일이 삭제되거나 이동되었다면 경로를 다시 설정해야 합니다. - OLE 링크 문제: 엑셀에서 다른 프로그램(예: 워드)의 데이터를 링크로 가져온 경우, 해당 프로그램이 실행되지 않거나 링크가 끊기면
#REF!가 발생합니다. 이때는 링크된 프로그램을 수동으로 실행해 보거나, 엑셀의 데이터 탭에서 연결 편집을 통해 링크를 업데이트하거나 끊을 수 있습니다. - 보안 센터 설정: 회사나 공용 PC에서 외부 콘텐츠가 차단된 경우에도 오류가 날 수 있습니다. 파일 > 옵션 > 보안 센터 > 보안 센터 설정 > 외부 콘텐츠에서 "모든 외부 데이터 연결에 대해 자동 업데이트 사용"을 체크하거나, 특정 통합문서에 대해 차단을 해제해야 할 수 있습니다.
Q. #REF! 오류가 난 셀이 너무 많아요. 한 번에 해결할 방법이 없나요?
A. #REF! 오류가 여러 셀에 퍼져 있다면, 가장 먼저 원인이 된 열이나 행 삭제를 취소(되돌리기, Ctrl+Z)해 보세요. 만약 이미 저장을 했거나 되돌릴 수 없는 상태라면, 수식이 들어 있는 전체 범위를 선택한 후 찾기 및 선택(홈 탭) > 이동 옵션 > 수식 > 오류를 선택하면 #REF! 셀만 한 번에 선택할 수 있습니다. 그런 다음 각 셀의 수식을 하나씩 수정하거나, 단순한 합계나 평균 같은 경우 아예 수식을 다시 입력하는 게 빠를 수 있습니다.
Q. VLOOKUP에서 #REF! 오류가 나는데, 값이 없어서 그런 건가요?
A. 아닙니다. VLOOKUP에서 #REF!는 값이 없어서 나는 #N/A 오류와 다릅니다. VLOOKUP의 세 번째 인수(열 번호)가 조회 범위의 열 개수보다 큰 경우에 발생합니다. 예를 들어 A:D 범위(4개 열)에서 5번째 열을 찾으라고 하면 #REF!가 나타납니다. 조회 범위를 A:E로 늘리거나 열 번호를 4 이하로 줄이면 해결됩니다.
Q. 열을 삭제한 후 #REF!가 떴는데, 다시 삽입해도 안 고쳐져요. 어떻게 하나요?
A. 열을 삭제한 후 다시 삽입하면 빈 열이 생기지만, 수식이 자동으로 예전 참조를 복구하지는 않습니다. 삽입 후 수식 입력줄에서 #REF!로 표시된 부분을 실제 셀 주소(예: C5)로 직접 수정해야 합니다. 또는 삭제 직후에 되돌리기(Ctrl+Z)를 누르는 게 가장 빠른 해결책입니다.