#REF!는 수식이 없는 셀을 참조한다는 뜻입니다. 흔한 원인은 삭제한 행, 열, 시트입니다. C열을 삭제하면 엑셀은 =B2*C2를 =B2*#REF!로 다시 쓰고, 그때부터 결과는 #REF!입니다. 삭제한 직후 Ctrl+Z(Mac에서는 Cmd+Z)를 누르면 열과 수식이 돌아옵니다.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! 존재하지 않는 셀을 참조합니다.수량 열은 삭제했다가 다시 입력했지만 수식은 여전히 #REF!입니다. 엑셀은 한 번 사라진 참조를 고쳐 주지 않습니다. D2를 클릭해 #REF!를 C2로 바꾸고 Enter를 누르세요. 열 전체가 따라 바뀌고 D2는 12를 보여 줍니다.
#REF!가 수식에 들어가는 경우
엑셀은 수식이 쓰던 셀이 사라질 때마다 수식에 #REF!를 씁니다:
| 한 작업 | D2의 =B2*C2는 이렇게 됩니다 |
|---|---|
| C열 삭제 | =B2*#REF! |
| 2행 삭제 | 수식이 그 행과 함께 삭제되고, 2행을 가리키던 다른 행의 수식에는 #REF!가 들어감 |
| 수식이 참조하는 시트 삭제 | =#REF!B2*2 (=Prices!B2*2 같은 수식의 경우) |
| 셀을 잘라 내 수식이 쓰는 셀 위에 붙여 넣기 | 덮어쓴 참조 자리에 #REF! |
범위 안의 셀을 삭제하는 것은 안전합니다. C열을 삭제하면 =SUM(B2:D2)는 =SUM(B2:C2)가 됩니다. 범위의 첫 셀이나 마지막 셀을 삭제해도 범위가 줄어들 뿐입니다. 그래서 =B2+#REF!+C2가 되는 =B2+C2+D2보다 =SUM(B2:D2)가 더 안전합니다.
VLOOKUP이 #REF!를 반환하는 이유
VLOOKUP의 세 번째 인수는 표 범위 안에서 열을 셉니다. 범위의 열 개수보다 크면 결과는 #REF!입니다.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! 존재하지 않는 셀을 참조합니다.A2:C6은 세 열이므로 4는 없습니다. 4를 3으로 바꾸면 F2는 25를 보여 줍니다. 조회 표에서 열을 삭제한 뒤에 가장 많이 생깁니다. 범위는 줄어드는데 직접 쓴 열 번호는 줄지 않기 때문입니다. =XLOOKUP(E2,A2:A6,C2:C6)처럼 XLOOKUP이나 INDEX와 MATCH는 반환 열을 직접 지정하므로 이 문제가 없습니다. 나머지 인수는 VLOOKUP을 참고하세요.
INDEX와 OFFSET의 #REF!
INDEX는 행이나 열 번호가 범위 밖이면, OFFSET은 1행 위나 A열 앞으로 벗어나면 #REF!를 반환합니다.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! 존재하지 않는 셀을 참조합니다.A2:A6에는 점수가 다섯 개 있으므로 INDEX(A2:A6,6)은 #REF!이고 INDEX(A2:A6,3)은 95를 반환합니다. 0행은 없으므로 OFFSET(A2,-2,0)은 #REF!이고, OFFSET(A2,4,0)은 A6에 닿아 81입니다. 위치가 다른 수식(MATCH, COUNT)에서 나온다면 그 수식부터 확인하세요. 자세한 내용은 INDEX 페이지에 있습니다.
INDIRECT도 텍스트가 올바른 주소가 아니거나(마지막 열이 XFD이므로 =INDIRECT("ZZZ1")) 닫힌 통합 문서를 가리키면 #REF!를 냅니다.
수식을 복사할 때 생기는 #REF!
상대 참조는 수식과 함께 움직입니다. 위나 옆으로 충분히 멀리 복사하면 참조가 시트 밖으로 떨어집니다:
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
다른 시트나 통합 문서로 복사한 수식이 거기에 없는 셀을 가리킬 때도 마찬가지입니다. 움직이면 안 되는 셀은 $로 고정하거나(=$B$2*2), 셀이 아니라 수식 입력줄에서 수식 텍스트를 복사하세요. $는 절대 참조에서 설명합니다.
통합 문서의 #REF!를 모두 찾아 없애기
- Ctrl+F(Mac에서는 Cmd+F)를 누르고
#REF!를 입력한 뒤 옵션을 열어 찾는 위치를 수식으로 지정하고 모두 찾기를 클릭합니다. 목록에 끊어진 참조가 있는 수식이 모두 나옵니다. - 여러 개를 한 번에 고치려면 Ctrl+H(Mac에서는 Control+H)로
#REF!를 찾아 올바른 참조로 바꾸세요. 단, 찾은 항목이 모두 같은 셀로 바뀌어야 할 때만 하세요. - 수식 > 이름 관리자를 확인합니다. 참조 대상 열에
#REF!가 보이는 이름은 그 이름을 쓰는 모든 수식을 망가뜨립니다. - 삭제한 데이터가 사라졌고 수식도 더 이상 필요 없다면 셀을 선택해 수식을 값으로 바꾸세요(복사한 뒤 홈 > 붙여넣기 > 값). 오류 값은 오류로 남으므로 그 셀은 나중에 지우세요.
#REF!를 반환하는 조회 고치기
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
직접 해 보세요: =VLOOKUP(E2,A2:C6,4,FALSE)가 #REF!를 반환했습니다. F2에 E2에 있는 제품의 재고를 반환하는 동작하는 조회를 쓰세요.
여기서 60을 반환하고 데이터를 따라가는 조회라면 무엇이든 통과합니다. 열 번호 3을 쓰는 VLOOKUP, =XLOOKUP(E2,A2:A6,C2:C6), =INDEX(C2:C6,MATCH(E2,A2:A6,0))입니다.
자주 묻는 질문
엑셀에서 #REF!는 무슨 뜻인가요?
수식이 존재하지 않는 셀을 가리킨다는 뜻입니다. 대개 수식이 쓰던 행, 열, 시트가 삭제되어 엑셀이 참조를 #REF!로 바꾼 경우로, =B2*C2가 =B2*#REF!가 됩니다. VLOOKUP과 INDEX도 열이나 행 번호가 범위보다 크면 #REF!를 반환합니다.
열을 삭제한 뒤 생긴 #REF!는 어떻게 고치나요?
바로 Ctrl+Z(Mac에서는 Cmd+Z)를 눌러 삭제를 취소하세요. 너무 늦었다면 수식을 클릭해 #REF!를 써야 할 셀로 바꾼 뒤 수식을 다시 아래로 채우세요.
VLOOKUP이 #REF!를 반환하는 이유는 무엇인가요?
열 번호가 표 범위의 열 개수보다 크기 때문입니다. =VLOOKUP(E2,A2:C6,4,FALSE)는 3열짜리 범위에서 4번째 열을 요청합니다. 3을 쓰거나 범위를 A2:D6으로 넓히세요.
통합 문서의 #REF! 오류를 모두 찾으려면 어떻게 하나요?
Ctrl+F(Mac에서는 Cmd+F)를 누르고 #REF!를 검색하며 찾는 위치를 수식으로 지정한 뒤 모두 찾기를 클릭합니다. 엑셀이 끊어진 참조가 있는 수식을 모두 나열합니다. 수식 > 이름 관리자도 확인하세요. 삭제 후 이름이 #REF!를 가리킬 수 있습니다.
행이나 열을 삭제할 때 #REF!를 피하려면 어떻게 하나요?
셀 하나하나 대신 범위를 참조하세요. C열이나 D열을 삭제하면 =SUM(B2:D2)는 =SUM(B2:C2)로 줄어들지만, =B2+C2+D2는 =B2+#REF!+C2가 됩니다. =XLOOKUP(E2,A2:A6,C2:C6)처럼 반환 열을 직접 지정하는 조회는 열을 삽입하거나 쓰지 않는 열을 삭제해도 문제없습니다.
