Planilha Travando – 7 Possíveis Soluções

Planilha Travando

Você já teve que usar alguma planilha travando? É uma experiência bem ruim, né? Nesse post vou te mostrar 7 passos para que você não passe mais por isso! Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Uma planilha do Excel travando ou lenta quase sempre tem causa identificável: referência circular, links para planilhas externas, formatação ou fórmulas em excesso, e cálculo automático sobrecarregado. Para resolver, verifique erros em Fórmulas > Verificação de Erros > Referências Circulares, limpe formatações e fórmulas não usadas, e mude o modo de cálculo de Automático para Manual em planilhas grandes.

O que você aprende neste vídeo

Resposta rápida: O instrutor percorre as causas mais comuns de planilha pesada e a correção de cada uma: referência circular, link com planilha externa, formatação aplicada em colunas e linhas inteiras, fórmula sobrando em células vazias, verificação de erros em segundo plano e o cálculo automático. No fim, salvar como pasta de trabalho binária.

Neste vídeo (19 min):

• 1:00 - Referência circular: fórmulas que se referenciam em looping. O Excel avisa com setas azuis e com o nome na barra de status.
• 2:00 - Para achá-la: guia Fórmulas > Verificação de Erros > Referências Circulares, o Excel lista as células envolvidas.
• 4:00 - Link com planilha externa: em vez de a fórmula buscar dados em outro arquivo, copie a base e cole como valor numa aba nova.
• 5:00 - Para mapear todos os vínculos: guia Dados > Editar Links, que mostra cada fórmula apontando para arquivo de fora.
• 6:00 - Formatação em coluna/linha inteira: pintar tudo carrega mais de 1 milhão de linhas e 16 mil colunas de informação inútil.
• 7:00 - Correção: selecionar da última coluna útil em diante com Ctrl + Shift + →, e em Página Inicial usar a borracha > Limpar tudo (idem para as linhas abaixo).
• 8:00 - Fórmula sobrando em centenas de linhas vazias: deixe uma folga razoável e apague o resto com Ctrl + Shift + ↓ e Delete.
• 10:00 - Verificação de erros (o triangulinho verde) consome memória: Fórmulas > Verificação de Erros > Opções e desmarcar “habilitar verificação de erros em segundo plano”.
• 11:00 - Quando nada explica o peso: copiar as abas para uma pasta nova (ou botão direito > Mover ou Copiar > Criar uma cópia), salvar e comparar o tamanho.
• 13:00 - Modo de cálculo: em Fórmulas > Opções de Cálculo, trocar Automático por Manual evita recalcular tudo a cada digitação, F9 recalcula quando você quiser.
• 16:00 - Fechando: salvar como pasta de trabalho binária (.xlsb), tirar filtros da planilha grande, trocar fórmulas de resumo por tabela dinâmica e considerar VBA.

Trechos do vídeo:

• A lógica que vale para formatação e para fórmula sobrando é a mesma: cada célula ocupada é informação carregada. Pintar a aba inteira de amarelo custa memória sem trazer nada, dá para pintar só a faixa em uso e limpar o resto.
• No modo Manual o valor exibido fica desatualizado de propósito, até você mandar recalcular. No exemplo, a soma continuou mostrando 65 enquanto o resultado real já era 20, é o comportamento esperado, não erro.
• A troca de formato costuma ser a mudança de maior retorno pelo esforço: “às vezes um arquivo tem 50 MB e, quando a gente muda para pasta de trabalho binária, pode cair pela metade” mantendo as mesmas informações.

Clique aqui para baixar a planilha utilizada nessa publicação!

O que são Planilhas Pesadas?

São planilhas que possuem muitos dados e por consequência acabam por deixar a execução mais lenta ou até travar o programa. Isso ocorre em grandes empresas, análise de dados, entre outras atividades que possuem uma grande quantidade de dados a serem analisadas e que possuem algumas características que favorecem esse aumento de tamanho nos arquivos.

Por que tratar Planilhas Pesadas?

A grande quantidade de dados deixa a planilha pesada e atrapalha o funcionamento da mesma, desta forma a planilha fica mais lenta, atrapalhando o trabalho de quem a está usando. Por este motivo é importante aprendermos ferramentas para tratar essas planilhas e fazer com que elas fiquem mais enxutas, menos pesadas e mais ágeis para que o usuário possa ganhar produtividade.

Então, ao tratar essas planilhas é possível diminuir seu tamanho, melhorar seu desempenho e por consequência diminuir as possibilidades da planilha parar de funcionar ou continuar lenta.

Planilha Travando – O que fazer?

Para tratar uma planilha travando é importante aprendermos alguns passos importantes. O primeiro deles é a referência circular.

A referência circular é quando criamos um loop, ou seja, quando referenciamos células ou abas que acabam voltando para elas mesmas. Quando isso é feito, o Excel nos mostra utilizando setas para indicar onde está esse loop.

Nesta planilha temos as seguintes informações:

B2 = B3; B3 = C3; C3 = B2;

Portanto, temos um loop, onde a célula B2 acaba referenciando ela mesma. Isso acaba gerando um erro no Excel, que diz para o usuário a existência desse tipo de referência circular.

Caso o Excel não indique que há esse problema o usuário pode verificar manualmente se há algum problema indo até a guia Fórmulas e em seguida Verificação de Erros e por fim em Referências Circulares. Desta forma é possível verificar onde estão essas referências.

Neste caso, como foi mostrado anteriormente, essas três células C3, B2 e B3 estão se referenciando, o que gera o problema da referência circular. Desta forma, é possível modificar uma dessas células para que o problema seja resolvido e a planilha fique menos pesada.

Neste caso deletamos a célula C3 e o problema foi resolvido.

Outro passo importante para diminuir o peso da planilha é verificar Links para Planilhas Externas, ou seja, há a possibilidade de dentro de uma fórmula referenciarmos uma outra planilha (outro arquivo de Excel). O problema é que esse tipo de atividade deixa a planilha pesada, então ao invés de fazer esse tipo de referência é recomendado que se copie os dados a serem utilizados e cole na mesma planilha que está utilizando a fórmula. Desta maneira podemos deixar a planilha mais leve.

Então, para sabermos se há algum link para uma planilha externa a que esteja sendo utilizada, podemos ir até a guia Dados e em seguida na opção Editar Links. Feito isso uma nova janela será aberta e irá mostrar os links que existem para dados externos. Desta maneira o usuário pode ir até a planilha externa e copiar os dados necessários para que melhore o desempenho da planilha atual.

A Formatação de Células é algo que também interfere bastante no tamanho da planilha. Quando o usuário deseja, por exemplo, preencher o fundo da planilha de uma cor, é recomendado que faça isso apenas nas células que serão vistas, não há necessidade de fazer esse preenchimento em todas as células da planilha. Formatar células que não estão sendo usadas acaba deixando a planilha pesada desnecessariamente. Neste caso, basta o usuário selecionar apenas o intervalo que deseja e formatar da maneira desejada e as outras células podem ser selecionadas utilizando CTRL + SHIFT + SETA PARA BAIXO E PARA DIREITA (partindo de uma célula desejada) e em seguidas podemos limpá-las com a borracha que se encontra na guia Página Inicial. Selecionando a opção de Limpar Tudo todas as formatações dessas células serão deletadas e assim o tamanho da planilha diminui.

Nesta imagem com zoom de 55% é possível observar que apenas uma parte da planilha está com a formatação, ou seja, apenas a parte visível que contém os dados. Desta forma podemos eliminar formatações desnecessárias e diminuir o tamanho da nossa planilha.

A utilização de Fórmulas em excesso sem necessidade também é algo que aumenta o tamanho da planilha, da mesma forma que foi feita a utilização da formatação sem necessidade no exemplo anterior, na parte de fórmulas também é utilizado mais fórmulas do que o necessário.

Neste caso, por exemplo, temos apenas 9 linhas com dados utilizados na coluna A e coluna B, no entanto, na coluna C estamos utilizando 33 linhas, sendo que da célula C10 até a C33 não temos dados nas colunas A e B, portanto as fórmulas excedentes da coluna C não estão sendo utilizadas, porém estão deixando a planilha mais pesada. Portanto, limpar as fórmulas não utilizadas é uma maneira de deixar a planilha mais leve.

Após a limpeza das células que não estavam sendo utilizadas podemos observar uma planilha mais agradável, com menos fórmulas e, consequentemente, mais leve.

Outro tema que pode te levar a ter uma planilha travando são as Verificações de Erro, ou seja, quando o Excel encontra um erro ele “informa” ao usuário a existência desse erro para que ele possa ser corrigido, no entanto essa informação utiliza memória para ser mostrada ao usuário, o que também pode deixar a planilha mais pesada.

Essas setas verdes são informações que o Excel mostra ao usuário para identificar um erro. Como essas informações geram mais informações dentro da planilha o Excel possibilita a remoção das mesmas. Para isso, basta ir até a guia Fórmulas, em seguida Verificação de Erros e Verificação de Erros novamente. Feito isso será aberta uma nova janela.

Nesta janela iremos clicar em Opções e seremos direcionados a outra janela.

Na nova janela de Opções do Excel basta desmarcar a caixa Habilitar verificação de erros em segundo plano e clicar em OK.

Feito isso as setas verdes que indicam os erros não serão mais mostradas ao usuário.

Outro tópico importante que vale ser ressaltado quando o tema é uma planilha travando são os Bugs de Abas. Às vezes o usuário pode ter feito várias verificações em uma aba e não encontrou erro algum, mas ainda assim a planilha está pesada. Para resolver o usuário pode tanto copiar os dados e colar em uma nova aba ou também poderá criar uma cópia daquela mesma aba. Como são bugs isso pode resolver e diminuir bastante o tamanho das planilhas.

Para criar uma cópia da aba desejada basta clicar com o botão direito sobre ela e selecionar a opção Mover ou Copiar.

Em seguida marcar a caixa Criar uma cópia e clicar em OK.

Feito isso será feita uma cópia da aba. A cópia tem exatamente os mesmos dados, no entanto pode não possuir os bugs que tinha na primeira, por isso acaba diminuindo o tamanho da planilha.

Um outro fator que pode deixar o Excel lento e até travar é quando as Opções de Cálculo estão no modo Automático. Isso quer dizer que toda vez que algum valor for alterado a fórmula irá se atualizar automaticamente.

Em uma planilha travando em que podemos ter diversas fórmulas cada alteração pode significar uma atualização de uma grande quantidade de fórmulas ao mesmo tempo. Essa atividade pode fazer com que o Excel trave, pare de funcionar ou fique bem lento. Então, para minimizar esse problema podemos alterar o modo de cálculo do Excel de automático para Manual.

Para fazer essa alteração basta ir a guia Fórmulas e em seguida Opções de Cálculo e selecionar a opção Manual.

Após essa seleção qualquer mudança nos valores que compõem as fórmulas só serão atualizados quando o usuário selecionar a opção de Calcular Agora. Desta forma, o usuário evita de fazer inúmeros cálculos toda vez que mudar o valor de cada célula, podendo mudar vários valores e atualizar as fórmulas apenas uma vez após as diversas mudanças.

Então, se mudarmos os valores das células que compõem a fórmula na célula B6 o Excel só irá atualizar quando a opção de calcular agora for selecionada.

Na figura abaixo podemos observar que ao mudar os valores no modo manual nada acontece.

Para que o valor seja atualizado basta selecionar a opção de Calcular Agora e o Excel irá atualizar todas as fórmulas.

O usuário poderá também ao invés de clicar na opção para atualizar utilizar o atalho F9, desta forma as fórmulas são atualizadas.

Outras informações importantes para diminuir o tamanho das planilhas:

– Salvar como: utilizar a opção de Pasta de trabalho Binária do Excel. Essa opção pode diminuir bastante o tamanho do arquivo.

– Cuidado com Filtros: remover os filtros da planilha muito grande e fazer a análise em outra planilha (em uma planilha auxiliar) dividindo as informações e análises a fim de diminuir o tamanho da planilha maior.

– Trocar fórmulas por Tabela Dinâmica: trocar as fórmulas que serão utilizadas para fazer uma análise em uma base de dados devem ser substituídas por uma tabela dinâmica para que a planilha fique mais enxuta.

– Utilizar o VBA (Visual Basic for Application): que é a programação por trás do Excel, podem fazer com que pulemos algumas etapas de algumas formas mais complexas do Excel e as vezes conseguir um mesmo resultado de uma forma menos complexa e menos pesada.

Para saber mais sobre planilhas pesadas e outros assuntos de Excel e VBA, acompanhe que vamos enviar outros posts.

Se você mora no Rio de Janeiro ou em Niterói e nunca fez um dos cursos da Hashtag Treinamentos você não sabe o que está perdendo! São mais de 13.000 alunos treinados em nossos cursos de Excel, VBA e Power BI!

---

Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

---

Perguntas frequentes sobre Excel travando e lento