Mi guía real para encontrar duplicados en Excel sin romper nada
En mi día a día gestionando bases de datos enormes, donde cada fila es un registro que puede costar dinero o reputación, aprender a detectar duplicados en Excel no fue una curiosidad académica, sino una necesidad urgente. Un listado con cien mil filas y un puñado de registros repetidos en el sitio equivocado te puede montar una reunión incómoda con el equipo comercial.
Lo primero que suelo recomendar a cualquiera que me pregunta es que empiece por lo visual, porque es instantáneo y no toca los datos. Seleccionas el rango, vas a Inicio → Estilos → Formato condicional → Resaltar reglas de celdas → Duplicar valores, y en el cuadro que aparece eliges la opción Duplicar. Excel pinta en rojo claro (o el color que tú prefieras) todo lo que aparece más de una vez. Las celdas únicas quedan sin fondo. Con eso ya ves de un vistazo dónde está el problema, sin borrar ni mover una sola celda.
Ahora bien, cuando la hoja tiene miles de filas, desplazarte con la rueda del ratón buscando manchas rojas es una tortura. Aquí entra un truco que no todo el mundo conoce: una vez aplicadas las reglas, activas el filtro y usas Filtrar por color. De golpe te quedas solo con los registros duplicados, el resto se oculta. Para extraer después la lista limpia, copias los valores a otro rango y aplicas el Filtro avanzado marcando la casilla de registros únicos. Así no alteras el original ni un milímetro.
Personalmente, lo que más uso cuando necesito algo más que un simple sí o no es la función CONTAR.SI (COUNTIF en inglés). La idea es directa: una columna auxiliar con la fórmula =CONTAR.SI(A:A;A2) arrastrada hacia abajo te devuelve cuántas veces aparece cada valor. Filtras por mayor que uno y ya tienes localizados los duplicados, pero además sabes cuántas veces se repiten. Y ese dato extra marca una diferencia enorme.
Para que te hagas una idea de la estructura que suelo montar:
| Método | Qué responde | Información extra |
|---|---|---|
| Formato condicional | ¿Se repite? | Color visual inmediato |
| CONTAR.SI | ¿Cuántas veces se repite? | Número exacto de apariciones |
| Formato condicional con fórmula | ¿Se repite exactamente N veces? | Filtro por intensidad |
Y aquí va lo que considero realmente insustituible: crear una regla de Formato condicional basada en fórmula. Entras en Inicio → Estilos → Formato condicional → Nueva regla, escribes algo como =CONTAR.SI(A:A;A2)=3 y Excel colorea solo las celdas que se repiten exactamente tres veces. En un listado con días de la semana, por ejemplo, solo el domingo que aparecía tres veces se marcó. Es la única técnica que permite filtrar visualmente por intensidad de repetición, no solo por presencia. Un duplicado que sale dos veces suele ser inocente; uno que aparece treinta veces es un error de importación clamoroso.
Para quien necesita un marcador de texto en vez de color, combino dos columnas auxiliares: una con el recuento =COUNTIF(A2:A7;A2) y otra con =IF(B2>1;"Sí";"No"). Sobre esa columna de Sí/No aplico Formato condicional con fórmula propia, algo como =$C2="Sí", y se resalta la fila entera asociada al duplicado. Mucho más legible que una celda pintada a medias.
Eso sí, hay un aviso que siempre repito en las formaciones: CONTAR.SI solo detecta duplicados exactos. Si un nombre tiene un espacio extra al final, o un texto lleva mayúsculas distintas, Excel lo considera un valor único. Antes de confiar en el resultado, normalizo el texto con ESPACIOS() o reviso a mano con Buscar y reemplazar las grafías inconsistentes.
En mis primeras experiencias montando catálogos online con miles de referencias, descubrí que la mayoría de duplicados reales no eran idénticos en una columna sino en un conjunto. Ahí es donde entra COUNTIFS, que admite múltiples criterios: una fórmula como =CONTAR.SI.CONJUNTO(A:A;A2;B:B;B2)>1 marca duplicados solo cuando coinciden a la vez dos o más columnas. Resuelve el caso típico del cliente con el mismo nombre pero distinto teléfono, o la misma dirección pero otro código postal.
Y una restricción que me ha dado más de un susto: Excel no puede resaltar duplicados en el área de Valores de una tabla dinámica. Si trabajas sobre un informe pivot, el Formato condicional simplemente no hace nada ahí. Copia los valores a un rango normal o trabaja sobre los datos de origen antes de aplicar cualquier regla de duplicidad.
“Quitar duplicados elimina permanentemente: trabajar sobre una copia de la hoja es obligatorio, no una sugerencia. Y Excel no resalta duplicados en Valores de tabla dinámica.”
Dato clave