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 repiten.

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 la opción “Duplicar” y seleccionamos el formato que se ha de aplicar a las celdas en las 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 no 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 sacar la lista de los datos duplicados, recurrimos a los filtros por colores.

En la herramienta Datos -> Filtros, elegimos filtros por color y seleccionamos el color con el 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 en el color que resaltó 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 en 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 presionadas 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 obtener 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.