Buscar duplicados en Excel: resaltarlos, contarlos y listarlos
Por Mirko Peters · · Actualizado
Busca duplicados en Excel con formato condicional, marcas con COUNTIF, UNIQUE, Power Query y comprobaciones entre hojas, además de cómo detectar coincidencias parecidas antes de eliminar nada.
Traducido automáticamente con IA a partir de la publicación original. Lee el original en inglés
Para encontrar duplicados en Excel sin eliminar nada, selecciona tus datos y usa Inicio > Formato condicional > Reglas para resaltar celdas > Valores duplicados. Excel colorea cada valor que aparece más de una vez. Si quieres números en lugar de colores, añade una columna auxiliar con una fórmula COUNTIF. Este artículo está pensado para usuarios de Excel que heredan listas desordenadas y necesitan ver el problema antes de corregirlo. Verás el método de resaltado, fórmulas para contar y marcar, una forma de listar solo los valores repetidos, una opción con Power Query, comprobaciones entre hojas y un plan para las coincidencias parecidas.
Puntos clave
- La forma más rápida de encontrar duplicados en Excel es Inicio, Formato condicional, Reglas para resaltar celdas, Valores duplicados. Colorea los valores repetidos y no elimina nada.
- Una columna auxiliar con COUNTIF indica cuántas veces aparece cada valor, y con COUNTIFS puedes comprobar varias columnas a la vez.
- COUNTIF no distingue mayúsculas de minúsculas y trata ? y * como comodines, así que el recuento puede no coincidir con lo que esperas.
- Para comparar dos hojas, cuenta cada valor frente a la otra lista con COUNTIF, y abre ambos libros si la segunda lista está en otro archivo.
- Las coincidencias parecidas, como Jon y John, no aparecen como duplicados. Limpia primero los espacios y las mayúsculas, y revisa el resto a simple vista o con el complemento Fuzzy Lookup.
Primero se busca y después se elimina. Si ya sabes qué filas quieres borrar, nuestra guía complementaria sobre cómo quitar duplicados en Excel explica el paso de eliminación. Aquí el objetivo es revisar primero en Excel, para que tú decidas qué cuenta como duplicado y qué conservar.
Resaltar duplicados con formato condicional
Es la comprobación más rápida, y Microsoft la recomienda como paso de revisión antes de eliminar nada. Sigue el artículo de Microsoft sobre filtrar valores únicos o quitar valores duplicados.
- Selecciona las celdas, la columna o el rango que quieres revisar. Normalmente basta con una columna.
- En la pestaña Inicio, abre Formato condicional.
- Elige Reglas para resaltar celdas y después Valores duplicados.
- Elige un formato, como un relleno rojo claro, y selecciona Aceptar.
Todas las celdas cuyo valor aparece más de una vez quedan ahora coloreadas. Desplázate por la hoja o filtra la columna por color para ver solo las celdas resaltadas. Para deshacerlo, vuelve a Formato condicional y borra las reglas del rango.
Hay una segunda forma que ofrece más control. Abre Formato condicional > Administrar reglas, crea una Nueva regla y elige Aplicar formato únicamente a valores únicos o duplicados. En la lista Aplicar formato a puedes elegir únicos o duplicados, y puedes definir el formato de número, la fuente, el borde o el relleno. Resulta útil cuando quieres colorear los valores que aparecen una sola vez y dejar sin cambios los repetidos.
Dos limitaciones que conviene conocer. Microsoft indica que Excel no puede resaltar duplicados en el área de Valores de una tabla dinámica. Además, la regla analiza una columna a la vez cuando seleccionas una sola columna. Una fila que solo se repite al observar varias columnas juntas no se resaltará. Para ese caso necesitas una fórmula.
Contar y marcar duplicados con COUNTIF
Una columna auxiliar te proporciona un número que puedes ordenar y filtrar. También es la herramienta adecuada cuando dos filas solo son duplicadas si coinciden dos o tres columnas.
Marcar todas las repeticiones
Supongamos que tus valores están en A2:A100. En B2, escribe:
=COUNTIF($A$2:$A$100,A2)
Arrastra la fórmula hacia abajo. Un resultado de 1 significa que el valor es único. Un 2 significa que aparece dos veces, y así sucesivamente. Todas las copias de un valor repetido reciben el mismo número, incluida la primera. Para convertir el recuento en VERDADERO o FALSO, añade una comparación: =COUNTIF($A$2:$A$100,A2)>1.
Marcar solo las copias posteriores
Cuando planeas eliminar filas, normalmente quieres conservar la primera. Para ello, usa un rango que se expande:
=COUNTIF(A$2:A2,A2)>1
El rango A$2:A2 crece a medida que copias la fórmula hacia abajo. La primera vez que aparece un valor obtienes FALSO. Cada aparición posterior devuelve VERDADERO. Esto coincide con lo que hace el comando Quitar duplicados de Excel, ya que conserva la primera aparición.
Comprobar varias columnas con COUNTIFS
Para un registro que solo es duplicado cuando, por ejemplo, coinciden el nombre y los apellidos, usa COUNTIFS con un par de rango y criterio por cada columna:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)
Un resultado mayor que 1 significa que la combinación aparece más de una vez. Añade más pares si tienes más columnas. No intentes construir la misma comprobación uniendo los rangos dentro de COUNTIF, como en COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF espera un rango real como primer argumento, así que esa fórmula no hace lo que parece. COUNTIFS es la solución limpia.
Lo que hace COUNTIF y sorprende a muchos
La página de COUNTIF de Microsoft enumera algunos comportamientos que importan cuando buscas duplicados:
- La coincidencia de texto no distingue entre mayúsculas y minúsculas. "manzanas" y "MANZANAS" se cuentan como el mismo valor. Excel los trata como duplicados en este caso.
- Los caracteres
?y*actúan como comodines. Si tus datos los contienen, por ejemplo en códigos de producto, COUNTIF puede coincidir con más de lo que esperas. Escribe una tilde delante del carácter para buscarlo literalmente. - COUNTIF devuelve resultados incorrectos para cadenas de más de 255 caracteres.
- Los espacios al principio o al final, las comillas rectas y curvas mezcladas y los caracteres no imprimibles pueden alterar el recuento. Microsoft sugiere las funciones TRIM y CLEAN para limpiar primero los datos.
Este último punto es el origen de la mayoría de las quejas de "Excel no encuentra mis duplicados". Dos celdas que parecen idénticas pueden diferenciarse por un espacio al final. Limpia primero y luego cuenta.
Filtrar y revisar
Una vez colocada la columna auxiliar, filtra por VERDADERO o por valores mayores que 1 y revisa las filas. Puedes ordenar por la columna auxiliar para agrupar todas las filas repetidas. Revisa una muestra antes de eliminar nada. Con frecuencia, un "duplicado" resulta ser dos registros reales que comparten un nombre.
Listar solo los valores duplicados
A veces quieres una lista corta de lo que se repite, no una marca junto a cada fila.
En Excel para Microsoft 365, 2024 y 2021, la función UNIQUE puede hacer el trabajo. Según la página de UNIQUE de Microsoft, =UNIQUE(array,[by_col],[exactly_once]) devuelve los valores distintos de un rango, y si exactly_once se establece en VERDADERO, devuelve solo los valores que aparecen una vez. Así, =UNIQUE(A2:A100) muestra cada valor una vez, y =UNIQUE(A2:A100,,TRUE) muestra los valores que no están duplicados en absoluto. Para obtener solo los valores repetidos, combina UNIQUE con FILTER y COUNTIF:
=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))
Esto conserva las filas donde el recuento es mayor que 1 y muestra cada una de ellas una vez. Necesita una versión de Excel con matrices dinámicas. Pruébala con algunas filas antes de confiar en ella con una lista grande.
En versiones anteriores, usa una columna auxiliar. Escribe =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") y arrástrala hacia abajo. La columna mostrará cada valor repetido y dejará en blanco el resto. Filtra los espacios en blanco. Verás cada valor repetido tantas veces como aparezca, lo cual es adecuado para una revisión.
Con Filtro avanzado, puedes copiar solo los registros únicos a una nueva ubicación. Ve a Datos > Avanzadas, elige Copiar a otra ubicación, escribe una celda en el cuadro Copiar a y marca Solo registros únicos. Comparar esta lista con la original muestra qué se repetía.
Usa Power Query para conservar solo los duplicados
Si tus datos son muy numerosos o llegan cada semana, deja que Power Query encuentre los duplicados. La documentación de Power Query de Microsoft describe un comando Conservar duplicados, lo contrario de Quitar duplicados. Filtra la tabla hasta dejar las filas cuyos valores en las columnas seleccionadas aparecen más de una vez y, a diferencia de Quitar duplicados, devuelve todas las filas coincidentes, no una por valor.
El artículo de Microsoft sobre conservar o quitar filas duplicadas explica los pasos para la parte de quitar. El inicio es el mismo para ambos:
- Selecciona una celda de tus datos y elige Consulta > Editar, o cárgalos con Datos > Desde una tabla o rango.
- Haz clic en el encabezado de una columna para seleccionarla. Mantén pulsada la tecla Mayús o Ctrl para seleccionar más columnas.
- Aplica el comando. Los pasos de Microsoft para quitar usan Inicio > Quitar filas > Quitar duplicados. Busca el comando Conservar duplicados correspondiente junto a los otros comandos de filas de la pestaña Inicio y revisa la vista previa.
Microsoft también indica que Power Query distingue mayúsculas y minúsculas, de modo que "Apple" y "apple" son valores diferentes allí, a diferencia de COUNTIF. Es una diferencia real entre los dos métodos. Si tu objetivo es una comprobación semanal repetible, decide un comportamiento y mantenlo, por ejemplo convirtiendo antes la columna a mayúsculas.
Encuentra duplicados entre dos hojas o listas
Una tarea habitual es comprobar si una lista de una hoja también aparece en otra: una nueva lista de clientes frente a la antigua, o los pedidos de este mes frente a los del mes pasado.
- Coloca las dos listas en hojas separadas, por ejemplo Hoja1 y Hoja2, con los valores en la columna A.
- En Hoja1, en B2, escribe
=COUNTIF(Hoja2!A:A,A2)>0. - Arrastra la fórmula hacia abajo. VERDADERO significa que el valor también aparece en Hoja2.
- Filtra por VERDADERO para ver las coincidencias, o por FALSO para ver lo nuevo.
Para dos columnas que deben coincidir en conjunto, usa COUNTIFS con un par para cada columna y los rangos de la otra hoja.
Si la segunda lista está en otro libro, Microsoft advierte de que una fórmula que hace referencia a un libro cerrado puede devolver un error #¡VALOR!. Mantén abiertos los dos libros mientras trabajas, o trae la segunda lista al primero con Power Query. Después de la comprobación, recuerda que el resultado depende de los datos en ese momento. Si cualquiera de las listas cambia, las marcas también cambian.
Coincidencias aproximadas: Jon Smith y John Smith
Todo lo anterior encuentra solo coincidencias exactas, salvo por las mayúsculas. Las listas reales también contienen erratas y ortografías distintas. Excel no tiene una función integrada que puntúe lo parecidos que son dos textos, así que lo resuelves en dos pasos.
Paso uno: elimina las diferencias falsas. Usa ESPACIOS para quitar los espacios sobrantes y LIMPIAR para eliminar los caracteres no imprimibles, y construye la comprobación sobre los valores limpios. En una columna auxiliar, =ESPACIOS(A2) muestra el texto limpio. Cuenta los duplicados en esa columna, no en la original. Como COUNTIF no distingue mayúsculas, no necesitas cambiar las mayúsculas allí. En Power Query, donde sí importan, convierte la columna a mayúsculas o minúsculas.
Paso dos: revisa lo que queda. Ordena la lista limpia alfabéticamente y revisa los registros contiguos. Las coincidencias aproximadas, como "Jon Smith" y "John Smith", quedan una junto a otra y así una persona puede verlas. En listas más grandes, Microsoft Research publicó el complemento Fuzzy Lookup para Excel. Puede encontrar filas duplicadas aproximadas dentro de una misma tabla o emparejar filas similares entre dos tablas, y asigna a cada coincidencia una puntuación de similitud. Antes de planificar en torno a él, comprueba los requisitos: la página de descarga indica Windows, con Excel 2007, 2010, 2013 y 2016, y una versión de 2020. Puede que no se instale en una configuración actual de Microsoft 365, y no está disponible en Mac.
Uses lo que uses, revisa a mano los resultados aproximados. Dos personas con nombres parecidos no son necesariamente la misma persona.
Errores comunes
- Comprobar una sola columna cuando el duplicado se define con dos. Un apellido repetido por sí solo no es un registro duplicado.
- Confiar en los recuentos de COUNTIF con datos que tienen espacios sobrantes o caracteres extraños. Limpia primero los datos con TRIM y CLEAN.
- Olvidar que COUNTIF no distingue mayúsculas de minúsculas mientras que Power Query sí, y después obtener resultados distintos con cada uno.
- Usar
COUNTIF($A$2:$A$100,A2)>1y eliminar todas las filas marcadas como TRUE. Eso marca también la primera copia, así que acabarías borrando todas las copias. Usa el rango expansivo para marcar solo las copias posteriores. - Buscar en columnas completas cadenas de más de 255 caracteres, donde COUNTIF devuelve resultados incorrectos.
- Eliminar justo después de resaltar. Copia primero la hoja, ya que Microsoft indica que los duplicados quitados se eliminan de forma permanente.
Temas relacionados para explorar
- Quita los duplicados que encontraste. El comando Quitar duplicados de la pestaña Datos conserva la primera coincidencia y elimina el resto. Ordena antes para controlar qué fila se conserva.
- Conserva el mejor registro, no el primero. Ordena por fecha o valor para que la fila que quieres quede primero y después marca las copias posteriores con el COUNTIF de rango expansivo.
- Compara listas entre archivos. Power Query puede combinar varios libros en una sola tabla para que puedas compararlos juntos.
- Evita nuevos duplicados. La validación de datos con una fórmula personalizada puede bloquear un valor que ya existe. Pruébalo en una hoja de prueba.
- Mantén un registro de auditoría. Deja la columna auxiliar en una copia de la hoja para poder mostrar después qué se marcó y por qué.
Siguiente paso
Elige una columna de tu archivo actual, resalta sus duplicados con formato condicional y cuenta cuántas celdas se iluminan. Luego añade una columna auxiliar con COUNTIF y compara los números. Si difieren, acabas de encontrar espacios ocultos o diferencias de mayúsculas y minúsculas, y ya sabes cómo encontrar duplicados en Excel antes de eliminar nada.
Fuentes
- Filtrar valores únicos o quitar valores duplicados (Soporte técnico de Microsoft)
- Función CONTAR.SI (Soporte técnico de Microsoft)
- Mantener o quitar filas duplicadas (Power Query) (Soporte técnico de Microsoft)
- Trabajar con valores duplicados (Microsoft Learn)
- Complemento Fuzzy Lookup para Excel (Centro de descarga de Microsoft)