Buscar valores repetidos en Excel

conocerlos, y en ocasiones, extraerlos en una lista aparte para lo cual hay varios métodos.

Valores duplicados en Excel con formato condicional.

La forma más sencilla de identificar los valores duplicados en Excel, es utilizar formato condicional, lo que nos permite resaltar con un color determinado aquellos valores que se repite.

Supongamos la siguiente tabla de datos:

A simple vista vemos que se repiten los siguientes datos:

• Pedro.
• Juan.
• Catalina.

Lo que haremos es resaltar en Excel esos valores repetidos con formato condicional, y para ello seleccionamos el rango que nos interesa, que en nuestro ejemplo es A1:F500, luego vamos a Formato condicional - Resaltar reglas de celda-Duplicar valores:

Allí seleccionamos a la opción “Duplicar” y seleccionamos el formato que se ha de aplicar a las celdas en que existan datos duplicados.

Con ello Excel resaltará las celdas que contengan datos duplicados, de manera que nos ubica visualmente en las celdas correspondientes, como se aprecia en la siguiente captura de pantalla:

Esta opción o requiere ingresar ningún tipo de fórmulas, por lo que su implementación es muy sencilla.

Listar los valores duplicados en Excel.

El formato condicional nos permite resaltar los datos que se repiten en Excel, pero no nos permite hacer una lista ni extraerlos aparte.

Para sacer la lista de los datos duplicados recurrimos a los filtros por colores.

En la herramienta Datos -> Filtros elegimos filtros por color y elegimos el color con que se han resaltado los datos repetidos, que en nuestro caso ha sido un color rosado.

Con ello se filtran los valores repetidos con base al color que resaltaron con el formato condicional, de modo que primero se debe aplicar el formato condicional para resaltar esos valores duplicados que luego filtraremos.

Extraer valores duplicados en Excel.

Si lo que necesitamos es extraer u una columna aparte los valores duplicados o repetidos, debemos recurrir a una fórmula un tanto compleja como la siguiente:

=SI.ERROR(INDICE($A$1:$A$10;K.ESIMO.MAYOR(SI.ERROR(COINCIDIR(B1:$B$10;$A$1:$A$10;0); ""); FILA())); "")

Se trata de una fórmula matricial, lo que significa que una vez se introduce la fórmula, mantenemos presionados las teclas Shift + Ctrl y luego presionamos Enter para que la fórmula quede entre corchetes así:

{=SI.ERROR(INDICE($A$1:$A$10;K.ESIMO.MAYOR(SI.ERROR(COINCIDIR(B1:$B$10;$A$1:$A$10;0); ""); FILA())); "")}

Luego arrastramos hacia abajo para copiar la fórmula en las siguientes celdas de la columna para llamar la lista de todos los valores duplicados.

Si queremos cambiar el orden en que se extraen los valores repetidos podemos sustituir K.ESIMO.MAYOR por k.ESIMO.MENOR.

Descargar archivo de ejemplo.

Función Si con funciones lógicas anidadas en Excel

Compartir un libro de Excel con varios usuarios