#REF! significa que uma fórmula se refere a uma célula que não está lá. A causa comum é uma linha, coluna ou planilha apagada: quando a coluna C é apagada, o Excel reescreve =B2*C2 como =B2*#REF!, e o resultado passa a ser #REF! daí em diante. Pressione Ctrl+Z (Cmd+Z no Mac) logo depois da exclusão para ter a coluna e a fórmula de volta. O erro tem o mesmo nome no Excel em português.
| 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! A fórmula aponta para uma célula que não existe.No Excel em português: =B2*#REF!A coluna de quantidade foi apagada e digitada de novo, mas a fórmula continua dizendo #REF!: o Excel nunca conserta uma referência que já se perdeu. Clique em D2, troque #REF! por C2 e pressione Enter. A coluna inteira acompanha, e D2 mostra 12.
Como o #REF! entra em uma fórmula
O Excel escreve #REF! em uma fórmula sempre que uma célula usada por ela desaparece:
| Você fez isto | =B2*C2 em D2 vira |
|---|---|
| Apagou a coluna C | =B2*#REF! |
| Apagou a linha 2 | a fórmula é apagada junto com a linha; fórmulas de outras linhas que apontavam para a linha 2 recebem #REF! |
| Apagou a planilha a que a fórmula se refere | =#REF!B2*2 (para uma fórmula como =Prices!B2*2) |
| Recortou uma célula e colou em cima de uma célula que a fórmula usa | #REF! no lugar da referência sobrescrita |
Apagar células dentro de um intervalo é seguro: =SOMA(B2:D2) vira =SOMA(B2:C2) quando a coluna C é apagada. Apagar a primeira ou a última célula de um intervalo só o encolhe. Por isso =SOMA(B2:D2) é mais seguro que =B2+C2+D2, que vira =B2+#REF!+C2.
Por que o PROCV retorna #REF!
O terceiro argumento do PROCV (VLOOKUP em inglês) conta colunas dentro do intervalo da tabela. Se ele for maior que a quantidade de colunas do intervalo, o resultado é #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! A fórmula aponta para uma célula que não existe.No Excel em português: =PROCV(E2;A2:C6;4;FALSO)A2:C6 tem três colunas, então a 4 não existe. Mude o 4 para 3 e F2 mostra 25. Você também pode digitar a fórmula em português na tabela, com ponto e vírgula: =PROCV(E2;A2:C6;3;FALSO). Isso acontece mais depois de apagar uma coluna da tabela de busca: o intervalo encolhe, o número fixo da coluna não. A PROCX ou o ÍNDICE com CORRESP evitam o problema porque nomeiam a coluna de retorno direto, como em =PROCX(E2;A2:A6;C2:C6). Veja PROCV para o resto dos argumentos.
#REF! com ÍNDICE e DESLOC
O ÍNDICE (INDEX em inglês) retorna #REF! quando o número da linha ou da coluna fica fora do intervalo, e o DESLOC (OFFSET em inglês) quando se move para acima da linha 1 ou antes da coluna A.
| 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! A fórmula aponta para uma célula que não existe.No Excel em português: =ÍNDICE(A2:A6;6)A2:A6 tem cinco notas, então ÍNDICE(A2:A6;6) é #REF!, enquanto ÍNDICE(A2:A6;3) retorna 95. A linha 0 não existe, então DESLOC(A2;-2;0) é #REF!, e DESLOC(A2;4;0) cai em A6: 81. Quando a posição vem de outra fórmula (uma CORRESP, uma CONT.NÚM), confira essa fórmula primeiro. Mais na página do ÍNDICE.
A INDIRETO também dá #REF! quando o texto dela não é um endereço válido (=INDIRETO("ZZZ1"), já que a última coluna é XFD) ou aponta para uma pasta de trabalho fechada.
#REF! ao copiar uma fórmula
Uma referência relativa se move junto com a fórmula. Copie longe o bastante para cima ou para o lado e a referência cai para fora da planilha:
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)
O mesmo acontece quando uma fórmula copiada para outra planilha ou pasta de trabalho aponta para células que não existem lá. Trave as células que não podem se mover com $ (=$B$2*2), ou copie o texto da fórmula pela barra de fórmulas em vez de copiar a célula. As referências absolutas explicam o $.
Encontrar e remover todos os #REF! de uma pasta de trabalho
- Pressione Ctrl+F (Cmd+F no Mac), digite
#REF!, abra Opções, defina Examinar como Fórmulas e clique em Localizar Tudo. A lista mostra todas as fórmulas com uma referência quebrada. - Para corrigir muitas de uma vez, use Ctrl+H (Control+H no Mac): localize
#REF!e substitua pela referência certa, mas só quando todos os resultados devem receber a mesma célula. - Confira Fórmulas > Gerenciador de Nomes: um nome cuja coluna Refere-se a mostra
#REF!quebra todas as fórmulas que o usam. - Se os dados apagados se perderam e a fórmula não é mais necessária, selecione as células e troque as fórmulas pelos valores delas (Copiar e depois Página Inicial > Colar > Valores). Valores de erro continuam sendo erros, então apague essas células depois.
Corrigir uma busca que retorna #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 |
Sua vez: =VLOOKUP(E2,A2:C6,4,FALSE) retornou #REF!. Escreva em F2 uma busca que funcione e retorne o estoque do produto de E2.
Qualquer busca que retorne 60 aqui e acompanhe os dados passa: o PROCV com a coluna 3, =PROCX(E2;A2:A6;C2:C6) ou =ÍNDICE(C2:C6;CORRESP(E2;A2:A6;0)).
Perguntas frequentes
O que significa #REF! no Excel?
A fórmula aponta para uma célula que não existe. Na maioria das vezes uma linha, coluna ou planilha que a fórmula usava foi apagada, e o Excel trocou a referência por #REF!, então =B2*C2 virou =B2*#REF!. PROCV e ÍNDICE também retornam #REF! quando o número da coluna ou da linha é maior que o intervalo.
Como corrigir o #REF! depois de apagar uma coluna?
Pressione Ctrl+Z (Cmd+Z no Mac) na hora para desfazer a exclusão. Se for tarde demais, clique na fórmula e troque o #REF! pela célula que ela deveria usar, depois arraste a fórmula para baixo de novo.
Por que o PROCV retorna #REF!?
O número da coluna é maior que a quantidade de colunas do intervalo da tabela. =PROCV(E2;A2:C6;4;FALSO) pede a 4ª coluna de um intervalo de 3 colunas. Use 3, ou amplie o intervalo para A2:D6.
Como encontrar todos os erros #REF! em uma pasta de trabalho?
Pressione Ctrl+F (Cmd+F no Mac), procure #REF!, defina Examinar como Fórmulas e clique em Localizar Tudo. O Excel lista todas as fórmulas com uma referência quebrada. Confira também Fórmulas > Gerenciador de Nomes: nomes podem apontar para #REF! depois de uma exclusão.
Como evitar o #REF! ao apagar linhas ou colunas?
Faça referência a intervalos em vez de células isoladas. =SOMA(B2:D2) encolhe para =SOMA(B2:C2) quando a coluna C ou D é apagada, enquanto =B2+C2+D2 vira =B2+#REF!+C2. Buscas que nomeiam a coluna de retorno, como =PROCX(E2;A2:A6;C2:C6), sobrevivem a colunas inseridas e à exclusão de colunas que elas não usam.
