원인 1: 수식이 참조하던 행·열·셀을 삭제했다
가장 흔한 경우다. =A1+B1 수식이 있는 상태에서 A열이나 1행 전체를 삭제하면, 그 자리를 가리키던 참조가 사라져 #REF!로 바뀐다. 이미 수식으로 굳어진 #REF!는 삭제를 취소해도 자동으로 복구되지 않을 수 있다.
가장 확실한 해결법은 삭제 직후 Ctrl+Z(실행 취소)다. 저장하기 전이라면 이 방법으로 100% 복구된다. 여러 번 삭제·수정이 있었다면 Ctrl+Z를 여러 번 눌러 삭제 시점 이전으로 돌아가야 한다.
이미 저장까지 끝나서 실행 취소가 안 먹는다면, 수식을 원래 의도대로 다시 입력하는 수밖에 없다. 이때는 #REF!가 뜬 셀 주변 데이터를 보고 '원래 무엇을 더하거나 참조했어야 하는지' 역으로 추적해서 셀 주소를 다시 지정해야 한다.
행·열을 삭제하지 말고 '숨기기'를 쓰는 습관을 들이면 이 문제 자체를 예방할 수 있다.
원인 2: 수식이 참조하던 시트를 삭제했다
='2월'!A1 처럼 다른 시트를 참조하는 수식에서, '2월' 시트 자체를 삭제하면 참조 대상이 통째로 사라져 #REF!가 뜬다. 시트 삭제는 삭제 버튼을 누르는 순간 경고창이 뜨고, 확인을 누르면 실행 취소가 안 되는 경우가 많다.
따라서 삭제 직후 바로 Ctrl+Z를 시도해 보되, 안 되면 시트 자체를 되살릴 방법은 없다. 이 경우 해결책은 백업 파일이나 클라우드 버전 기록에서 삭제 전 상태를 찾는 것이다.
엑셀 파일이 OneDrive나 SharePoint에 저장되어 있다면 파일 > 정보 > 버전 기록에서 삭제 전 시점의 파일을 열어 해당 시트 데이터를 복사해 올 수 있다. 로컬 저장 파일이라면 자동 복구 임시 파일(보통 파일 > 정보 > 통합 문서 관리)이 남아있는지 확인해봐야 한다.
이런 백업이 전혀 없다면 시트를 복구할 방법은 없고, 참조하던 데이터를 다른 곳에서 다시 구해 넣는 수밖에 없다.
원인 3: 다른 통합 문서(외부 파일) 참조가 끊겼다
='C:\Data\[매출.xlsx]Sheet1'!A1 처럼 다른 엑셀 파일을 참조하는 수식에서, 원본 파일이 삭제되었거나 이름이 바뀌었거나 다른 폴더로 이동하면 링크가 끊겨 #REF!가 뜬다. 파일을 열 때 '외부 링크 업데이트' 알림이 뜨는 경우 이 문제일 가능성이 높다.
해결하려면 데이터 탭 > 연결 편집(Edit Links)으로 들어가서 문제가 된 원본 파일 경로를 확인한다. 원본 파일이 이동만 했고 삭제되지 않았다면, 여기서 '원본 변경'을 눌러 새 경로를 지정하면 참조가 살아난다.
원본 파일 자체가 삭제됐다면 그 파일을 다시 만들거나 백업에서 복구해야 하고, 참조 구조(시트 이름, 셀 위치)까지 원래와 똑같이 맞춰야 링크가 정상적으로 붙는다.
이런 외부 참조 오류를 예방하려면 애초에 다른 파일을 참조하는 대신 필요한 데이터를 같은 통합 문서 안 시트로 복사해 쓰는 방법이 있다.
원인 4: VLOOKUP·INDEX 등에서 열/행 번호가 범위를 벗어났다
=VLOOKUP(A1, B:D, 4, 0) 처럼 참조 범위(B:D, 즉 3개 열)보다 큰 열 번호(4)를 지정하면 #REF!가 뜬다. 이런 경우는 셀이나 시트를 삭제해서 생긴 게 아니라, 애초에 함수 안의 숫자가 범위와 맞지 않아서 생긴다.
특히 원래는 맞았는데 나중에 참조 범위 안의 열을 하나 삭제해서 범위가 좁아졌을 때도 이 오류가 난다. 예를 들어 B:E(4개 열)를 참조하던 VLOOKUP에서 열 하나를 지우면 범위가 B:D(3개 열)로 줄어드는데, 함수 안 숫자는 그대로 4로 남아있어 #REF!가 뜨는 식이다.
해결법은 수식을 열어 참조 범위의 실제 열 개수를 세어보고, 몇 번째 열에 원하는 값이 있는지 다시 확인해 숫자를 고치는 것이다. INDEX/MATCH 조합에서도 마찬가지로 행/열 번호가 실제 범위를 벗어나지 않는지 확인해야 한다.
이런 오류를 줄이려면 VLOOKUP보다 열 삭제에 덜 취약한 INDEX/MATCH나 XLOOKUP(엑셀 최신 버전 지원) 사용을 고려할 수 있다.
지금 바로 해봐야 할 복구 순서
오류를 발견한 시점에 따라 대응이 다르다. 먼저 방금 삭제·편집한 직후라면 무조건 Ctrl+Z부터 여러 번 눌러본다. 이게 가장 빠르고 확실하다.
이미 저장했다면, 파일 > 정보 > 버전 기록(OneDrive/SharePoint 저장 시) 또는 파일이 있던 폴더에서 자동 저장된 이전 버전이 있는지부터 확인한다. 로컬 PC라면 문서 폴더나 임시 파일 경로에 백업이 남아있을 수 있다.
복구본이 없다면 Ctrl+F(찾기)로 #REF!를 검색해 오류가 난 셀을 모두 찾아 목록화한 뒤, 하나씩 원인(위 1~4번 중 어디에 해당하는지)을 판단해 수동으로 수식을 다시 입력하는 수밖에 없다.
앞으로를 위해서는 중요한 파일은 수식을 대량 수정하기 전에 다른 이름으로 복사본을 만들어두는 습관, 또는 OneDrive처럼 버전 기록이 남는 곳에 저장하는 습관을 들이는 게 재발을 막는 실질적인 방법이다.