Encontrar, destacar, filtrar, contar e excluir duplicatas no Excel

Ao procurar células duplicadas em apenas uma coluna ou lista, podemos utilizar a função COUNTIF para localizar e contar rapidamente dados duplicados de forma simples.
11. Selecione uma célula em branco ao lado da coluna na qual você deseja encontrar duplicatas.

2. Digite a fórmula =COUNTIF($C$3:$C$12, C3)>1 e pressione Enter.

3. Arraste a alça de preenchimento automático da célula da fórmula para copiar a fórmula para as demais células.

Observações: Na fórmula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 é a coluna ou lista onde você irá procurar valores duplicados. Como a coluna é fixa ao copiar a fórmula para outras células, normalmente é usada referência absoluta com “$”.
(2) C3 é a primeira célula da coluna especificada. É uma referência relativa porque precisa mudar automaticamente ao copiar a fórmula para outras células.
(3) Esta fórmula irá retornar Sim ou Não. Sim significa que o valor correspondente é duplicado, enquanto Não indica que o valor correspondente é único na coluna.
(4) Às vezes, Sim ou Não podem não ser intuitivos. Podemos combinar a fórmula original com a função SE para retornar Duplicados diretamente.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicados","")

Em alguns casos, é necessário comparar duas colunas e identificar os valores duplicados. Por exemplo, você tem duas listas de nomes e deseja encontrar os nomes duplicados na segunda lista em relação à primeira. Você pode usar as funções PROCV e SEERRO para facilitar esse processo.

1. Selecione uma célula em branco ao lado da segunda lista de nomes.

2. Digite a fórmula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),"") e pressione Enter.

3. Arraste a alça de preenchimento automático da célula da fórmula para copiar a fórmula para as demais células conforme necessário.

Observações: Na fórmula acima,
(1) D3 é a primeira célula da segunda lista de nomes. A referência precisa mudar automaticamente ao copiar a fórmula para outras células, por isso é relativa.
(2) $B$3:$B$18 é a primeira lista de nomes. É uma referência absoluta porque o intervalo precisa permanecer fixo ao copiar a fórmula para outras células.
(3) Quando um nome é duplicado com nomes da primeira lista, a fórmula retorna o nome; caso contrário, retorna vazio.
(4) Você também pode usar a fórmula =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicados","") para encontrar nomes duplicados na segunda lista em comparação com a primeira. Esta fórmula retorna "Duplicados" se o nome correspondente for duplicado.

(5) Se precisar encontrar duplicatas em duas colunas de planilhas diferentes, basta adicionar o nome da planilha antes da referência da coluna comparada. No exemplo, basta alterar $B$3:$B$18 para Sheet1!$B$3:$B$18 na fórmula.

As fórmulas apresentadas acima não consideram maiúsculas e minúsculas ao encontrar duplicatas, ou seja, "maçã" é tratado como duplicado de "MAÇÃ". Você pode usar uma fórmula de matriz para localizar valores duplicados em uma única coluna levando em conta as letras maiúsculas e minúsculas.

1. Selecione uma célula em branco ao lado da coluna na qual você deseja encontrar duplicatas.

2. Digite a fórmula de matriz =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicado") e pressione Ctrl + Shift + Enter.

3. Arraste a alça de preenchimento automático da célula da fórmula para copiar a fórmula de matriz para as demais células.

Observações: Na fórmula de matriz acima,
(1) $C$3:$C$12 é a coluna onde você precisa encontrar valores duplicados. A referência é absoluta porque permanece fixa ao copiar a fórmula de matriz para outras células.
(2) C4 é a primeira célula da coluna. A referência é relativa, pois precisa mudar automaticamente ao copiar a fórmula de matriz para outras células.
(3) Se a célula correspondente for duplicada, a fórmula de matriz retorna "Duplicado"; caso contrário, retorna vazio.

Você pode usar o recurso Formatação Condicional para destacar rapidamente células duplicadas em uma coluna ou intervalo.

1. Selecione a coluna onde deseja destacar as células duplicadas.

2. Clique em Página Inicial > Formatação Condicional > Regras de Realce de Células > Valores Duplicados. Veja a imagem abaixo:

3. Na janela Valores Duplicados, selecione Duplicado na primeira lista suspensa, escolha um cenário de destaque na segunda lista suspensa e clique em OK.

Observações: Se os cenários de destaque predefinidos não atenderem às suas necessidades, selecione Formato Personalizado na segunda lista suspensa e, em seguida, escolha cor de destaque, fonte e bordas das células conforme necessário na janela Formatar Células.

Agora todas as células duplicadas serão destacadas na coluna selecionada, conforme mostrado na imagem abaixo.

Observações:
(1) Após destacar as células duplicadas, é possível filtrar facilmente essas duplicatas. (Clique para ver como)
(2) Após destacar as células duplicadas, também é possível remover as duplicatas em lote facilmente. (Clique para ver como)

Alguns usuários preferem destacar linhas com base em células duplicadas em uma determinada coluna. Nessa situação, podemos criar uma regra personalizada de formatação condicional para realizar essa tarefa.

1. Selecione o intervalo (excluindo a linha de cabeçalho) onde deseja destacar linhas baseadas em células duplicadas.

2. Clique em Página Inicial > Formatação Condicional > Nova Regra.

3Na janela Nova Regra de Formatação,
(1) Clique para selecionar Usar uma fórmula para determinar quais células formatar opção;
(2) Digite a fórmula =COUNTIF($C$3:$C$12,$C3)>1 em Formatar valores onde esta fórmula for verdadeira caixa;
DicasNa fórmula, $C$3:$C$12 é a coluna que contém as células duplicadas, e $C3 é a primeira célula da coluna.
(3) Clique no Formatar botão.

4. Na janela Formatar Células, defina a cor de preenchimento, fonte e bordas conforme necessário e clique em OK para salvar as configurações.

Agora, no intervalo selecionado, as linhas são destacadas com base nas células duplicadas da coluna especificada. Veja a imagem:

Para destacar linhas duplicadas em um determinado intervalo, você também pode usar o recurso Formatação Condicional.

1. Selecione o intervalo, exceto a linha de cabeçalho.

2. Clique em Página Inicial > Formatação Condicional > Nova Regra.

3Na janela Nova Regra de Formatação:
(1) Clique para selecionar a Usar uma fórmula para determinar quais células formatar opção;
(2) Em Formatar valores onde esta fórmula for verdadeira caixa, digite a fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Clique no Formatar botão.

ObservaçõesNa fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 é a primeira coluna do intervalo, e $B3 é a primeira célula dessa coluna;
(2) $C$3:$C$12 é a segunda coluna do intervalo, e $C3 é a primeira célula da coluna;
(3) $D$3:$D$12 é a terceira coluna do intervalo, e $D3 é a primeira célula da coluna;
(4) Se houver mais colunas no seu intervalo, adicione as referências das colunas e suas primeiras células sucessivamente na fórmula.

4. Na janela Formatar Células, defina a cor de destaque, fonte, bordas etc. conforme necessário e clique em OK para salvar as configurações.

Agora, as linhas duplicadas são identificadas e destacadas no intervalo selecionado. Veja a imagem:

Você deve ter notado que todos os duplicados são identificados ou destacados pelos métodos acima. Às vezes, você quer ver apenas os itens duplicados exceto as primeiras ocorrências. Isso também pode ser feito usando o recurso Formatação Condicional e uma fórmula diferente.

1Selecione a coluna com cabeçalho.
DicasSe precisar destacar linhas baseadas em duplicatas em uma coluna exceto as primeiras ocorrências, selecione o intervalo sem a linha de cabeçalho.

2. Clique em Página Inicial > Formatação Condicional > Nova Regra.

3Na janela Nova Regra de Formatação:
(1) Clique para destacar a Usar uma fórmula para determinar quais células formatar opção;
(2) Em Formatar valores onde esta fórmula for verdadeira caixa, digite a fórmula =COUNTIF($C$3:$C3, C3)>1;
DicasPara destacar linhas baseadas em duplicatas em uma coluna, digite a fórmula =COUNTIF($C$3:$C3, $C3)>1.
(3) Clique no Formatar botão.

4. Na janela Formatar Células, defina a cor de destaque, fonte, bordas etc. conforme necessário e clique em OK para salvar as configurações.

Assim, as células duplicadas exceto as primeiras ocorrências na coluna selecionada (ou linhas baseadas em duplicatas na coluna especificada) serão destacadas. Veja a imagem:

Ao destacar células ou linhas duplicadas com o recurso Formatação Condicional, todas as duplicatas são destacadas com a mesma cor. No entanto, se diferentes séries de valores duplicados forem destacadas com cores distintas, será mais fácil visualizar e distinguir as diferenças. Para isso, um VBA pode ajudar a realizar essa tarefa no Excel.

1. Pressione Alt + F11 juntos para abrir a janela Microsoft Visual Basic for Applications.

2. Na janela, clique em Inserir > Módulo e cole o código abaixo na nova janela do módulo.

VBA: Destacar células duplicadas com cores diferentes:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Pressione F5 ou clique no ícone Executar para rodar o VBA.

4. Na janela do Kutools para Excel que aparecer, selecione a coluna onde deseja destacar células duplicadas com cores diferentes e clique em OK.

Cada série de valores duplicados será destacada com uma cor distinta.

A primeira ferramenta que apresento é o recurso Selecionar Células Duplicadas & Únicas, fornecido pelo Kutools para Excel. Esse recurso permite localizar rapidamente células únicas ou duplicadas.

1. Selecione a coluna ou intervalo onde deseja encontrar e destacar as células duplicadas.

2. Clique em Kutools > Selecionar > Selecionar Células Duplicadas & Únicas.

3Na janela Selecionar Células Duplicadas & Únicas, marque as opções conforme necessário e clique em Ok para concluir a operação.

ObservaçõesNa janela Selecionar Células Duplicadas & Únicas,
(1) Se desejar selecionar ou destacar todas as duplicatas exceto as primeiras ocorrências, marque a opção Valores Duplicados (Excluir a primeira correspondência) caso contrário, marque a opção Valores Duplicados (Incluir a primeira correspondência) opção.
(2) Para destacar as duplicatas, marque a opção Preencher cor de fundo e escolha uma cor de destaque conforme necessário.
(3) Para selecionar ou destacar linhas com base em duplicatas na coluna selecionada, marque a opção Selecionar linha inteira opção.
(4) Para selecionar ou destacar valores duplicados diferenciando maiúsculas de minúsculas, marque a opção Diferenciar maiúsculas de minúsculas opção.

O Kutools para Excel também oferece uma ferramenta incrível – Comparar Células, para ajudar a localizar e destacar facilmente células duplicadas em duas colunas.

1. Clique em Kutools > Comparar Células para abrir a janela Selecionar Células Iguais & Diferentes.

2. Na janela Selecionar Células Iguais & Diferentes, especifique as duas colunas nas caixas Procurar Valores em e De acordo com, marque a opção Valor igual e outras opções conforme necessário.

Observações:
(1) Para localizar linhas duplicadas, marque Por linha opção; e para localizar células duplicadas, marque a opção Por célula na seção Método ;
(2) Marque a opção Preencher cor de fundo e escolha uma cor de destaque se desejar destacar as linhas ou células duplicadas;
(3) Marque a opção Selecionar linha inteira caso precise selecionar ou destacar a linha inteira com base nas duplicatas;
(4) Marque a opção Diferenciar maiúsculas de minúsculas caso queira localizar ou destacar duplicatas diferenciando maiúsculas de minúsculas.

3. Clique em Ok para concluir as configurações.

Assim, as duplicatas na coluna especificada na caixa Procurar valores em serão identificadas e destacadas.

Este método mostra como identificar e destacar células duplicadas por uma regra de formatação condicional e, em seguida, filtrar facilmente pela cor de destaque no Excel.

1. Aplique a formatação condicional para localizar e destacar duplicatas na coluna especificada. (Clique para ver como)

2Clique para selecionar o cabeçalho da coluna especificada e clique em Dados > Filtro.

3Em seguida, clique no ícone de filtro no cabeçalho da coluna e selecione Filtrar por Core depois selecione a cor de formatação condicional na lista suspensa. Veja a imagem:

Assim, apenas as linhas com células duplicadas serão filtradas. Veja a imagem:

Como alternativa, também podemos identificar duplicatas com fórmula em uma coluna auxiliar e, em seguida, filtrar duplicatas facilmente usando essa coluna no Excel.

1. Ao lado dos dados originais, adicione uma coluna auxiliar e digite Duplicado como cabeçalho da coluna.

2. Selecione a primeira célula em branco abaixo do cabeçalho, digite a fórmula =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicado","") e arraste a alça de preenchimento automático para copiar a fórmula para as demais células.

Observações: Na fórmula acima, $C$3:$C$12 é a coluna que contém os dados duplicados e C3 é a primeira célula (exceto o cabeçalho) da coluna.

3. Clique para selecionar o cabeçalho da coluna – Duplicados, e clique em Dados > Filtro.

4Em seguida, clique no ícone de filtro no cabeçalho da coluna, marque apenas Duplicadoe clique em OK Veja a imagem:

Agora, apenas as linhas com valores duplicados serão filtradas. Veja a imagem:

Em geral, podemos usar =COUNTIF(intervalo, critério) para contar o número total de vezes que um determinado valor aparece no intervalo especificado. Por exemplo, para contar quantas vezes “Maçã” aparece na lista A2:A10, podemos usar a fórmula =COUNTIF(A2:A10, "Maçã").

No entanto, a fórmula =COUNTIF(intervalo, critério) conta apenas o valor duplicado especificado. E se precisar contar valores duplicados com dois ou mais critérios? E se precisar contar duplicatas diferenciando maiúsculas de minúsculas com critérios? Os métodos abaixo ajudam a resolver esses problemas.

Podemos usar uma fórmula de matriz para contar valores duplicados diferenciando maiúsculas de minúsculas com critérios no Excel. Por exemplo, para contar quantas vezes o valor "Maçã" aparece na lista B2:B21 considerando maiúsculas e minúsculas, faça o seguinte:

1. Selecione uma célula em branco.

2. Digite a fórmula =SUM(--EXACT(B2:B20,E2)).

3. Pressione Ctrl + Shift + Enter para obter o resultado da contagem.

ObservaçõesNa fórmula de matriz,
(1) B2:B20 é a coluna onde você irá contar as duplicatas. (2) E2 é a célula que contém o valor especificado que você deseja contar. Você pode alterar a referência da célula para o valor entre aspas, por exemplo, "Maçã".

Às vezes, você pode querer contar duplicatas com dois ou mais critérios. Você pode usar a função COUNTIFS para isso.
Por exemplo, há uma tabela de vendas de frutas como mostrado na imagem abaixo. Precisamos contar quantas vezes a maçã foi vendida em05/07/2020 e o valor da venda é maior que300. Você pode contar as duplicatas com esses critérios da seguinte forma:

1. Selecione uma célula em branco.

2. Digite a fórmula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Pressione Enter para obter o resultado da contagem.

ObservaçõesNa fórmula acima,
(1) B3:B20 é a coluna de datas (primeira), e G4 é o critério de data;
(2) C3:C20 é a coluna de frutas (segunda), e G3 é o critério de fruta;
(3) D3:D20 é a coluna de valores (terceira), e ">300" é o critério de valor.
(4) Se houver mais colunas e critérios na sua tabela, adicione as referências das colunas e critérios.

Para contar todas as duplicatas em uma coluna exceto as primeiras ocorrências, siga os passos:

1. Selecione uma célula em branco ao lado da coluna.

2Digite a fórmula =IF(COUNTIF($B$3:B3,B3)>1,"SIM","")e arraste a alça de preenchimento automático para copiar a fórmula para as demais células.

ObservaçõesNa fórmula acima,
(1) $B$3:B3 é o intervalo onde você conta as duplicatas. Em $B$3:B3, B3 mudará automaticamente ao copiar a fórmula para outras células.
(2) B3 é a primeira célula da coluna especificada.
(3) Esta fórmula retorna SIM ou vazio. SIM indica valor duplicado, enquanto vazio significa valor único.

Assim, todas as duplicatas na coluna especificada são identificadas. Podemos contar os resultados da fórmula para obter o número total de duplicatas.

3. Selecione uma célula em branco.

4Digite a fórmula =COUNTIF(C3:C16,"SIM")e pressione Enter para finalizar.

ObservaçõesNa fórmula acima,
(1) C3:C16 é o intervalo onde aplicamos a fórmula para identificar duplicatas na etapa anterior.
(2) SIM é o valor retornado pela fórmula anterior.

Assim, obtemos o número total de valores duplicados na coluna especificada. O total não inclui as primeiras ocorrências.

Para contar o número de todas as duplicatas incluindo as primeiras ocorrências no Excel, você pode usar uma fórmula de matriz.

1. Selecione uma célula em branco.

2. Digite a fórmula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. Pressione Ctrl + Shift + Enter para obter o resultado da contagem.

Observações: Na fórmula acima, B3:B16 é a coluna especificada na qual queremos contar duplicatas incluindo as primeiras ocorrências.

Para simplificar seu trabalho e evitar memorizar fórmulas longas, você pode usar o recurso Selecionar Células Duplicadas & Únicas, fornecido pelo Kutools para Excel, para contar rapidamente o número de valores duplicados na lista ou coluna especificada, incluindo ou excluindo as primeiras ocorrências.

1. Selecione a coluna onde irá contar o número de valores duplicados e clique em Kutools > Selecionar > Selecionar Células Duplicadas & Únicas.

2. Na janela Selecionar Células Duplicadas & Únicas, marque a opção Valores Duplicados (Excluir a primeira correspondência) ou Valores Duplicados (Incluir a primeira correspondência) conforme necessário e clique em Ok.

3. Assim, todos os valores duplicados incluindo ou excluindo as primeiras ocorrências serão selecionados e, ao mesmo tempo, uma janela mostrará quantas células foram selecionadas. Veja a imagem acima.

Suponha que você queira comparar duas listas de nomes e contar o número de duplicatas entre elas. Como resolver isso rapidamente? Também podemos fazer isso com uma fórmula no Excel.

1. Selecione uma célula em branco.

2. Digite a fórmula =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Pressione Enter .

ObservaçõesNa fórmula acima,
(1) B3:B12 é a primeira coluna de nomes onde você irá contar duplicatas.
(2) D3:D18 é a segunda coluna de nomes usada como base para a contagem de duplicatas.

Como alternativa, podemos usar o suplemento Kutools para Excel para contar rapidamente o número total de células duplicadas entre duas colunas.

1. Clique em Kutools > Selecionar > Selecionar Células Iguais & Diferentes.

2Na janela Selecionar Células Iguais & Diferentes,
(1) Especifique as duas colunas nas caixas Procurar valores em e De acordo com separadamente.
(2) Marque a opção Por célula opção.
(3) Marque a opção Valor igual opção.

4. Clique em Ok.

Assim, todas as células duplicadas na primeira coluna serão selecionadas e, ao mesmo tempo, uma janela mostrará quantas células duplicadas foram selecionadas. Veja a imagem:

Observações: Este recurso conta o número total de valores duplicados na coluna especificada na caixa Procurar valores em na janela Selecionar Células Iguais & Diferentes. Se precisar contar o total de duplicatas na segunda coluna, utilize novamente o recurso Selecionar Células Iguais & Diferentes especificando a segunda coluna na caixa Procurar valores em.

Você pode contar rapidamente cada valor duplicado uma vez com a seguinte fórmula:

1. Selecione uma célula em branco.

2. Digite a fórmula =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&""))

3. Pressione Enter para obter o resultado da contagem.

Observações: Na fórmula acima, C3:C19 é a coluna especificada onde você deseja contar cada valor duplicado uma vez.

Ao contar uma lista, você pode contar cada valor duplicado uma vez diferenciando maiúsculas de minúsculas usando uma fórmula de matriz no Excel.

1. Selecione uma célula em branco.

2. Digite a fórmula de matriz =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))),0),0)).

3. Pressione Ctrl + Shift + Enter juntos para obter o resultado da contagem.

Observações: Na fórmula de matriz acima, C3:C19 é a coluna especificada onde você irá contar cada série de duplicatas uma vez diferenciando maiúsculas de minúsculas.

Se você tiver o Kutools para Excel instalado, também pode usar o recurso Extrair células únicas em um intervalo (incluindo o primeiro duplicado) para contar rapidamente cada série de valores duplicados uma vez no Excel.

1. Selecione uma célula em branco.

2. Clique em Kutools > Assistente de Fórmulas > Contagem > Extrair células únicas em um intervalo (incluindo o primeiro duplicado).

3. Na janela Assistente de Fórmulas, especifique a coluna onde irá contar duplicatas uma vez na caixa Intervalo e clique em Ok.

O resultado da contagem será preenchido imediatamente na célula selecionada.

Podemos usar o recurso Subtotal para contar cada série de valores duplicados em uma coluna no Excel.

1Selecione a coluna onde irá contar cada série de valores duplicados e clique em Dados > Classificar Crescente ou Classificar Decrescente.

2. Na janela de aviso de classificação, marque a opção Expandir a seleção e clique em Classificar.

Assim, a seleção será classificada pelos valores duplicados da coluna especificada.

3Mantenha a seleção e clique em Dados > Subtotal.

4Na janela Subtotal,
(1) Selecione a coluna especificada em A cada alteração em lista suspensa;
(2) Selecione Contagem em Usar função lista suspensa;
(3) Marque apenas a coluna especificada em Adicionar subtotal a caixa de lista;
(4) Clique em OK botão.

Assim, cada série de valores duplicados será contada e o resultado será exibido abaixo de cada série, conforme imagem acima.

Também é possível criar uma tabela dinâmica para contar rapidamente cada série de valores duplicados no Excel.

1. Selecione o intervalo contendo a coluna especificada e clique em Inserir > Tabela Dinâmica.

2. Na janela Criar Tabela Dinâmica, especifique onde deseja posicionar a nova tabela dinâmica e clique em OK.

3. No painel Campos da Tabela Dinâmica, arraste a coluna especificada para as seções Linhas e Valores . Assim, cada série de valores duplicados na coluna será contada em lote. Veja a imagem:

Se você já instalou o Kutools para Excel, pode usar o recurso Mesclar Linhas Avançado para contar rapidamente cada série de valores duplicados na coluna especificada.

Observações: O recurso Mesclar Linhas Avançado irá modificar o intervalo selecionado e remover linhas com base em valores duplicados na coluna chave especificada. Para proteger seus dados, recomenda-se fazer backup ou copiar os dados antes de realizar as operações abaixo.

1. Adicione uma coluna em branco à direita do intervalo de dados original e nomeie-a como Contagem.

2. Selecione o intervalo de dados original e a nova coluna juntos e clique em Kutools > Mesclar & Dividir > Mesclar Linhas Avançado.

3Na janela Mesclar Linhas Avançado,
(1) Clique para selecionar a coluna especificada que você irá contar cada série de valores duplicados e clique em Coluna Chave.
(2) Clique para selecionar a nova coluna (Contagem) e depois clique em Calcular > Contagem.
(3) Especifique tipos de combinação ou cálculo para outras colunas, se necessário.
(4) Clique em Ok botão.

Assim, cada série de valores duplicados na coluna especificada será contada em lote. Veja a imagem:

Suponha que há uma lista de frutas em uma coluna. Algumas frutas aparecem várias vezes. Agora, você precisa marcar cada fruta duplicada na ordem em que aparece. Como resolver isso? Aqui, mostramos uma fórmula para facilitar esse processo no Excel.

1. Adicione uma coluna em branco à direita dos dados originais.

2. Digite a fórmula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") na primeira célula da nova coluna.

3. Arraste a alça de preenchimento automático para copiar a fórmula para as demais células.

ObservaçõesNa fórmula acima,
(1) $C$3:$C$14 é a coluna especificada onde você deseja contar os valores duplicados em ordem.
(2) C3 é a primeira célula da coluna especificada.
(3) Se o valor correspondente for duplicado, a fórmula retorna o número sequencial1,2,3… conforme a ordem de aparição; se for único, retorna vazio.

Você pode usar o recurso Remover Duplicatas para remover diretamente todos os valores duplicados exceto as primeiras ocorrências.

1. Selecione a coluna onde deseja remover todos os valores duplicados exceto as primeiras ocorrências.

2. Clique em Dados > Remover duplicatas.

3. Na janela de aviso Remover Duplicatas, marque a opção Continuar com a seleção atual e clique em Remover Duplicatas.

Dicas: Para remover linhas com base nos valores duplicados da seleção, marque a opção Expandir a seleção .

4. Na janela Remover Duplicatas, marque apenas a coluna especificada e clique em OK.

Dicas: Se você marcou a opção Expandir a seleção na etapa anterior, todas as colunas serão listadas aqui. Mesmo assim, marque apenas a coluna especificada.

5. Em seguida, uma janela mostrará quantos valores duplicados foram removidos. Clique em OK para fechar.

Você também pode usar o recurso Filtro Avançado para remover todos os valores duplicados da coluna especificada facilmente.

1. Clique em Dados > Avançado.

2Na janela Filtro Avançado,
(1) Marque a opção Copiar para outro local ;
(2) Em Intervalo da lista caixa, selecione a coluna especificada de onde irá remover duplicatas;
(3) Em Copiar para caixa, especifique o intervalo onde irá colar a coluna;
(4) Marque a opção Somente registros únicos opção.
(5) Clique em OK botão.

Assim, a coluna especificada será colada no intervalo definido com todos os valores duplicados removidos, exceto as primeiras ocorrências. Veja a imagem:

Você também pode usar um VBA para remover rapidamente valores duplicados exceto as primeiras ocorrências de uma coluna no Excel.

1. Pressione Alt + F11 para abrir a janela Microsoft Visual Basic for Application.

2. Clique em Inserir > Módulo e cole o código VBA abaixo na nova janela do módulo.

VBA: Remover valores duplicados exceto as primeiras ocorrências

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Pressione F5 ou clique no botão Executar para rodar o VBA.

4. Na janela que aparecer, especifique o intervalo de onde irá remover duplicatas e clique em OK.

Todos os valores duplicados, exceto as primeiras ocorrências, serão removidos imediatamente do intervalo especificado.

Observação: Este código VBA diferencia maiúsculas de minúsculas.

Podemos destacar todos os valores duplicados, incluindo as primeiras ocorrências, em uma coluna ou lista com uma regra de formatação condicional e, em seguida, filtrar todos os valores duplicados pela cor de destaque. Depois disso, basta selecionar todas as células filtradas e removê-las em lote.

1. Aplique a formatação condicional para destacar valores duplicados. (Clique para ver como)

2. Selecione a coluna de onde irá remover valores duplicados (incluindo as primeiras ocorrências) e clique em Dados > Filtro.

3Clique no ícone de filtro no cabeçalho da coluna especificada. No menu suspenso, selecione Filtrar por Core, em seguida, escolha a cor de destaque no submenu.

Assim, todos os valores duplicados serão filtrados.

4. Selecione todas as células filtradas, clique com o botão direito e selecione Excluir Linha no menu de contexto. Na janela de confirmação, clique em OK para continuar.

5. Assim, todos os valores duplicados serão removidos em lote. Mantenha a lista filtrada selecionada e clique em Filtro > Dados novamente para cancelar o filtro.

Agora, todas as células duplicadas, incluindo as primeiras ocorrências, serão removidas em lote, restando apenas os valores únicos.

Observações: Este método remove linhas com base nos valores duplicados da coluna especificada.

Também podemos usar uma fórmula para identificar valores duplicados, incluindo as primeiras ocorrências, em uma coluna auxiliar, depois filtrar os valores duplicados pelos resultados da fórmula e, por fim, remover esses valores filtrados em lote.

1. Adicione uma coluna auxiliar ao lado da coluna especificada, digite a fórmula =COUNTIF($B$3:$B$11,B3) na primeira célula da coluna auxiliar e arraste a alça de preenchimento automático para copiar a fórmula para as demais células. Veja a imagem:

Observação: Na fórmula acima, $B$3:$B$11 é a coluna especificada de onde irá remover duplicatas e B3 é a primeira célula da coluna.

2. Selecione a coluna auxiliar e clique em Dados > Filtro.

3Clique no ícone de filtro no cabeçalho da coluna auxiliar, no menu suspenso marque todos os valores exceto 1e clique em OK Veja a imagem:

4. Agora, todos os valores duplicados serão filtrados. Selecione as células filtradas na coluna auxiliar, clique com o botão direito e selecione Excluir Linha no menu de contexto.

5. Na janela de confirmação, clique em OK para continuar.

6. Agora, todos os valores duplicados e suas linhas serão removidos em lote. Clique em Dados > Filtro novamente para cancelar o filtro.

Assim, todos os valores duplicados, incluindo as primeiras ocorrências, serão excluídos em lote.

Se você tiver o Kutools para Excel instalado, também pode usar o recurso Selecionar Células Duplicadas & Únicas para selecionar e excluir rapidamente valores duplicados, incluindo ou excluindo as primeiras ocorrências, no Excel.

1. Selecione a coluna de onde irá remover duplicatas.

2. Clique em Kutools > Selecionar > Selecionar Células Duplicadas & Únicas.

3. Na janela Selecionar Células Duplicadas & Únicas, marque a opção Valores Duplicados (Incluir a primeira correspondência) e clique em Ok.

Observações:
(1) Para selecionar e excluir valores duplicados exceto as primeiras ocorrências, marque a opção Valores Duplicados (Excluir a primeira correspondência) opção.
(2) Para selecionar e excluir linhas com base em valores duplicados na coluna especificada, marque a opção Selecionar linha inteira opção.
(3) Para selecionar e excluir valores duplicados diferenciando maiúsculas de minúsculas, marque a opção Diferenciar maiúsculas de minúsculas opção.
(4) Para selecionar, destacar e excluir células ou linhas duplicadas, marque as opções Preencher cor de fundo ou Preencher cor da fonte e defina as cores conforme necessário.

4. Em seguida, uma janela mostrará quantas células foram selecionadas; clique em OK para fechar.

5. Clique com o botão direito nas células selecionadas e selecione Excluir no menu de contexto.

6. Na janela Excluir, marque a opção Deslocar células para cima e clique em OK.

Assim, todos os valores duplicados, incluindo as primeiras ocorrências, serão removidos em lote.

Na maioria dos casos, identificamos valores duplicados em uma coluna e, em seguida, removemos as linhas inteiras com base nesses valores. Na prática, essa operação é semelhante à remoção de duplicatas de uma única coluna. Portanto, podemos usar soluções semelhantes para excluir linhas com base em duplicatas na coluna especificada.

O primeiro método é usar o recurso interno Remover Duplicatas para remover linhas com base em duplicatas na coluna especificada. Basta selecionar o intervalo de onde irá remover linhas, clicar em Dados > Remover Duplicatas para ativar o recurso, marcar apenas a coluna especificada na janela Remover Duplicatas e clicar em OK para concluir a remoção.

Também podemos usar os recursos Formatação Condicional e Filtro para remover linhas baseadas em valores duplicados na coluna especificada. Primeiro, destaque as linhas com base em valores duplicados em uma determinada coluna usando uma regra de formatação condicional (clique para ver como). Em seguida, filtre o intervalo por cor. Depois, exclua todas as linhas filtradas facilmente. Por fim, limpe ou cancele o filtro e você verá apenas as linhas com valores únicos na coluna especificada.

Como alternativa, adicione uma coluna auxiliar e use a fórmula =COUNTIF($C$3:$C$21,C3) para identificar duplicatas na coluna especificada. Depois, filtre os números maiores que1 na coluna auxiliar e remova todas as linhas filtradas facilmente. Após limpar o filtro, restarão apenas as linhas com valores únicos na coluna.

O suplemento Kutools para Excel também oferece um recurso prático, Selecionar Células Duplicadas & Únicas, para selecionar rapidamente linhas com base em valores duplicados na coluna especificada e, em seguida, remover essas linhas selecionadas facilmente pelo menu de clique direito.

O recurso Mesclar Linhas Avançado do Kutools para Excel também pode remover rapidamente linhas com base em valores duplicados na coluna chave especificada.

Podemos adicionar uma coluna auxiliar e usar uma fórmula para identificar valores duplicados entre duas colunas e, em seguida, filtrar e excluir esses valores facilmente.

1. Adicione uma coluna em branco ao lado da coluna especificada de onde irá remover duplicatas.

2. Na primeira célula da coluna auxiliar (exceto o cabeçalho), digite a fórmula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Único","Duplicado") e arraste a alça de preenchimento automático para copiar a fórmula para as demais células.

ObservaçõesNa fórmula acima,
(1) C2 é a primeira célula da coluna especificada de onde irá remover duplicatas;
(2) $A$2:$A$13 é a outra coluna que precisamos comparar.
(3) Esta fórmula retorna Duplicado se o valor correspondente for duplicado com valores da outra coluna, e retorna Único se for diferente dos valores da outra coluna.

3. Selecione a coluna auxiliar e clique em Dados > Filtro.

4Clique no ícone de filtro no cabeçalho da coluna auxiliar, no menu suspenso marque apenas Duplicadoe clique em OK botão.

5. Agora, todos os valores duplicados serão filtrados. Selecione as células filtradas, clique com o botão direito e selecione Excluir Linha no menu de contexto. Em seguida, clique em OK na janela de confirmação.

6. Assim, todos os valores duplicados serão removidos da coluna especificada. Clique em Dados > Filtro novamente para cancelar o filtro.

Assim, apenas valores únicos permanecerão na coluna especificada. Você pode remover a coluna auxiliar se desejar.

Observações: Este método remove as linhas inteiras com base nos valores duplicados da coluna especificada.

Se você tiver o Kutools para Excel instalado, pode usar o recurso Selecionar Células Iguais & Diferentes para selecionar rapidamente valores duplicados entre duas colunas e, em seguida, removê-los facilmente.

1. Clique em Kutools > Selecionar > Selecionar Células Iguais & Diferentes para ativar o recurso.

2. Na janela Selecionar Células Iguais & Diferentes, especifique ambas as colunas nas caixas Procurar Valores em e De acordo com separadamente, marque as opções Por célula e Valor igual e clique em Ok. Veja a imagem:

3. Assim, todos os valores duplicados entre as duas colunas serão selecionados na primeira coluna (a coluna especificada na caixa Procurar valor em). Clique em OK na janela que aparecer.

4. Você pode pressionar Delete para remover esses valores duplicados diretamente ou clicar com o botão direito e selecionar Excluir no menu de contexto.