Blog

Postado em em 12 de dezembro de 2019

Como TRAVAR CELULA Excel – Formas de Trancamento de Referências

Nessa publicação vou te ensinar a travar celula excel. Basicamente vamos aprender quais são as formas de trancamento e como utilizá-las.

Caso prefira essa explicação em vídeo, acesse:

Para baixar a planilha utilizada nessa aula, preencha seu e-mail abaixo:

O que é o Trancamento de Células?

Trancamento é uma forma que temos dentro do Excel para travar as referências, seja por coluna, linha ou coluna e linha. Isso quer dizer que, ao trancarmos utilizando alguma dessas opções, podemos deixa-las fixas, facilitando na hora de expandir uma fórmula.

Quando Travar Celula Excel?

Vamos utilizar essa opção quando precisamos trancar uma referência para quando copiarmos/arrastarmos/expandirmos a fórmula ela fique com o dado que queremos. Isso é muito importante, pois algumas vezes temos uma fórmula e ao arrastar o Excel automaticamente já muda a referência para acompanhar, no entanto dependendo o que esteja fazendo isso pode vir a ser um problema.

Então para resolver esse problema temos a opção de trancamento das referências, evitando assim problemas na hora de arrastar uma fórmula que tenha uma referência que precise ser fixa.

Como Travar Celula Excel?

O trancamento é bem simples de ser utilizado, basta colocar o símbolo $ antes da coluna ou linha que deseja trancar na hora de fazer a referência, ou utilizando a tecla F4. Vamos utilizar 4 exemplos para demonstrar o que muda em cada um deles, ou seja, vamos observar o que acontece ao arrastarmos as fórmulas entre linhas e colunas.

Tabela inicial para exemplificar os trancamentos
Tabela inicial para exemplificar os trancamentos

Essa será a planilha utilizada para demonstrar cada um dos trancamentos, veja que para exemplificar temos algumas referências das 3 primeiras colunas e linhas.

Na parte à direita temos a classificação de cada um para verificar o que acontece ao arrastarmos a fórmula da primeira célula para as duas direções.

As células inicialmente coloridas possuem exatamente a referência mostrada na descrição, então estão todas se referindo a célula A1, no entanto com diferentes tipos de trancamento.

Para expandir a fórmula vamos selecionar cada uma das células coloridas, clicar no canto inferior direito da célula (terá um quadrado verde) e vamos arrastar para baixo e em seguida para a direita.

Tabela final após travar celula excel e expansão das fórmulas para as demais células
Tabela final após travar celula excel e expansão das fórmulas para as demais células

Vamos as análises de cada um dos trancamentos.

Normal – Aqui vamos ter exatamente as referências que temos nas 3 primeiras colunas, pois o Excel ao arrastar uma fórmula ele move a referência de acordo com a movimentação que o usuário está fazendo para acompanhar a fórmula. Isso é muito útil para que o usuário não tenha que ficar reescrevendo fórmulas quando possui várias células com a mesma fórmula mudando apenas a referência.

Coluna – Nesta parte temos o $ antes da letra, pois estamos trancando a coluna indicada. Feito isso é possível perceber que ao arrastar a fórmula para baixo o Excel responde normalmente, no entanto ao arrastar a fórmula para a direita (o que iria acarretar a mudança de coluna) o Excel não permite justamente pelo trancamento. Então ele irá manter a referência que é a coluna A.

Linha – Neste caso temos o $ antes do número, pois agora estamos trancando a linha indicada. Feito isso é possível perceber que temos o oposto do exemplo anterior. Então o Excel irá responder normalmente ao arrastar para a direita, no entanto para baixo teremos os mesmos valores, pois a linha da referência deve se manter, e essa é a linha 1.

Coluna e Linha – Este caso é a junção dos dois trancamentos, ou seja, vamos trancar tanto a linha quanto a coluna, portanto nos sobra apenas uma única célula. Isso quer dizer que ao trancar coluna e linha vamos estar travando completamente aquela referência e quando arrastar a fórmula essa referência ficará fixa. Isso é muito utilizado quando precisamos referenciar um valor, como por exemplo de total para calcular porcentagem, mas não queremos que ele mude ao copiar a fórmula para as outras diversas células da tabela.

A utilização do trancamento é muito comum dentro do Excel, pois permite que o usuário continue utilizando a mesma fórmula, mas sem que tenha que alterar sua fórmula manualmente para que atenda suas necessidades.

Não há uma atividade em específico que vamos utilizar o trancamento, isso vai depender muito da atividade que o usuário esteja executando. No exemplo de cálculo da porcentagem é necessário o trancamento da referência de total, pois ao arrastar a célula o Excel não irá pegar dados de outras células para assumir como total.

Por isso é importante a utilização do trancamento, pois nesse caso se o usuário não trancar a referência de total pode ser que o Excel pegue valores impróprios e retorne com um resultado errado, o que não é bom para o usuário.

Exemplo Prático de Trancamento no Excel

Você já viu como fazer o trancamento com um exemplo mais simples, só que é importante que você entenda como isso funciona dentro do Excel em uma atividade do dia a dia.

Travar Celula Excel

Por esse motivo vou te mostrar um exemplo prático com dois tipos de trancamento, o trancamento total que faz o congelamento tanto de linha e de coluna e temos também o trancamento parcial que faz o congelamento de linha ou coluna.

Vamos iniciar pelo trancamento total, ou seja, quando fixamos tanto a linha quanto a coluna de uma célula. Isso quer dizer que ao fixar a linha e coluna a célula vai permanecer a mesma ao arrastar uma fórmula (ou copiar e colar).

Desta forma o Excel não vai “conseguir” alterar essa referência, pois ela foi fixada em linha e coluna.

Planilha de bônus dos vendedores
Planilha de bônus dos vendedores

Neste primeiro caso vamos calcular o bônus de cada um dos vendedores. É uma fórmula simples onde faremos a multiplicação do valor de venda de cada um deles pelo percentual que está na célula G7.

Fórmula para o cálculo do bônus
Fórmula para o cálculo do bônus

Inicialmente o que as pessoas iriam fazer logo de cara é simplesmente multiplicar, o que não está errado, no entanto repare que ao dar um clique duplo no quadrado inferior direito dessa célula o Excel vai replicar essa fórmula para as demais células.

Resultado da fórmula sem o trancamento - Travar Célula no Excel
Resultado da fórmula sem o trancamento

É possível observar que somente o primeiro vendedor ficou com as informações corretas. Isso acontece porque quando arrastamos ou copiamos e colamos uma fórmula dentro do Excel que possua uma referência o Excel vai “mover” aquela referência de acordo onde estamos inserindo essa informação.

Então para a segunda linha teremos um deslocamento de C8*G7 para C9*G8 porque descemos uma linha, então teremos o aumento de uma linha dentro da referência.

Verificando a fórmula da linha logo abaixo
Verificando a fórmula da linha logo abaixo

A referência de vendas realmente precisa sempre se mover em uma linha, pois se trata de um novo vendedor, mas veja que a referência do bônus foi alterada e com isso a fórmula passa a multiplicar o valor da venda por 0, pois não temos informações nessa célula.

Então o que temos que fazer para corrigir esse problema é trancar a referência G7 antes de replicar a fórmula para as demais células.

Trancamento total na referência G7 - Travar Célula no Excel
Trancamento total na referência G7

Para isso o usuário terá que editar a fórmula utilizando a tecla F2. Em seguida basta clicar em cima da referência que deseja alterar e pressionar a tecla F4. Essa tecla é para inserir automaticamente o símbolo de $ antes da coluna e antes da linha, com isso o Excel entende que aquela referência está trancada.

OBS: Caso o usuário pressione F4 mais de uma vez o Excel vai alterar para o trancamento somente em linha e depois somente em coluna.

Agora sim o usuário será capaz de replicar a fórmula para as demais células, pois agora o programa entende que ao andar uma linha ou uma coluna a referência não terá nenhuma alteração, pois foi feito o trancamento total dela.

Verificando as outras células (forma correta)
Verificando as outras células (forma correta)

É possível observar que agora todos os resultados estão corretos e qualquer célula desse intervalo vai fazer referência a venda daquela linha, mas fará referência a célula G7 que possui o valor de bônus.

Para o segundo caso vamos ver um exemplo de trancamento de linha, ou seja, a linha da referência ficará fixa, mas a coluna poderá ser modificada quando o usuário replica a fórmula para diferentes colunas.

Planilha para o trancamento em linha - Travar Célula no Excel
Planilha para o trancamento em linha

Nesse exemplo temos o mesmo cálculo de bônus, no entanto queremos calcular para cada um dos vendedores em diferentes meses do ano. Então para evitar com que o usuário tenha que escrever uma fórmula para cada mês e depois replicar para os demais funcionários, vamos escrever apenas uma e replicar para toda a tabela.

Para fazer isso aprendemos no exemplo anterior que o trancamento total ele fixa uma célula para que não seja movimentada em linhas ou em colunas, neste caso vamos efetuar o trancamento de linha, pois as informações que temos de bônus estão na linha 9 da tabela, ou seja, ao replicar a fórmula para os vendedores (que estão em diferentes linhas) não queremos que o percentual de bônus mude.

Então vamos até a fórmula de bônus, clicar na referência do primeiro bônus que se encontra na célula D9 e apertar F4 duas vezes, ou escrever manualmente o $ antes do número da linha.

Fórmula para o cálculo de bônus com trancamento
Fórmula para o cálculo de bônus com trancamento

Feito isso o usuário poderá clicar no quadrado verde que fica no canto inferior direito da célula e arrastar para baixo para preencher o bônus de cada um dos vendedores naquele mês.

Replicando a fórmula para as demais células
Replicando a fórmula para as demais células

Feito isso ainda com essa seleção o usuário pode repetir o procedimento, mas agora arrastar até o último mês. Assim teremos toda a tabela preenchida de forma muito rápida e eficiente.

Replicando a fórmula para o restante da tabela
Replicando a fórmula para o restante da tabela

É possível entrar para editar qualquer fórmula para que o usuário verifique se as informações estão corretas.

Verificando se a fórmula está correta
Verificando se a fórmula está correta

Neste caso estamos no mês de fevereiro e pegando o bônus do vendedor Uelliton. Como podemos observar a fórmula está de fato pegando o valor de venda desse vendedor exatamente do mês indicado.

Então ao invés de escrever diversas fórmulas uma a uma bastou escrever uma única fórmula com o trancamento parcial e replicá-la para as demais células.

Para o último caso temos o trancamento parcial de colunas, ou seja, nesse caso temos algumas metas para os anos de 2015 a 2019, mas queremos dividir essa meta em partes iguais para completar a tabela e saber quanto teremos de meta em cada um dos meses.

Planilha para o trancamento em coluna
Planilha para o trancamento em coluna

Para simplificar o exemplo vamos de fato fazer uma divisão igual para todos os meses, ou seja, vamos pegar o valor da meta e dividir pela quantidade de meses.

Fórmula para a média mensal
Fórmula para a média mensal

A fórmula é bem simples, no entanto queremos que a meta seja diferente para cada linha, pois são anos diferentes, desta forma não podemos trancar as linhas, pois queremos que ao alterar a linha a informação também seja alterada.

Por outro lado, ao replicar a fórmula para a direita para preencher os outros meses não queremos que o valor da meta pegue a coluna vazia, depois pegue a coluna de janeiro, fevereiro e assim por diante.

Se isso acontecer vamos ter somente o primeiro mês com as informações corretas e os demais com as informações erradas. Então para resolver o problema vamos efetuar o trancamento de coluna, desta forma ao replicar a fórmula para as outras colunas a coluna D ficará fixa.

Fixando a coluna D - Travar Célula no Excel
Fixando a coluna D

Neste caso o usuário pode pressionar a tecla F4 3 vezes, ou escrever manualmente o símbolo $ logo antes da letra que indica a coluna. Esse é um atalho indispensável quando o assunto é travar célula no Excel.

Feito isso basta repetir o procedimento de clicar e arrastar no quadrado verde que fica no canto inferior direito da célula.

Replicando a fórmula para toda a tabela
Replicando a fórmula para toda a tabela

Assim é possível notar que os valores estão divididos igualmente, pois todos estão pegando a referência correta que está na coluna D.

Verificando se a fórmula está correta - Travar Célula no Excel
Verificando se a fórmula está correta

Conclusão – Travar Celula Excel

Nesta eu te mostrei como travar celula excel e a facilidade que o trancamento traz. Até mesmo em atividades simples, pois evita com que o usuário tenha um trabalho muito grande tendo que reescrever uma mesma fórmula diversas vezes, e ainda, faz com que o usuário evite erros durante esse procedimento.

Então ao utilizar o trancamento o usuário garante que as informações das outras células estarão corretas, diminui o trabalho que teria para preencher todas elas e ainda evita erros!

Hashtag Treinamentos

Para acessar outras publicações de Excel Básico, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?

Quer sair do zero em Python e virar uma referência na sua empresa? Matricule-se agora mesmo no Python Impressionador!