Mi fórmula de cabecera para cazar datos repetidos en Excel sin perder la cabeza
A lo largo de mis años manejando hojas de cálculo, he descubierto que la función que más utilizo para rastrear valores duplicados es CONTAR.SI. No hace falta ser un genio de las fórmulas, basta con entender una idea sencilla: cuentas cuántas veces aparece un valor dentro de un rango y, si el resultado supera el uno, ya tienes tu duplicado. En mi caso, cuando gestiono listados grandes, siempre arranco por aquí antes de complicarme con funciones más avanzadas.
La expresión básica que me salva la vida es =CONTAR.SI($A$5:$A$20;A5). El primer argumento fija el rango completo con anclaje absoluto, y el segundo apunta a la celda de la fila actual. Al arrastrar la fórmula hacia abajo, cada registro obtiene su propio conteo. Devuelve un 1 cuando el dato es único, un 2 si aparece una segunda vez, un 3 en la tercera, y así sucesivamente. Es, sin exagerar, el punto de partida de cualquier estrategia contra duplicados que he probado.
Lo que hace realmente cómodo este enfoque es que se puede envolver dentro de un SI para obtener una etiqueta legible: =SI(CONTAR.SI($A$5:$A$20;A5)>1;"Sí";"No"). De golpe, la columna auxiliar muestra "Sí" en cada fila duplicada y te permite filtrar solo esas. También puedes usar la versión booleana pura, =CONTAR.SI($A$5:$A$20;A5)>1, que devuelve VERDADERO o FALSO, muy útil si el resultado alimenta otra fórmula o una regla de formato condicional. En mis proyectos de organización de datos siempre prefiero la versión con "Repetido" y "Único" porque al cliente le resulta más intuitiva.
Una variante que descubrí hace tiempo y que considero casi imprescindible es la del rango expandible: =CONTAR.SI($A$5:A5;A5). Fíjate que solo el primer extremo lleva el símbolo de dólar. Al copiar hacia abajo, el rango crece fila a fila, así que la primera aparición devuelve 1, la segunda devuelve 2, la tercera devuelve 3. Filtrando los valores mayores que 1, localizas con precisión exacta las filas sobrantes y conservas la primera instancia de cada registro. Esto me resultó revelador cuando empecé a depurar bases de datos enormes.
La sintaxis oficial de CONTAR.SI es CONTAR.SI(rango;criterios), ambos argumentos obligatorios. El rango admite números, matrices, rangos con nombre y referencias, ignorando celdas vacías. Los criterios pueden ser un número, una expresión, una referencia de celda o una cadena de texto. Si necesitas varios criterios simultáneamente, la función que toca es CONTAR.SI.CONJUNTO, no CONTAR.SI.
| Fórmula | Qué devuelve | Uso típico |
|---|---|---|
| =CONTAR.SI($A$5:$A$20;A5) | Número de apariciones (1, 2, 3…) | Conteo base |
| =SI(CONTAR.SI($A$5:$A$20;A5)>1;"Sí";"No") | Etiqueta legible | Filtrar duplicados |
| =CONTAR.SI($A$5:A5;A5) | Posición de aparición (1ª, 2ª, 3ª…) | Identificar sobrantes |
| =CONTAR.SI($A$5:$A$20;A5)>1 | VERDADERO / FALSO | Formato condicional |
Un detalle que conviene tener grabado: CONTAR.SI no distingue mayúsculas de minúsculas. "manzanas" y "MANZANAS" coinciden. Para listados de nombres o códigos esto suele ser una ventaja, pero si necesitas exactitud tipográfica, esta función se queda corta y hay que buscar otras construcciones. También admite comodines: el asterisco (*) sustituye cualquier secuencia y el signo de interrogación (?) un carácter individual. Microsoft documenta además operadores dentro de los criterios, del tipo =CONTAR.SI(B2:B5;">55") o comparaciones con concatenación usando &.
PASOS CONCRETOS:
- Colócate en la celda adyacente a la primera fila de datos (por ejemplo B5 si trabajas con A5:A20).
- Escribe =CONTAR.SI($A$5:$A$20;A5) y pulsa Enter.
- Arrastra o rellena la fórmula hasta el final de la tabla.
- Para una etiqueta amigable, cambia a =SI(CONTAR.SI($A$5:$A$20;A5)>1;"Repetido";"Único").
- Activa los encabezados y aplica Datos > Filtro para aislar los "Repetido".
- Para detección visual rápida sin fórmulas: selecciona el rango, ve a Inicio > Formato condicional > Reglas para resaltar celdas > Valores duplicados, elige "Duplicado" y acepta.
“En Microsoft 365: =UNICOS(FILTRAR(A5:A20;CONTAR.SI(A5:A20;A5:A20)>1)) genera al instante el listado de duplicados únicos.”
Dato clave