#REF!는 수식이 가리키던 자리 자체가 사라졌다는 뜻입니다. 값이 틀린 게 아니라 주소가 없어진 것이라, 셀 서식을 바꾸거나 F9로 다시 계산해도 그대로 남습니다.
방금 무언가를 지웠다면 Ctrl + Z 한 번으로 되살아납니다. 다만 저장하고 닫은 뒤라면 수식 자체가 이미 =C2*D2에서 =#REF!*D2로 바뀌어 있어서, 되돌리기가 아니라 손으로 고쳐야 합니다.
목차
수식 입력줄에 보이는 모양부터
같은 #REF!라도 모양이 셋으로 갈리고, 그 모양이 원인을 반쯤 알려줍니다.
셀에는 #REF! 네 글자만 뜹니다. 정보는 전부 수식 입력줄에 있으니, 오류가 난 셀을 한 번 클릭하고 위쪽 입력줄을 봅니다.
곱하던 대상 하나가 통째로 없어진 모양입니다. 수식의 글자 자체가 이미 바뀌어 저장돼 있습니다.
시트 이름이 있어야 할 자리에 #REF!가 앉았습니다. 참조하던 시트가 삭제된 경우입니다.
이쪽은 수식 글자가 멀쩡합니다. 어디에도 #REF!가 없는데 결과만 #REF!로 나오는 경우로, 함수에 넘긴 숫자가 범위를 벗어났다는 신호입니다.
주의 — 앞의 두 모양은 원래 주소를 엑셀이 기억하지 않습니다
수식 글자가 바뀐 뒤에는 그 자리가 원래 C2였는지 D2였는지 알아낼 방법이 없습니다. 되돌리기가 살아 있는 동안에만 복구가 가능하고, 그 뒤로는 사람이 기억해서 다시 써 넣어야 합니다.
이 글에서 쓰는 예제
아래 표 하나로 다섯 가지 원인을 전부 재현합니다.
새 통합 문서에 이 값만 넣으면 됩니다. 이후에 나오는 셀 주소는 전부 이 표 기준이고, 1행에는 코드·상품명·단가·수량이라는 머리글이 들어 있다고 봅니다. 회사 파일을 열어 두고 따라 하기보다 이렇게 빈 문서에서 한 번 재현해 보는 편이 훨씬 빠릅니다. 지워 보고 되돌려 봐야 하는 작업이라, 진짜 파일에서 하면 손이 굳습니다.
| 행 | A · 코드 | B · 상품명 | C · 단가 | D · 수량 |
|---|---|---|---|---|
| 2 | P-101 | 감자칩 | 1800 | 12 |
| 3 | P-102 | 고구마스틱 | 2200 | 7 |
| 4 | P-103 | 옥수수과자 | 1500 | 20 |
여기에 수식 두 개를 얹습니다. F2는 단가와 수량을 곱해 금액을 구하고, F5는 코드로 단가를 찾아옵니다. 하나는 셀을 직접 가리키는 수식이고 다른 하나는 범위를 통째로 넘기는 수식이라, #REF!가 나는 방식도 서로 다릅니다.
F5 : =VLOOKUP("P-102",A2:C4,3,FALSE)
지금 해보세요 — 확인
F2에 21600, F5에 2200이 나오면 준비가 끝난 것입니다. 값이 다르게 나오면 단가 열에 숫자가 아니라 텍스트가 들어갔을 가능성이 큽니다.
먼저 60초만, 세 가지
원인을 찾기 전에 되돌릴 수 있는 상황인지부터 가릅니다.
1Ctrl + Z를 한 번 눌러 본다
파일을 저장하고 닫기 전이라면 지워진 행·열·시트가 그대로 돌아오고 수식도 원래 글자로 복구됩니다. 파일을 닫았다 다시 열었다면 되돌리기 기록이 비어 있어 이 방법은 통하지 않습니다.
2Ctrl + ` 로 시트 전체를 수식으로 본다
숫자 1 왼쪽에 있는 물결 키입니다. 셀마다 결과 대신 수식이 보이게 바뀌어서, #REF!가 어느 수식 어느 자리에 박혔는지 한눈에 잡힙니다. 같은 키를 다시 누르면 원래대로 돌아옵니다.
3Ctrl + F로 몇 개인지 센다
찾을 내용에 #REF! 를 넣고 옵션을 펼쳐 범위를 통합 문서, 찾는 위치를 수식으로 바꿉니다. 모두 찾기를 누르면 목록과 개수가 나옵니다. 한 개면 손으로 고치고, 수십 개면 원인이 하나일 가능성이 높으니 그 하나를 먼저 찾습니다.
지금 해보세요 — 확인
모두 찾기 결과에서 셀 주소를 클릭하면 그 셀로 바로 이동합니다. 목록의 첫 줄과 마지막 줄이 같은 열에 몰려 있으면 그 열 하나가 원인입니다.
내 수식은 어디에 해당하나
수식 입력줄에 보이는 모양으로 갈라서, 해당하는 줄만 읽으면 됩니다.
| 수식 입력줄에 보이는 것 | 무슨 일이 있었나 | 갈 곳 |
|---|---|---|
| =A2*#REF! 처럼 한 자리만 바뀜 | 참조하던 행·열·셀이 삭제됨 | 원인 1 |
| =#REF!A2 · =#REF!1 | 시트가 통째로 삭제됨 | 원인 1 |
| VLOOKUP·HLOOKUP·INDEX가 들어 있고 수식은 멀쩡함 | 열·행 번호가 범위 폭보다 큼 | 원인 2 |
| 붙여넣은 셀에서만 뜨고 원본은 멀쩡함 | 상대 참조가 시트 밖으로 밀림 | 원인 3 |
| 셀 주소 대신 이름이 들어 있음 (=SUM(단가범위)) | 이름 정의가 가리키던 범위가 사라짐 | 원인 4 |
| INDIRECT · OFFSET이 들어 있음 | 가리키는 시트나 칸이 없음 | 원인 5 |
두 줄 이상에 해당하면 위에서부터 처리합니다. 아래쪽 원인은 위쪽을 고치면 같이 사라지는 경우가 많습니다. 열 하나를 지운 탓에 그 열을 쓰던 이름 정의까지 죽는 식이라, 이름 쪽부터 손대면 같은 일을 두 번 하게 됩니다.
표의 어느 줄에도 해당하지 않는다면 오류가 이 파일 안에서 만들어진 게 아닐 수 있습니다. 그 경우는 다섯 개가 다 아니라면으로 건너뜁니다.
원인별로 고치기
빈도 순으로 놓았습니다. 위 두 개에서 대부분 끝납니다.
원인 1 · 참조하던 행·열·시트를 지웠다
아주 흔함여기서 갈리는 지점이 하나 있습니다. Delete 키로 내용을 지운 것과, 칸 자체를 없앤 것은 다릅니다.
Delete 키는 값만 비웁니다. 빈 칸은 계산할 때 0으로 취급되니 =C2*D2는 0이 될 뿐 오류가 나지 않습니다. 반면 아래 네 가지는 칸의 존재 자체를 지우기 때문에 수식이 가리킬 주소가 없어집니다.
- 열 머리글을 우클릭 → 삭제
- 행 머리글을 우클릭 → 삭제
- 셀을 선택하고
Ctrl + -→ 셀을 위로 밀기 / 왼쪽으로 밀기 - 시트 탭을 우클릭 → 삭제
지금 해보세요 — 해결
되돌리기가 살아 있으면 그것으로 끝냅니다. 이미 저장했다면 #REF!가 든 자리를 지우고 지금 남아 있는 열 주소로 다시 씁니다. 예제에서 단가 열(C)을 지웠다면 상품명 열이 C로 밀려 있으니, 단가를 다시 만들어 넣은 뒤 수식을 새 주소로 고칩니다.
시트를 지운 경우도 같습니다. 시트 이름 자리에 #REF!가 앉았으니, 시트를 되살릴 수 없다면 그 이름을 새 시트 이름으로 바꿔 씁니다. 찾기와 바꾸기에서 찾는 위치를 수식으로 두고 #REF!를 새 시트 이름으로 한꺼번에 바꾸면 수십 개도 한 번에 정리됩니다.
원인 2 · 열 번호가 범위 폭보다 크다
흔함수식 글자는 멀쩡한데 결과만 #REF!인 경우, 열에 하나가 걸립니다.
범위 A2:C4는 세 열짜리인데 네 번째 열의 값을 달라고 했습니다. 없는 열이라 #REF!입니다.
주의 — 열 번호는 시트의 열 문자가 아닙니다
범위가 D열부터 시작하면 D가 1번, E가 2번입니다. 시트의 열 문자를 그대로 세어 넣으면 어긋나고, 범위 앞에 열을 하나 끼워 넣어도 수식 안의 숫자는 자동으로 따라가지 않습니다. 오늘 잘 되던 수식이 다음 달에 갑자기 #REF!가 되는 이유가 대부분 이것입니다.
지금 해보세요 — 해결
빈 셀에 범위 폭을 세어 보고, 그 숫자를 넘지 않게 고칩니다. 열이 자주 늘어나는 표라면 숫자를 직접 쓰지 말고 머리글 이름으로 찾게 만들어 두면 다시 터지지 않습니다.
=VLOOKUP("P-102",$A$1:$C$4,MATCH("단가",$A$1:$C$1,0),FALSE)
원인 3 · 복사해 붙였더니 참조가 시트 밖으로 밀렸다
흔함원본 셀은 멀쩡한데 붙여넣은 쪽만 #REF!라면 이쪽입니다.
엑셀의 상대 참조는 주소를 기억하는 게 아니라 거리를 기억합니다. B2에 =A2가 있으면 엑셀은 이것을 "왼쪽 한 칸"으로 저장합니다. 이 셀을 복사해 A2에 붙이면 A열의 왼쪽 한 칸을 찾아야 하는데, 그런 칸은 없습니다.
지금 해보세요 — 해결
움직이면 안 되는 참조에 $를 붙입니다. 수식 입력줄에서 주소를 클릭하고 F4를 누르면 절대 참조로 바뀝니다. 표 맨 위나 맨 왼쪽 근처에 붙여넣을 일이 많다면 붙여넣을 자리를 한 칸 안쪽으로 옮기는 편이 빠릅니다.
원인 4 · 이름 정의 안에 #REF!가 박혔다
가끔수식에 셀 주소가 아니라 이름이 들어 있으면 이 경우를 의심합니다.
=SUM(단가범위)처럼 쓴 수식은 겉보기에 아무 문제가 없습니다. 이름이 가리키던 범위를 지우면 이름은 그대로 남고 주소만 죽기 때문에, 본문 수식만 들여다봐서는 원인이 안 보입니다.
지금 해보세요 — 확인
수식 탭 → 이름 관리자를 엽니다. Ctrl + F3이 더 빠릅니다. 참조 대상 열에 #REF!가 들어간 줄이 있는지 훑어봅니다.
지금 해보세요 — 해결
그 줄을 선택하고 편집을 누른 뒤, 참조 대상 칸의 내용을 지우고 시트에서 범위를 다시 끌어 잡습니다. 안 쓰는 이름이면 삭제하는 편이 낫습니다. 죽은 이름을 남겨 두면 시트를 다른 파일로 복사할 때 같이 따라갑니다.
원인 5 · INDIRECT·OFFSET이 없는 자리를 가리킨다
가끔이 두 함수는 주소를 글자로 만들어 넘기기 때문에, 없는 곳을 가리켜도 수식 자체는 멀쩡해 보입니다.
=OFFSET(A1,-1,0) → 1행 위는 시트 밖이라 #REF!
INDIRECT 쪽은 시트 이름 오타나 이름에 섞인 공백이 원인인 경우가 많습니다. 시트 이름에 공백이나 괄호가 들어 있으면 작은따옴표로 감싸야 합니다.
지금 해보세요 — 해결
INDIRECT에 넘기는 글자를 옆 빈 셀에 그대로 만들어 보고, 그 결과를 시트 탭 이름과 눈으로 대조합니다. 공백이 들어간 이름은 이렇게 감쌉니다.
OFFSET 쪽은 기준 셀을 표 안쪽으로 옮기면 대부분 해결됩니다. 1행이나 A열을 기준으로 잡고 음수를 넣는 순간 시트 밖으로 나가기 때문입니다.
다섯 개가 다 아니라면
파일 밖에 원인이 있는 경우가 남았습니다.
다른 통합 문서를 참조하던 수식은, 그 파일이 옮겨지거나 지워져도 마지막 값을 기억하고 있어서 한동안 멀쩡해 보입니다. 링크를 갱신하는 순간 #REF!로 바뀝니다. 어제까지 잘 되던 파일이 오늘 아침에 열자마자 오류로 도배됐다면 이쪽을 먼저 봅니다.
지금 해보세요 — 확인
데이터 탭 → 연결 편집을 엽니다. 상태 열에 오류나 원본을 찾을 수 없음이 뜬 줄이 범인입니다. 원본 변경으로 옮겨진 파일을 다시 잡아 주면 되살아납니다.
피벗 테이블에서 값을 끌어 쓰는 수식도 같은 방식으로 끊깁니다. 원본 표가 없어지거나 필드 이름이 바뀌면 그 자리를 가리키던 수식이 갈 곳을 잃습니다. 이때는 피벗을 새로 고치기 전에 원본 범위부터 되돌려 놓아야 하고, 순서가 뒤바뀌면 피벗 안의 필드 배치까지 같이 날아갑니다.
주의 — 오류가 난 셀을 값으로 붙여넣지 않습니다
#REF!가 뜬 범위를 복사해 값 붙여넣기로 덮으면 오류가 문자로 굳어 버립니다. 나중에 원본을 되찾아도 자동으로 계산되지 않고, 어디가 원래 수식이었는지도 알 수 없게 됩니다.
다시 안 나게 하는 습관
세 가지만 몸에 붙이면 같은 자리에서 두 번 나지 않습니다.
1지우기 전에 참조하는 셀 추적을 켠다
지울 셀을 고르고 수식 탭 → 참조하는 셀 추적을 누르면 그 셀을 쓰고 있는 수식으로 파란 화살표가 뻗습니다. 화살표가 없으면 마음 놓고 지워도 됩니다. 확인이 끝나면 연결선 제거로 화살표를 지웁니다.
2열 번호를 숫자로 박아 두지 않는다
VLOOKUP의 세 번째 인수를 MATCH로 계산하게 두면, 나중에 누가 열을 끼워 넣어도 수식이 알아서 따라갑니다. 열이 늘어날 일이 없는 표라면 숫자로 둬도 됩니다.
3삭제 대신 숨기기를 쓴다
보기 싫은 열이라면 지우지 말고 숨깁니다. 숨긴 열은 수식이 그대로 참조하고 인쇄에서만 빠집니다. 정말 지워야 한다면 파일을 다른 이름으로 한 번 저장해 두고 지웁니다.
셋 중에 실제로 효과가 큰 것은 첫 번째입니다. #REF!는 지우는 순간에는 아무 경고도 없이 조용히 만들어지고, 보통 며칠 뒤에 다른 사람이 발견합니다. 화살표를 한 번 켜 보는 3초가 그 며칠을 없애 줍니다. 여러 셀을 한꺼번에 확인하려면 열 머리글을 통째로 골라도 화살표가 나옵니다.
자주 묻는 질문
본문에서 다루지 못한 것들만 모았습니다.
Q.#REF!와 #NAME?은 뭐가 다른가요
A.#REF!는 주소가 사라진 것이고, #NAME?은 엑셀이 못 알아듣는 글자가 들어간 것입니다. 함수 이름 오타나 없는 이름을 썼을 때 #NAME?이 뜹니다. 이름 관리자에서 이름을 지우면 그 이름을 쓰던 수식은 #REF!가 아니라 #NAME?이 됩니다.
Q.잘라내기로 셀을 옮기면 #REF!가 나나요
A.나지 않습니다. 잘라내기로 옮기면 그 셀을 참조하던 수식이 새 주소로 따라갑니다. 다만 잘라낸 것을 참조받고 있던 셀 위에 붙이면 그 셀의 원래 값이 덮여 사라집니다. 오류는 안 나고 결과 숫자만 조용히 바뀌기 때문에 오히려 더 늦게 발견됩니다.
Q.#REF!가 든 셀이 범위에 있으면 합계가 안 나옵니다
A.SUM은 범위 안에 오류가 하나라도 있으면 결과도 오류로 냅니다. 원인을 고치는 게 먼저이고, 급하게 숫자만 봐야 한다면 AGGREGATE로 오류를 무시하고 더할 수 있습니다.
Q.IFERROR로 감싸서 빈칸으로 만들어도 되나요
A.화면은 깨끗해지지만 참조는 여전히 끊긴 상태입니다. #N/A처럼 값이 없어서 나는 오류에는 IFERROR가 어울리지만, #REF!는 파일이 망가졌다는 신호라 덮어 두면 나중에 숫자가 틀린 채로 보고됩니다. 고치고 나서 감싸는 순서가 안전합니다.
고치고 나서 확인할 것들
한 자리를 고쳐도 같은 원인으로 다른 시트가 남아 있는 경우가 많습니다.
정리 — 위에서부터 체크하세요
□ Ctrl + F로 통합 문서 전체에서 #REF!를 다시 찾아 0건인가
□ 이름 관리자(Ctrl + F3)에 #REF!가 든 이름이 남아 있지 않은가
□ 데이터 탭 → 연결 편집의 상태가 모두 정상인가
□ 고친 수식의 결과가 고치기 전 숫자와 맞는가
□ 값 붙여넣기로 굳어 버린 #REF! 문자가 남아 있지 않은가
□ 숨은 시트에도 같은 수식이 있는지 확인했는가
특히 마지막 줄을 놓치기 쉽습니다. 시트 탭을 우클릭해 숨기기 취소가 활성화돼 있다면 숨은 시트가 있다는 뜻이고, 거기에도 같은 수식이 복사돼 있는 경우가 많습니다.
#REF!가 한 번 났던 파일은 다음 달에 같은 자리에서 또 나는 편입니다. 열을 지우는 사람도 지우는 자리도 매번 같기 때문인데, 그래서 고친 뒤에 그 열을 골라 참조하는 셀 추적을 한 번 돌려 두면 다음번에 지울지 말지가 그 파란 화살표로 결정됩니다.
#REF오류 #엑셀오류 #엑셀참조오류 #VLOOKUP열번호 #이름관리자
