Como destacar células e linhas duplicadas no Excel

Neste tutorial, você aprenderá como mostrar duplicatas no Excel. Vamos analisar de perto os diferentes métodos para sombrear células duplicadas, linhas inteiras ou dupes consecutivos usando formatação condicional. Além disso, você verá como destacar duplicatas com cores diferentes usando uma ferramenta especializada.

Na última semana, exploramos diferentes maneiras de identificar duplicatas no Excel. Sem dúvida, as fórmulas duplicadas são muito úteis, mas destacar entradas duplicadas com uma cor definida pode facilitar ainda mais a análise dos dados.

A maneira mais rápida de encontrar e destacar duplicatas no Excel é usando a formatação condicional. A maior vantagem deste método é que ele não só mostra as duplicatas existentes, mas também detecta e colore novas duplicatas conforme você insere, edita ou sobrescreve seus dados.

Outras maneiras neste tutorial, você encontrará uma série de maneiras de destacar registros duplicados, dependendo da sua tarefa específica. Estas técnicas funcionam em todas as versões do Excel 2016, Excel 2013, Excel 2010 e inferiores.

• Aumentar duplicatas no Excel com as primeiras ocorrências (built-em regra)
• Como destacar duplicatas no Excel excepto na 1ª instância
• Como destacar duplicatas num intervalo (múltiplas colunas)
• Como destacar Nth e subsequente duplicata records
• Shade linhas inteiras baseadas em valores duplicados em uma coluna
• Como destacar linhas duplicadas no Excel
• Como visualizar células duplicadas consecutivas
• Duplicate Remover – a maneira mais rápida de destacar dupes no Excel

Como destacar duplicatas no Excel usando a regra interna (com 1ª ocorrência)

Para começar, em todas as versões do Excel, há uma regra predefinida para destacar as células duplicadas. Para usar esta regra em suas planilhas, execute os seguintes passos:

1. Selecione os dados que você deseja verificar para duplicatas. Isso pode ser uma coluna, uma linha ou um intervalo de células.
2. Na guia Home, no grupo Estilos, clique em Formatação condicional >Regras de destaque de células >Valores duplicados…
   
3. A janela de diálogo Valores duplicados será aberta com o formato Preenchimento vermelho claro e Texto vermelho escuro selecionado por padrão. Para aplicar o formato padrão, simplesmente clique em OK.

Parte do preenchimento vermelho e formatação de texto, um punhado de outros formatos pré-definidos estão disponíveis na lista suspensa. Para destacar duplicatas usando alguma outra cor, clique em Custom Format… (o último item na lista suspensa) e selecione a cor de preenchimento e/ou fonte de seu gosto.

Usando a regra embutida, você pode destacar duplicatas em uma coluna ou em várias colunas como mostrado na seguinte captura de tela:

Ao usar a regra embutida do Excel para destacar valores duplicados, tenha em mente as duas coisas a seguir:

• Funciona apenas para células individuais. Para destacar linhas duplicadas, você precisaria criar suas próprias regras baseadas em valores em uma coluna específica ou comparando valores em várias colunas.
• Sombreia células duplicadas incluindo suas primeiras ocorrências. Para destacar todas as duplicatas, exceto nas primeiras instâncias, crie uma regra de formatação condicional com base nesta fórmula.

Como destacar duplicatas sem as primeiras ocorrências

Para destacar a segunda e todas as subsequentes ocorrências duplicadas, selecione as células que deseja colorir, e crie uma regra baseada em fórmulas desta forma:

1. Na guia Home, no grupo Estilos, clique em Formatação condicional > Nova regra > Use uma fórmula para determinar quais células devem ser formatadas.
2. Na caixa Formatar valores onde esta fórmula é verdadeira, insira uma fórmula semelhante a esta:
   =COUNTIF($A:$A2,$A2)>1

   Onde A2 é a célula mais alta da gama seleccionada.

3. Clique no botão Formatar… e seleccione a cor de preenchimento e/ou fonte que pretende.
4. Finalmente, clique em OK para salvar e aplicar a regra.

Se você não tiver muita experiência com a formatação condicional do Excel, você encontrará os passos detalhados para criar uma regra baseada em fórmulas no seguinte tutorial: Formatação condicional do Excel baseada em outro valor de célula.

Como resultado, as células duplicadas excluindo as primeiras instâncias serão destacadas com a cor que você escolher:

Como mostrar 3º, 4º e todos os registros duplicados subsequentes

Para ver duplicatas começando com a Nª ocorrência, crie uma regra de formatação condicional baseada na fórmula como no exemplo anterior, com a única diferença que você substitui >1 no final da fórmula com o número requerido. Por exemplo:

Para destacar a 3ª e todas as instâncias duplicadas subsequentes, crie uma regra de formatação condicional baseada nesta fórmula:

=COUNTIF($A:$A2,$A2)>=3

Para destacar a 4ª e todas as instâncias duplicadas subsequentes, use esta fórmula:

=COUNTIF($A:$A2,$A2)>=4

Para destacar apenas ocorrências específicas, use o igual ao operador (=). Por exemplo, para destacar apenas 2ª instâncias, você iria com esta fórmula:

=COUNTIF($A:$A2,$A2)=2

Como destacar duplicatas em um intervalo (múltiplas colunas)

Quando quiser verificar duplicatas em múltiplas colunas, não comparando as colunas entre si, mas encontrando todas as instâncias do mesmo item em todas as colunas, use uma das seguintes soluções.

Duas duplicatas em várias colunas, incluindo as primeiras ocorrências

Se a primeira instância de um item que aparece no conjunto de dados mais de uma vez for considerada duplicata, a maneira mais fácil é usar a regra interna do Excel para duplicatas.

Or, crie uma regra de formatação condicional com esta fórmula:

Por exemplo, para destacar duplicatas no intervalo A2:C8, a fórmula vai da seguinte forma:

=COUNTIF($A:$C, A2)>1

Por favor note o uso de referências absolutas de células para o intervalo ($A$2:$C$8), e referências relativas para a célula superior (A2).

Duas duplicatas com luz forte em múltiplas colunas sem as primeiras ocorrências

A solução para este cenário é muito mais complicada, não admira que o Excel não tenha uma regra embutida para isso:)

Para destacar entradas duplicadas em várias colunas ignorando as primeiras ocorrências, você terá de criar 2 regras com as seguintes fórmulas:

Regra 1. Aplica-se à primeira coluna

Aqui você usa exatamente a mesma fórmula que usamos para destacar duplicatas sem as 1ª ocorrências em uma coluna (os passos detalhados podem ser encontrados aqui).

Neste exemplo, estamos criando uma regra para A2:A8 com esta fórmula:

=COUNTIF($A:$A2,$A2)>1

Como resultado, os itens duplicados sem as 1ª ocorrências são destacados na coluna mais à esquerda do intervalo (existe apenas um item deste tipo no nosso caso):

Regra 2. Aplica-se a todas as colunas subsequentes

Para destacar duplicatas nas restantes colunas (B2:C8), use esta fórmula:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

Na fórmula acima, a primeira função COUNTIF conta as ocorrências de um determinado item na primeira coluna, e a segunda COUNTIF faz o mesmo para todas as colunas subsequentes. E então, você soma esses números e verifica se a soma é maior que 1.

Como resultado, todos os itens duplicados, excluindo as suas primeiras ocorrências, são encontrados e destacados:

Duas duplicatas com uma única regra

Outra solução possível é adicionar uma coluna vazia à esquerda do conjunto de dados e combinar as fórmulas acima em uma única fórmula como esta:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Onde B2 é a célula superior com dados na 2ª coluna do intervalo de destino.

Para entender melhor a fórmula, vamos dividi-la em 2 partes principais:

• Para a primeira coluna (B), a condição IF nunca é cumprida, então apenas a segunda função COUNTIF é aplicada, que é a fórmula regular que já usamos para encontrar duplicatas sem 1ª ocorrências em uma coluna.
• Para todas as colunas subsequentes (C2:D8), o ponto chave é o uso inteligente de referências absolutas e relativas nas duas funções COUNTIF. Para facilitar a compreensão, eu copiei para a coluna G, assim você pode ver como a fórmula muda quando aplicada a outras células:
  

Porque a condição IF é sempre VERDADEIRA para todas as colunas que não a primeira (o número de colunas é maior que 1), a fórmula procede desta forma:

• Conta o número de ocorrências de um determinado item (D5 na imagem da tela acima) em todas as colunas à esquerda da coluna dada: COUNTIF(B:$C,D5)
• Conta o número de ocorrências de um determinado item na coluna do item, até a célula do item: COUNTIF(D:D5,D5)
• Finalmente, a fórmula adiciona os resultados de ambas as funções COUNTIF. Se o número total for maior que 1, ou seja, se houver mais de uma ocorrência do item, a regra é aplicada e o item é destacado.

Aumentar linhas inteiras com base em valores duplicados em uma coluna

Se a sua tabela contém várias colunas, você pode querer destacar linhas inteiras com base em registros duplicados em uma coluna específica.

Como você já sabe, a regra interna do Excel para duplicatas funciona apenas no nível da célula. Mas uma regra personalizada baseada em fórmulas não tem problemas com linhas de sombreamento. O ponto chave é selecionar as linhas inteiras e depois criar uma regra com uma das seguintes fórmulas:

• >Para destacar linhas duplicadas excluindo as primeiras ocorrências:
  =COUNTIF($A:$A2, $A2)>1
• Para destacar linhas duplicadas incluindo as primeiras ocorrências:
  =COUNTIF($A:$A, $A2)>1

Onde A2 é a primeira célula e A15 é a última célula usada na coluna que você deseja verificar para duplicatas. Como você vê, o uso inteligente de referências de células absolutas e relativas é o que faz a diferença.
A seguinte captura de tela demonstra ambas as regras em ação:

Como destacar linhas duplicadas no Excel

O exemplo anterior demonstrou como colorir linhas inteiras baseadas em valores duplicados em uma coluna específica. Mas e se você quiser visualizar linhas que têm valores idênticos em várias colunas? Ou, como destacar linhas absolutamente duplicadas, aquelas que têm valores completamente iguais em todas as colunas?

Para isso, utilize a função COUNTIFS que permite comparar células por múltiplos critérios. Por exemplo, para destacar linhas duplicadas que tenham valores idênticos nas colunas A e B, use uma das seguintes fórmulas:

• Para destacar linhas duplicadas, exceto as primeiras ocorrências:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Para destacar linhas duplicadas com as primeiras ocorrências:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

A seguinte captura de tela demonstra o resultado:

Como você entende, o exemplo acima é apenas para fins de demonstração. Ao destacar linhas duplicadas em suas planilhas da vida real, você naturalmente não está limitado a comparar valores apenas em 2 colunas, a função COUNTIFS pode processar até 127 pares de intervalos/critérios.

Aumentar células duplicadas consecutivas no Excel

Por vezes, você pode não precisar destacar todas as duplicatas em uma coluna, mas mostrar apenas as células duplicadas consecutivas, ou seja, as que estão próximas umas das outras. Para fazer isso, selecione as células com dados (sem incluir o cabeçalho da coluna) e crie uma regra de formatação condicional com uma das seguintes fórmulas:

• Para destacar duplicatas consecutivas sem as primeiras ocorrências:
  =$A1=$A2
• Para destacar duplicatas consecutivas com 1ª ocorrências:
  =OR($A1=$A2, $A2=$A3)

A seguinte captura de tela demonstra destacar textos duplicados consecutivos, mas estas regras também funcionarão para números e datas duplicados consecutivos:

Se a sua planilha Excel puder ter linhas vazias e você não quiser que as células consecutivas em branco sejam destacadas, faça as seguintes melhorias nas fórmulas:

• Para destacar células duplicadas consecutivas sem 1ª ocorrência e ignorar células em branco:
  =AND($A2<>"", $A1=$A2)
• Para destacar células duplicadas consecutivas com 1ª ocorrência e ignorar células em branco:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Como você pode ver, não é difícil destacar duplicatas no Excel usando formatação condicional. No entanto, há ainda uma maneira mais rápida e fácil. Para descobrir, leia a próxima seção deste tutorial.

Como destacar duplicatas no Excel com Duplicate Remover

O add-in Duplicate Remover é a solução tudo-em-um para lidar com registros duplicados no Excel. Ele pode encontrar, destacar, selecionar, copiar ou mover células duplicadas ou linhas duplicadas inteiras.

Embora seu nome, o add-in pode rapidamente destacar duplicatas em cores diferentes sem excluí-las.

O Duplicate Remover adiciona 3 novos recursos à sua Fita do Excel:

• Tabela de Deduplicação – para encontrar e destacar duplicatas imediatamente em uma tabela.
• Administrador de Duplicatas – assistente passo a passo com opções avançadas para identificar e destacar duplicatas ou valores únicos em 1 tabela.
• Comparar 2 Tabelas – encontrar e destacar duplicatas comparando duas colunas ou duas tabelas separadas.

Após instalar Ultimate Suite para Excel, você encontrará estas ferramentas na guia Ablebits Data no grupo Deduplicação:

Dublagem de duplicatas em Excel em alguns cliques

Para este exemplo, criei a seguinte tabela com algumas centenas de linhas. E nosso objetivo é destacar linhas duplicadas que tenham valores iguais nas três colunas:

Acreditar ou não, você pode obter o resultado desejado com apenas 2 cliques do mouse 🙂

1. Com qualquer célula da tabela selecionada, clique no botão Tabela de Deduplicação, e o add-in inteligente irá pegar a tabela inteira.
2. A janela de diálogo da Tabela de Deduplicação irá abrir com todas as colunas selecionadas automaticamente, e a opção Duplicatas coloridas escolhida por padrão. Então, tudo que você tem que fazer é clicar OK 🙂 Feito!

Dica. Se você quiser detectar linhas duplicadas por uma ou mais colunas, desmarque todas as colunas irrelevantes e deixe apenas a(s) coluna(s) chave(s) selecionada(s).

E o resultado seria semelhante a isto:

Como você vê na imagem acima, a ferramenta Tabela de Duplicações realçou linhas duplicadas sem primeiras ocorrências.

Se você quiser destacar duplicatas incluindo as primeiras ocorrências, ou se quiser colorir registros únicos em vez de dupes, ou se não gostar da cor vermelha padrão, então use o assistente de Duplicate Remover que tem todos esses recursos e muito mais.

Dublagem de luz alta no Excel usando um assistente avançado passo a passo

Comparado com a ferramenta Tabela de Deduplicação rápida, o assistente de Duplicate Remover requer mais alguns cliques, mas compensa isso com uma série de opções adicionais. Deixe-me mostrá-lo em ação:

1. Selecione qualquer célula dentro de sua tabela onde você queira destacar duplicatas, e clique no botão Duplicate Remover na fita. O assistente será executado e a tabela inteira será selecionada. O add-in também irá sugerir a criação de uma cópia de segurança da sua tabela, por via das dúvidas. Se você não precisar, desmarque essa caixa.

   Verifiquem se a tabela foi selecionada corretamente e clique em Next.

   

2. Selecione um dos seguintes tipos de dados que você quer encontrar:
   • Duplicações exceto 1ª ocorrências
   • Duplicações com 1ª ocorrências
   • Valores únicos
   • Valores únicos e 1ª ocorrência duplicada

   Para este exemplo, vamos encontrar Duplicações + 1ª ocorrência:
   

3. Agora, selecione as colunas para verificar duplicados. Como queremos destacar linhas duplicadas completas, selecionei todas as 3 colunas.
   

   Adicionalmente, o add-in permite especificar se sua tabela tem cabeçalhos e se você deseja pular células vazias. Ambas as opções são selecionadas por padrão.

4. Finalmente, escolha a ação a ser executada nas duplicatas. Você tem várias opções como selecionar, excluir, copiar, mover duplicatas ou adicionar uma coluna de status para identificar as duplicatas.

Desde que hoje estamos explorando diferentes maneiras de destacar duplicatas no Excel, nossa escolha é óbvia 🙂 Então, selecione Preencher com cor e escolha uma das cores temáticas padrão, ou clique em Mais Cores… e escolha qualquer cor RGB ou HSL personalizada.

Clique no botão Finalizar e aproveite o resultado 🙂

É assim que você destaca duplicatas no Excel usando nosso add-in Duplicate Remover. Se você estiver curioso para experimentar esta ferramenta em suas próprias planilhas, você é muito bem-vindo para baixar uma versão de avaliação totalmente funcional do Ultimate Suite que inclui todas as nossas ferramentas que economizam tempo para o Excel. E seu feedback nos comentários será muito apreciado!

Se você estiver satisfeito com o desempenho do Duplicate Remover, temos o prazer de oferecer esta oportunidade muito especial para você:

Você também pode estar interessado em

• Como identificar duplicatas no Excel (em 1 tabela)
• Como comparar 2 colunas ou tabelas para correspondências e diferenças
• Como remover linhas duplicadas no Excel