Encontrar y quitar duplicados en Excel: aprende en 3 pasos

Información clave

Al trabajar con largas listas en Excel muchas veces se quieren quitar o, al menos, identificar aquellos valores que están duplicados y es muy difícil hacerlo a mano. Encontrar y quitar duplicados es ampliamente útil, especialmente cuando te encuentras trabajando con listas o bases de datos que contienen muchísima información. Usando formato condicional y la pestaña “Datos” lograrás encontrar y quitar duplicados fácilmente de tus planillas Excel.

Lo básico sobre encontrar duplicados

Concepto: Excel considera como valor duplicado aquellos valores que aparecen más de una vez, es decir, no es exclusivamente que un valor esté sólo dos veces, sino que puede aparecer dos, tres o más veces.

Finalidad: Identificar qué celdas o conjunto de celdas están duplicadas en una lista de Excel. A veces se quieren eliminar los duplicados o simplemente identificarlos para hacer algo con aquellos datos repetidos.

Encontrar duplicados

Si es que tienes una lista de datos y quieres encontrar duplicados en Excel puedes hacerlo en 3 simples pasos. Vamos a comenzar con lo siguiente:

Paso 1: Selecciona los datos en los que quieres encontrar los valores duplicados.

Paso 2: En la pestaña de “Inicio”, selecciona “Formato condicional”, luego “Regla para resaltar las celdas” y finalmente “Valores duplicados…”.

Paso 3: En la ventana que se abre se ve que te ofrece marcar los “Duplicados” y también puedes elegir el formato que le quieres dar a esos duplicados. En este caso elegiremos un relleno de rojo claro con letras oscuras. Luego, haz click en “Aceptar”.

Como resultado obtenemos en rojo todas las celdas duplicadas (recuerda que en Excel esto es lo mismo que repetidas), tanto las que están dos (Arturo), tres (Gonzalo) o más veces (Karen).

Para quitar el formato que le acabamos de dar a este conjunto de celdas, debes ir a “Formato Condicional” y luego a “Borrar reglas”.

Si quieres, puedes ver el siguiente video explicativo de este proceso con otro ejemplo:

Tip Ninja: Para encontrar los valores únicos puedes elegir la opción “Únicos” en la ventana que se abre en el Paso 3, como se ve en la siguiente imagen:

Y se obtiene:

Tip Ninja: Al evaluar si es que una celda está duplicada, Excel toma en cuenta los espacios. Esto quiere decir que “Juan”, “Juan·“ y “·Juan” los toma como valores distintos. Una buena opción para asegurarse que estos problemas no ocurran es usar la función ESPACIOS, que deja sólo un espacio entre dos palabras y elimina los espacios a los extremos de las frases.

Encontrar valor duplicados una cantidad definida de veces

¿Qué pasa cuando quieres resaltar aquellos valores que sólo están tres veces (no dos, no cuatro ni más)? Para esto debes crear una nueva regla. Estos son los pasos a seguir.

Paso 1: Selecciona los datos en los que quieres encontrar los valores duplicados.

Paso 2: En la pestaña de “Inicio”, selecciona “Formato condicional” -> Nueva Regla

Paso 3: En la ventana que se abre elige la opción “Utilice una fórmula que determina las celdas para aplicar formato” y escribe la siguiente fórmula: =CONTAR.SI($B$3:$D$10;B3)=3

Paso 4: Luego, puedes elegir el formato que le quieres dar a los triplicados. En este caso elegiremos relleno de rojo claro con letras oscuras. Finalmente, haz click en “Aceptar”.

Como resultado obtenemos en rojo sólo las celdas repetidas tres veces (Gonzalo):

¿Cómo funciona =CONTAR.SI($B$3:$D$10;B3)=3:?

Se usa la fórmula CONTAR.SI para contar la cantidad de celdas en el rango de B3 hasta D10 que son iguales a la celda B3 y si es que esa cantidad es igual a 3, entonces le da el formato rojo a la celda. En este caso, va a contar cuántas veces está “Karen” desde B3 hasta D10. Luego, como Karen aparece 4 veces, no le asigna el formato rojo (porque sólo se pone si está 3 veces).

Como en el Paso 1 seleccionamos todos los datos antes de ir a “Formato condicional”, Excel automáticamente copia la fórmula para todas las celdas de la selección, pero usando referencias relativas. Así, en la celda B4 tiene la fórmula: =CONTAR.SI($B$3$D$10;B4)=3 y así cada celda usa la misma fórmula pero comparando el valor de las otras celdas con su propio valor. En este caso, Excel va a contar cuántas veces aparece “Cristine” desde B3 a D10. Como sólo aparece una vez, no le asigna el formato rojo.

Tip Ninja: Puedes usar cualquier formula que quieras para esto. Por ejemplo, puedes usar la fórmula: =CONTAR.SI($B$3:$D$10;B3)>=3. En este caso, le decimos a Excel que cuente los duplicados que se encuentran repetidos tres o más veces, al utilizar en la expresión “>=3” (mayor o igual a tres).

Encontrar filas duplicadas

Para encontrar filas completas duplicadas en tu lista de Excel podemos usar el método aprendido anteriormente, pero el resultado no es muy fácil de manejar. Por ejemplo, si empezamos con lo siguiente:

Si es que aplicamos la técnica recién aprendida obtendremos:

Este resultado es correcto pero se ve muy engorroso y no es tan fácil de interpretar. Vemos que Gonzalo queda en rojo pero son dos Gonzalos distintos (uno de apellido Mesa y otro de apellido Jeria). También, vemos que Juan Fernández tiene una compra de $15.000 repetida pero otra de $85.000, pero están en rojo su nombre, apellido e ID.

Idealmente, lo que queremos es tener en formato distinto las celdas en el caso en que toda la fila esté duplicada. Para esto, crearemos una columna nueva que sea todos los valores de la fila juntos. Puedes escribir en F3: =B3&C3&D3&E3 y luego arrastrar hacia abajo. También puedes usar la función CONCAT, escribiendo “=CONCAT(B3:E3)”.

Luego, aplicamos el formato condicional para encontrar duplicados sobre la columna nueva y obtenemos:

Así, tenemos identificadas las filas que están duplicadas en tu lista de Excel. Esto es porque la columna F es la combinación de todas las otras columnas, entonces al ver si la columna F tiene duplicados estamos viendo si hay un duplicado que sea idéntico en todas las columnas.

Con eso puede ser suficiente, pero habíamos dicho en que íbamos a darle formato a la fila completa, no a una columna extra. Para esto debes seguir pasos similares al ítem anterior:

Paso 1: Selecciona los datos en los que quieres encontrar los valores duplicados.

Paso 2: En la pestaña de “Inicio”, selecciona “Formato condicional” -> Nueva Regla.

Paso 3: En la ventana que se abre elige el Estilo “Clásico” y la opción “Utilice una fórmula que determina las celdas para aplicar formato”.

Paso 4: Escribe la siguiente fórmula: =CONTAR.SI($F$3:$F$9;$F3)>1

Paso 5: Luego, puedes elegir el formato que le quieres dar a las filas duplicadas. En este caso elegiremos relleno de rojo claro con letras oscuras. Finalmente, haz click en “Aceptar”.

Así, se obtiene:

En cuanto a =CONTAR.SI($F$3:$F$9;$F3)>1, es un procedimiento similar a la sección anterior. En este caso, queremos comparar el valor de una celda de la columna nueva con el resto de la columna. Así, para la celda B3 el criterio para darle formato rojo es contar la cantidad de veces que el valor de F3 se repite entre F3 y F9. Vemos que está ese mismo valor en F8, eso nos da un resultado de 2. Eso es mayor a 1, por lo tanto se le da formato rojo. 

Luego, para C3 va a pasar lo mismo: se ve la a asignar el formato rojo dependiendo de la cantidad de veces que aparezca F3 entre F3 y F9, por lo que también se le da formato. Así, el mecanismo se repite para toda la fila y esta queda con el formato rojo. 

Después, para B4 el criterio es si es que se repite el valor de F4 entre F3 y F9. Como no se repite, queda con su formato original y así es para toda esa fila. Así, se recorren todos los datos y quedan sólo las filas duplicadas con el formato en rojo.

Tip Ninja: Si no quieres que se vea la columna F en que tienes todo concatenado, entonces puedes ocultarla seleccionando toda la columna y haciendo click en “Ocultar”.

Y se obtiene:

Quitar duplicados

Si es que lo que buscas es encontrar y quitar duplicados debes seguir los siguientes pasos:

Paso 1: Haz click en cualquier parte de la lista o tabla en donde tienes tus datos.

Paso 2: Ve a la pestaña de “Datos” y haz click en “Quitar duplicados”.

Paso 3: En la ventana que se abre, si es que tu lista o tabla tienen encabezado (título), marca la opción “Mi lista tiene encabezados”.

Paso 4: Debes seleccionar las columnas que definirán el criterio de que los datos estén duplicados. En este caso seleccionaremos todos porque queremos eliminar la fila sólo si está 100% repetida. Haz click en “Aceptar”.

Y tenemos como resultado final la tabla en que se elimina la compra de Juan Fernández por $15.000:

Tip Ninja: Al encontrar y quitar duplicados estás alterando tus datos, por lo que es recomendable que tengas una copia de tus datos originales.

Si quisiéramos, por ejemplo, eliminar todas las celdas en que “Nombre” esté duplicado, en el Paso 3 seleccionaríamos sólo la opción de “Nombre”, como se ve en la siguiente foto:

Al eliminar los duplicados, siempre se mantiene la primera mención y se eliminarán las filas de abajo. Así, para el nombre “Juan” sólo queda la primera compra de Juan Fernández por $15.000 y respecto a “Gonzalo” sólo queda Gonzalo Mesa, eliminando a Gonzalo Jeria.

​Con estos tips puedes fácilmente encontrar y quitar duplicados de tus listas y bases de datos.