티스토리 뷰
엑셀에서 #REF! 오류는 "수식이 참조하고 있는 셀이 사라졌거나 범위를 벗어났다"는 신호입니다. 원인은 대부분 행/열 삭제, 수식 복사 후 범위 이탈, INDIRECT 함수 오류 세 가지 중 하나입니다. 원인만 정확히 알면 2분 안에 해결됩니다.
📎 첨부한 실습 파일(REF오류_실습파일.xlsx)에 3가지 원인을 시트별로 재현해뒀습니다. 노란 칸을 직접 클릭해서 오류를 확인하고, 옆의 해결 수식과 비교해보세요.
이 글에서 답하는 질문
- 분명히 있던 데이터를 참조하고 있는데 갑자기 #REF!가 뜨는 이유
- 행이나 열을 지웠더니 수식이 전부 #REF!로 바뀐 경우
- 수식을 옆으로/아래로 복사했더니 #REF!가 나타난 경우
원인 1: 수식이 참조하던 행 또는 열을 삭제했을 때
가장 흔한 원인입니다. B2 셀에 =A2+C2 라는 수식이 있는데 C열을 삭제하면, 수식이 참조하던 C2가 사라지면서 #REF!로 바뀝니다.
확인 방법: #REF! 셀을 클릭하고 수식 입력줄을 보면 삭제된 범위 자리에 "#REF!"라는 문자가 직접 보입니다. 예: =A2+#REF!
해결 방법:
- 삭제를 실행 직후라면 Ctrl+Z로 즉시 되돌립니다.
- 이미 저장된 상태라면 수식을 다시 작성해서 존재하는 셀을 참조하도록 수정합니다.
원인 2: 수식을 복사했더니 참조 범위가 시트 밖으로 벗어났을 때
상대참조를 사용한 수식을 아래나 옆으로 너무 많이 복사하면, 참조 범위가 행 1보다 위 또는 열 A보다 왼쪽으로 밀려나 유효하지 않은 위치를 가리키게 됩니다.
예시: D5 셀에 =D5-C3 라는 수식이 있을 때 이 수식을 위로 2행 복사하면 =D3-C1이 되고, 한 번 더 올리면 =D2-C0이 되어 C0이라는 존재하지 않는 셀을 참조해 #REF! 오류가 납니다.
해결 방법: 수식의 참조 방향과 복사 방향을 맞춰 절대참조($)나 혼합참조를 적절히 적용합니다.
원인 3: INDIRECT 함수에서 잘못된 셀 주소 문자열을 입력했을 때
INDIRECT 함수는 문자열로 입력한 셀 주소를 참조합니다. 입력한 문자열이 실제 존재하지 않는 시트명이나 범위를 가리키면 #REF!가 납니다.
예시: =INDIRECT("Sheet5!A1") 에서 Sheet5라는 시트가 없으면 #REF!
해결 방법: INDIRECT 함수 안의 문자열이 실제 존재하는 시트명과 셀 주소를 정확히 가리키는지 확인합니다. 시트명에 공백이 있다면 작은따옴표로 감싸야 합니다.
원인별 한눈에 정리
| 증상 | 원인 | 해결 방법 |
|---|---|---|
| 행/열 삭제 직후 #REF! | 삭제된 셀을 참조 중 | Ctrl+Z 또는 수식 재작성 |
| 수식 복사 후 일부만 #REF! | 참조 범위가 시트 밖으로 이탈 | 절대참조($)로 고정 |
| INDIRECT 함수에서 #REF! | 존재하지 않는 시트/범위 참조 | 시트명·범위 문자열 확인 |
#REF!가 여러 셀에 한꺼번에 퍼졌을 때
한 셀의 #REF!가 연쇄 참조로 퍼진 경우, 원인이 된 최초 셀 하나만 고치면 나머지가 연쇄적으로 자동 해결됩니다. Ctrl+F로 "#REF!"를 검색하면 오류 셀 위치를 빠르게 찾을 수 있습니다.
마무리
#REF! 오류는 원인이 명확한 만큼 찾는 법만 알면 금방 해결됩니다. 첫 번째 단계는 항상 "수식 입력줄에서 #REF! 문자가 어느 위치에 있는지 직접 눈으로 확인하는 것"입니다. 그 위치를 찾으면 원인이 자연스럽게 드러납니다.
관련 글: 엑셀 VLOOKUP N/A 오류 해결법 5가지 / 엑셀 #VALUE 오류 완벽 해결 가이드