Como encontrar referências circulares no Excel para evitar dados incorretos

Você viu o erro de referência circular em uma planilha do Excel e se perguntou se a planilha está quebrada? Confie em mim! Você não é o primeiro a enfrentar esse erro do Excel. Centenas e milhares de usuários do Excel experimentam erros de referência circular todos os dias. Não se preocupe! Veja como encontrar referências circulares no Excel e resolvê-las.

Sendo um ávido usuário do Excel, você raramente digita os mesmos dados em muitas células. Em vez disso, você importa um conjunto de dados da origem e usa a referência de célula para criar fórmulas. Assim, você cria uma planilha dinâmica.

Quando alguém altera os valores das células das referências de célula, a planilha é atualizada automaticamente. Isso é chamado de automação de análise de dados e os analistas de dados adoram brincar com referências de células.

No entanto, o problema surge quando você digita intencionalmente ou acidentalmente o endereço da célula onde está realizando um cálculo. Ou, quando você está criando muitas fórmulas em toda a planilha, uma célula contendo uma fórmula aparece em outra fórmula indiretamente.

Em tais e em muitas outras situações semelhantes, você obtém o erro de referência circular no Excel. Continue lendo para saber o que é referência circular, seus motivos, por que evitá-la e, o mais importante, como encontrar referências circulares no Excel para se livrar dessas anomalias para sempre.

O que é uma referência circular no Excel?

O Excel encontra um erro de referência circular quando vê que uma ou mais funções da planilha fazem referência ao endereço da célula onde suas funções foram criadas.

Pode parecer confuso para você, então pense em como isso pode ser grave para o aplicativo Excel. Resumindo, você nunca deseja colocar o endereço da célula em uma fórmula onde a está criando. Veja o exemplo a seguir para um esclarecimento visual:

Você está tentando multiplicar 10 da célula B1 e colocar o resultado na própria célula B1 .

Agora, no cenário acima, se você apenas pressionar OK , o Excel fechará o erro e mostrará zero como resultado calculado. Este é apenas um exemplo direto.

O que é referência circular no Excel

Agora, imagine que existem centenas de fórmulas em uma planilha de contabilidade, planilha de planejamento financeiro, folha de pagamento de funcionários, planilha de pagamento de fornecedores e assim por diante.

Você confiará nos dados incorretos calculados pelo Excel devido ao erro de referência circular e isso, por sua vez, afetará sua empresa ou tomada de decisão profissional.

Além disso, a referência circular pode reduzir drasticamente a velocidade dos cálculos no aplicativo Excel. Em casos graves, o próprio erro pode causar desempenho extremamente lento no PC também se não for tratado imediatamente.

No entanto, se você estiver trabalhando em fórmulas avançadas do Excel em que seu modelo de dados exige entradas intermediárias da referência circular para continuar com uma função longa, talvez seja necessário usá-la. Na verdade, o Excel permite que você use esse método de cálculo quando necessário.

Por exemplo, em unidades de produção de energia, como moinhos de vento, você precisa calcular a energia de saída reciclada a partir de uma fração da energia de entrada. Alternativamente, em uma planta química, os engenheiros usam a haste de saída como entrada para a mesma ou outra caldeira ou vaso de processo. Esses cálculos de engenharia de alto nível realmente requerem referências circulares para cálculos iterativos.

Quando você vê o erro de referência circular no Excel?

Claramente falando, o pop-up de referência circular não é uma mensagem de erro. É uma forma do Excel informar que encontrou uma referência circular em uma ou várias fórmulas e que pode influenciar seu algoritmo de cálculo e desempenho.

Para evitar danos causados ​​por referências circulares em fórmulas do Excel, você deve saber quando elas aparecem e quando não aparecem. Encontre abaixo as instâncias:

• Em qualquer planilha ou pasta de trabalho aberta, você cria uma fórmula e insere a referência circular pela primeira vez.
• Você ou alguém removeu todas as referências circulares do arquivo Excel e o salvou. Agora, novamente você ou outro usuário insere uma referência circular no mesmo arquivo.
• Você fechou todas as pastas de trabalho abertas. Agora, abra uma pasta de trabalho diferente e digite uma fórmula contendo uma referência circular.

Quando você vê o erro de referência circular no Excel

• Você acabou de abrir uma pasta de trabalho que continha anteriormente uma ou várias referências circulares. Você verá o aviso.
• Depois que o Excel mostrar a mensagem de aviso de referência circular uma vez e você clicar em OK , ele não exibirá mais o mesmo pop-up na mesma sessão, mesmo que você continue criando mais fórmulas de referência circular.

Portanto, você deve procurar cuidadosamente pelas instâncias acima para detectar avisos de referência circular e resolvê-los se não estiver procurando por cálculos iterativos.

Tipos de referências circulares no Excel

Se você quer aprender a encontrar referências circulares no Excel, você deve entender seus tipos. Porque localizar e eliminar referências circulares no Excel não é uma tarefa automatizada. Encontre abaixo os tipos comuns deste erro:

Um exemplo de uso de referência circular

• As referências circulares pretendidas ocorrem quando você as usa intencionalmente para fins de cálculo iterativo. Por exemplo, você precisa inserir um carimbo de data/hora em outra célula quando alguém insere um valor em uma célula referenciada usando funções voláteis e lógicas como IF, OR, SUMIF, etc.

Referência circular não intencional no Excel

• Referência circular não intencional quando você tenta trabalhar em uma fórmula do Excel mais rapidamente e se refere à célula que contém a fórmula por engano. Por exemplo, você precisa aplicar a função SOMA da célula A1 à A9 e buscar o resultado na célula A10 . Mas por engano, você digita a fórmula assim na célula A10:

=SOMA(A2:A10)

• As referências circulares ocultas são aquelas que não mostram nenhuma mensagem de aviso quando você cria uma fórmula ou abre a planilha pela primeira vez. Isso acontece porque alguém permitiu o cálculo iterativo no arquivo do Excel.

Um exemplo de uma referência circular indireta

• Referências circulares indiretas também são populares e ocorrem quando você cria uma nova fórmula em qualquer célula, por exemplo, célula A1 , e essa nova fórmula usaria outra função em outra célula, como a célula B1 , que já está usando a célula A1 como referência .

Agora que você entendeu o básico sobre referências circulares no Excel, vamos descobrir como encontrar referências circulares no Excel abaixo:

Como encontrar referências circulares no Excel: na barra de status

Desde que o recurso de cálculo iterativo esteja desativado, você pode ver as seguintes notificações sobre referências circulares na barra de status do Excel:

• Referências circulares: C3  (ou qualquer outra referência de célula) quando você estiver em uma planilha contendo uma fórmula circular na pasta de trabalho.

Mostrando referências circulares na barra de status do Excel

• Você só verá referências circulares se estiver em uma planilha que não contém nenhuma, mas a pasta de trabalho tem algumas dessas fórmulas circulares.

Como encontrar referências circulares no Excel: usando a verificação de erros

É a melhor maneira de verificar referências circulares em uma pasta de trabalho ou planilha. Aqui estão os passos que você deve saber:

• Abra uma planilha que você suspeita conter fórmulas circulares.
• Clique no menu Fórmulas na faixa de opções do Excel.

Use a verificação de erros para Como encontrar referências circulares no Excel

• No grupo de comandos Auditoria de fórmulas , clique na lista suspensa Verificação de erros  .
• Passe o cursor sobre a opção Referências Circulares para ver todas as instâncias disponíveis em todas as planilhas.
• Selecione cada instância e o Excel identificará essas células automaticamente.

Como encontrar referências circulares no Excel: do rastreamento de fórmula

Se você não achar o método de verificação de erros eficiente o suficiente para detectar um grande número de fórmulas com referências circulares, tente estas etapas para usar o método de rastreamento de fórmula:

• Selecione qualquer célula referenciada ou a célula que contém a fórmula.
• Vá para a guia Fórmulas no menu da faixa de opções do Excel e clique em Rastrear precedentes .
• O Excel destacará as células que afetam o resultado da fórmula na célula atual com setas.

Use o rastreamento de fórmula para Como localizar referências circulares no Excel

• Você também pode clicar em Rastrear dependentes para descobrir células afetadas pelo valor da referência de célula selecionada.
• Se o Excel não mostrar nenhum erro depois que você clicar nos comandos acima, não haverá referências circulares em sua planilha.
• Repita as etapas em todas as planilhas para verificar toda a pasta de trabalho.

Esse método é particularmente importante quando os recursos de cálculo iterativo estão ativos na planilha do Excel.

Como encontrar referências circulares no Excel: usando as opções do Excel

• Clique na guia Arquivo na faixa de opções do Excel e abra as Opções do Excel na barra lateral exibida.
• Na caixa de diálogo Opções do Excel , selecione o menu Fórmulas na barra lateral e desmarque a caixa de seleção Ativar cálculo iterativo .

Como desativar o cálculo iterativo no Excel

• Clique em OK para salvar as alterações.
• Agora, a pasta de trabalho ou planilha mostrará automaticamente o aviso de referência circular.
• Se não aparecer instantaneamente, salve a pasta de trabalho, feche-a e reabra-a para ver o aviso.
• Depois de ver o aviso, verifique a barra de status do Excel para saber qual célula está na referência circular.

Como resolver referências circulares no Excel

A maneira certa de corrigir um ou muitos erros de referência circular no Excel é editando manualmente cada fórmula que o contém. Dessa forma, você pode resolver referências circulares acidentais e indiretas no Excel.

Como resolver referências circulares no Excel

Outra maneira fácil é remover a fórmula da célula atual e colocá-la em outro lugar dentro da mesma planilha.

Como encontrar referências circulares no Planilhas Google (método de bônus)

Como o Excel, o Planilhas Google não permite referências circulares em uma ou mais fórmulas se você não tiver ativado o recurso de cálculo iterativo. Portanto, o Planilhas Google mostrará imediatamente o #REF! erro na célula que contém uma fórmula circular.

Planilhas Google Como encontrar referências circulares

Portanto, você pode simplesmente selecionar todas as células que contêm o #REF! aviso para localizar e resolver referências circulares no Planilhas Google.

Referências Circulares do Excel: Palavras Finais

Então, agora você conhece todos os segredos de como encontrar referências circulares no Excel. Use o método que você gosta e abandone todas as referências circulares para garantir que você não fique confuso com dados não confiáveis ​​de um relatório do Excel ou painel matemático.

A seguir, os  melhores modelos de calendário do Excel para qualquer ano  e os  melhores modelos de orçamento do Excel .