Como encontrar e remover duplicatas no Excel

Valores duplicados no Excel podem ser irritantes, mas, felizmente, existem vários métodos para localizá-los e removê-los. Recomendamos fazer uma cópia de backup de sua planilha Excel antes de remover as duplicatas. Vejamos como contar, localizar e remover valores duplicados no Excel.

1. Use o botão Remover Duplicados

O método mais rápido para localizar e remover duplicatas no Excel é usar o botão “Remover Duplicatas” do Excel. Este método permite pesquisar duplicatas com base nos dados em uma ou mais colunas. Ele remove linhas inteiras quando duplicatas são encontradas.

Clique em qualquer célula com dados em sua planilha. Não precisa ser a célula, coluna ou linha da qual você deseja remover duplicatas.

Selecione a guia “Dados” e clique no botão “Remover Duplicados” na barra de ferramentas.

Marque a(s) coluna(s) nas quais deseja remover duplicatas. Todas as colunas são verificadas por padrão. Além disso, se suas colunas não tiverem títulos, desmarque a caixa “Meus dados têm cabeçalhos” para que sua primeira linha seja incluída. Clique em “OK” depois de selecionar as colunas desejadas.

O Excel fornece uma mensagem informando quantas linhas foram excluídas e quantas linhas permanecem. Este método remove a linha inteira, não apenas o valor. Pressione “OK” para aceitar os resultados e retornar à sua planilha.

Se você não gostar dos resultados, pressione Ctrl+ Zpara retornar os valores excluídos à sua planilha.

2. Encontre dados duplicados usando formatação condicional

Se você preferir revisar as duplicatas no Excel antes de excluir qualquer coisa, tente usar a formatação condicional . Isso encontra duplicatas no Excel, mas não as exclui.

Selecione as colunas ou a planilha inteira onde deseja pesquisar valores duplicados.

Abra a guia “Home” e clique no botão “Formatação Condicional”.

Selecione “Destacar regras de célula” e escolha “Valores duplicados”.

Escolha a cor na qual deseja que os valores duplicados apareçam. Você também pode mudar o “Tipo de regra” de “Valores duplicados” para “Valores únicos” se preferir ver os valores únicos destacados. Clique em “Concluído” quando terminar.

Revise as células destacadas e exclua as duplicatas indesejadas. No meu exemplo, quero apenas excluir linhas onde todos os valores estão duplicados.

3. Remova duplicatas usando filtro condicional

Se você quiser facilitar a remoção de duplicatas no Excel após usar a formatação condicional, use um filtro do Excel baseado na cor da célula para exibir apenas duplicatas ou valores exclusivos. Com esta opção, você removerá manualmente as linhas inteiras ou apenas as células duplicadas.

Configure a formatação condicional com base na seção anterior.

Abra a guia “Página inicial”, selecione o botão “Classificar e filtrar” e escolha “Filtro”.

Setas suspensas aparecerão no cabeçalho de cada coluna. Clique na seta e selecione “Filtrar por cor”. Selecione a cor das células duplicadas para visualizá-las apenas. Ou selecione “Sem preenchimento” para selecionar apenas os valores exclusivos.

Observe que nenhuma duplicata (incluindo o valor original) é mostrada quando você escolhe “Sem preenchimento”. Se ainda quiser ver todos os valores, selecione “Filtrar por cor”. Isso coloca as células coloridas ou as células sem preenchimento no topo da lista.

No meu exemplo, estou classificando pela cor das células duplicadas. Isso permite revisar as duplicatas e excluir quaisquer linhas ou valores que você não deseja manter. Se você optar por mostrar apenas os valores exclusivos, poderá copiar ou mover todos os dados visíveis para uma nova planilha. Isso deixará apenas duplicatas para trás.

Quando terminar a edição dos dados, clique na seta suspensa novamente e escolha “Limpar filtro”.

Se você deseja remover as setas suspensas, selecione “Classificar e filtrar” na barra de ferramentas e escolha “Filtrar”.

4. Encontre duplicatas no Excel com uma fórmula

As fórmulas do Excel podem realizar quase todas as tarefas em uma planilha, incluindo encontrar duplicatas. Existem várias fórmulas diferentes que você pode usar com base no que deseja ver. Tudo isso é baseado em funções COUNT . Essas fórmulas não removem duplicatas. Você deve fazer isso manualmente assim que forem encontrados. Não importa qual fórmula você use, o processo é o mesmo:

Crie uma nova coluna na mesma planilha. Eu rotulei os meus como “Duplicados”.

Selecione a primeira célula vazia em sua nova coluna. Insira a fórmula desejada (veja as várias fórmulas abaixo das etapas) na barra de funções/fórmulas ou na própria célula.

Se quiser estender a fórmula para outras células da coluna, clique e segure o pequeno quadrado no canto inferior direito da célula que contém a fórmula. Puxe para baixo para preencher quantas células desejar.

Agora que você sabe como inserir a fórmula, estas são as melhores opções para usar:

• =COUNTIF(Range, Criteria) >1 – O intervalo é a sua coluna e os critérios são a célula superior. Isso funciona bem para localizar duplicatas em uma única coluna. Por exemplo, para ver duplicatas com base apenas na coluna D, você usaria =COUNTIF(D:D, D2) >1. Um resultado “Verdadeiro” significa duplicado, enquanto “Falso” significa único.

• Se você não quiser que a referência de célula mude conforme você a arrasta, use referências de célula absolutas. Por exemplo, eu usaria =COUNTIF($D$2:$D$105, $D2) >1para verificar toda a coluna D.
• Se você quiser mostrar algo diferente de True ou False, coloque CONT.SE dentro de uma função IF: =IF(COUNTIF($D$2:$D$105, $D2) > 1, "Duplicate", ""). Isso mostrará “Duplicado” para duplicatas e uma célula em branco para valores exclusivos. Você pode facilmente usar qualquer palavra que desejar, como “Duplicado” e “Único”.
• Se quiser comparar várias colunas de uma vez, você precisará usar referências absolutas de células e uma instrução IF, como: =IF(COUNTIFS($D$2:$D$105,$D2,$E$2:$E$105,$E2,$F$2:$F$105,$F2) >1, "Duplicate", "Unique")Esta fórmula só mostrará “Duplicado” se os valores em D, E e F forem iguais. Se algum dos três for único em uma linha, “Único” será exibido.
• Se você quiser mostrar apenas os valores duplicados e não a primeira ocorrência do valor, tente isto: =IF(COUNTIFS($D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2) >1, "Duplicate", "Unique")Isso mostra apenas “Duplicado” para a segunda ou mais ocorrências.

5. Conte o número de duplicatas usando uma fórmula

Você pode usar a fórmula acima sem o > 1texto para contar o número de duplicatas em uma coluna. As duas fórmulas se tornarão =COUNTIF($D$2:$D$105, $D2)e =COUNTIF(D:D, D2). Insira a nova coluna para exibir quantas vezes cada item aparece nos dados.

Um valor “1” significa que é um valor único. Qualquer outra coisa é igual a duplicatas.

6. Remova valores duplicados usando uma fórmula

Depois de encontrar os valores duplicados (Seção 4) ou a contagem de duplicatas (Seção 5), use o método de filtro para remover duplicatas e reter valores exclusivos.

Clique em qualquer lugar da coluna com seus valores ou contagem duplicados. Vá para “Guia inicial -> Classificar e filtrar -> Filtro” para ativar a caixa suspensa de filtro no cabeçalho da coluna.

Clique na seta suspensa na coluna duplicada.

Verifique os valores que deseja manter, desmarque o que não deseja e clique em “Aplicar”. Oculte todas as duplicatas selecionando apenas “Único”, “Falso” ou qualquer que seja o seu rótulo para valores exclusivos para a duplicata método de valores e “1” para contagem de duplicatas. Veja apenas as duplicatas desmarcando os identificadores exclusivos.

Selecione as linhas visíveis pressionando Ctrl+ C. Se, por algum motivo, isso selecionar todas as linhas, incluindo linhas ocultas, use o atalho Alt+ .;

Copie os valores exclusivos ou duplicados (o que você escolher mostrar) para outra planilha. Exclua esses valores da planilha original.

Vá para “Classificar e Filtrar -> Filtrar”. Clicar em “Filtro” removerá o filtro e mostrará todas as duplicatas restantes ou valores únicos.

A partir daqui, percorra as duplicatas e remova as células ou linhas que não deseja mais. Lembre-se de que a ocorrência original é listada com os valores duplicados.

7. Remova dados duplicados usando filtros avançados

Até agora, usamos apenas filtros básicos, mas você também pode localizar e remover duplicatas no Excel usando filtros avançados.

Selecione a coluna que deseja filtrar. Abra a guia “Dados” e clique em “Avançado”.

• Na caixa “Filtro Avançado”, escolha “Filtrar a lista, no local”. Isso oculta as duplicatas no mesmo conjunto de dados. Posteriormente, você pode copiar e colar manualmente os valores exclusivos em um local diferente na mesma planilha ou em uma planilha diferente.

Se você ainda não selecionou suas colunas, selecione-as. Eles aparecerão automaticamente no campo “List range”. Deixe “Intervalo de critérios” em branco.

Marque a caixa ao lado de “Somente registros exclusivos” e clique em “OK”.

Isso mostrará valores exclusivos em seus dados. Use o atalho Alt+ ;para selecionar linhas visíveis apenas se desejar realizar alguma ação nelas, como copiar para outro local.

Pelo contrário, selecione “Copiar para outro local” na caixa Filtro Avançado se desejar que o Excel copie automaticamente os valores exclusivos para um local diferente na mesma planilha.

Selecione primeiro o “Intervalo da lista”. Você pode manter o “Intervalo de critérios” em branco.

Clique uma vez no campo “Copiar para” e selecione as linhas da planilha onde deseja copiar os dados exclusivos.

Certifique-se de que a caixa ao lado de “Somente registros exclusivos” esteja marcada.

Este método oculta toda a linha duplicada, não apenas os valores. Também oculta a ocorrência original do valor duplicado, não apenas as versões duplicadas. Você desejará visualizar as duplicatas para extrair os valores originais.

8. Remova duplicatas do Excel usando o Power Query

O Power Query pode remover valores duplicados no Excel, conforme mostrado abaixo. Este método remove toda a linha duplicada.

Abra a guia “Dados” e selecione “Da tabela/intervalo”.

Todos os seus dados devem ser selecionados automaticamente. Caso contrário, insira todo o intervalo da planilha na caixa “Criar Tabela”. Clique em “OK” quando terminar.

O editor “Power Query” será aberto. Selecione as colunas e clique com o botão direito no cabeçalho da coluna selecionada. Escolha “Remover duplicatas” no menu. Isso remove todas as duplicatas no editor do Power Query. Nesta ferramenta, a ocorrência original permanece na lista.

Se quiser remover duplicatas de toda a tabela, clique no botão “Tabela” no canto superior esquerdo e escolha “Manter duplicatas” para mostrar apenas as entradas duplicadas e remover o restante.

Isso mostra todas as duplicatas, incluindo as entradas originais. Para remover os extras e deixar apenas as entradas originais, clique em “Tabela” novamente e selecione “Remover duplicatas”.

Clique em “Fechar e Carregar” na parte superior para abrir a tabela em uma nova aba da mesma pasta de trabalho. Somente os resultados da sua consulta serão carregados. Isso não remove nada da planilha original.

9. Usando uma tabela dinâmica

Use Tabelas Dinâmicas para exibir apenas os valores exclusivos em seus dados, removendo assim as entradas duplicadas. Na verdade, isso não remove nenhuma linha ou valor dos dados originais; apenas mostra os valores exclusivos.

Abra a guia “Inserir” e selecione “Tabela Dinâmica”. Selecione “Da Tabela/Intervalo”.

Selecione a tabela ou intervalo onde deseja ocultar os valores duplicados. Todo o seu conjunto de valores deve ser selecionado por padrão. Caso contrário, pressione Ctrl+ Apara selecionar tudo. Ou use o mouse para selecionar um intervalo personalizado.

Selecione se a Tabela Dinâmica deve ser colocada na mesma planilha ou em uma nova planilha. Se for uma planilha existente, insira ou selecione a célula que deseja usar na caixa “Local”. Clique OK.”

Na barra lateral da Tabela Dinâmica, marque ou arraste as colunas das quais deseja extrair valores exclusivos para a seção “Linhas”.

Você precisará formatar a tabela dinâmica para exibi-la em formato tabular. Para isso, acesse a aba “Design” e execute os seguintes passos:

Selecione “Layout do relatório -> Mostrar em formato tabular”.

Selecione “Subtotais -> Não mostrar subtotais”.

Selecione “Layout do relatório -> Repetir todos os rótulos de itens”.

Selecione “Totais gerais -> Desativado para linhas e colunas”.

Você obterá uma tabela dinâmica com valores exclusivos em formato tabular.

Com tantas maneiras de localizar e remover duplicatas no Excel, não há razão para fazer isso novamente manualmente. Escolha seu método favorito e execute-o. Enquanto você economiza tempo com problemas duplicados, experimente estas dicas e truques do Microsoft Excel para economizar ainda mais tempo. Além disso, aprenda como limpar ainda mais seus dados no Excel .

Crédito da imagem: Pixabay . Todas as capturas de tela de Crystal Crowder.