Se sua planilha está lenta, travando ou demora para responder, este artigo é para você! Hoje iremos aprender a como melhorar o desempenho da sua planilha. São dicas de uso de fórmulas, configurações e se você usa VBA, também deixamos algumas dicas para melhorar o desempenho que irá fazer você economizar um tempo precioso nas suas análises com o ganho de produtividade que você irá conseguir.

Para desempenho, use todo poder de processamento!

Por padrão, o Excel já utiliza todo o poder de processamento do computador ao calcular uma planilha, porém, em alguns casos, é possível que esta opção esteja desativada. Para verificar, acesse Arquivo -> Opções -> Acesse o campo Avançado -> Procure o subtítulo “Formulas” e verifique se os cálculos multithread estão ativos, e marque a opção de usar todos os processadores neste computador, conforme imagem abaixo.

Agora o Excel está usando todos os processadores disponíveis, no seu sistema a quantidade de processadores disponível pode variar.

Evite usar muitas fórmulas voláteis

Fórmulas voláteis são recalculadas toda vez que há uma alteração na planilha, com isso, o uso de muitas formulas pode ocasionar lentidão nos cálculos da planilha. Devemos evitar referenciar essas formulas em planilhas com grande volume de dados, por exemplo: se usarmos a formula HOJE dentro de um PROCV, toda vez que alterarmos uma célula, este PROCV será recalculado. Abaixo, estão algumas fórmulas voláteis:
=HOJE() , =AGORA(), =DESLOC() , =ALEATÓRIO(), =ALEATÓRIOENTRE(), =INFORMAÇÕES(), =INDIRETO(), =CÉLULA()

Tome cuidado com fórmulas matriciais!

As fórmulas matriciais, como diz o nome, utiliza matrizes para fazer os cálculos. Essas fórmulas são bastante úteis quando temos que fazer testes lógicos em várias células para obtermos o resultado.
Esse tipo de fórmula é uma ótima solução para uma quantidade relativamente baixa de dados, porém, quando usadas em uma quantidade grande de colunas e linhas, os cálculos da planilha podem ficar extremamente lentos.
Uma solução, se você sempre utilizar este tipo de fórmula nos mesmos intervalos seria utilizar VBA para fazer os cálculos e deixar os dados estáticos (deixarei um exemplo abaixo), ou selecionar os intervalos com o as fórmulas, copiar e colar valores para deixas os mesmos estáticos sem a necessidade de VBA. Uma outra solução seria desativar os cálculos automáticos e deixá-los em manual.
Algumas fórmulas já são matriciais por padrão, são elas SOMASE, SOMASES, CONTSE, CONTSES, etc.

Utilize o modo de cálculo manual

Se você tem uma grande quantidade de dados com fórmulas complexas em sua planilha e a mesma fica muito tempo calculando, talvez uma das soluções será desativar o modo de cálculo automático.
O Excel, por padrão, utiliza o cálculo automático, este recurso é ótimo para termos o resultado assim que editamos alguma fórmula ou algum dado, porém, quando temos muitas fórmulas complexas, o tempo de processamento pode ser muito grande, com isso, podemos mudar para o modo de cálculo manual.
Para alterar, vamos na guia Fórmulas -> Opções de Cálculo -> Manual

Você pode apertar F9 toda vez que for necessário fazer os cálculos. É importante também saber que, no cálculo manual, somente a célula que você alterar será calculada, todas as outras não serão, por isso devemos ficar atentos para não acabarmos interpretando dados incorretamente.

Use as fórmulas de maneira eficaz!

Algumas fórmulas são semelhantes e podem nos retornar o mesmo resultado, porém, pensando em desempenho, devemos utilizar a mais adequada a situação. Por exemplo, em casos que usamos o SE para ignorar números negativos, podemos utilizar a fórmula MÁXIMO, pois, o SE irá fazer o cálculo duas vezes para testar a condição, a fórmula MÁXIMO fará uma vez só, o que a deixará bem rápida. O mesmo serve para a fórmula MÍNIMO, para ignorarmos valores positivos. Exemplo de uso:

Além das fórmulas, uma outra maneira de ganhar desempenho é evitar utilizar colunas inteiras por exemplo “A:B” para realizar os cálculos, ao invés disso, podemos colocar o intervalo exato da tabela de dados, por exemplo “A1:B300”.


Exemplo de fórmula que utiliza toda a coluna:

Exemplo de fórmula que utiliza somente o intervalo necessário:

Planilha binária, pouco conhecida, mas muito útil.

Normalmente nós salvamos nossa planilha no formato .XLSX, esse formato é o padrão do Excel. Quando salvamos neste formato, o Excel compacta este arquivo com vários outros arquivos do XML aberto, que, dependendo da quantidade de dados da sua planilha, o arquivo acaba ficando bastante pesado, além disso, não é possível salvar macros neste formato.

Por outro lado, como o próprio nome diz, a planilha binária tem seus dados salvos no formato binário, este formato tem a vantagem de ser mais leve que o formato .XLSX, em alguns casos, o arquivo pode ficar até 50% menor e também é aberta em menos tempo, além de poder ter macros. A desvantagem deste formato é que ele não tem compatibilidade com grande parte de outros programas, o que pode ser um problema se você os utiliza para importar os dados de sua planilha.

Aumentando a velocidade de cálculo do seu código VBA

Se você usa VBA, também é possível aumentar a velocidade das operações de cálculo. Abaixo, explicaremos o qual a função dos códigos e mostraremos um exemplo usando o SOMASE, mostrando como é possível substituir as fórmulas por valores estáticos e explicaremos passo a passo qual a função de cada comando, você também pode copiar o código da planilha de exemplo para sua pasta de trabalho e fazer o teste ou alterar conforme sua necessidade:
Os códigos e suas funções:

Application.ScreenUpdating: Neste código é possível desativar a atualização da tela, com ele desativado, o Excel não acompanha a execução do código na planilha, com isso ganhamos bastante tempo em códigos complexos.

Sub exemplo()
 Application.ScreenUpdating = False
 ‘Código
 Application.ScreenUpdating = True
 End sub

Application.Calculation: Com este código é possível desativarmos o cálculo automático, deixando a planilha com a execução dos códigos mais rápida.

Sub exemplo()
 Application.Calculation = xlCalculationManual 
 ‘Código
 Application.Calculation = xlCalculationAutomatic
 End sub

Application.DisplayAlerts: Este código possibilita desativarmos os alertas que possam aparecer na planilha, o que evita interações desnecessárias na execução do código.

Sub exemplo()
 Application.DisplayAlerts = False
 ‘Código
 Application.DisplayAlerts = True
 End sub

Lembrando, que desativamos a função no começo do código e depois reativamos no final do código, assim evitamos algum problema após a execução.

Código VBA utilizando SOMASE

No exemplo de código, vamos simular uma situação em que queremos substituir as formulas SOMASE pelo cálculo estático, assim, além de automatizarmos essa tarefa, a planilha não ficará calculando toda vez. Exemplo:

Abaixo está o código. O mesmo estará na planilha de exemplo:

Disponibilizamos esse código na planilha abaixo

Faça o download da planilha com o VBA