▷Eliminar archivos de Excel duplicados: todas las técnicas 🥇

No siempre es fácil detectar duplicados en una tabla, especialmente cuando es grande. Pero Excel tiene muchas funciones para encontrarlos, mostrarlos, ocultarlos, contarlos y borrarlos si es necesario.

Si bien es normal que algunas de sus tablas tengan datos duplicados en una columna en particular, si está en el comercio, afortunadamente tiene derecho a vender la misma referencia de producto el viernes y luego el martes y jueves siguientes.! -, es igualmente normal querer, en un momento u otro, mantener solo una lista de datos únicos, o acumular para estos duplicados, o incluso realizar estas dos tareas al mismo tiempo.

Una simple ordenación en la columna derecha de una tabla de Excel ya es suficiente para identificar valores iguales o similares, ya sean etiquetas de texto o fechas, por ejemplo. Pero Excel nació en 1985, sobre las versiones, ¡también se ha acumulado! Hoy ofrece muchas funciones, a veces redundantes o con sutiles características, y que se pueden encontrar en diferentes menús, para ayudarte a tratar estos duplicados como desees: resaltarlos, ocultarlos, combinarlos o calcular la media, o incluso eliminarlos.

El diseño y el nombre de las opciones difieren un poco entre las versiones de Excel para Windows, Mac y la web, pero funcionan de manera idéntica.

¿Cómo detectar duplicados en una o más columnas en Excel?

Hay varias formas de detectar todos los valores duplicados en una o más columnas de un vistazo, sin eliminarlos realmente.

Haga clic en cualquier celda de su tabla y, para seleccionar todos los datos en ella, presione la combinación de teclas Ctrl + A en PC o Cmd + A en Mac. Si la primera fila de encabezado (Estudiante, Asunto) es parte de la selección o no, es irrelevante para la función que va a ejecutar aquí. Tenga en cuenta, sin embargo, que al presionar de nuevo Ctrl + A, la selección se extenderá a estos títulos de columna (si estos encabezados no están ya seleccionados), y que una nueva presión sobre Ctrl + A seleccionaría toda la hoja.

Eliminar archivos de Excel duplicados: todas las técnicas

También puede seleccionar solo una columna para que solo le interesen los duplicados en esta columna: haga clic en el primer valor (por ejemplo, en Peter en nuestro ejemplo) y presione las tres teclas Ctrl + Mayús + Flecha abajo, en PC o Mac o en Excel para la Web. También puede hacer clic, en la parte superior, en el A de la columna A.
En la pestaña Iniciohacer clic en Formato condicional> Reglas de resaltado de celdas> Valores duplicados.

En la primera lista desplegable, seleccione duplicar (el otro criterio posible es: uniques), y en la segunda lista, elija un formato de celda, por ejemplo Relleno rojo claro o especificar otro haciendo clic en Tamaño personalizado…

Si ha elegido un formato personalizado, el Formato de celda le permite elegir el formato de los duplicados usando las pestañas Número, fuente, borde et relleno. Haga clic, por ejemplo, en el botón Patrones y texturas, puede elegir dos colores de la paleta para crear un degradado.

Presione varias veces OK para validar sus opciones, las celdas duplicadas se formatean como usted especificó para una identificación más fácil.

Este formato no sobrescribe el formato anterior de sus celdas, desaparecerá cuando seleccione su tabla (Ctrl + A ou Cmd + A) y eso, en la pestaña Inicio, lanzarás Formato condicional> Borrar reglas de las celdas seleccionadas. Para evitar tener que seleccionar toda la tabla, también puede hacer clic en Borre las reglas de toda la hoja.

Si un valor, por ejemplo Emma, aparece dos veces en la columna Estudiante, Excel marca ese valor dos veces como Emma. Esto lo especificamos porque en otras funciones que veremos más adelante, Excel deja intacta la primera aparición de Emma, pero actúa solo sobre las siguientes, los duplicados.
Incluso si se seleccionan varias columnas (como en nuestro ejemplo), esta función de Excel busca duplicados en cada columna, independientemente de las demás. Por lo tanto, Excel no busca en la tabla todas las filas en las que aparece el par Emma + Historial, da formato a Emma si el nombre aparece varias veces en la columna Estudiante y formatea Historial si este título aparece varias veces en la columna Asunto. También podría ejecutar el comando en la primera columna, luego en la segunda, obtendría los mismos resultados que si lo lanzara directamente en las dos columnas de la tabla.
Excel no tiene en cuenta el caso (mayúsculas): toto, Toto, ToTo y TotO se mencionan como duplicados.
La hoja de cálculo tampoco tiene en cuenta el formato: si tres celdas almacenan la fecha 11/11/2021, todas serán mencionadas como duplicadas incluso si, debido a su formato de visualización, la primera muestra 11-Nov, la segunda 11/11 y el tercero 11/11/20.

¿Cómo filtrar datos para mostrar duplicados en Excel?

Los filtros son muy útiles para mostrar solo ciertas filas en una tabla. Excel oculta las filas que no coinciden con sus criterios, pero no elimina ningún dato; si está utilizando una hoja de cálculo compatible con Excel, seguramente se le ofrecerá una función de filtro. Con los filtros puede, por ejemplo, mostrar en una lista de productos solo aquellos cuya marca es Sony, lo que equivale a buscar todos los duplicados de Sony. Y con la misma facilidad cree filtros multicriterio en varias columnas, como: Marca = Sony y Categoría = Televisión.

Los filtros de Excel son reconocibles por la pequeña flecha desplegable que aparece a la derecha del encabezado de cada columna, para ordenar o filtrar sus datos según los criterios que usted especificará. Es esencial que cada una de las columnas comience con un encabezado (un título de columna).

Para activar los filtros, un primer método consiste en hacer clic en una celda no vacía de su rango de celdas y, en la pestaña Inicio, para hacer clic en Ordenar y filtrar, luego abajo en Filtro. El atajo de teclado es Ctrl + May + L en macOS y Windows (este icono Filtro también está disponible en la pestaña Datos Personales). Aparecen pequeñas flechas desplegables en la primera fila que contiene los títulos.

Otro posible método para activar filtros, más específico de Excel y que te ofrece más posibilidades (verás un uso de este más adelante para calcular la suma de duplicados): crea una "tabla" a partir de tus datos. Comience haciendo clic en uno de sus datos. Bajo la pestaña Inicio, ahora haga clic en Formatear como una tabla, seleccione un formato entre los propuestos ...

… Excel selecciona toda la tabla y marca si tiene encabezados o no. Si sus columnas no tienen una, agrega algunas insertando una fila encima y las llama Columna 1, Columna 2, etc. Pero es preferible que usted mismo nombre sus columnas (Fecha, Cliente, Servicio…). Haga clic en OK, la tabla se formatea y los filtros se activan automáticamente.

Cualquiera que sea el método que haya habilitado para los filtros, haga clic en un flecha desplegable un título de columna para descubrir opciones de clasificación y filtrado. La presentación, aunque un poco diferente en Mac, ofrece la misma riqueza.

Si solo desea ver, por ejemplo, las líneas correspondientes al cliente Smith, la forma más rápida es primero desmarcar la casilla (Seleccionar todo) - que, por lo tanto, anulará la selección de todo -, luego para marcar solo la casilla Dupond (Excel considera que Dupond y dupond son iguales).

Solo se muestran las líneas correspondientes a este cliente: por lo tanto, verá los duplicados. Las otras líneas simplemente están ocultas, lo puede ver a la izquierda, en nuestra ilustración, los números de línea indican: 1, 2, 4, 6, 11 (mientras que las líneas 3, 5, 7, 8, 9, 10 están ocultas) .

Puede agregar criterios de filtro en las otras columnas. Para una búsqueda de varios criterios, por ejemplo, para conservar solo el artículo del Blog de servicio del cliente Dupond, haga clic, además, en la flecha desplegable Servicio para marcar solo este título. Alternativamente, puede solicitar que la celda contenga (o no contenga) el texto que especifique, etc.

Para eliminar un filtro en una columna, haga clic en el botón desplegable y luego en Filtro claro.

Para desactivar permanentemente los filtros y eliminar las pequeñas flechas desplegables: en la pestaña Iniciohacer clic en Ordenar y filtrary luego Filtro.

¿Cómo filtrar datos para ocultar duplicados en Excel?

Los filtros le permiten mostrar solo duplicados en una hoja de trabajo. Pero puede utilizar los filtros avanzados de Excel para, por el contrario, ocultarlos sin eliminar los datos duplicados. Tenga en cuenta que esta función solo está disponible en las versiones de Excel para Windows y Mac, y no en la versión web.

En su rango de datos, seleccione solo la columna que probablemente tenga duplicados. Haga clic, por ejemplo, en su primer valor y presione las tres teclas Ctrl + Mayús + Flecha abajo.

Haga clic, en la parte superior, en la pestaña Datos Personalesy luego Avanzado, en la sección Ordenar y filtrar.

En la ventana Filtro avanzado, asegúrese de que solo esté seleccionada la columna con los duplicados.

Marcar la casilla Extracción duplicada y deja el campo en blanco Área de criterios.
Si quieres Filtrar la lista en el acto, solo se muestra la primera aparición de un elemento duplicado, las filas de su tabla que contienen duplicados están ocultas. Puede verlo a la izquierda, mirando los números de línea: en nuestro ejemplo, vamos de la línea 3 a la línea 6.

Para volver a mostrar las líneas ocultas, puede: en la pestaña Datos Personales, haga clic en borrar...

... o, en la pestaña Inicio, haga clic en Ordenar y filtrary luego borrar.

Un consejo para PC: si realmente desea eliminar estas celdas ocultas, haga clic en la pestaña Archivo> Información> bouton Comprueba que no haya problemas, choix Inspeccione el documento> bouton inspeccionar… Desplácese hacia abajo en la lista de problemas inspeccionados y, en la sección Filas y columnas ocultas, presiona el botón Eliminar todos. Tenga en cuenta que esta acción no se puede deshacer.

Si tu prefieres Copiar a otra ubicación (esto solo es posible en la hoja activa), haga clic en una celda vacía, a continuación hemos elegido H1... Después de revisar Extracción duplicada y presionó el botón OK, los elementos únicos se copian de H1 à H6. Sin tocar su tabla original, esta columna podría usarse para calcular la cantidad generada por cada cliente, le mostraremos la fórmula a continuación.

¿Cómo eliminar duplicados en Excel?

¿Quiere eliminar de forma permanente los duplicados de una hoja de cálculo? Si se trata de una tabla de varias columnas, se eliminará toda la fila de la tabla donde aparece el duplicado. Si sus datos se reducen a una columna, obtendrá la lista de datos únicos. Excel ofrece una función especial para esto. Su método: mantiene el primer elemento y elimina todos los duplicados que luego detecta, sin preocuparse por la relevancia de mantener el primero en lugar del último, y sin, por ejemplo, realizar ninguna acumulación (aprende aquí cómo acumular duplicados). Dependiendo de sus datos, a veces será útil una clasificación previa ...

Incluso si obviamente es posible cancelar la acción (Ctrl + Z en PC o Cmd + Z en Mac), es mejor trabajar en una copia de sus datos o incluso duplicar la hoja de cálculo antes de realizar la eliminación. Un pequeño clic derecho en la pestaña de la hoja de trabajo lo ayudará ...

Aunque no es obligatorio, es mejor si sus columnas tienen una fila de encabezado.
haz clic en sur una sola celda, cualquiera, de su mesa.
En la pestaña Datos Personaleshacer clic en Eliminar duplicados.

Si ha transformado sus datos en una "tabla" de Excel a través de la pestaña Inicio > Formatear como una tabla, esta opción Eliminar duplicados también se puede encontrar en la pestaña Creación de tablas en PC, o Cuadro en Mac, o creación en Excel para la web. Esta pestaña solo es visible cuando selecciona una de las celdas de la tabla.
Excel selecciona toda la tabla y enumera los encabezados de columna que ha marcado.

Tenga la seguridad: ya sea que marque o desmarque tal o cual casilla, Excel no se arriesga a eliminar un valor en una columna sin afectar a los demás en la misma fila, lo que crearía un cambio como una camisa mal abrochada. Si tiene que eliminar un duplicado, en cualquier caso, eliminará una linea completa rango de datos (las celdas fuera de su tabla no se ven afectadas).
Si dejas marcados todos los nombres de las columnas, Excel considerará como duplicados solo las filas donde aparecen, en nuestro ejemplo: ambas la misma fecha, pero además el mismo nombre de cliente, pero también el mismo tipo de servicio, y finalmente la misma cantidad . Si alguno de los valores difiere en las filas siguientes, no se considerará un duplicado.
Por lo tanto, es más probable que desee marcar solo ciertas columnas. Comprobando, por ejemplo, solo la columna Testimonios de (ilustración a continuación): el nombre de cada cliente solo aparecerá una vez en su tabla (se eliminan 3 Dupond, 1 Durand y 1 Matthieu).

Comprobando, por ejemplo Testimonios de et Prestacion (ilustración siguiente): solo las líneas que tienen a la vez el mismo nombre et el mismo servicio se considera duplicado. En nuestro ejemplo, tres líneas se comportaron como Servicio al cliente Valores Dupond / Publicación de blog, se eliminan las dos últimas líneas duplicadas.

Anteriormente, hemos considerado el caso en el que desea eliminar filas duplicadas directamente en su tabla. Pero podría, por ejemplo, copiar y pegar una sola columna de la tabla, por ejemplo, copiar las celdas B1: B11 de la columna Cliente, para pegarlos en la celda F1. Y pidiendo la eliminación de duplicados en F1: F11 ...

… Obtendrá la lista de sus clientes únicos. Esta columna se puede utilizar para calcular la facturación generada por cada cliente, como veremos a continuación.

¿Cómo calcular la suma de duplicados en una hoja de Excel?

Saber que hay duplicados es bueno. Elimínelos para mantener solo los elementos únicos, ¿por qué no? Pero la mayoría de las veces, querrá hacer cálculos en estos duplicados primero, al menos obtener el total, ¡incluso si eso significa eliminarlos! A continuación, le indicamos cómo hacerlo, según el método que elija para detectar valores duplicados.

Calcular el total de valores filtrados

Si acaba de filtrar sus datos, una pequeña fórmula será suficiente para calcular el total de celdas filtradas y, por lo tanto, el total general si se muestran todas las celdas.

En un rango de celdas filtradas a través de la pestaña Inicio> Ordenar y filtrar> Filtrar, la funcion SOUS.TOTAL Excel le permite calcular la suma de las celdas mostradas (o el promedio, o el número de celdas, etc., según los parámetros que especifique).
En el siguiente ejemplo, ingresando en la celda G1 la formula :
= SUBTOTAL (109; $ D: $ D)
para calcular el total de la columna D, obtenemos el total general ya que aquí no se filtra ninguna columna. El valor 109 significa: realiza la suma, ignorando las celdas ocultas.

Si no desea tener en cuenta toda la columna D, sino solo las celdas donde se almacenan sus datos, la fórmula podría escribirse:
= SUBTOTAL (109; D2: D11)
Si filtramos la columna Testimonios de Para retener solo al cliente de Smith, la función SUBTOTAL, que siempre excluye los valores filtrados de sus cálculos, le dice el total de los datos mostrados, por lo tanto, del cliente de Smith.

Calcular el total de una tabla de Excel

Si transformó su rango de celdas a una tabla de Excel a través de la pestaña Inicio> Formatear como tabla, no tiene que escribir una fórmula, Excel la administra por usted.

Haga clic en cualquier celda de la tabla: una pestaña verde Creación de tablas en PC (o Cuadro en Mac, o creación en Excel para la Web) se agrega a las pestañas ya presentes (Archivo, Inicio, Insertar, etc.) en la parte superior de la pantalla.
Haga clic en esta pestaña Creación de tablas luego, abajo, en Fila total. Excel agrega una fila total en la parte inferior de su tabla, para columnas que contienen números.

Si filtra sus datos, el total le dice la suma de solo los valores mostrados. Una flecha desplegable le permite elegir el promedio u otra función, en lugar de la suma.

Calcule el total de cada duplicado

Sin tocar sus datos iniciales, ¿creó una columna libre de duplicados para mantener solo elementos únicos? Si se trata, por ejemplo, de una lista de clientes, una pequeña fórmula será suficiente para calcular la facturación que genera cada uno de estos clientes.

En nuestro ejemplo, la columna G incluye la lista de clientes únicos. La fórmula escrita en H2 permite calcular la facturación generada por el cliente registrado en celda G2, por lo tanto Dupond:
= SUM.SI ($ B: $ B; G2; $ D: $ D)

Con esta fórmula, Excel compara el contenido de la celda G2 (Dupond) en cada valor de la columna B. Si hay una equivalencia, acumula el valor escrito en la misma fila, pero en la columna. D. Por ejemplo, si compara a Smith con el contenido de la celda B5, que también incluye Dupond, suma a su acumulación el valor ingresado en D5.
Copia esta fórmula en las celdas H3 à H6 conocer la facturación que genera cada cliente. La fórmula copiada en la celda. H3 se convierte, por ejemplo, en:
= SUM.SI ($ B: $ B; G3; $ D: $ D)

Insertar subtotales automáticos en tablas

Última solución posible que ofrecemos: subtotales. Excel para Windows y Mac (pero no para la Web) puede, de hecho, insertar automáticamente líneas de subtotales y gran total para acumular sus datos. La hoja de cálculo puede calcular la suma, o la media, o el mínimo, el máximo, la desviación estándar, etc. Con algunas limitaciones para saber:

Los subtotales no se admiten en "tablas de Excel". Cuando hace clic en una celda en su rango de datos, si una pestaña verde Creación de tablas se muestra en la parte superior de la pantalla (se llama Cuadro en Mac y creación en Excel para la web), primero debe hacer clic a continuación en el icono Convertir a rango para que pueda pedirle a Excel que inserte subtotales.
Tan pronto como haga clic en el icono Subtotal, Excel vacía su historial de deshacer. Por lo tanto, ya no podrá cancelar un pedido anterior escribiendo Ctrl + Z, pero aún es posible cerrar el archivo y volver a abrirlo para volver al último registro del libro de Excel. Trabaje en una copia de su archivo si está experimentando.
La inserción de subtotales activa la Modo plan, que inicialmente puede impresionar a los principiantes. No hay razón !

A continuación, le mostramos cómo agregar subtotales a sus columnas numéricas.

Comience ordenando sus datos para que los duplicados se agrupen uno tras otro. Si es una ordenación en una columna, haga clic en cualquier valor de esta columna (solo en una celda) y luego, en la pestaña Datos Personales, haga clic por ejemplo en el icono AZ para un tipo simple y ascendente. Si necesita ordenar en dos o más columnas, o especificar otros criterios de ordenación, haga clic en el icono en su lugar. Trier.

En el siguiente ejemplo, nuestra matriz, previamente ordenada en la columna Fecha, ahora está ordenado en la columna Testimonios de , en orden alfabético, pinchando en el icono AZ.

Una vez que sus datos estén ordenados, seleccione cualquier celda en su tabla (pero solo una celda) y, aún debajo de la pestaña Datos Personales, haga clic en el icono Subtotal.
Aparece la ventana de opciones de subtotal. Excel sugiere realizar un subtotal En cada cambio de fecha. Desenrolle esta lista y especifique en su lugar En cada cambio de cliente.

La lista Usa la función espectáculos suma. Haga clic en él si prefiere otra función (media, mínima, máxima, desviación estándar, número de valores, etc.).
A continuación, verifique las partidas numéricas (puede ser una columna de importes facturados o una columna de horas indicando la duración de cada servicio, por ejemplo) sobre las que se debe realizar la acumulación.
Si prefiere que la fila del total general aparezca en la parte superior de la tabla en lugar de al final de la lista, y que el subtotal de cada cliente aparezca arriba en lugar de debajo de sus filas de detalles, desmarque la casilla Resumen bajo los datos.
Tenga en cuenta la presencia del botón Eliminar todos, para eliminar las líneas de subtotal y desactivar el mundo Plan.
Aquí está nuestra tabla con, para nuestra columna Monto, los subtotales en negrita insertados por Excel (Total Bertrand, Total Dupond, etc.), y el gran total al final de la lista. Por su parte, si su tabla tiene varias columnas que merecen un subtotal (columnas de números, duraciones, etc.), marque varias.

A la izquierda, si hace clic en el nivel 2 del plan, solo muestra las líneas correspondientes a los subtotales y al gran total.

Si realiza cálculos por hora y encuentra que los totales por hora son incorrectos porque siempre se devuelven a las 24:00:00 (¡21h + 5h = 2h en lugar de 26h!), Seleccione las celdas que contienen sus datos por hora, haga clic con el botón derecho en el mouse, elija Formato de celda desde el menú contextual. Bajo la pestaña Nombre, haga clic izquierdo en tiempo y, en la lista Tipo, haga clic en el formato de ejemplo 37: 30: 55.

Excel ahora muestra los totales por hora correctos. ¿Quiere crear un formato de hora personalizado? Rodear el [h] que representan las horas para pedirle a Excel que muestre tiempos superiores a 24 horas: [h]: mm: ss En lugar de h: mm: ss

¿Cómo prohibir la entrada de duplicados en una columna de Excel?

Para evitar que se ingrese el mismo valor más de una vez en una columna, todo lo que tiene que hacer es usar la función de validación de datos de Excel.

Seleccione toda la columna o rango de celdas donde deberá ingresar sus datos, debajo de la columna B.

En la pestaña Datos Personaleshacer clic en Validación de datos.

En la lista permiso, seleccione personnalisé.
En el área de entrada Fórmulas, dado que la celda B1 está actualmente seleccionada, escriba la fórmula:
= NB.SI (B: B; B1) = 1

Adapte esta fórmula: si, por ejemplo, se selecciona la celda C3, escriba = NB.SI (C: C; C3) = 1
En esta misma ventana de diálogo de Validación de datos, haga clic en la pestaña Alerta de error.

En la zona Título, ingrese un mensaje corto, aquí Duplicar.
En la zona Message d'erreur, sea más explícito.
En la zona Estilos : la elección Detener prohibir la entrada de duplicados; las opciones Renuncia ou Información muestre su mensaje pero deje que el usuario ingrese un duplicado si lo desea.
En este ejemplo, cuando presiona la tecla Entrada, el estilo Detener prohíbe ingresar a Dupond dos veces, incluso en minúsculas, y propone Volver a intentar.

Para obtener más información sobre la validación de datos y la ayuda para la entrada, en particular utilizando listas desplegables, consulte nuestra hoja práctica Crea listas desplegables en Excel.

Eliminar archivos de Excel duplicados: todas las técnicas

Contenidos Identificar duplicados en columnas Filtrar datos para mostrar duplicados Filtrar datos para ocultar duplicados Eliminar duplicados en Excel Calcular la suma de duplicados Prohibir la entrada de ...