Quando você está trabalhando com grandes quantidades de dados, é fácil perder o controle dos dados e vê-los aparecer várias vezes na tabela. Algumas duplicatas são colocadas intencionalmente, enquanto outras são erros. Quaisquer que sejam os casos, você pode querer que essas duplicatas sejam destacadas automaticamente para você.
Encontrar células duplicadas em uma pequena planilha é fácil, mas ao lidar com conjuntos de dados grandes e complexos, pode ser muito difícil fazê-lo manualmente. Felizmente, existem ferramentas internas no Excel que permitem destacar valores duplicados. Neste tutorial, mostraremos como destacar dados duplicados com o recurso de formatação condicional do Excel.
Destaque duplicatas com formatação condicional no Excel. Geralmente, você pode querer encontrar duplicatas no Excel, porque mais frequentemente as duplicatas estão lá por engano e devem ser excluídas ou as duplicatas são importantes para análise e devem ser destacadas no Excel.
Existem dois métodos para localizar valores duplicados com formatação condicional no Excel. Eles são:
- Destacar duplicatas usando a regra de valor duplicado
- Destaque as duplicatas usando a fórmula personalizada do Excel (CONT.SE e CONT.SE)
Destacar duplicados usando regra de valor duplicado
Vamos supor que temos este conjunto de dados:
Primeiro, selecione o intervalo de células que contém valores duplicados. Em seguida, vá para a guia ‘Página inicial’, clique em ‘Formatação condicional’ na seção Estilos da faixa de opções. No menu suspenso, mova o cursor sobre a primeira opção para ‘Destacar regras de célula’ e ele exibirá novamente uma lista de regras em uma caixa pop-out. Escolha a opção ‘Valores duplicados’ aqui.
Depois de clicar em Duplicate Values, a caixa de diálogo Duplicate Values aparecerá. Aqui você pode selecionar o tipo de formatação para os valores duplicados. Você pode selecionar cores para preencher apenas as células, apenas para a fonte, como uma borda ou um formato personalizado, se preferir. Em seguida, clique em ‘OK’ para fechar a caixa de diálogo.
Aqui, estamos selecionando ‘Preenchimento Verde com Texto Verde Escuro’ para nosso exemplo.
Depois de selecionar o tipo de formatação, ele destacará todos os valores duplicados no intervalo selecionado, conforme mostrado abaixo.
Realçar Duplicados UFórmula CONT.SE
Outro método para destacar valores duplicados é usar a formatação condicional com a fórmula CONT.SE simples em uma única coluna ou em várias colunas.
Selecione o intervalo de dados onde deseja destacar as duplicatas. Em seguida, na guia ‘Página inicial’ e clique na opção ‘Formatação condicional’. No menu suspenso, clique na opção ‘Nova regra’.
Isso abrirá uma caixa de diálogo Nova regra de formatação.
Na caixa de diálogo Nova regra de formatação, selecione a opção ‘Usar uma fórmula para determinar quais células formatar’ na caixa de listagem Selecione um tipo de regra e, em seguida, insira a seguinte fórmula CONT.SE para contar duplicatas.
= CONT.SE ($ A $ 1: $ C $ 11, A1)> 1
Em seguida, clique no botão ‘Formatar’ para ir para a caixa de diálogo Formatar células. Na caixa de diálogo Formatar células, você pode escolher a cor de preenchimento na paleta de cores para destacar as células e clicar em ‘OK’. Aqui, estamos escolhendo a cor de preenchimento azul para formatar as duplicatas.
Em seguida, clique em ‘OK’ novamente para fechar a caixa de diálogo. A fórmula destacará todos os valores das células que aparecem mais de uma vez.
Sempre insira a fórmula para a célula superior esquerda no intervalo selecionado (A1: C11). O Excel copia automaticamente a fórmula para as outras células.
Você também pode definir regras como quiser. Por exemplo, se você deseja encontrar valores que aparecem apenas duas vezes em uma tabela, insira esta fórmula (na caixa de diálogo Nova regra de formatação):
= CONT.SE ($ A $ 1: $ C $ 11, A1) = 2
O resultado:
Às vezes, você pode querer ver apenas as duplicatas e filtrar os valores exclusivos. Para fazer isso, selecione o intervalo, vá para a guia Home, clique na opção ‘Classificar e Filtrar’ no canto superior direito do Excel e selecione a opção ‘Filtrar’.
A primeira célula de cada coluna mostrará um menu suspenso onde você pode definir o critério do filtro. Clique no menu suspenso na primeira célula da coluna e selecione ‘Filtrar por cor’. Em seguida, escolha a cor azul.
Agora, você verá apenas as células destacadas e poderá fazer o que quiser com elas.
Encontre e destaque linhas duplicadas no Excel usando a fórmula COUNTIFS
Se você quiser localizar e destacar linhas duplicadas no Excel, use COUNTIFS em vez de COUNTIF.
Selecione o intervalo, vá para a guia ‘Página inicial’ e clique em ‘Formatação condicional no grupo Estilos. No menu suspenso, clique na opção ‘Nova regra’.
Na caixa de diálogo Nova regra de formatação, selecione a opção ‘Use uma fórmula para determinar quais células formatar’ na caixa de listagem Selecione um tipo de regra e insira a fórmula COUNTIFS abaixo:
= COUNTIFS ($ A $ 1: $ A $ 20, $ A1, $ B $ 1: $ B $ 20, $ B1, $ C $ 1: $ C $ 20, $ C1)> 1
Na fórmula acima, o intervalo A1: A20 se refere à coluna A, B1: B20 se refere à coluna B e C1: C20 se refere à coluna C. A fórmula conta o número de linhas com base em vários critérios (A1, B2 e C1) .
Em seguida, clique no botão ‘Formatar’ para selecionar um estilo de formatação e clique em ‘OK’.
Agora, o Excel destaca apenas as linhas duplicadas, conforme mostrado abaixo.
É isso.