멀쩡하던 수식이 갑자기 #REF!로 바뀔 때가 있습니다. 다른 오류와 달리 이건 수식 자체가 망가진 상태라 서식을 바꾼다고 돌아오지 않습니다.
결론부터 말하면 원인은 하나입니다. 수식이 참조하던 셀이 사라졌습니다.
REF는 Reference(참조)의 약자예요. “가리키던 곳이 없다”는 뜻입니다.
방금 그랬다면 — 즉시 Ctrl+Z
가장 먼저 할 일입니다. 행이나 열을 지운 직후라면 Ctrl + Z로 되돌리면 원래 수식이 살아납니다.
한 번 다른 작업을 하거나 파일을 저장하고 닫으면 되돌릴 수 없습니다. 수식을 처음부터 다시 써야 해요. 그래서 #REF!를 발견하면 다른 걸 건드리기 전에 되돌리기부터 눌러보세요.
어떤 상황에서 생기나
1. 참조하던 행이나 열을 삭제했다
가장 흔합니다. =A1+B1이라고 썼는데 A열을 통째로 지우면 =#REF!+B1이 됩니다.
여러 시트에 걸친 표를 정리하다가 다른 시트가 참조하고 있는 걸 모르고 지우는 경우가 많습니다.
2. VLOOKUP의 열 번호가 범위를 벗어났다
=VLOOKUP(A2, B2:D100, 5, 0)범위가 B부터 D까지 3열인데 5번째 열을 가져오라고 했습니다. 존재하지 않으니 #REF!가 뜹니다.
범위를 좁게 다시 잡았는데 열 번호를 안 고쳤을 때 자주 생깁니다.
3. 잘라내기·붙여넣기로 셀을 옮겼다
Ctrl + X → Ctrl + V로 셀을 옮기면 원래 자리가 비면서, 그 자리를 참조하던 수식이 깨집니다.
복사(Ctrl+C)는 괜찮습니다. 잘라내기가 문제예요.
4. 다른 통합문서를 참조했는데 그 파일이 사라졌다
파일 이름을 바꾸거나 옮기면 링크가 끊깁니다.
어디가 깨졌는지 찾기
#REF!가 여러 개 흩어져 있으면 하나씩 찾기 어렵습니다.
Ctrl + F → 찾을 내용에 #REF! 입력 → 옵션에서 찾는 위치를 수식으로 변경 → 모두 찾기
깨진 셀이 목록으로 나옵니다. 하나씩 클릭하면서 수식을 고치면 됩니다.
F5 → 옵션 → 수식 → 오류 를 선택하는 방법도 있습니다. 오류가 난 셀이 한 번에 선택됩니다.
고치는 법
참조를 다시 지정합니다. 수식에서 #REF! 부분을 지우고 올바른 셀 주소를 넣으면 됩니다.
VLOOKUP 열 번호 문제라면 범위의 열 개수를 세어보고 그 안의 숫자로 바꾸세요.
=VLOOKUP(A2, $B$2:$D$100, 3, 0) ← B,C,D 세 열이니 최대 3다시 안 생기게 하려면
① 열 번호 대신 범위를 직접 지정하는 함수를 쓰세요.
VLOOKUP은 열 번호가 숫자라서 구조가 바뀌면 깨집니다. XLOOKUP이나 INDEX+MATCH는 범위를 직접 가리키니 열을 지우거나 추가해도 따라 움직입니다.
=XLOOKUP(A2, $B$2:$B$100, $D$2:$D$100)이 둘의 차이는 XLOOKUP과 VLOOKUP 차이 글에서 자세히 다뤘습니다.
② 지우기 전에 참조 여부를 확인하세요.
수식 탭 → 참조되는 셀 추적 / 참조하는 셀 추적 을 쓰면 화살표로 관계가 표시됩니다. 어느 셀이 이 셀을 쓰고 있는지 눈으로 확인하고 지울 수 있어요.
③ 이름 정의를 활용하세요.
자주 쓰는 범위에 이름을 붙여두면(수식 → 이름 정의) 수식이 읽기 쉬워지고 구조 변경에도 덜 깨집니다.
다른 오류와 헷갈리지 마세요
| 오류 | 뜻 | 대표 원인 | |—|—|—| | #REF! | 참조가 없음 | 셀·행·열 삭제 | | #N/A | 값을 못 찾음 | VLOOKUP이 못 찾음 | | #VALUE! | 값의 종류가 안 맞음 | 숫자 자리에 텍스트 | | #NAME? | 이름을 모름 | 함수명 오타, 버전 미지원 | | #DIV/0! | 0으로 나눔 | 분모가 비었거나 0 |
#N/A가 뜬다면 VLOOKUP #N/A 오류 글을, #VALUE!라면 다음 글을 참고하세요. 오류마다 원인이 완전히 달라서 접근법도 다릅니다.
급할 때는 감추기라도
보고서를 당장 내야 하는데 시간이 없다면 임시로 감출 수 있습니다.
=IFERROR(원래수식, "")다만 이건 문제를 가리는 것이지 고치는 게 아닙니다. #REF!는 데이터가 틀렸다는 신호라, 숨기면 잘못된 숫자가 그대로 보고서에 들어갑니다. 반드시 원인을 찾아 고치고 나서 쓰세요.