티스토리 뷰
수식은 멀쩡했는데 갑자기 #REF! 오류가 뜨는 경험, 다들 한 번쯤 있으실 겁니다. 특히 여러 사람이 같이 쓰는 재고표나 발주서에서 누군가 행이나 열을 삭제한 순간 여기저기서 #REF! 가 터지기 시작하죠. 이번 글에서는 #REF! 오류가 발생하는 원인 4가지와 상황별 해결법, 그리고 애초에 이 오류를 예방하는 수식 작성 팁까지 정리합니다.
#REF! 오류란
#REF!는 "Reference"의 줄임말로, 수식이 참조하던 셀이 더 이상 존재하지 않을 때 나타나는 오류입니다. 다른 오류(#VALUE!, #NAME?)와 달리 수식 자체는 문법적으로 맞았는데, 참조 대상이 사라진 경우에 발생한다는 게 특징입니다.
원인 1: 참조하던 행/열을 삭제한 경우
가장 흔한 원인입니다. =SUM(B2:B10)이라는 수식이 있는데, B5행을 통째로 삭제하면 참조 범위가 깨지면서 #REF! 가 뜹니다.
해결법
- 삭제 대신 내용만 지우기(Delete 키)를 사용하면 참조 구조가 유지됩니다.
- 이미 삭제해서 오류가 난 경우,
Ctrl+Z로 즉시 되돌리는 것이 가장 빠른 해결책입니다. - 이미 저장까지 해버렸다면, 수식을 다시 원래 참조 범위로 수동 수정해야 합니다.
원인 2: 다른 시트/파일을 참조했는데 그 시트가 삭제된 경우
='2월실적'!B5위 수식에서 "2월실적" 시트 자체를 삭제하면 #REF! 오류가 발생합니다.
해결법
- 시트를 삭제하기 전에 해당 시트를 참조하는 수식이 있는지
Ctrl+F→ 찾을 내용에 시트명 입력 → 옵션에서 "수식" 검색으로 확인합니다. - 이미 삭제된 경우, 시트를 복구(휴지통 또는 백업 파일)하거나 수식의 참조 시트명을 수정해야 합니다.
원인 3: VLOOKUP/INDEX 함수에서 범위를 벗어난 경우
=VLOOKUP(A2, B:D, 5, FALSE)B:D는 3개 열(B, C, D)뿐인데 5번째 열을 찾으라고 지정하면 #REF! 오류가 납니다.
해결법
- 열 번호는 참조 범위의 첫 열부터 몇 번째인지 정확히 세어야 합니다. 위 예시는 최대 3까지만 가능합니다.
- INDEX 함수도 마찬가지로, 지정한 행/열 번호가 실제 범위를 벗어나면 동일한 오류가 발생합니다.
원인 4: 외부 링크(다른 파일 참조)가 깨진 경우
다른 엑셀 파일을 참조하는 수식이 있는데, 원본 파일이 이동되거나 삭제, 이름 변경된 경우입니다.
해결법
데이터 탭 → 연결 편집 → 원본 변경새 파일 경로를 다시 지정해주면 연결이 복구됩니다. 만약 원본 파일 자체가 없어졌다면, 값을 붙여넣기(값만 붙여넣기)로 전환해 참조를 끊는 것도 방법입니다.
#REF! 오류를 애초에 예방하는 수식 작성법
1. 절대 범위 대신 표(테이블) 기능 사용Ctrl+T로 데이터를 표로 변환하면, 행이 추가/삭제되어도 수식이 자동으로 범위를 따라가기 때문에 #REF! 오류가 훨씬 적게 발생합니다.
2. 이름 정의(Named Range) 활용
자주 참조하는 범위는 이름을 정의해서 쓰면, 범위가 변경되어도 이름 관리자에서 한 번에 수정할 수 있어 관리가 쉬워집니다.
수식 탭 → 이름 정의 → "매출범위" = Sheet1!$B$2:$B$1003. IFERROR로 오류 방지 처리
=IFERROR(VLOOKUP(A2,B:D,3,FALSE), "확인필요")근본 원인을 없애는 방법은 아니지만, 오류가 다른 수식으로 전파되는 것을 막아 전체 시트가 깨지는 것을 방지할 수 있습니다.
여러 사람이 함께 쓰는 파일에서 #REF! 방지하기
발주서, 재고표처럼 여러 명이 동시에 편집하는 파일은 #REF! 발생 빈도가 특히 높습니다.
- 행/열 삭제 권한을 제한하려면 시트 보호 기능을 활용합니다 (검토 탭 → 시트 보호)
- 원본 구조는 잠금 처리하고, 데이터 입력 셀만 편집 가능하도록 설정하면 실수로 인한 참조 파괴를 크게 줄일 수 있습니다.
자주 묻는 질문
Q. #REF! 오류가 뜬 셀을 그냥 지워도 되나요?
지우면 당장은 화면에서 사라지지만, 그 셀을 참조하던 다른 수식들이 연쇄적으로 오류를 일으킬 수 있습니다. 근본 원인(깨진 참조)을 찾아 수정하는 것이 안전합니다.
Q. 저장 후에도 되돌리기(Ctrl+Z)가 되나요?
저장은 되돌리기 기록과 별개이므로, 파일을 닫지 않았다면 저장 후에도 Ctrl+Z가 작동합니다. 다만 파일을 닫았다 다시 열면 되돌리기 기록이 사라지므로, 오류를 발견하면 닫기 전에 먼저 되돌리기를 시도해야 합니다.
Q. 공유 파일에서 동시에 여러 명이 작업할 때도 안전한가요?
공동 작업 중에는 한 사람의 행 삭제가 다른 사람 화면의 수식까지 깨뜨릴 수 있습니다. 가능하면 구조 변경(행/열 삭제, 시트 삭제)은 담당자 한 명만 하도록 규칙을 정하는 것을 권장합니다.
#REF! 오류는 대부분 "누군가 뭔가를 지웠다"는 신호입니다. 원인만 정확히 짚으면 해결 자체는 어렵지 않으니, 당황하지 말고 위 4가지 케이스부터 순서대로 확인해보시기 바랍니다.