Você precisa encontrar e marcar entradas repetidas em seus dados do Excel rapidamente. A formatação condicional é um recurso nativo que aplica formatos visuais às células com base em regras. Este artigo explica como usá-la para destacar valores duplicados em uma única coluna ou em várias colunas.
Principais Pontos: Destacar Valores Duplicados no Excel
- Início > Formatação Condicional > Regras de Realce de Células > Valores Duplicados: O método principal para aplicar um formato de cor padrão a todas as entradas duplicadas em um intervalo selecionado.
- Início > Formatação Condicional > Nova Regra > Usar uma fórmula: Cria uma regra personalizada para cenários mais complexos, como destacar duplicatas em duas colunas.
- Início > Formatação Condicional > Gerenciar Regras: Abre uma caixa de diálogo para editar, excluir ou alterar a prioridade de todas as regras de formatação aplicadas à planilha.
Entendendo a Formatação Condicional para Duplicatas
O recurso de formatação condicional do Excel altera automaticamente a aparência de uma célula quando seu conteúdo atende a critérios específicos. A regra dedicada “Valores Duplicados” foi projetada para comparar todas as células dentro de um intervalo selecionado. Em seguida, aplica uma cor de preenchimento, cor da fonte ou borda a cada célula que possui um valor correspondente em outro lugar nesse intervalo.
Você deve selecionar o intervalo de dados antes de aplicar a regra. A regra avalia valores, não fórmulas. Se uma célula contiver uma fórmula, seu valor resultante é usado para a verificação de duplicatas. A formatação é dinâmica e será atualizada imediatamente se você alterar dados que criam ou removem uma duplicata.
O que é Considerado Duplicata
A regra não diferencia maiúsculas de minúsculas. “TEXTO” e “texto” são considerados duplicatas. Ela também trata números e seus equivalentes em texto como diferentes. O valor “100” em uma célula formatada como número não é duplicata de “100” em uma célula formatada como texto. Espaços à esquerda ou à direita podem causar incompatibilidades, então pode ser necessário limpar os dados primeiro.
Passos para Destacar Duplicatas em um Único Intervalo
Este é o método padrão para encontrar repetições dentro de uma lista, como uma coluna de endereços de e-mail ou números de nota fiscal.
- Selecione seu intervalo de dados
Clique e arraste para selecionar as células que deseja verificar. Pode ser uma única coluna, uma linha ou um bloco retangular de células. - Abra o menu Formatação Condicional
Vá para a guia Início na faixa de opções. No grupo Estilos, clique no botão Formatação Condicional. - Escolha a regra Valores Duplicados
No menu suspenso, passe o mouse sobre Regras de Realce de Células. Em seguida, selecione Valores Duplicados no submenu. - Defina o estilo de formatação
Uma caixa de diálogo aparecerá. O menu suspenso esquerdo já deve estar definido como “Duplicado”. Use o menu suspenso direito para selecionar um formato predefinido, como “Preenchimento Vermelho Claro com Texto Vermelho Escuro” ou “Preenchimento Amarelo com Texto Amarelo Escuro”. Você também pode clicar em Formato Personalizado para escolher suas próprias cores de fonte, borda e preenchimento. - Aplique a regra
Clique em OK na caixa de diálogo. O Excel destacará imediatamente todas as células duplicadas no intervalo selecionado com o formato escolhido.
Usando uma Fórmula para Destacar Duplicatas Complexas
Para mais controle, use uma regra baseada em fórmula. Isso é necessário para tarefas como destacar um valor na Coluna A apenas se ele também aparecer na Coluna C.
- Selecione o intervalo alvo
Selecione as células que deseja formatar. Por exemplo, selecione as células A2:A100. - Crie uma nova regra
Vá para Início > Formatação Condicional > Nova Regra. Na caixa de diálogo Nova Regra de Formatação, selecione “Usar uma fórmula para determinar quais células devem ser formatadas”. - Insira a fórmula
Na caixa de fórmula, digite um teste lógico. Para destacar um valor em A2 que aparece em qualquer lugar na coluna C, use: =CONT.SE($C:$C, $A2)>0. Os cifrões bloqueiam as referências de coluna. A fórmula testa cada célula no intervalo selecionado (começando com A2) em relação a toda a coluna C. - Defina o formato personalizado
Clique no botão Formatar. Na caixa de diálogo Formatar Células, vá para a guia Preenchimento e escolha uma cor de destaque. Clique em OK. - Finalize e aplique
Clique em OK na caixa de diálogo Nova Regra de Formatação. As células selecionadas na coluna A serão destacadas se seu valor for encontrado na coluna C.
Erros Comuns e Limitações a Evitar
Formatação Condicional Não Destacando Duplicatas Corretas
Verifique espaços extras ou tipos de dados diferentes. Use a função ARRUMAR para remover espaços e a função VALOR para converter texto em números. Certifique-se de que sua seleção inclui todas as células que você pretende comparar. A regra funciona apenas dentro do intervalo selecionado.
Como Destacar Apenas a Segunda ou Terceira Duplicata
A regra nativa destaca todas as duplicatas. Para destacar apenas ocorrências subsequentes, use uma regra de fórmula. Para uma lista em A2:A100, selecione esse intervalo e crie uma nova regra com esta fórmula: =CONT.SE($A$2:A2, A2)>1. Isso conta as ocorrências do topo do intervalo até a linha atual, destacando apenas a segunda, terceira, etc., instância.
Removendo ou Editando o Realce de Duplicatas
Vá para Início > Formatação Condicional > Gerenciar Regras. Na caixa de diálogo Gerenciador de Regras de Formatação Condicional, selecione a regra que deseja alterar ou excluir. Use o botão Excluir Regra para removê-la ou o botão Editar Regra para alterar seu formato ou intervalo.
Regra Nativa vs. Regra de Fórmula: Principais Diferenças
| Item | Regra Nativa de Valores Duplicados | Regra de Fórmula Personalizada |
|---|---|---|
| Facilidade de uso | Rápida, configuração com um clique e formatos predefinidos | Requer escrever uma fórmula do Excel |
| Flexibilidade | Destaca todas as duplicatas dentro de um único intervalo selecionado | Pode comparar intervalos ou planilhas separadas |
| Controle de lógica | Destaca todas as células correspondentes | Pode destacar apenas a primeira, última ou enésima ocorrência |
| Opções de formato | Limitado a alguns predefinidos ou formato personalizado básico | Acesso completo a todas as opções da caixa de diálogo Formatar Células |
| Melhor para | Verificações rápidas e simples de duplicatas em um conjunto de dados | Cenários complexos, verificações de referência cruzada, instâncias específicas de duplicatas |
Agora você pode usar a formatação condicional para identificar visualmente dados duplicados em suas planilhas. Experimente o método de fórmula para destacar linhas onde um ID de cliente em uma tabela aparece em um log separado. Para análise avançada, combine a fórmula CONT.SE com a função E para destacar duplicatas que também atendem a outra condição, como um valor acima de um valor específico.