Resaltar duplicados en Google Sheets

52 comentarios / Preguntas frecuentes de Sheets / Por
Identificar Duplicados en Google Sheets

Una de las principales situaciones con las que te vas a encontrar en cuanto al análisis de datos en Google Sheets, es el manejo de registros y celdas duplicadas. Bien sea porque las debes eliminar, o simplemente porque necesitas identificar los duplicados.

Hay muchas formas y técnicas de hacer esto, dependiendo de los datos y el objetivo.

Hoy vamos a ver algunas de estas técnicas para resaltar o marcar los duplicados en Google Sheets (Muchas de estas también te funcionarán en Excel).

Si prefieres ver este artículo en video, aquí te lo dejo

Duplicados en Google Sheets

Primero debemos ponernos de acuerdo en lo que consiste un duplicado para después solucionarlo, o trabajarlo.

En el caso de una lista, es decir de una tabla de una sola columna, es muy fácil. Un duplicado es un valor que se repite, como en el siguiente ejemplo:

Ya cuando tenemos una tabla con varias columnas, normalmente hablamos de duplicados cuando toda la fila se repite.

Pero también puede darse un tercer caso, donde se repita algún identificador (como el nombre, la referencia, u otro). En este caso usaríamos otras técnicas.

¿Por qué identificar duplicados?

A veces tenemos que identificar duplicados cuando realizamos auditoria de alguna hoja de cálculo, por varias razones:

  1. Para ver posibles errores de la base (Si fuera una llave única no debería tener duplicados)
  2. Encontrar registros puestos dos veces por error
  3. Identificar campos que si deberían estar duplicados
  4. Después de haber juntado, copiado y pegado o eliminado registros.
  5. O simplemente queremos ver, por ejemplo las diferentes ventas de un mismo cliente, o de un mismo vendedor

Ya después podemos decidir que hacer con ellos.

Muchas veces vamos a querer eliminarlos (Aquí un tutorial entero de cómo eliminar filas duplicadas), pero a veces solo queremos “marcarlos” o asegurarnos que sean duplicados. Esto es lo que vamos a ver hoy.

Cómo marcar duplicados en Sheets

Hay varias maneras de identificar duplicados, y todo depende de lo que queramos hacer exactamente.

Una manera de identificar los duplicados es usando una nueva columna que identifique un registro como repetido o no.

Otra forma, que puede ser más conveniente, es usando formatos condicionales, para poner un color especial o negrita a las celdas o filas que estén duplicadas

Otra cosa que podemos hacer es identificar todas las instancias del repetido, o que me deje el “original” din marcar

Por otro lado, también depende de que tanto control tengamos sobre la base, y que tanto podamos modificarla, reordenarla o agregar nuevas columnas. Estas son algunas de las formas en que podemos resaltar duplicados:

  1. Ordenando y añadiendo una columna auxiliar formulada.
  2. Ordenando y añadir un formato condicional
  3. Añadir una columna auxiliar (sin ordenar)
  4. Añadir un formato condicional (sin ordenar)

A continuación verás en detalle algunas de estas.

Ordenar los datos

La manera más fácil de ver rápidamente si hay valores repetidos es ordenando por la llave o alguna columna de referencia (Aquí todo acerca de ordenar datos en Google Sheets)

Una vez ordenas, tienes dos opciones: La primera es añadir una columna adicional, con una fórmula que me avisa si el valor es repetido o no. Para esto, seguimos los siguientes pasos:

  1. Agregar un filtro
  2. Ordenar los datos por la columna de referencia.
  3. En la primera celda de la columna siguiente introducimos la fórmula =SI(A2=A1;”REPETIDO”;”OK”)
  4. Arrastramos la fórmula, o la copiamos y pegamos.

Esta metodología es ideal para cuando queremos hacer algo adicional a los duplicados: eliminarlos, copiarlos y pegarlos, o alguna otra acción.

Homologación Excel: Este método funciona exactamente igual en Excel

Nota: Con este método dejamos el original sin marcar

Ordenar y añadir una columna condicional y un formato condicional

Al método anterior le podemos agregar una cosa más y es un formato condicional para que nos ayude a identificar de manera más visual y de verdad destaque todos los resaltados.

Adicional a los pasos anteriores, añadimos lo siguiente:

  1. Seleccionamos la columna B.
  2. Clic derecho y escogemos “Formato Condicional”
  3. Escogemos la condición “La celda es igual a”
  4. Escribimos “REPETIDO”

Homologación Excel: En Excel se haría todo igual, excepto que el formato condicional se aplica de manera un poco diferente.

Ordenar y añadir un formato condicional

Si solo queremos identificarlos visualmente, una buena opción es incluir la fórmula anterior en un formato condicional directamente, quitando así la necesidad de incluir una columna auxiliar.

En otras palabras, fusionamos los dos métodos anteriores en uno solo:

Lo haríamos así:

  1. Escoger la columna B (Desde la fila 2)
  2. Una de dos:
    1. Ir a Formato > Formato Condicional
    2. Clic derecho y escoger “Formato Condicional”
  3. En “Reglas de Formato”, escoger, “La fórmula personalizada es:”
  4. En el campo escribir “=A2=A1”, asumiendo que la primera celda del rango es A1.
  5. Poner cualquier formato que resalte los repetidos.

Resaltar toda la fila

Podemos hacer una pequeña modificación para que resalte toda la fila y así hacer que sea más visible la columna repetida.

Para esto simplemente cambiamos el rango del formato hacemos un pequeño cambio en la formula del formato condicional. Para esto:

  1. Seleccionamos todo nuestro rango
  2. Vamos a “Formato Condicional”
  3. Asegurar que el rango incluya las dos columnas (En nuestro caso A2:B14)
  4. En “Reglas de Formato”, escoger “La fórmula personalizada es:”
  5. Cambiar la fórmula para que quede fija la columna identificadora (en nuestro caso la A, y quedaría así =$A2=$A3)

Añadir una columna auxiliar formulada (sin tener que ordenar)

Esta es una de las metodologías que más me gusta.

Con la ayuda de la función CONTAR.SI, podemos ver si un registro está más de dos veces en la columna.

Hay dos formas de hacerlo. Una es que resalte todas las instancias del valor duplicado (la forma fácil), otra es que deje la primera sin resaltar (Algo así como el original, o el innovador). Igual que en los primeros ejemplos. De nuevo, todo depende de lo que quieras hacer con los duplicados.

Identificar todos los duplicados

  1. Nos situamos en la columna a la derecha del primer dato e ingresamos la fórmula =SI(CONTAR.SI(A:A;A2)>1;”REPETIDO”;”ORIGINAL”)
  2. Arrastramos la fórmula
  3. También podríamos añadir un formato condicional para hacerlo más visual, tal como hicimos en los ejemplos anteriores.

Homologación Excel: Esta fórmula se puede aplicar exactamente igual en Excel

Identificar los duplicados pero no el “original”

El método anterior es muy fácil, pero a veces vamos a querer que nos deje sin marcar el original, es decir la primera ocurrencia de algún valor repetido, tal como en los primeros ejemplos.

Para lograr lo anterior, repetimos los pasos del último ejemplo pero tenemos que modificar nuestra fórmula, a:

=SI(CONTAR.SI($A$2:A2;A2)>1;”REPETIDO”;”OK”)

Homologación Excel: Esta fórmula se puede aplicar exactamente igual en Excel

Formato condicional para resaltar los duplicados

Esta es, en principio, la forma más fácil, pero no me gusta que necesariamente resalta todas las instancias del duplicado.

No deja “el original”, como me gusta, pero puede ser útil en muchas ocasiones.

  1. Escoger la columna donde estan los datos
  2. Una de dos:
    1. Ir a Formato > Formato Condicional
    2. Click derecho y escoger “Formato Condicional”
  3. En “Reglas de Formato”, escoger, “La fórmula personalizada es:”
  4. En el campo escribir “=CONTAR.SI(A1:A;A1)>1”, asumiendo que la primera celda del rango es A1.
  5. Poner cualquier formato que resalte los repetidos.

En muchos artículos en la web encontrarán solo esta última. Me parece que no es lo suficientemente flexible, y si conocen todas las anteriormente expuestas tienen un kit de herramientas muchísimo más amplio para enfrentar este y otros retos en Google Sheets.

Conclusión

Pudiste ver varios métodos distintos para resaltar duplicados en Google Sheets. Es importante conocerlos, porque depende de que tanto puedan modificar la base original y de lo que quieran hacer con sus duplicados

¿Qué tal te parece? ¿Tienes algún otro método? ¿Tienes algún otro caso que no haya mencionado? Cuéntame en los comentarios.

Si te gustó este contenido, puedes acceder a tips quincenales, nuevos videos, artículos, guías y plantillas.

52 comentarios en “Resaltar duplicados en Google Sheets”

  2. DAVID FRANCISCO PUERTAS

    Hola.
    He procurado sin éxito colorear de rojo las columnas B, C, y L utilizando la siguiente fórmula en el formato condicional: =Y(CONTAR.SI(B:B;B4)>1;CONTAR.SI(C:C;C4)>1);CONTAR.SI(J:J;J4)>1)
    Sería posible que me orientes al respecto?

    Responder
  3. Niko

    Hola, soy solo un aficionado. Me detengo a publicar un agradecimiento a JuanSguzman por su gran ayuda a todos. Inclusive a mi, sin entender mucho del asunto. Muchas gracias Guzman por ser tan amable. Dios te bendiga.

    Responder
  4. YULISSA

    HOLA TENGO 4 COLUMNAS QUE QUIERO QUE ME RESALTE LOS DUPLICADOS

    EJEMPLO:
    COLUMNAS A B C D
    10 15 20 25
    11 25 21 15
    25 10 20 11

    QUE FUNCION PUEDO USAR SOLO QUIERO QUE ME RESALTEN LOS DUPLICADOS COLOR ROJO ESPERO QUE ME AYUDEN LO MAS PRONTO GRACIAS

    Responder
  5. Paloma

    Hola! he intentado todas las opciones que nombraste y de ninguna manera puedo hacer que las casillas de la columna f puedan resaltarse los duplicados. Vi que se resaltan algunos repetidos pero otros repetidos no lo he verificado buscandolo con ctrl+f buscandolo. Podrias ayudarme pues frustrada estoy.

    Responder
  6. Sofia

    Hola vi todos tus videos pero no encuentro la solución para mi duda.

    Lo que necesito es que tengo 2 columnas en una misma hoja. Quiero comparar y que resalte en la columna A si es que se repite el nombre en la columna B. No quiero que se resalte si es que se repite en la misma columna A, solo debe resaltar si está en la columna B. Y si se repite 2 veces que se marque de otro color.

    Lo que me pasa es que se resalta cuando se repite en cualquier columna, y yo quiero que se resalte solo al momento de comparar con la columna B.

    Espero se entienda mi duda,

    Gracias

    Responder
  7. Cecilia

    Hola! Necesito saber si existe una formula que resalte el último dato que ingreso. Aplique la formula Contar.Si pero me marca en rojo el dato original. Manejo planillas con mas de 3000 filas y se me hace muy laborioso. Me gustaria, al ingresar un nuevo dato en la columna, que se marque en rojo si el dato ya esta presente. Desde ya muchas gracias!

    Responder
  8. Lupita

    ¡Hola! tengo una consulta, se me ha estado dificultando para ver cómo le puedo hacer en el siguiente caso: tengo una base de datos en la cual se ingresan facturas, sin embargo, sucede que a veces la factura llega dos veces y se duplica su ingreso, pensaba poner un formato condicional para que en el número de la factura se marcara si ese número ya había sido ingresado, sin embargo, me comentan que distintos proveedores pueden tener el mismo número de factura sin necesariamente ser la misma, por lo tanto no sabría cómo hacer ahí, no sé si habrá alguna manera de concatenar la columna de orden de compra, nombre de proveedor y número de factura para que solamente marque cuando se repitan en otra misma linea, no sé si me di a entender.

    Saludos.

    Responder
  10. Jose Valencia Bautista

    Hola, tengo una consulta.

    como resaltar valores duplicados pero dos columnas, decir, resalte las celdas con valor duplicado de columna A y B.

    Responder
  11. peters

    Hola, tengo una consulta.
    necesito resaltar los repetidos de una fila, los datos que van en cada celda se obtienen de una lista desplegable.

    no logro hacer funcionar la condición REPETIDO, ni el contar.si.

    gracias por la ayuda

    Responder
  12. Angela

    Hola, quiero resaltar todos los duplicados encontrados en una tabla, por ejemplo C1:E1500, pero solo resalta los que encuentra en la columna C. Como lo consigo? que fórmula debo usar?

    Responder
  13. Angela

    Hola, tengo una base de datos de contactos con más de un número de teléfono y quiero encontrar (resaltar de un solo color) todos los duplicados en un rango, por ejemplo: C1:E1500. Que fórmula puedo usar?. Ya he usado CONTAR.SI(C:E,C1)>1, pero no resalta los duplicados de la columna D y E, solo resalta los duplicados de la columna C.

    Responder
    1. jsguzmanb

      Hola Rodrigo

      A que te refieres con texto

      Como esta, te encuentra duplicados sean numeros o textos

      En cuanto a las filas enteras, hay que hacer un par de modificaciones de las que hablaré en un proximo artículo.

      Saludos!

      Responder
    1. jsguzmanb

      Wow. Buena sugerencia

      Se me ocurre hacer formatos condicionales para los diferentes valores de contar.si

      Si contar.si da 2, un color, si da 3, otro y asi.

      Muy interesante

      Responder
  16. gabriel

    Hola, como podria aplicar la ultima forma, la de CONTAR.SI(A:A;A1)>1 pero que tome los datos de dos columnas? es decir, busque y resalte los duplicados en las columnas A y B. Tengo dos columnas donde cada una indica dos numeros telefonicos y quiero saber si coincide algun numero con otro. Agradecido por la guia

    Responder
    1. jsguzmanb

      Hola Gabriel

      Si estas buscando duplicado sin importar la fila, simplemente amplia la formula a las dos columnas

      seria CONTAR.SI(A:B;A1)>1

      Me cuentas si te sirve.

      Saludos!

      Responder
      1. gabriel

        Hola, si me resulto, pero solamente me resalta los duplicados de la columna A. Aunque si toma en cuenta los resultados de la columna B solo resalta los de la columna A, agradecido de antemano por la ayuda ya brindada

        Responder
  18. Sofia

    Hola! Como le pongo el formato condicional a =SI(CONTAR.SI(A:A;A2)>1;»REPETIDO»;»ORIGINAL»)

    Me trae perfecto el resultado, pero me serviria que tambien me resalte las que aparecen como “REPETIDO”

    Responder
    1. jsguzmanb

      Hola Sofia

      Gracias por la pregunta

      Escoge la columna.

      Dale click derecho, formato condicional

      En Formato Condicional, incluye la condición “El texto es exactamente”

      y en el campo pones REPETIDO

      Cuéntame si te funciona.

      Saludos!

      Responder
  20. inma

    Hola buenas,
    tengo una fila con datos todos diferentes y cuando aplico el formato condicional me considera algunos como repetidos, y no entiendo porque. Podrias ayudarme?

    Responder
      1. inma

        905901680915082013722020-905901680915082091007061
        905901840915082062770071-905901840915082091007062
        905901030915082062770072-905901030915082091007063
        905901190915082062770073-905901190915082091007064
        905901350915082062770074-905901350915082091007065

        Empiezan igual pero terminan diferentes y los considera como valores repetidos
        la formula que utilizo en el formato condicional es esta =CONTAR.SI($C$6:$C$20;C6)>1

        Responder
  23. Johann Barrero

    Hola, a parte de que resalte los repetidos hay una manera de que resalte todos los duplicados, con la última formula =CONTAR.SI(A1:A;A1)>1 me resalta uno de los 2 datos repetidos pero no me resalta los 2 datos duplicados.

    Responder
  24. jsguzmanb

    Hola!

    Gracias por escribir

    Si claro, si tu columna esta en la columna A, y te ubicas por ejemplo en la fila 7, puedes usar la formula

    =COINCIDIR(A7;$A$1:A6;0)

    Despues la arrastras

    Esto te va a devolver el numero de la fila de la primera coincidencia del repetido. Si no esta repetido, te trae un error. Si hay mas de uno repetido solo te va a traer la primera que encuentre.

    Me cuentas si te sirve.

    Saludos!

    Responder
    1. jsguzmanb

      Excelente! Me alegro. Estoy trabajando en una macro para que sea aún más sencillo con un solo botón.

      La compartiré más adelante

      Un gran saludo!

      Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This