Cómo resaltar celdas y filas duplicadas en Excel

En este tutorial, aprenderás a mostrar duplicados en Excel. Vamos a ver de cerca diferentes métodos para sombrear celdas duplicadas, filas enteras o duplicados consecutivos utilizando el formato condicional. Además, verás cómo resaltar los duplicados con diferentes colores utilizando una herramienta especializada.

La semana pasada, exploramos diferentes formas de identificar duplicados en Excel. Sin duda, las fórmulas de duplicados son muy útiles, pero resaltar las entradas duplicadas con un color definido podría facilitar aún más el análisis de los datos.

La forma más rápida de encontrar y resaltar duplicados en Excel es utilizando el formato condicional. La mayor ventaja de este método es que no sólo le muestra los duplicados existentes, sino que detecta y colorea los nuevos duplicados a medida que introduce, edita o sobrescribe sus datos.

Más adelante en este tutorial, encontrará una serie de formas de resaltar los registros duplicados en función de su tarea específica. Estas técnicas funcionan en todas las versiones de Excel 2016, Excel 2013, Excel 2010 e inferiores.

• Resaltar duplicados en Excel con las primeras ocurrencias (regla incorporada)en la regla)
• Cómo resaltar duplicados en Excel excepto 1ª instancia
• Cómo resaltar duplicados en un rango (múltiples columnas)
• Cómo resaltar el enésimo y siguientes registros duplicados registros
• Cómo resaltar filas enteras en base a valores duplicados en una columna
• Cómo resaltar filas duplicadas en Excel
• Cómo ver celdas duplicadas consecutivas
• Duplicate Remover – la forma más rápida de resaltar duplicados en Excel

Cómo resaltar duplicados en Excel utilizando la regla incorporada (con las primeras ocurrencias)

Para empezar, en todas las versiones de Excel, existe una regla predefinida para resaltar las celdas duplicadas. Para utilizar esta regla en sus hojas de trabajo, realice los siguientes pasos:

1. Seleccione los datos que desea comprobar si hay duplicados. Puede tratarse de una columna, una fila o un rango de celdas.
2. En la ficha Inicio, en el grupo Estilos, haga clic en Formato condicional > Reglas para resaltar celdas > Valores duplicados…
   
3. Se abrirá la ventana de diálogo Valores duplicados con el formato Relleno rojo claro y Texto rojo oscuro seleccionado por defecto. Para aplicar el formato predeterminado, simplemente haga clic en Aceptar.

Además del relleno rojo y el formato de texto, hay un puñado de otros formatos predefinidos disponibles en la lista desplegable. Para resaltar los duplicados utilizando algún otro color, haga clic en Formato personalizado… (el último elemento de la lista desplegable) y seleccione el color de relleno y/o de fuente que desee.

Usando la regla incorporada, puede resaltar los duplicados en una columna o en varias columnas como se muestra en la siguiente captura de pantalla:

Cuando utilice la regla incorporada de Excel para resaltar valores duplicados, tenga en cuenta las dos cosas siguientes:

• Sólo funciona para celdas individuales. Para resaltar las filas duplicadas, deberá crear sus propias reglas basándose en los valores de una columna específica o comparando los valores de varias columnas.
• Sombrea las celdas duplicadas incluyendo sus primeras apariciones. Para resaltar todos los duplicados excepto las primeras apariciones, cree una regla de formato condicional basada en esta fórmula.

Cómo resaltar los duplicados sin las primeras ocurrencias

Para resaltar las segundas y todas las subsiguientes ocurrencias de los duplicados, seleccione las celdas que desea colorear y cree una regla basada en una fórmula de esta manera:

1. En la pestaña Inicio, en el grupo Estilos, haga clic en Formato condicional >Nueva regla >Utilizar una fórmula para determinar qué celdas formatear.
2. En el cuadro Formato de valores donde esta fórmula es verdadera, introduzca una fórmula similar a esta:
   =COUNTIF($A:$A2,$A2)>1

   Donde A2 es la celda más alta del rango seleccionado.

3. Haga clic en el botón Formato… y seleccione el color de relleno y/o de fuente que desee.
4. Por último, haga clic en Aceptar para guardar y aplicar la regla.

Si no tiene mucha experiencia con el formato condicional de Excel, encontrará los pasos detallados para crear una regla basada en una fórmula en el siguiente tutorial: Formato condicional de Excel basado en el valor de otra celda.

Como resultado, las celdas duplicadas excluyendo las primeras instancias se resaltarán con el color que usted elija:

Cómo mostrar el 3º, 4º y todos los registros duplicados posteriores

Para ver los duplicados a partir de la enésima aparición, cree una regla de formato condicional basada en la fórmula como en el ejemplo anterior, con la única diferencia de que sustituirá >1 al final de la fórmula por el número requerido. Por ejemplo:

Para resaltar la 3ª y todas las instancias duplicadas posteriores, cree una regla de formato condicional basada en esta fórmula:

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

Para resaltar la 4ª y todas las instancias duplicadas posteriores, utilice esta fórmula:

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

Para resaltar sólo instancias específicas, utilice el operador igual a (=). Por ejemplo, para resaltar sólo las segundas instancias, utilizaría esta fórmula:

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

Cómo resaltar duplicados en un rango (varias columnas)

Cuando quiera comprobar si hay duplicados en varias columnas, no comparando las columnas entre sí, sino encontrando todas las instancias del mismo elemento en todas las columnas, utilice una de las siguientes soluciones.

Subrayar duplicados en varias columnas, incluyendo la primera aparición

Si la primera instancia de un elemento que aparece en el conjunto de datos más de una vez se considera un duplicado, la forma más fácil de hacerlo es utilizar la regla incorporada de Excel para duplicados.

O bien, cree una regla de formato condicional con esta fórmula:

Por ejemplo, para resaltar los duplicados en el rango A2:C8, la fórmula es la siguiente:

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

Nota el uso de referencias absolutas de celda para el rango ($A$2:$C$8), y referencias relativas para la celda superior (A2).

Resaltar duplicados en múltiples columnas sin las primeras ocurrencias

La solución para este escenario es mucho más complicada, no es de extrañar que Excel no tenga ninguna regla incorporada para ello 🙂

Para resaltar las entradas duplicadas en varias columnas ignorando las 1ª ocurrencias, tendrás que crear 2 reglas con las siguientes fórmulas:

Regla 1. Se aplica a la primera columna

Aquí se utiliza exactamente la misma fórmula que usamos para resaltar los duplicados sin 1ª ocurrencia en una columna (los pasos detallados se pueden encontrar aquí).

En este ejemplo, estamos creando una regla para A2:A8 con esta fórmula:

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

Como resultado, los elementos duplicados sin 1ª ocurrencia se resaltan en la columna más a la izquierda del rango (sólo hay un elemento de este tipo en nuestro caso):

Regla 2. Se aplica a todas las columnas posteriores

Para resaltar los duplicados en las columnas restantes (B2:C8), utilice esta fórmula:

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

En la fórmula anterior, la primera función COUNTIF cuenta las ocurrencias de un elemento determinado en la primera columna, y la segunda COUNTIF hace lo mismo para todas las columnas posteriores. Luego, se suman esos números y se comprueba si la suma es mayor que 1.

Como resultado, se encuentran y se resaltan todos los elementos duplicados excluyendo su 1ª aparición:

Resaltar los duplicados en todas las columnas con una sola regla

Otra posible solución es añadir una columna vacía a la izquierda de su conjunto de datos, y combinar las fórmulas anteriores en una sola fórmula como esta:

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

Donde B2 es la celda superior con datos en la 2ª columna del rango objetivo.

Para entender mejor la fórmula, vamos a desglosarla en 2 partes principales:

• Para la primera columna (B), la condición IF no se cumple nunca, por lo que sólo se aplica la segunda función COUNTIF, que es la fórmula regular que ya utilizamos para encontrar duplicados sin 1ª ocurrencia en una columna.
• Para todas las columnas posteriores (C2:D8), el punto clave es el uso inteligente de las referencias absolutas y relativas en las dos funciones COUNTIF. Para facilitar la comprensión, lo he copiado en la columna G, para que puedas ver cómo cambia la fórmula cuando se aplica a otras celdas:
  

Debido a que la condición IF es siempre TRUE para todas las columnas que no sean la primera (el número de columnas es mayor que 1), la fórmula procede de esta manera:

• Cuenta el número de apariciones de un elemento dado (D5 en la captura de pantalla anterior) en todas las columnas a la izquierda de la columna dada: COUNTIF(B:$C,D5)
• Cuenta el número de apariciones de un elemento dado en la columna del elemento, hasta la celda del elemento: COUNTIF(D:D5,D5)
• Por último, la fórmula suma los resultados de ambas funciones COUNTIF. Si el número total es mayor que 1, es decir, si hay más de una ocurrencia del elemento, la regla se aplica y el elemento se resalta.

Resaltar filas enteras basadas en valores duplicados en una columna

Si su tabla contiene varias columnas, puede querer resaltar filas enteras basadas en registros duplicados en una columna específica.

Como ya sabe, la regla incorporada de Excel para los duplicados funciona sólo a nivel de celda. Pero una regla basada en una fórmula personalizada no tiene ningún problema para sombrear las filas. El punto clave es seleccionar las filas completas y, a continuación, crear una regla con una de las siguientes fórmulas:

• Para resaltar las filas duplicadas excluyendo las primeras ocurrencias:
  =COUNTIF($A:$A2, $A2)>1
• Para resaltar las filas duplicadas incluyendo las primeras ocurrencias:
  =COUNTIF($A:$A, $A2)>1

Donde A2 es la primera celda y A15 es la última celda utilizada en la columna que quieres comprobar si hay duplicados. Como ves, el uso inteligente de las referencias de celdas absolutas y relativas es lo que marca la diferencia.
La siguiente captura de pantalla demuestra ambas reglas en acción:

Cómo resaltar filas duplicadas en Excel

El ejemplo anterior ha demostrado cómo colorear filas enteras basadas en valores duplicados en una columna específica. Pero, ¿qué pasa si quieres ver las filas que tienen valores idénticos en varias columnas? O bien, ¿cómo resaltar las filas duplicadas absolutas, las que tienen valores completamente iguales en todas las columnas?

Para ello, emplee la función COUNTIFS que permite comparar celdas por múltiples criterios. Por ejemplo, para resaltar las filas duplicadas que tienen valores idénticos en las columnas A y B, utilice una de las siguientes fórmulas:

• Para resaltar las filas duplicadas excepto las primeras ocurrencias:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Para resaltar las filas duplicadas con las primeras ocurrencias:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

La siguiente captura de pantalla demuestra el resultado:

Como comprenderá, el ejemplo anterior es sólo para fines de demostración. Cuando resalte filas duplicadas en sus hojas de la vida real, naturalmente no está limitado a comparar valores sólo en 2 columnas, la función COUNTIFS puede procesar hasta 127 pares de rango/criterio.

Resaltar celdas duplicadas consecutivas en Excel

A veces, puede que no necesite resaltar todos los duplicados en una columna, sino mostrar sólo las celdas duplicadas consecutivas, es decir, las que están una al lado de la otra. Para ello, selecciona las celdas con datos (sin incluir la cabecera de la columna) y crea una regla de formato condicional con una de las siguientes fórmulas:

• Para resaltar los duplicados consecutivos sin 1ª ocurrencia:
  =$A1=$A2
• Para resaltar duplicados consecutivos con 1ª ocurrencia:
  =OR($A1=$A2, $A2=$A3)

La siguiente captura de pantalla demuestra el resaltado de textos consecutivos duplicados, pero estas reglas también funcionarán para números y fechas consecutivos duplicados:

Si su hoja de Excel puede tener filas vacías y no quiere que se resalten las celdas consecutivas en blanco, realice las siguientes mejoras en las fórmulas:

• Para resaltar las celdas duplicadas consecutivas sin 1ª ocurrencia e ignorar las celdas en blanco:
  =AND($A2<>"", $A1=$A2)
• Para resaltar las celdas duplicadas consecutivas con 1ª ocurrencia e ignorar las celdas en blanco:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Como ves, no es nada del otro mundo resaltar los duplicados en Excel utilizando el formato condicional. Sin embargo, hay una forma más rápida y sencilla. Para descubrirlo, lea la siguiente sección de este tutorial.

Cómo resaltar duplicados en Excel con Duplicate Remover

El complemento Duplicate Remover es la solución todo en uno para tratar los registros duplicados en Excel. Puede encontrar, resaltar, seleccionar, copiar o mover celdas duplicadas o filas enteras duplicadas.

A pesar de su nombre, el complemento puede resaltar rápidamente los duplicados en diferentes colores sin eliminarlos.

El eliminador de duplicados añade 3 nuevas funciones a su cinta de Excel:

• Tabla deduplicada – para encontrar y resaltar inmediatamente los duplicados en una tabla.
• Duplicate Remover – asistente paso a paso con opciones avanzadas para identificar y resaltar duplicados o valores únicos en 1 tabla.
• Comparar 2 Tablas – encontrar y resaltar duplicados comparando dos columnas o dos tablas separadas.

Después de instalar Ultimate Suite for Excel, encontrará estas herramientas en la pestaña Ablebits Data en el grupo Dedupe:

Resaltar duplicados en Excel en un par de clics

Para este ejemplo, he creado la siguiente tabla con unos cientos de filas. Y nuestro objetivo es resaltar las filas duplicadas que tienen valores iguales en las tres columnas:

Aunque no lo crea, puede obtener el resultado deseado con sólo 2 clics del ratón 🙂

1. Con cualquier celda de su tabla seleccionada, haga clic en el botón Dedupe Table, y el inteligente complemento recogerá toda la tabla.
2. Se abrirá la ventana de diálogo Dedupe Table con todas las columnas seleccionadas automáticamente, y la opción Color duplicados elegida por defecto. Por lo tanto, todo lo que tienes que hacer es hacer clic en Aceptar 🙂 Hecho!

Consejo. Si desea detectar filas duplicadas por una o más columnas, desmarque todas las columnas irrelevantes y deje sólo la(s) columna(s) clave(s) seleccionada(s).

Y el resultado sería similar a esto:

Como se ve en la imagen de arriba, la herramienta Tabla de Duplicados ha resaltado las filas duplicadas sin primera instancia.

Si quiere resaltar los duplicados incluyendo las primeras ocurrencias, o si quiere colorear los registros únicos en lugar de los duplicados, o si no le gusta el color rojo por defecto, entonces utilice el asistente de eliminación de duplicados que tiene todas estas características y muchas más.

Subraye los duplicados en Excel mediante un asistente avanzado paso a paso

Comparado con la rápida herramienta de Tabla Deduplicada, el asistente de Eliminación de Duplicados requiere unos cuantos clics más, pero lo compensa con una serie de opciones adicionales. Permítame mostrárselo en acción:

1. Seleccione cualquier celda de su tabla en la que desee resaltar los duplicados y haga clic en el botón Eliminador de duplicados de la cinta. El asistente se ejecutará y se seleccionará toda la tabla. El complemento también le sugerirá que cree una copia de seguridad de su tabla, por si acaso. Si no la necesita, desmarque esa casilla.

   Verifique que la tabla se ha seleccionado correctamente y haga clic en Siguiente.

   

2. Seleccione uno de los siguientes tipos de datos que desee encontrar:
   • Duplicados excepto 1ª ocurrencia
   • Duplicados con 1ª ocurrencia
   • Valores únicos
   • Valores únicos y 1ª ocurrencia de duplicados

   Para este ejemplo, vamos a encontrar Duplicados + 1ª ocurrencia:
   

3. Ahora, seleccione las columnas para buscar duplicados. Como queremos resaltar las filas duplicadas completas, he seleccionado las 3 columnas.
   

   Además, el complemento le permite especificar si su tabla tiene encabezados y si desea omitir las celdas vacías. Ambas opciones están seleccionadas por defecto.

4. Por último, elija la acción a realizar sobre los duplicados. Tienes varias opciones como seleccionar, eliminar, copiar, mover los duplicados o añadir una columna de estado para identificar los duplicados.

Dado que hoy estamos explorando diferentes formas de resaltar los duplicados en Excel, nuestra elección es obvia 🙂 Por lo tanto, seleccione Rellenar con color y elija uno de los colores estándar del tema, o haga clic en Más colores… y elija cualquier color RGB o HSL personalizado.

Haga clic en el botón Finalizar y disfrute del resultado 🙂

Así es como se resaltan los duplicados en Excel utilizando nuestro complemento Duplicate Remover. Si tiene curiosidad por probar esta herramienta en sus propias hojas de trabajo, le invitamos a descargar una versión de prueba totalmente funcional de la Ultimate Suite que incluye todas nuestras herramientas de ahorro de tiempo para Excel. Y su opinión en los comentarios será muy apreciada!

Si está satisfecho con el rendimiento de Duplicate Remover, nos complace ofrecerle esta oportunidad tan especial:

También puede interesarle

• Cómo identificar duplicados en Excel (en 1 tabla)
• Cómo comparar 2 columnas o tablas para encontrar coincidencias y diferencias
• Cómo eliminar filas duplicadas en Excel