Detectar erros em fórmulas

A tabela a seguir resume alguns dos erros mais comuns que os usuários podem cometer ao inserir uma fórmula e explica como corrigi-los.

Não deixe de	Para saber mais
Iniciar todas as funções com o sinal de igual (=)	Se você omitir o sinal igual, o que você digitar poderá ser exibido como texto ou como uma data. Por exemplo, se você digitar SOMA(A1:A10), o Excel exibirá a cadeia de texto SOMA(A1:A10) e não executará o cálculo. Se você digitar 2/11, o Excelexibirá a data 2/nov (supondo que o formato da célula seja Geral) em vez de dividir 11 por 2.
Combinar todos os parênteses de abertura e de fechamento	Verifique se todos os parênteses possuem um par correspondente de abertura e fechamento. Quando você usa uma função em uma fórmula, é importante que cada parêntese esteja em sua posição correta para que a função funcione corretamente. Por exemplo, a fórmula =SE(B5<0);"Não válido";B5*1,05) não funcionará, pois existem dois parênteses de fechamento e apenas um de abertura, e deve haver somente um de cada. A fórmula correta tem o seguinte formato: =SE(B5<0;"Não válido";B5*1,05).
Usar dois-pontos para indicar um intervalo	Quando você fizer referência a um intervalo de células, use dois-pontos (:) para separar a referência para a primeira célula do intervalo e a referência para a última célula no intervalo. Por exemplo, =SUM(A1:A5), não =SUM(A1 A5), que retornaria um #NULL! Erro.
Inserir todos os argumentos obrigatórios	Algumas funções exigem argumentos. Além disso, verifique se você não inseriu argumentos demais.
Inserir o tipo correto de argumento	Algumas funções, como SOMA, exigem argumentos numéricos. Outras funções, como SUBSTITUIR, exigem um valor de texto para pelo menos um de seus argumentos. Se você usar o tipo errado de dados como argumento, Excel poderá retornar resultados inesperados ou exibir um erro.
Aninhar no máximo 64 funções	Você pode inserir ou aninhar no máximo 64 níveis de funções dentro de uma função.
Colocar outros nomes de planilha entre aspas simples	Se a fórmula se referir a valores ou a células de outras planilhas ou pastas de trabalho, e o nome da outra pasta de trabalho ou planilha contiver caracteres não alfabéticos, você deverá colocar o nome entre aspas simples ('), como ='Dados Trimestrais'!D3 ou =’123’!A1.
Colocar um ponto de exclamação (!) depois do nome da planilha quando você se referir a ela em uma fórmula	Por exemplo, para retornar o valor da célula D3 em uma planilha chamada Dados do Trimestre na mesma pasta de trabalho, use essa fórmula: ='Dados do Trimestre'!D3.
Incluir o caminho para planilhas externas	Verifique se cada referência externa contém um nome de pasta de trabalho e o caminho para ele. Uma referência a uma pasta de trabalho inclui o nome da pasta de trabalho e deve ser colocada entre colchetes ([Nomedapastadetrabalho.xlsx]). A referência deve também conter o nome da planilha na pasta de trabalho. Se a pasta de trabalho à qual você deseja se referir não estiver aberta no Excel, você ainda poderá incluir uma referência a ela em uma fórmula. Forneça o caminho completo para o arquivo, como o seguinte exemplo: =LINS('C:\Meus Documentos\[Operações T2.xlsx]Vendas'!A1:A8). Essa fórmula retornará o número de linhas do intervalo que inclui as células A1 a A8 na outra pasta de trabalho (8). Observação: Quando o caminho completo contém caracteres de espaço, como no exemplo anterior, você deve colocar o caminho entre aspas simples (no começo do caminho ou depois do nome da planilha, antes do ponto de exclamação).
Digitar números sem formatação	Não formate os números ao inseri-los em fórmulas. Por exemplo, se o valor que deseja inserir for R$ 1.000, digite 1000 na fórmula. Se você inserir uma vírgula como parte de um número, o Excel vai tratá-lo como um caractere separador. Caso prefira os números exibidos de forma que mostrem separadores de milhares ou milhões, ou mesmo símbolos de moeda, formate as células depois de inserir os números. Por exemplo, se você quiser adicionar 3100 ao valor na célula A3 e inserir a fórmula =SUM(3.100,A3), Excel adiciona os números 3 e 100 e adiciona esse total ao valor da A3, em vez de adicionar 3100 ao A3, que seria =SUM(3100,A3). Se você inserir a fórmula =ABS(-2;134), o Excel exibirá uma mensagem de erro, pois a função ABS aceita apenas um argumento: =ABS(-2134).

Você pode implementar certas regras para verificar erros em fórmulas. Essas regras não garantem que a planilha fique livre de erros, mas podem ajudar a encontrar erros comuns. Você pode ativar ou desativar qualquer uma dessas regras individualmente.

Os erros podem ser marcados e corrigidos de duas maneiras: um erro de cada vez (como um verificador ortográfico) ou imediatamente quando eles surgirem na planilha, à medida que você inserir os dados.

Você pode resolve um erro usando as opções exibidas pelo Excel ou ignorar o erro selecionando Ignorar Erro. Caso você ignore o erro de uma determinada célula, ele não será exibido nas próximas verificações de erro. No entanto, você pode redefinir todos os erros que foram ignorados para que eles sejam exibidos novamente.

1. Para Excel no Windows, acesse Opções de > de Arquivos > Fórmulas ou
   para Excel no Mac, selecione o menu do Excel > Preferências > Verificação de Erros.

2. Em Verificação de Erros, marque a caixa de seleção Habilitar verificação de erros em segundo plano. Qualquer erro encontrado é marcado com um triângulo no canto superior esquerdo da célula.

   

3. Para alterar a cor do triângulo que marca onde há um erro, na caixa Indicar erros usando essa cor, selecione a cor que desejar.

4. Em Regras de verificação do Excel, marque ou desmarque as caixas de seleção de qualquer uma das seguintes regras:

   • Células que contêm fórmulas que resultam em um erro: uma fórmula não usa a sintaxe, argumentos ou tipos de dados esperados. Os valores de erro incluem #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!e #VALUE!. Cada um desses valores de erro tem causas diferentes e é resolvido de maneiras diferentes.

     Observação: Se você inserir um valor de erro diretamente em uma célula, ele será armazenado como esse valor de erro, mas não será marcado como um erro. No entanto, se uma fórmula de outra célula fizer referência à essa célula, a fórmula vai retornar o valor de erro dessa célula.

   • Fórmula de coluna calculada inconsistente em tabelas: uma coluna calculada pode incluir fórmulas individuais diferentes da fórmula da coluna mestre, o que cria uma exceção. Exceções em colunas calculadas são criadas quando você realiza uma das seguintes ações:

     • Digitar dados e não uma fórmula em uma célula de uma coluna calculada.

     • Digite uma fórmula em uma célula de coluna calculada e use Ctrl +Z ou selecione Desfazer na barra de ferramentas de acesso rápido.

     • Digitar uma nova fórmula em uma coluna calculada que já contenha uma ou mais exceções.

     • Copiar dados em uma coluna calculada que não correspondam à fórmula da coluna calculada. Se os dados copiados contiverem uma fórmula, essa fórmula sobrescreverá os dados na coluna calculada.

     • Mover ou excluir uma célula em outra área da planilha que seja referenciada por uma das linhas de uma coluna calculada.

   • Células contendo anos representadas como 2 dígitos: a célula contém uma data de texto que pode ser mal interpretada como o século errado, quando usado em fórmulas. Por exemplo, a data na fórmula =ANO("1/1/31") poderia ser 1931 ou 2031. Use essa regra para verificar datas de texto ambíguas.

   • Números formatados como texto ou precedidos por um apóstrofo: a célula contém números armazenados como texto. Isso ocorre normalmente quando os dados são importados de outras fontes. Os números armazenados como texto podem causar resultados de classificação inesperados, portanto, é melhor convertê-los em números. ‘=SOMA(A1:A10) é vista como texto.

   • Fórmulas inconsistentes com outras fórmulas na região: a fórmula não corresponde ao padrão de outras fórmulas próximas a ela. Em muitos casos, as fórmulas adjacentes a outras fórmulas diferem apenas nas referências usadas. No exemplo a seguir de quatro fórmulas adjacentes, o Excel exibe um erro ao lado da fórmula =SUM(A10:C10) na célula D4 porque as fórmulas adjacentes incrementam por uma linha e essa incrementa por 8 linhas — o Excel espera a fórmula =SUM(A4:C4).

     

     Se as referências usadas em uma fórmula não forem consistentes com as das fórmulas adjacentes, o Excel exibirá um erro.

   • Fórmulas que omitem células em uma região: uma fórmula pode não incluir automaticamente referências a dados que você insere entre o intervalo original de dados e a célula que contém essa fórmula. Essa regra compara a referência em uma fórmula com base no intervalo real de células adjacente à célula que contém a fórmula. Se as células adjacentes incluírem valores adicionais e não estiverem em branco, o Excel exibirá um erro ao lado da fórmula.

     Por exemplo, o Excel insere um erro ao lado da fórmula =SUM(D2:D4) quando essa regra é aplicada, pois as células D5, D6 e D7 são adjacentes às células referenciadas na fórmula e na célula que contém a fórmula (D8) e essas células contêm dados que deveriam ter sido referenciados na fórmula.

     

   • Células não bloqueadas que contêm fórmulas: a fórmula não está bloqueada para proteção. Por padrão, todas as células em uma planilha são bloqueadas para que não possam ser alteradas quando a planilha está protegida. Isso pode ajudar a evitar erros inesperados, como a exclusão ou alteração acidental de fórmulas. Esse erro indica que a célula foi definida como desbloqueada, mas a planilha não está protegida. Verifique se você não deseja que a célula seja bloqueada.

   • Fórmulas que fazem referência a células vazias: a fórmula contém uma referência a uma célula vazia. Isso pode gerar resultados inesperados, como mostra o exemplo a seguir.

     Digamos que você queira calcular a média dos números na seguinte coluna de células. Se a terceira célula estiver em branco, ela não será incluída no cálculo e o resultado será 22,75. Se ela contiver 0, o resultado será 18,2.

     

   • Os dados inseridos em uma tabela são inválidos: há um erro de validação em uma tabela. Verifique a configuração de validação da célula indo para a guia Dados > grupo Ferramentas de Dados > Validação de Dados.

1. Selecione a planilha em que deseja verificar se há erros.

2. Se a planilha for calculada manualmente, pressione F9 para calcular novamente.

   Se a caixa de diálogo Verificação de Erros não for exibida, selecione Fórmulas >Auditoria de Fórmulas > Verificação de Erros.

3. Se você já ignorou qualquer erro anteriormente, poderá marcar esses erros novamente fazendo o seguinte: vá para Opções > de Arquivo > Fórmulas. Para o Excel no Mac, selecione o menu do Excel > Preferências > Verificação de Erros.

   Na seção Verificação de Erros , selecione Redefinir Erros Ignorados > OK.

   

   Observação: A redefinição de erros ignorados redefine todos os erros em todas as planilhas na pasta de trabalho ativa.

   Dica: Sugerimos mover o diálogo Verificação de Erros logo abaixo da barra de fórmulas.

   

4. Selecione um dos botões de ação no lado direito da caixa de diálogo. As ações disponíveis são diferentes para cada tipo de erro.

5. Selecione Avançar.

1. Ao lado da célula, selecione Verificação de erro e selecione a opção desejada. Os comandos disponíveis são diferentes para cada tipo de erro, e a primeira entrada descreve o erro.

   Se você selecionar Ignorar Erro, o erro será marcado como ignorado para cada marcar consecutivo.

   

Se uma fórmula não puder avaliar corretamente um resultado, o Excel exibirá um valor de erro, como #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!e #VALUE!. Cada tipo de erro tem causas diferentes e soluções diferentes.

A tabela a seguir contém links para artigos que descrevem esses erros em detalhes e uma breve descrição para você começar.

Tópico	Descrição
Corrigir um erro ####	O Excel exibe este erro quando uma coluna não tem largura suficiente para exibir todos os caracteres em uma célula ou quando a célula contém data ou valores de data e hora negativos. Por exemplo, uma fórmula que subtraia uma data no futuro de uma data no passado, tal como =15/06/2008-01/07/2008, resulta em um valor de data negativo. Dica: Tente ajustar a célula automaticamente clicando duas vezes entre os cabeçalhos da coluna. Se ### for exibido porque o Excel não pode exibir todos os caracteres, isso o corrigirá.
Corrigir um erro #DIV/0! erro	Quando um número é dividido por zero (0) ou por uma célula vazia, o Excel exibirá esse erro. Dica: Adicionar um manipulador de erro como no exemplo a seguir, que é =SE(C2;B2/C2;0)
Corrigir um erro #N/D	O Excel exibe esse erro quando um valor não está disponível para uma função ou fórmula. Se você está usando uma função como PROCV, o que você está tentando pesquisar tem uma correspondência no intervalo de pesquisa? Geralmente, não. Tente usar SEERRO para suprimir o #N/D. Nesse caso, você poderia usar: =SEERRO(PROCV(D2;$D$6:$E$8;2;VERDADEIRO);0)
Corrigir um erro #NOME? erro	Esse erro é exibido quando o Excel não reconhece o texto em uma fórmula. Por exemplo, um nome de intervalo ou o nome de uma função podem ser escritos incorretamente. Observação: Se você está usando uma função, verifique se o nome da função está escrito corretamente. Nesse caso, SOMA está escrito incorretamente. Remova o "e" e o Excel o corrige.
Corrija um #NULO! erro	Quando você especifica uma interseção de duas áreas que não formam uma interseção (cruzada), o Excel exibe esse erro. O operador de interseção é um caractere de espaço que separa referências em uma fórmula. Observação: Verifique se os intervalos estão corretamente separados: as áreas C2:C3 e E4:E6 não têm interseção e, portanto, ao digitar a fórmula =SOMA(C2:C3 E4:E6), o erro #NULO! será retornado. Colocar uma vírgula entre os intervalos C e E corrige =SUM(C2:C3,E4:E6)
Corrigir um erro #NÚM! erro	O Excel mostra esse erro quando uma fórmula ou função contém valores numéricos inválidos. Você está usando uma função que itera, como TIR ou TAXA? Em caso afirmativo, o erro #NUM! provavelmente ocorre porque a função não consegue encontrar um resultado. Consulte o tópico de ajuda para ver as etapas de resolução.
Corrigir um erro #REF! erro	O Excel exibe esse erro quando uma referência de célula não é válida. Por exemplo, você pode ter excluído células que foram referidas por outras fórmulas, ou você pode ter colado células que você moveu em cima de células que foram referidas por outras fórmulas. Você excluiu uma linha ou coluna acidentalmente? Excluímos a coluna B nesta fórmula, =SOMA(A2;B2;C2); veja o que aconteceu. Use Desfazer (Ctrl+Z) para desfazer a exclusão, recriar a fórmula ou usar uma referência de intervalo contínuo como esta: =SOMA(A2:C2), que seria atualizada automaticamente quando a coluna B foi excluída.
Corrigir um erro #VALOR! erro	O Excel pode exibir esse erro, caso sua fórmula inclua células que contenham diferentes tipos de dados. Você está usando operadores matemáticos (+, -, *, /, ^) com tipos de dados diferentes? Se sim, tente usar uma função. Nesse caso, =SOMA(F2:F5) corrigiria o problema.

Quando as células não estão visíveis em uma planilha, você pode watch essas células e suas fórmulas na barra de ferramentas Janela do Relógio. Com a Janela de Inspeção fica mais fácil inspecionar, auditar ou confirmar cálculos e resultados de fórmulas em grandes planilhas. Ao usar a Janela de Inspeção, você não precisa rolar repetidamente ou ir para diferentes partes da planilha.

Essa barra de ferramentas pode ser movida ou ancorada como qualquer outra barra de ferramentas. Por exemplo, você pode encaixá-la na parte inferior da janela. A barra de ferramentas mantém o controle das seguintes propriedades de célula: 1) Pasta de Trabalho; 2) Planilha; 3) Nome (se a célula tiver um Intervalo Nomeado correspondente); 4) Endereço de célula; 5) Valor; e 6) Fórmula.

Adicionar células à Janela de Inspeção

1. Escolha as células que deseja inspecionar.

   Para selecionar todas as células em uma planilha com fórmulas, acesse Home > Editando > selecione Localizar & Selecionar (ou você pode usar Ctrl+G ou Control+G no Mac)> Ir para Fórmulasespeciais de >.

   

2. Acesse Fórmulas > Auditoria de Fórmulas > selecione Janela de Observação.

3. Selecione Adicionar Relógio.

   

4. Confirme se você selecionou todas as células que deseja watch e selecione Adicionar.

   

5. Para alterar a largura de uma Janela de Inspeção, arraste o limite do lado direito do título da coluna.

6. Para exibir a célula à qual a entrada na barra de ferramentas da Janela de Inspeção faz referência, clique duas vezes na entrada.

   Observação: As células que possuem referências externas a outras pastas de trabalho são exibidas na barra de ferramentas da Janela de Inspeção, apenas quando tais pastas estão abertas.

Remover células da Janela de Inspeção

1. Se a barra de ferramentas Janela do Relógio não for exibida, vá para Fórmulas > Auditoria de Fórmulas > selecione Janela de Observação.

2. Selecione as células que deseja remover.

   Para selecionar várias células, pressione CTRL e selecione as células.

3. Selecione Excluir Relógio.

   

Às vezes, é difícil entender como uma fórmula aninhada calcula o resultado final, pois existem diversos cálculos intermediários e testes lógicos. No entanto, usando o diálogo Avaliar Fórmula, você pode conferir as várias partes de uma fórmula aninhada, avaliada na ordem em que a fórmula é calculada. Por exemplo, a fórmula =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0 é mais fácil de entender quando você pode ver os seguintes resultados intermediários:

Caixa de diálogo Avaliar Fórmula	Descrição
=SE(MÉDIA(D2:D5)>50;SOMA(E2:E5);0)	A fórmula aninhada é exibida inicialmente. As funções MÉDIA e SOMA são aninhas dentro da função SE. O intervalo de células D2:D5 contém os valores 55, 35, 45 e 25; portanto, o resultado da função MÉDIA(D2:D5) é 40.
=SE(40>50;SOMA(E2:E5);0)	O intervalo de células D2:D5 contém os valores 55, 35, 45 e 25; portanto, o resultado da função MÉDIA(D2:D5) é 40.
=SE(Falso;SOMA(E2:E5);0)	Como 40 não é maior do que 50, a expressão no primeiro argumento da função SE (o argumento teste_lógico) é Falso. A função IF retorna o valor do terceiro argumento (o argumento value_if_false). A função SUM não é avaliada porque é o segundo argumento para a função IF (value_if_true argumento) e é retornada somente quando a expressão é True.

1. Selecione a célula que deseja avaliar. Apenas uma célula por vez pode ser avaliada.

2. Acesse Fórmulas > Formula Auditing > Assess Formula.

3. Selecione Avaliar para examinar o valor da referência sublinhada. O resultado da avaliação é mostrado em itálico.

   Se a parte sublinhada da fórmula for uma referência a outra fórmula, selecione Avançar para exibir a outra fórmula na caixa Avaliação . Selecione Sair para voltar para a célula e a fórmula anteriores.

   O botão Depuração total não estará disponível na segunda vez em que a referência for exibida na fórmula ou se a fórmula fizer referência a uma célula em uma pasta de trabalho separada.

4. Continue selecionando Avaliar até que cada parte da fórmula tenha sido avaliada.

5. Para ver a avaliação novamente, selecione Reiniciar.

6. Para encerrar a avaliação, selecione Fechar.