Las fórmulas que uso en Excel para encontrar datos repetidos sin perder tiempo
Cuando abro un libro de Excel con miles de filas y necesito saber si hay valores que se repiten, mi primera reacción siempre es la misma: seleccionar la columna y lanzar el formato condicional. Es el atajo más rápido que conozco para visualizar duplicados al instante, sin escribir una sola fórmula. Simplemente voy a Inicio, luego Formato condicional, Resaltar reglas de celdas y Duplicar valores. Elige un color de relleno (el rojo claro viene por defecto, pero yo suelo cambiarlo por un amarillo más discreto) y listo, las celdas que aparecen más de una vez se colorean automáticamente.
Cuando gestiono catálogos de productos para tiendas online, me apoyo muchísimo en CONTAR.SI como columna auxiliar porque me permite etiquetar cada fila sin tocar los datos originales. La fórmula básica que repito una y otra vez es =CONTAR.SI(A:A;A2), que devuelve cuántas veces aparece el valor de A2 en toda la columna. Arrastrando hacia abajo obtienes un recuento para cada registro. Si quieres una identificación directa, escribe =CONTAR.SI($C$3:$C$12;C3)>1. Devuelve VERDADERO si es duplicado y FALSO si es único, pero como esos valores no son muy intuitivos, yo siempre envuelvo la prueba dentro de un SI: =SI(CONTAR.SI($C$3:$C$12;C3)>1;"Duplicado";""). Así la columna auxiliar muestra literalmente la palabra "Duplicado" junto a cada registro repetido.
Un truco que descubrí hace años y que casi nadie comenta: el cuadro de diálogo de formato condicional permite resaltar también los valores únicos, no solo los duplicados. Al aplicar esa variante, quedan sin color únicamente las celdas que aparecen una sola vez. Combinado con un filtro automático y la opción "Filtrar por color", obtienes una vista limpia donde solo se muestran los registros problemáticos. Si además necesitas una lista de valores únicos, copia los datos a otro rango y aplica Filtro avanzado.
Para casos donde el duplicado se define por la coincidencia de varias columnas a la vez (por ejemplo, nombre + fecha + importe), CONTAR.SI.CONJUNTO es la herramienta adecuada. La regla de formato condicional personalizada sería: =CONTAR.SI.CONJUNTO($B$3:$B$12;$B3;$C$3:$C$12;$C3;$D$3:$D$12;$D3)>1. Si tu tabla tiene más columnas, simplemente añades pares de rango y celda sucesivos dentro de la fórmula. También puedes resaltar toda una fila evaluando solo una columna concreta fijando la referencia con $C (columna absoluta, fila relativa).
Un matiz que me ha dado más de un disgusto: CONTAR.SI y CONTAR.SI.CONJUNTO no distinguen mayúsculas de minúsculas, así que "manzana" y "MANZANA" cuentan como duplicados. Para una coincidencia exacta necesitas la función EXACT dentro de una fórmula matricial: =SI(SUMAR((--EXACT($C$3:$C$12;C3)))<=1;"";"Duplicado"), confirmada con Ctrl+Mayús+Entrar en versiones antiguas. En Excel 365 las matrices dinámicas lo simplifican bastante.
También existe la opción de resaltar solo las repeticiones a partir de la segunda aparición, excluyendo la primera instancia de cada valor. Se logra con una fórmula que compara el recuento acumulado contra un rango que crece al arrastrar hacia abajo. Así puedes borrar filas sobrantes sin tocar el registro original, algo imprescindible cuando trabajas con bases de datos donde la primera entrada es la válida.
Hay una limitación oficial que conviene recordar: Excel no puede resaltar duplicados en el área de Valores de una tabla dinámica. Para esos casos toca recurrir a fórmulas auxiliares o copiar los datos de salida a un rango normal antes de aplicar la regla de formato condicional.
Una variante especialmente práctica que me encanta es crear una regla que resalte valores que se repiten un número exacto de veces, por ejemplo =CONTAR.SI(A:A;A2)=3 localiza solo los que aparecen tres veces. No es una detección binaria de duplicado/no duplicado, sino una herramienta para identificar patrones de frecuencia en la lista.
Y si lo que necesitas es simplemente suprimir los registros repetidos, la orden Quitar datos > Quitar duplicados hace el trabajo de forma permanente. Mi consejo personal: copia siempre los datos originales a otra hoja antes de ejecutarla, porque no hay botón de deshacer que salve una mala selección de columnas.
Al usar Quitar duplicados puedes activar o desactivar columnas en la detección. Por ejemplo, si quieres que el criterio ignore el precio, desactivas esa columna y el sistema evalúa la duplicidad sobre el resto de campos, conservando información relevante que de otra forma se perdería.
“DESREF recalcula toda la hoja en cada cambio: en listas de miles de filas, una columna auxiliar con CONTAR.SI es mucho más rápida y fácil de mantener”
Dato clave