Como encontrar duplicatas no Excel: destacar, contar e listar

By Mirko Peters · 2026-11-12

Encontre duplicatas no Excel com formatação condicional, sinalizadores com COUNTIF, UNIQUE, Power Query e verificações entre planilhas, além de como identificar correspondências parecidas antes de excluir.

Automatically translated with AI from the original post. Read the English original

Para encontrar duplicatas no Excel sem excluir nada, selecione seus dados e use Página Inicial > Formatação Condicional > Regras de Realce das Células > Valores Duplicados. O Excel colore cada valor que aparece mais de uma vez. Se você preferir números em vez de cores, adicione uma coluna auxiliar com uma fórmula CONT.SE. Este artigo é para usuários do Excel que recebem listas bagunçadas e precisam ver o problema antes de corrigi-lo. Você verá o método de destaque, fórmulas de contagem e sinalização, uma forma de listar apenas os valores repetidos, uma opção com Power Query, verificações entre planilhas e um plano para correspondências parecidas.

Pontos-chave

• A maneira mais rápida de encontrar duplicatas no Excel é Página Inicial, Formatação Condicional, Regras de Realce das Células, Valores Duplicados. Ela colore os valores repetidos e não exclui nada.
• Uma coluna auxiliar com CONT.SE mostra quantas vezes cada valor aparece, e com CONT.SES você pode verificar várias colunas ao mesmo tempo.
• CONT.SE ignora diferenças entre maiúsculas e minúsculas e trata ? e * como curingas, então a contagem pode ser diferente do que você espera.
• Para comparar duas planilhas, conte cada valor em relação à outra lista com CONT.SE, e abra as duas pastas de trabalho se a segunda lista estiver em outro arquivo.
• Correspondências parecidas, como Jon e John, não aparecem como duplicatas. Limpe espaços e diferenças de maiúsculas e minúsculas primeiro e, depois, revise o restante manualmente ou com o suplemento Fuzzy Lookup.

Encontrar vem antes de remover. Se você já sabe quais linhas quer excluir, nosso guia complementar sobre como remover duplicatas no Excel explica a etapa de exclusão. Aqui, o objetivo é analisar primeiro, no Excel, para que você decida o que conta como duplicata e o que manter.

Destaque duplicatas com formatação condicional

Esta é a verificação mais rápida, e a Microsoft a recomenda como etapa de revisão antes de remover qualquer coisa. Ela segue o artigo da Microsoft sobre filtrar valores exclusivos ou remover valores duplicados.

1. Selecione as células, a coluna ou o intervalo que você quer verificar. Normalmente, uma coluna é suficiente.
2. Na guia Página Inicial, abra Formatação Condicional.
3. Escolha Regras de Realce das Células e, em seguida, Valores Duplicados.
4. Escolha um formato, como um preenchimento vermelho claro, e clique em OK.

Agora, toda célula cujo valor aparece mais de uma vez está colorida. Role pela planilha ou filtre a coluna por cor para ver apenas as células destacadas. Para desfazer, volte a Formatação Condicional e limpe as regras do intervalo.

Existe uma segunda forma que oferece mais controle. Abra Formatação Condicional > Gerenciar Regras, crie uma Nova Regra e escolha Formatar somente valores exclusivos ou duplicados. Na lista Formatar tudo, você pode escolher exclusivo ou duplicado, além de definir o formato numérico, a fonte, a borda ou o preenchimento. Isso é útil quando você quer colorir os valores que aparecem apenas uma vez e deixar os repetidos sem alteração.

Duas limitações para conhecer. A Microsoft informa que o Excel não consegue destacar duplicatas na área de Valores de uma Tabela Dinâmica. E a regra analisa uma coluna por vez quando você seleciona apenas uma coluna. Uma linha que se repete somente quando você olha várias colunas juntas não será destacada. Para isso, você precisa de uma fórmula.

Contar e sinalizar duplicatas com CONT.SE

Uma coluna auxiliar oferece um número que você pode classificar e filtrar. Ela também é a ferramenta certa quando duas linhas só são duplicatas se duas ou três colunas coincidirem.

Sinalizar todas as repetições

Suponha que seus valores estejam em A2:A100. Na célula B2, digite:

=CONT.SE($A$2:$A$100;A2)

Arraste a fórmula para baixo. Um resultado 1 significa que o valor é único. Um 2 significa que ele aparece duas vezes, e assim por diante. Todas as cópias de um valor repetido recebem o mesmo número, inclusive a primeira. Para transformar a contagem em VERDADEIRO ou FALSO, adicione uma comparação: =CONT.SE($A$2:$A$100;A2)>1.

Sinalizar apenas as cópias posteriores

Quando você planeja excluir linhas, normalmente quer manter a primeira ocorrência. Use um intervalo expansível:

=CONT.SE(A$2:A2;A2)>1

O intervalo A$2:A2 cresce conforme você copia a fórmula para baixo. A primeira vez que um valor aparece, o resultado é FALSO. Cada aparição posterior resulta em VERDADEIRO. Isso corresponde ao comportamento do comando Remover Duplicatas do Excel, que mantém a primeira ocorrência.

Verificar várias colunas com CONT.SES

Para um registro que só é duplicado quando, por exemplo, nome e sobrenome coincidem, use CONT.SES com um par de intervalo e critério para cada coluna:

=CONT.SES($A$2:$A$100;A2;$B$2:$B$100;B2)

Um resultado acima de 1 significa que a combinação aparece mais de uma vez. Adicione mais pares para mais colunas. Não tente criar a mesma verificação unindo os intervalos dentro de CONT.SE, como em CONT.SE($A$2:$A$100&$B$2:$B$100;A2&B2). CONT.SE espera um intervalo real como primeiro argumento, então essa fórmula não faz o que parece fazer. CONT.SES é a solução limpa.

O que o CONT.SE faz e surpreende as pessoas

A página do CONT.SE da Microsoft lista alguns comportamentos que importam quando você procura duplicatas:

• A comparação de texto ignora diferenças entre maiúsculas e minúsculas. "maçãs" e "MAÇÃS" são contados como o mesmo valor. O Excel trata esses valores como duplicatas.
• Os caracteres ? e * funcionam como curingas. Se seus dados contiverem esses caracteres, por exemplo em códigos de produtos, o CONT.SE pode encontrar mais correspondências do que você espera. Coloque um til antes do caractere para pesquisá-lo literalmente.
• O CONT.SE retorna resultados incorretos para cadeias de texto com mais de 255 caracteres.
• Espaços no início ou no fim, aspas retas misturadas com aspas curvas e caracteres não imprimíveis podem alterar a contagem. A Microsoft sugere as funções ARRUMAR e EXTRAI.NÃO.IMPRIMÍVEIS para limpar os dados antes.

Esse último ponto é a origem da maioria das reclamações do tipo "o Excel não encontra minhas duplicatas". Duas células que parecem idênticas podem diferir por um espaço no fim. Limpe primeiro, depois conte.

Filtrar e revisar

Depois que a coluna auxiliar estiver pronta, filtre por VERDADEIRO ou por valores acima de 1 e leia as linhas. Você pode classificar pela coluna auxiliar para agrupar todas as linhas repetidas. Dê uma olhada em uma amostra antes de excluir qualquer coisa. Muitas vezes, uma "duplicata" acaba sendo dois registros reais que compartilham um nome.

Listar apenas os valores duplicados

Às vezes você quer uma lista curta do que se repete, e não um sinalizador ao lado de cada linha.

No Excel para Microsoft 365, 2024 e 2021, a função UNIQUE pode fazer o trabalho. De acordo com a página da função UNIQUE da Microsoft, =UNIQUE(matriz;[by_col];[exactly_once]) retorna os valores distintos de um intervalo, e definir exactly_once como VERDADEIRO retorna apenas os valores que ocorrem uma vez. Assim, =UNIQUE(A2:A100) retorna cada valor uma vez, e =UNIQUE(A2:A100;;VERDADEIRO) retorna os valores que não estão duplicados. Para listar somente os valores repetidos, combine UNIQUE com FILTRO e CONT.SE:

=UNIQUE(FILTRO(A2:A100;CONT.SE(A2:A100;A2:A100)>1))

Isso mantém as linhas em que a contagem é maior que 1 e mostra cada uma delas uma vez. Ela exige uma versão do Excel com matrizes dinâmicas. Teste com algumas linhas antes de confiar nela em uma lista grande.

Em versões mais antigas, use uma coluna auxiliar. Digite =SE(CONT.SE($A$2:$A$100;A2)>1;A2;"") e arraste a fórmula para baixo. A coluna passa a mostrar cada valor repetido e deixa em branco o restante. Filtre os espaços em branco. Você verá cada valor repetido tantas vezes quantas ele ocorrer, o que é suficiente para uma revisão.

Com o Filtro Avançado, você pode copiar apenas os registros exclusivos para um novo local. Vá em Dados > Avançado, escolha Copiar para outro local, informe uma célula na caixa Copiar para e marque Somente registros exclusivos. Comparar essa lista com a original mostra o que se repetia.

Use o Power Query para manter apenas as duplicatas

Se seus dados forem grandes ou chegarem toda semana, deixe o Power Query encontrar as duplicatas. A documentação do Power Query da Microsoft descreve o comando Manter duplicatas, o oposto de Remover duplicatas. Ele filtra a tabela para as linhas cujos valores nas colunas selecionadas aparecem mais de uma vez e, diferentemente de Remover duplicatas, retorna todas as linhas correspondentes, não uma por valor.

O artigo da Microsoft sobre manter ou remover linhas duplicadas traz os passos para o lado de remoção. O início é o mesmo para os dois:

1. Selecione uma célula em seus dados e escolha Consulta > Editar, ou carregue-os com Dados > De Tabela/Intervalo.
2. Clique em um cabeçalho de coluna para selecioná-la. Mantenha Shift ou Ctrl pressionado para selecionar mais colunas.
3. Aplique o comando. Os passos da Microsoft para remoção usam Página Inicial > Remover Linhas > Remover Duplicatas. Procure o comando correspondente Manter Duplicatas junto com os outros comandos de linhas na guia Página Inicial e confira a visualização.

A Microsoft também observa que o Power Query diferencia maiúsculas de minúsculas, então "Apple" e "apple" são valores diferentes ali, ao contrário do que acontece com o COUNTIF. Essa é uma diferença real entre os dois métodos. Se seu objetivo é uma verificação semanal repetível, defina um comportamento e mantenha-o, por exemplo convertendo antes a coluna para maiúsculas.

Encontrar duplicatas entre duas planilhas ou listas

Uma tarefa comum é verificar se uma lista em uma planilha também aparece em outra: uma nova lista de clientes em comparação com a antiga, ou os pedidos deste mês em comparação com os do mês passado.

1. Coloque as duas listas em planilhas separadas, por exemplo Planilha1 e Planilha2, com os valores na coluna A.
2. Na Planilha1, em B2, digite =CONT.SE(Planilha2!A:A;A2)>0.
3. Arraste a fórmula para baixo. VERDADEIRO significa que o valor também aparece na Planilha2.
4. Filtre por VERDADEIRO para ver a sobreposição, ou por FALSO para ver o que é novo.

Para duas colunas que precisam coincidir em conjunto, use CONT.SES com um par para cada coluna e os intervalos da outra planilha.

Se a segunda lista estiver em outra pasta de trabalho, a Microsoft alerta que uma fórmula que se refere a uma pasta de trabalho fechada pode retornar um erro #VALOR!. Mantenha as duas pastas abertas enquanto trabalha ou traga a segunda lista para a primeira com o Power Query. Depois da verificação, lembre-se de que o resultado depende dos dados naquele momento. Se qualquer uma das listas mudar, os indicadores mudam.

Correspondências aproximadas: Jon Smith e John Smith

Tudo o que foi visto acima encontra apenas correspondências exatas, à parte as maiúsculas. Listas reais também têm erros de digitação e grafias diferentes. O Excel não tem uma função nativa que calcule o quanto dois textos são parecidos, então você resolve isso em duas etapas.

Etapa um: remova as diferenças falsas. Use TIRAR para eliminar espaços extras e LIMPAR para remover caracteres não imprimíveis, e construa a verificação com base nos valores limpos. Em uma coluna auxiliar, =TIRAR(A2) mostra o texto limpo. Conte as duplicatas nessa coluna, não na original. Como o CONT.SE ignora maiúsculas e minúsculas, não é preciso alterar a caixa ali. No Power Query, onde a diferença importa, converta a coluna para maiúsculas ou minúsculas.

Passo dois: veja o que sobrou. Ordene a lista limpa em ordem alfabética e leia os registros vizinhos. Correspondências parecidas, como "Jon Smith" e "John Smith", acabam ficando lado a lado, e assim uma pessoa consegue enxergá-las. Para listas maiores, a Microsoft Research publicou o complemento Fuzzy Lookup para o Excel. Ele consegue encontrar linhas duplicadas aproximadas dentro de uma mesma tabela ou comparar linhas parecidas entre duas tabelas, e atribui a cada correspondência uma pontuação de similaridade. Verifique os requisitos antes de planejar seu uso: a página de download indica Windows, com Excel 2007, 2010, 2013 e 2016, e uma versão de 2020. Ele pode não instalar em uma configuração atual do Microsoft 365, e não está disponível para Mac.

Independentemente da ferramenta que usar, revise manualmente os resultados de correspondência aproximada. Duas pessoas com nomes parecidos não são necessariamente a mesma pessoa.

Erros comuns

• Verificar apenas uma coluna quando a duplicidade é definida por duas. Um sobrenome repetido, por si só, não caracteriza um registro duplicado.
• Confiar nas contagens do COUNTIF em dados com espaços extras ou caracteres estranhos. Limpe os dados com TRIM e CLEAN antes.
• Esquecer que o COUNTIF não diferencia maiúsculas de minúsculas, enquanto o Power Query diferencia, e então obter resultados diferentes entre os dois.
• Usar COUNTIF($A$2:$A$100,A2)>1 e excluir todas as linhas marcadas como VERDADEIRO. Isso também marca a primeira cópia, de modo que você acabaria excluindo todas as cópias. Use o intervalo expansivo para marcar apenas as ocorrências posteriores.
• Pesquisar colunas inteiras em busca de textos com mais de 255 caracteres, situação em que o COUNTIF retorna respostas incorretas.
• Excluir logo após destacar. Copie a planilha antes, pois a Microsoft informa que as duplicatas removidas são excluídas permanentemente.

Tópicos relacionados para explorar

• Remova as duplicatas encontradas. O comando Remover Duplicatas, na guia Dados, mantém a primeira ocorrência e exclui o restante. Ordene antes para controlar qual linha permanece.
• Mantenha o melhor registro, e não o primeiro. Ordene por data ou valor para que a linha desejada fique em primeiro lugar e, em seguida, marque as cópias posteriores com o COUNTIF de intervalo expansivo.
• Compare listas entre arquivos. O Power Query pode combinar várias pastas de trabalho em uma única tabela para que você as verifique em conjunto.
• Evite novas duplicatas. A validação de dados com uma fórmula personalizada pode bloquear um valor que já existe. Teste em uma planilha de teste.
• Mantenha uma trilha de auditoria. Deixe a coluna auxiliar em uma cópia da planilha para que você possa mostrar depois o que foi marcado e por quê.

Próximo passo

Escolha uma coluna do seu arquivo atual, destaque as duplicatas dela com formatação condicional e conte quantas células são realçadas. Depois, adicione uma coluna auxiliar com COUNTIF e compare os números. Se forem diferentes, você acabou de encontrar espaços ocultos ou diferenças de maiúsculas e minúsculas, e agora já sabe como encontrar duplicatas no Excel antes de excluir qualquer coisa.