Suas regras de formatação condicional mudam inesperadamente ao copiá-las para novas células. Isso acontece porque as referências de célula na fórmula são relativas por padrão. Ao copiar uma regra, o Excel ajusta essas referências com base na nova localização. Este artigo explica por que esse deslocamento ocorre e mostra como travar as referências para evitá-lo.
Principais Conclusões: Corrigir Deslocamento na Formatação Condicional
- Referência absoluta ($A$1): Trava a coluna e a linha para que a referência não mude ao copiar a regra.
- Referência mista ($A1 ou A$1): Trava apenas a coluna ou a linha, permitindo ajuste parcial ao copiar para baixo ou para os lados.
- Caixa de diálogo Gerenciar Regras: Use-a para editar regras existentes e corrigir os tipos de referência sem recriá-las.
Por que as Referências da Formatação Condicional Mudam
As fórmulas de formatação condicional usam os mesmos tipos de referência que as fórmulas padrão do Excel. Por padrão, uma referência como A1 é relativa. Isso significa que o Excel a interpreta como “a célula uma coluna à esquerda e uma linha acima da posição da célula atual”. Ao aplicar a regra a um novo intervalo, o Excel recalcula essa posição relativa para cada célula nesse intervalo.
Por exemplo, uma regra na célula B2 com a fórmula =A1>10 verifica o valor na célula A1. Se você copiar essa formatação para a célula C3, a regra se ajusta para =B2>10. Esse comportamento é útil para criar realces linha por linha, mas causa erros quando você precisa comparar todas as células a um valor fixo ou a uma célula específica em outra planilha.
O Papel do Intervalo “Aplica-se a”
O deslocamento está ligado à célula superior esquerda do intervalo “Aplica-se a” definido na regra. O Excel trata a fórmula como se tivesse sido escrita para essa primeira célula. Em seguida, aplica a mesma lógica relativa a todas as outras células do intervalo. Se o “Aplica-se a” for $B$2:$B$10, as referências da fórmula são relativas à célula B2 para toda a coluna.
Como Travar Referências na Formatação Condicional
Você controla o comportamento das referências adicionando cifrões ($) antes da letra da coluna e do número da linha. Use as etapas a seguir para criar ou editar uma regra com referências absolutas.
- Selecione o intervalo de dados
Destaque as células onde deseja aplicar a formatação. Para uma nova regra, selecione todo o intervalo de destino primeiro. - Abra o menu Formatação Condicional
Vá para a guia Página Inicial na faixa de opções. No grupo Estilos, clique em Formatação Condicional. Selecione Nova Regra no menu suspenso. - Escolha uma regra de fórmula
Na caixa de diálogo Nova Regra de Formatação, selecione “Usar uma fórmula para determinar quais células devem ser formatadas”. - Insira sua fórmula com referências absolutas
No campo de fórmula, digite sua condição. Para travar uma referência a uma única célula, adicione cifrões. Por exemplo, para comparar todas as células selecionadas ao valor na célula C5 da Planilha1, use =A1>Planilha1!$C$5. Observe que você normalmente referencia a célula ativa do intervalo selecionado, geralmente a célula superior esquerda. - Defina o formato e aplique a regra
Clique no botão Formatar para escolher a cor de preenchimento, estilo de fonte ou borda. Clique em OK para retornar à caixa de diálogo Nova Regra de Formatação. Verifique se o intervalo “Aplica-se a” está correto e clique em OK para criar a regra.
Editando uma Regra Existente para Corrigir Referências
Se uma regra já estiver se deslocando, você pode editar sua fórmula diretamente.
- Abra a caixa de diálogo Gerenciar Regras
Selecione qualquer célula no intervalo formatado. Vá para Página Inicial > Formatação Condicional > Gerenciar Regras. - Selecione e edite a regra
Na caixa de diálogo, certifique-se de que “Esta Planilha” esteja selecionado no menu suspenso para ver todas as regras. Clique na regra que deseja alterar e clique em Editar Regra. - Modifique a fórmula
Na caixa de diálogo Editar Regra de Formatação, adicione cifrões às referências na fórmula que não devem mudar. Clique em OK, depois clique em Aplicar e OK no gerenciador para salvar.
Erros Comuns e Como Evitá-los
Regra se Aplica ao Intervalo Errado Após Copiar
Ao copiar uma célula com formatação, o Excel pode criar uma nova regra com um intervalo “Aplica-se a” diferente em vez de estender a existente. Isso gera várias regras duplicadas difíceis de gerenciar. Sempre verifique a caixa de diálogo Gerenciar Regras após copiar a formatação e exclua quaisquer regras duplicadas indesejadas.
Usar Referências Absolutas para Toda a Coluna da Tabela
Um erro comum é usar uma referência totalmente absoluta como =$A$1 ao realçar uma coluna inteira. Isso faz com que cada célula verifique a mesma célula única, o que geralmente está correto. No entanto, se você precisa que cada linha verifique seu próprio valor em uma coluna específica, use uma referência mista. Para uma tabela onde a coluna D deve ser realçada com base em seu próprio valor, use =$D1>100 para um intervalo começando na linha 1. Isso trava a coluna em D, mas permite que o número da linha se ajuste.
Referências Quebram Quando Linhas São Inseridas
Mesmo referências absolutas como $A$1 podem causar problemas se sua regra referenciar uma célula que pode ser excluída. Se você excluir a linha 1, uma referência a $A$1 se torna #REF! e a regra falha. Quando possível, referencie uma célula dedicada fora da tabela de dados principal, como uma célula em uma seção separada de “critérios” da sua planilha que não será excluída.
Tipos de Referência para Formatação Condicional
| Item | Referência Relativa (A1) | Referência Absoluta ($A$1) |
|---|---|---|
| Sintaxe | A1 | $A$1 |
| Comportamento ao copiar para baixo | O número da linha muda (A2, A3) | A referência permanece fixa na célula $A$1 |
| Comportamento ao copiar para os lados | A letra da coluna muda (B1, C1) | A referência permanece fixa na célula $A$1 |
| Melhor caso de uso | Realçar linhas alternadas em uma lista | Comparar todas as células a um valor fixo ou célula |
| Exemplo de fórmula para a regra | =A1>B1 (compara células na mesma linha) | =A1>$D$5 (compara a uma célula de critério específica) |
Agora você pode criar regras de formatação condicional que se comportam de forma previsível ao serem copiadas. Use referências absolutas para comparar dados com um ponto de referência fixo. Experimente usar referências mistas como $A1 para condições mais complexas baseadas em linhas. Para controle avançado, use as funções OFFSET ou ÍNDICE em sua regra para criar pontos de referência dinâmicos que se ajustam com base em outros valores de célula.