Para impedir a digitação de valores duplicados em uma coluna do Excel, selecione o intervalo, abra Dados > Validação de Dados, escolha Personalizado em Permitir, e informe a fórmula =CONT.SE($A$2:$A$1000;A2)=1 (ajustando o intervalo e a primeira célula) — assim, o Excel só aceita um valor se ele ainda não existir na coluna.
Você já teve uma planilha de cadastro em que o mesmo código, CPF ou e-mail acabou sendo digitado duas vezes por engano, e só percebeu o problema depois, quando um relatório saiu com números inflados ou um cliente apareceu duas vezes na lista?
Sem uma trava no momento da digitação, encontrar e corrigir duplicatas depois que elas já entraram na planilha exige rodar uma limpeza manual periódica, torcendo para notar o erro antes que ele afete algum cálculo ou relatório que depende daquela coluna estar sem repetição.
Neste artigo iremos mostrar como configurar a Validação de Dados para bloquear valores duplicados no momento da digitação, como personalizar a mensagem de erro exibida, e um exemplo prático com uma lista de códigos de produto.
Por que a validação de dados padrão não resolve isso sozinha
As opções prontas da Validação de Dados (Número, Data, Comprimento do texto, Lista) não têm uma regra pronta chamada “sem duplicados”. Para isso, é preciso usar a opção Personalizado, que aceita qualquer fórmula que retorne VERDADEIRO ou FALSO como critério de aceitação — e a fórmula certa para essa checagem é justamente o CONT.SE contando quantas vezes o valor já aparece na coluna.
Passo a passo: configurando a regra personalizada
Selecione o intervalo onde a regra vai valer, começando da primeira célula com dado (por exemplo, A2:A1000, deixando uma margem de linhas para crescimento futuro da lista). Vá em Dados > Validação de Dados, na aba Configurações escolha Personalizado em Permitir, e no campo Fórmula digite:
=CONT.SE($A$2:$A$1000;A2)=1
Repare que o intervalo do CONT.SE está com $ (travado, sempre o mesmo intervalo inteiro), mas a segunda referência (A2) está sem travar — isso garante que, ao aplicar a mesma regra em A3, A4 e assim por diante, cada célula se compare contra a coluna inteira, mas sempre testando o próprio valor digitado nela mesma.
Por que a fórmula usa “=1” e não “=0”
O CONT.SE conta quantas vezes um valor aparece no intervalo, incluindo a própria célula que está sendo validada (já que ela também faz parte do intervalo $A$2:$A$1000). Por isso, um valor que ainda não existia antes da digitação atual tem contagem 1 (só ele mesmo) no momento em que passa pela validação — se o mesmo valor já existisse em outra linha, a contagem seria 2 ou mais, e a regra bloquearia a entrada.
Ignorando diferença entre maiúsculas e minúsculas
Por padrão, o CONT.SE não diferencia maiúsculas de minúsculas — “ABC123” e “abc123” já são tratados como duplicados pela fórmula acima, o que geralmente é o comportamento desejado para códigos e nomes. Se, pelo motivo contrário, você precisar que a validação diferencie maiúsculas de minúsculas, é necessário trocar o CONT.SE por uma fórmula matricial com a função EXATO, um caso mais raro e específico.
Personalizando a mensagem de erro exibida
Sem configurar nada além da fórmula, o Excel exibe uma mensagem genérica de erro quando o valor duplicado é rejeitado. Para deixar mais claro para quem preenche a planilha, vá na aba Alerta de Erro da mesma janela de Validação de Dados, e escreva um título (como “Valor duplicado”) e uma mensagem (como “Esse código já foi cadastrado nesta coluna. Verifique antes de continuar.”), mantendo o estilo Parar, que impede a digitação até o valor ser corrigido.
Exemplo prático: bloqueando códigos de produto repetidos
Considere uma coluna de cadastro de código de produtos, em que cada código precisa ser único:
| A (código) | |
|---|---|
| 1 | Código |
| 2 | PRD-001 |
| 3 | PRD-002 |
| 4 | (tentativa de digitar PRD-001 de novo) |
Com a regra =CONT.SE($A$2:$A$1000;A2)=1 aplicada em A2:A1000, ao tentar digitar “PRD-001” novamente na linha 4, o Excel recusa o valor e mostra o alerta de erro configurado, porque naquele momento a contagem de “PRD-001” na coluna já seria 2, não 1. A pessoa precisa apagar e digitar um código diferente para a célula aceitar o valor.
O que fazer com os dados que já estão duplicados na planilha
Essa validação só impede novas duplicatas a partir do momento em que é configurada — ela não apaga nem sinaliza duplicatas que já existiam antes na coluna. Para limpar duplicatas já existentes, use o comando Dados > Remover Duplicatas, ou destaque-as primeiro com uma regra de formatação condicional para “Valores duplicados”, revisando manualmente antes de decidir o que apagar.
Disponibilidade
A Validação de Dados com fórmula personalizada e CONT.SE funciona em todas as versões do Excel, incluindo 2016, 2019, 365, Online e Mac. O Google Sheets também tem validação de dados com fórmula personalizada, usando CONT.SE (COUNTIF) da mesma forma, com vírgula em vez de ponto e vírgula entre os argumentos.
Perguntas frequentes
Essa validação apaga os valores duplicados que já existem na coluna?
Não. A regra só bloqueia novas tentativas de digitar um valor que já existe, a partir do momento em que é configurada. Para limpar duplicatas que já estavam na coluna antes, use o comando Dados > Remover Duplicatas.
A validação diferencia maiúsculas de minúsculas?
Não por padrão. A fórmula com CONT.SE trata “ABC” e “abc” como o mesmo valor, bloqueando a segunda digitação. Diferenciar maiúsculas de minúsculas exigiria uma fórmula matricial mais complexa, com a função EXATO.
Dá para personalizar a mensagem que aparece quando alguém tenta digitar um valor repetido?
Sim. Na mesma janela de Validação de Dados, na aba Alerta de Erro, é possível escrever um título e uma mensagem personalizados, além de escolher entre os estilos Parar, Aviso ou Informações.
Veja também: Como Usar a Validação de Dados no Excel para Evitar Erros, o guia completo do recurso, incluindo listas suspensas, faixas numéricas e mensagens de erro; este artigo detalha especificamente a regra de fórmula personalizada para bloquear duplicados.
Compartilhe ou Comente
Se você curtiu esse artigo aonde mostramos como impedir a digitação de valores duplicados em uma coluna do Excel usando Validação de Dados com fórmula personalizada e CONT.SE, incluindo como personalizar a mensagem de erro, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.
Sua planilha já teve algum problema por causa de um código ou CPF cadastrado duas vezes? Prefere bloquear a duplicata na hora da digitação ou limpar depois com Remover Duplicatas? Conta para nós nos comentários!