Si necesitas encontrar celdas duplicadas en una sola columna o lista, puedes usar la función COUNTIF para identificar y contar datos duplicados fácilmente.
11. Selecciona una celda en blanco junto a la columna donde buscarás duplicados.

2. Escribe la fórmula =COUNTIF($C$3:$C$12, C3)>1 y pulsa la tecla Enter.

3. Arrastra el controlador de AutoRelleno de la celda con la fórmula para copiarla al resto de celdas.

Notas: En la fórmula =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 es la columna o lista donde buscarás valores duplicados. Como la columna es estática al copiar la fórmula, se utiliza referencia absoluta con “$”.
(2) C3 es la primera celda de la columna especificada. Es una referencia relativa porque debe cambiar automáticamente al copiar la fórmula a otras celdas.
(3) Esta fórmula devolverá Sí o No. Sí significa que el valor correspondiente es duplicado, mientras que No indica que el valor correspondiente es único en la columna.
(4) A veces, los valores Sí o No pueden no ser intuitivos. Podemos combinar la fórmula original con la función SI para que devuelva Duplicados directamente.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Duplicados","")

En algunos casos, necesitas comparar dos columnas y encontrar los valores duplicados. Por ejemplo, tienes dos listas de nombres y quieres identificar los nombres duplicados en la segunda lista respecto a la primera. Puedes usar las funciones BUSCARV y SI.ERROR para lograrlo fácilmente.

1. Selecciona una celda en blanco junto a la segunda lista de nombres.

2. Introduce la fórmula =IFERROR(VLOOKUP(D3,$B$3:$B$18,1,0),"") y pulsa la tecla Enter.

3. Arrastra el controlador de AutoRelleno de la celda con la fórmula para copiarla al resto de celdas según necesites.

Notas: En la fórmula anterior,
(1) D3 es la primera celda de la segunda lista de nombres. La referencia debe cambiar automáticamente al copiar la fórmula, por lo que es relativa.
(2) $B$3:$B$18 es la primera lista de nombres. Es referencia absoluta porque el rango debe permanecer estático al copiar la fórmula.
(3) Cuando un nombre es duplicado respecto a la primera lista, la fórmula devolverá el nombre; de lo contrario, devolverá vacío.
(4) También puedes usar la fórmula =IF(COUNTIF($B$3:$B$18,D3)>0,"Duplicados","") para identificar los nombres duplicados en la segunda lista respecto a la primera. Esta fórmula devolverá "Duplicados" si el nombre correspondiente es duplicado.

(5) Si necesitas buscar duplicados en dos columnas de diferentes hojas, solo debes añadir el nombre de la hoja antes de la referencia de la columna comparada. En nuestro ejemplo, simplemente cambia $B$3:$B$18 por Sheet1!$B$3:$B$18 en la fórmula.

Las fórmulas anteriores no distinguen entre mayúsculas y minúsculas al buscar duplicados, es decir, "manzana" se considera duplicado de "MANZANA". Puedes usar una fórmula de matriz para encontrar valores duplicados en una sola columna respetando las mayúsculas y minúsculas.

1. Selecciona una celda en blanco junto a la columna donde buscarás duplicados.

2. Escribe la fórmula de matriz =IF(SUM((--EXACT($C$3:$C$12,C3)))<=1,"","Duplicado"), y pulsa Ctrl + Shift + Enter.

3. Arrastra el controlador de AutoRelleno de la celda con la fórmula para copiarla al resto de celdas.

Notas: En la fórmula de matriz anterior,
(1) $C$3:$C$12 es la columna donde necesitas buscar valores duplicados. La referencia es absoluta porque permanece estática al copiar la fórmula de matriz.
(2) C4 es la primera celda de la columna. La referencia es relativa, ya que debe cambiar automáticamente al copiar la fórmula de matriz.
(3) Si la celda correspondiente es duplicada, la fórmula de matriz devolverá "Duplicado"; de lo contrario, devolverá vacío.

Puedes usar la función Formato condicional para resaltar rápidamente las celdas duplicadas en una columna o rango.

1. Selecciona la columna donde vas a resaltar las celdas duplicadas.

2. Haz clic en Inicio > Formato condicional > Reglas para resaltar celdas > Valores duplicados. Consulta la siguiente imagen:

3. En el cuadro de diálogo Valores duplicados, selecciona Duplicado en la primera lista desplegable, elige un escenario de resaltado en la segunda lista y haz clic en OK.

Notas: Si los escenarios de resaltado predefinidos no se ajustan a tus necesidades, puedes seleccionar Formato personalizado en la segunda lista desplegable y luego elegir el color de resaltado, fuente y bordes de celda en el cuadro de diálogo Formato de celdas.

Verás que todas las celdas duplicadas se resaltan en la columna seleccionada, como se muestra en la imagen.

Notas:
(1) Tras resaltar las celdas duplicadas, puedes filtrarlas fácilmente. (Haz clic para ver cómo)
(2) Tras resaltar las celdas duplicadas, también puedes eliminarlas en bloque fácilmente. (Haz clic para ver cómo)

Algunos usuarios prefieren resaltar filas basadas en celdas duplicadas en una columna específica. En este caso, podemos crear una regla personalizada de formato condicional para lograrlo.

1. Selecciona el rango (excluyendo la fila de encabezado) donde vas a resaltar filas basadas en celdas duplicadas.

2. Haz clic en Inicio > Formato condicional > Nueva regla.

3En el cuadro de diálogo Nueva regla de formato,
(1) Haz clic para seleccionar Usar una fórmula para determinar qué celdas formatear opción;
(2) Escribe la fórmula =COUNTIF($C$3:$C$12,$C3)>1 en el Cuadro Formatear valores donde esta fórmula sea verdadera ;
Consejos: En la fórmula, $C$3:$C$12 es la columna que contiene las celdas duplicadas, y $C3 es la primera celda de la columna.
(3) Haz clic en el Formato botón.

4. En el cuadro de diálogo Formato de celdas, especifica el color de relleno, fuente y bordes de celda según necesites, y haz clic en OK para guardar los ajustes.

Ahora, en el rango seleccionado, las filas se resaltan según las celdas duplicadas en la columna especificada. Consulta la imagen:

Para resaltar filas duplicadas en un rango determinado, también puedes usar la función Formato condicional.

1. Selecciona el rango excepto la fila de encabezado.

2. Haz clic en Inicio > Formato condicional > Nueva regla.

3En el cuadro de diálogo Nueva regla de formato:
(1) Haz clic para seleccionar la Usar una fórmula para determinar qué celdas formatear opción;
(2) En el Cuadro Formatear valores donde esta fórmula sea verdadera , escribe la fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Haz clic en el Formato botón.

Notas: En la fórmula =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 es la primera columna del rango, y $B3 es la primera celda de esta columna;
(2) $C$3:$C$12 es la segunda columna del rango, y $C3 es la primera celda de la columna;
(3) $D$3:$D$12 es la tercera columna del rango, y $D3 es la primera celda de la columna;
(4) Si hay más columnas en tu rango, puedes añadir las referencias de columna y sus primeras celdas sucesivamente en la fórmula.

4. En el cuadro de diálogo Formato de celdas, especifica el color de resaltado, fuente, bordes de celda, etc., y haz clic en OK para guardar la configuración.

Ahora, las filas duplicadas están identificadas y resaltadas en el rango seleccionado. Consulta la imagen:

Habrás notado que todos los duplicados se identifican o resaltan con los métodos anteriores. Sin embargo, a veces solo quieres ver los elementos duplicados excepto la primera aparición. También puedes hacerlo con la función Formato condicional y una fórmula diferente.

1Selecciona la columna con encabezado.
Consejos: Si necesitas resaltar filas basadas en duplicados en una columna excepto la primera aparición, selecciona el rango sin la fila de encabezado.

2. Haz clic en Inicio > Formato condicional > Nueva regla.

3En el cuadro de diálogo Nueva regla de formato:
(1) Haz clic para resaltar la Usar una fórmula para determinar qué celdas formatear opción;
(2) En el Cuadro Formatear valores donde esta fórmula sea verdadera , escribe la fórmula =COUNTIF($C$3:$C3, C3)>1;
Consejos: Para resaltar filas basadas en duplicados en una columna, escribe la fórmula =COUNTIF($C$3:$C3, $C3)>1.
(3) Haz clic en el Formato botón.

4. En el cuadro de diálogo Formato de celdas, especifica el color de resaltado, fuente, bordes de celda, etc., y haz clic en OK para guardar los ajustes.

Verás que las celdas duplicadas excepto la primera aparición en la columna seleccionada (o filas basadas en duplicados en la columna especificada) se resaltan. Consulta la imagen:

Cuando resaltamos celdas o filas duplicadas con la función Formato condicional, todos los duplicados se marcan con el mismo color. Sin embargo, si diferentes series de valores duplicados se resaltan con colores distintos, será más fácil distinguirlos. Aquí, un VBA puede ayudarte a lograrlo en Excel.

1. Pulsa Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. En la ventana, haz clic en Insertar > Módulo y pega el siguiente código en el nuevo módulo.

VBA: Resaltar celdas duplicadas con diferentes colores:

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. Pulsa F5 o haz clic en el icono Ejecutar para ejecutar este VBA.

4. En el cuadro de diálogo de Kutools para Excel, selecciona la columna donde vas a resaltar celdas duplicadas con diferentes colores y haz clic en OK.

Ahora, cada serie de valores duplicados se resaltará con un color distinto.

La primera herramienta que presento es la función Seleccionar celdas duplicadas y únicas, proporcionada por Kutools para Excel. Esta función permite encontrar rápidamente celdas únicas o duplicadas.

1. Selecciona la columna o rango donde deseas buscar y resaltar las celdas duplicadas.

2. Haz clic en Kutools > Seleccionar > Seleccionar celdas duplicadas y únicas.

3En el cuadro de diálogo Seleccionar celdas duplicadas y únicas, marca las opciones que necesites y haz clic en el Ok botón para finalizar la operación.

Notas: En el cuadro de diálogo Seleccionar celdas duplicadas y únicas,
(1) Si necesitas seleccionar o resaltar todos los duplicados excepto la primera aparición, marca la opción Valores Duplicados (Excluir la primera coincidencia) ; de lo contrario, marca la opción Valores Duplicados (Incluir la primera coincidencia) según corresponda.
(2) Si necesitas resaltar los duplicados, marca la opción Color de fondo de relleno y especifica el color de resaltado que desees.
(3) Si deseas seleccionar o resaltar filas basadas en duplicados en la columna seleccionada, marca la opción Seleccionar fila completa según corresponda.
(4) Si deseas seleccionar o resaltar valores duplicados distinguiendo mayúsculas y minúsculas, marca la opción Distinguir mayúsculas y minúsculas según corresponda.

Kutools para Excel también ofrece una herramienta excelente – Comparar Celdas, para ayudarnos a encontrar y resaltar fácilmente celdas duplicadas en dos columnas.

1. Haz clic en Kutools > Comparar Celdas para abrir el cuadro de diálogo Seleccionar celdas iguales y diferentes.

2. En el cuadro de diálogo Seleccionar celdas iguales y diferentes, especifica las dos columnas en los cuadros Buscar valores en y Según, marca la opción Valor igual y selecciona otras opciones según necesites.

Notas:
(1) Si necesitas buscar filas duplicadas, marca Por fila ; y para buscar celdas duplicadas, marca la opción Por celda en la sección Método ;
(2) Marca la opción Color de fondo de relleno y especifica un color de resaltado si necesitas resaltar las filas o celdas duplicadas;
(3) Marca la opción Seleccionar fila completa si necesitas seleccionar o resaltar la fila completa basada en duplicados;
(4) Marca la opción Distinguir mayúsculas y minúsculas si deseas buscar o resaltar duplicados distinguiendo mayúsculas y minúsculas.

3. Haz clic en Ok para finalizar la configuración.

Verás que los duplicados en la columna que especificaste en el cuadro Buscar valores en se identifican y resaltan.

Este método te guía para identificar y resaltar celdas duplicadas mediante una regla de formato condicional, y luego filtrar fácilmente por color de resaltado en Excel.

1. Aplica formato condicional para buscar y resaltar duplicados en la columna especificada. (Haz clic para ver cómo)

2Haz clic para seleccionar el encabezado de la columna especificada y haz clic en Datos > Filtro.

3A continuación, haz clic en el icono de filtro en el encabezado de la columna y selecciona Filtrar por colory luego selecciona el color de formato condicional en la lista desplegable. Consulta la imagen:

Verás que solo se filtran las filas con celdas duplicadas. Consulta la imagen:

Como alternativa, también podemos identificar duplicados con una fórmula en una columna auxiliar y luego filtrar los duplicados fácilmente usando esa columna en Excel.

1. Junto a los datos originales, añade una columna auxiliar y escribe Duplicado como encabezado de columna.

2. Selecciona la primera celda en blanco bajo el encabezado, introduce la fórmula =IF(COUNTIF($C$3:$C$12,C3)>1,"Duplicado","") y arrastra el controlador de AutoRelleno para copiar la fórmula al resto de celdas.

Notas: En la fórmula anterior, $C$3:$C$12 es la columna que contiene los datos duplicados y C3 es la primera celda (excepto el encabezado) de la columna.

3. Haz clic para seleccionar el encabezado de la columna – Duplicados, y haz clic en Datos > Filtro.

4A continuación, haz clic en el icono de filtro en el encabezado de la columna y marca solo Duplicadoy haz clic en el OK botón. Consulta la imagen:

Ahora, solo se filtran las filas con valores duplicados. Consulta la imagen:

En general, podemos usar =COUNTIF(rango, criterio) para contar cuántas veces aparece un valor específico en el rango indicado. Por ejemplo, para contar cuántas veces aparece “Manzana” en la lista A2:A10, aplicamos la fórmula =COUNTIF(A2:A10, "Manzana").

Sin embargo, la fórmula =COUNTIF(rango, criterio) solo cuenta el valor duplicado especificado. ¿Cómo contar valores duplicados con dos o más criterios? ¿Y si queremos contar duplicados distinguiendo mayúsculas y minúsculas con criterios? Los siguientes métodos te ayudarán a resolver estos casos.

Podemos usar una fórmula de matriz para contar valores duplicados distinguiendo mayúsculas y minúsculas con criterios en Excel. Por ejemplo, para contar cuántas veces aparece el valor "Manzana" en la lista B2:B21 respetando mayúsculas y minúsculas, haz lo siguiente:

1. Selecciona una celda en blanco.

2. Introduce la fórmula =SUM(--EXACT(B2:B20,E2)).

3. Pulsa Ctrl + Shift + Enter para obtener el resultado.

Notas: En la fórmula de matriz,
(1) B2:B20 es la columna donde contarás los duplicados. (2) E2 es la celda que contiene el valor específico que deseas contar. Puedes cambiar la referencia de celda por el valor entre comillas, por ejemplo, "Manzana".

A veces, puede que quieras contar duplicados con dos o más criterios. Puedes usar la función COUNTIFS para lograrlo.
Por ejemplo, tienes una tabla de ventas de frutas como se muestra en la imagen. Aquí necesitamos contar cuántas veces se vendió manzana el5/7/2020 y el importe es mayor que300. Puedes contar los duplicados con estos criterios así:

1. Selecciona una celda en blanco.

2. Introduce la fórmula =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Pulsa Enter para obtener el resultado.

Notas: En la fórmula anterior,
(1) B3:B20 es la columna de fechas (primera columna), y G4 es el criterio de fecha;
(2) C3:C20 es la columna de frutas (segunda columna), y G3 es el criterio de fruta;
(3) D3:D20 es la columna de importe (tercera columna), y ">300" es el criterio de importe.
(4) Si hay más columnas y criterios en tu tabla, puedes añadir la referencia de columna y el criterio.

Para contar todos los duplicados en una columna excepto la primera aparición, sigue estos pasos:

1. Selecciona una celda en blanco junto a la columna.

2Introduce la fórmula =IF(COUNTIF($B$3:B3,B3)>1,"SÍ","")y arrastra el controlador de AutoRelleno hacia abajo para copiar la fórmula al resto de celdas.

Notas: En la fórmula anterior,
(1) $B$3:B3 es el rango donde cuentas los duplicados. En $B$3:B3, B3 cambiará automáticamente al copiar la fórmula.
(2) B3 es la primera celda de la columna especificada.
(3) Esta fórmula devolverá SÍ o vacío. SÍ indica que el valor correspondiente es duplicado, mientras que vacío significa único.

Ahora, todos los duplicados en la columna especificada están identificados. Podemos contar los resultados de la fórmula para obtener el número total de duplicados.

3. Selecciona una celda en blanco.

4Introduce la fórmula =COUNTIF(C3:C16,"SÍ")y pulsa la tecla Enter para obtener el resultado.

Notas: En la fórmula anterior,
(1) C3:C16 es el rango donde aplicamos la fórmula para identificar duplicados en el paso anterior.
(2) SÍ es el valor devuelto por la fórmula anterior.

Así obtenemos el número total de valores duplicados en la columna especificada. El total no incluye la primera aparición.

Para contar el número de todos los duplicados incluyendo la primera aparición en Excel, puedes usar una fórmula de matriz.

1. Selecciona una celda en blanco.

2. Introduce la fórmula =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. Pulsa Ctrl + Shift + Enter para obtener el resultado.

Notas: En la fórmula anterior, B3:B16 es la columna donde queremos contar duplicados incluyendo la primera aparición.

Para simplificar tu trabajo y evitar memorizar fórmulas largas, puedes usar la función Seleccionar celdas duplicadas y únicas, proporcionada por Kutools para Excel, para contar rápidamente el número de valores duplicados en la lista o columna especificada, excluyendo o incluyendo la primera aparición.

1. Selecciona la columna donde contarás los valores duplicados y haz clic en Kutools > Seleccionar > Seleccionar celdas duplicadas y únicas.

2. En el cuadro de diálogo Seleccionar celdas duplicadas y únicas, marca la opción Valores Duplicados (Excluir la primera coincidencia) o Valores Duplicados (Incluir la primera coincidencia) según necesites y haz clic en Ok.

3. Todos los valores duplicados, incluyendo o excluyendo la primera aparición, se seleccionan y, al mismo tiempo, aparece un cuadro de diálogo mostrando cuántas celdas se han seleccionado. Consulta la imagen anterior.

Supón que quieres comparar dos listas de nombres y contar cuántos duplicados hay entre ellas. ¿Cómo hacerlo rápidamente? También podemos lograrlo con una fórmula en Excel.

1. Selecciona una celda en blanco.

2. Introduce la fórmula =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3. Pulsa Enter .

Notas: En la fórmula anterior,
(1) B3:B12 es la primera columna de nombres donde contarás duplicados.
(2) D3:D18 es la segunda columna de nombres con la que compararás los duplicados.

Como alternativa, podemos usar el complemento Kutools para Excel para contar rápidamente el número total de celdas duplicadas entre dos columnas.

1. Haz clic en Kutools > Seleccionar > Seleccionar celdas iguales y diferentes.

2En el cuadro de diálogo Seleccionar celdas iguales y diferentes,
(1) Especifica las dos columnas en el cuadro Buscar valores en y Según por separado.
(2) Marca la opción Por celda según corresponda.
(3) Marca la opción Valor igual según corresponda.

4. Haz clic en Ok.

Ahora, todas las celdas duplicadas en la primera columna se seleccionan y, al mismo tiempo, aparece un cuadro de diálogo mostrando cuántas celdas duplicadas se han seleccionado. Consulta la imagen:

Notas: Esta función contará el número total de valores duplicados en la columna que especificaste en el cuadro Buscar valores en del cuadro de diálogo Seleccionar celdas iguales y diferentes. Si necesitas contar el número total de duplicados en la segunda columna, aplica la función Seleccionar celdas iguales y diferentes de nuevo especificando la segunda columna en el cuadro Buscar valores en.

Puedes contar rápidamente cada valor duplicado solo una vez con la siguiente fórmula:

1. Selecciona una celda en blanco.

2. Introduce la fórmula =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&""))

3. Pulsa Enter para obtener el resultado.

Notas: En la fórmula anterior, C3:C19 es la columna donde quieres contar cada valor duplicado una vez.

Al contar una lista, puedes contar cada valor duplicado solo una vez respetando mayúsculas y minúsculas usando una fórmula de matriz en Excel.

1. Selecciona una celda en blanco.

2. Introduce la fórmula de matriz =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19)), ""), MATCH(ROW(C3:C19), ROW(C3:C19))),0),0)).

3. Pulsa Ctrl + Shift + Enter para obtener el resultado.

Notas: En la fórmula de matriz anterior, C3:C19 es la columna donde contarás cada serie de duplicados una vez respetando mayúsculas y minúsculas.

Si tienes instalado Kutools para Excel, también puedes usar su función Contar celdas únicas para contar rápidamente cada serie de valores duplicados una vez en Excel.

1. Selecciona una celda en blanco.

2. Haz clic en Kutools > Asistente de fórmulas > Contar > Extraer celdas únicas en un rango (incluyendo el primer duplicado).

3. En el cuadro de diálogo Asistente de fórmulas, especifica la columna donde contarás duplicados una vez en el cuadro Rango y haz clic en Ok.

El resultado se mostrará inmediatamente en la celda seleccionada.

Podemos usar la función Subtotal para contar cada serie de valores duplicados en una columna en Excel.

1Selecciona la columna donde contarás cada serie de valores duplicados y haz clic en Datos > Ordenar de A a Z o Ordenar de Z a A.

2. En el cuadro de advertencia de ordenación, marca la opción Expandir la selección y haz clic en Ordenar.

Verás que la selección se ha ordenado por los valores duplicados de la columna especificada.

3Mantén la selección y haz clic en Datos > Subtotal.

4En el cuadro de diálogo Subtotal,
(1) Selecciona la columna especificada en la lista desplegable En cada cambio en ;
(2) Selecciona Contar en la lista desplegable Usar función ;
(3) Marca solo la columna especificada en la lista Agregar subtotal a ;
(4) Haz clic en el OK botón.

Verás que cada serie de valores duplicados se cuenta y el resultado aparece debajo de cada serie, como se muestra en la imagen.

También podemos crear una tabla dinámica para contar rápidamente cada serie de valores duplicados en Excel.

1. Selecciona el rango que contiene la columna especificada y haz clic en Insertar > Tabla Dinámica.

2. En el cuadro de diálogo Crear tabla dinámica, especifica dónde ubicar la nueva tabla dinámica y haz clic en OK.

3. En el panel de campos de la tabla dinámica, arrastra la columna especificada a las secciones Filas y Valores . Así verás que cada serie de valores duplicados en la columna se cuenta en bloque. Consulta la imagen:

Si ya tienes instalado Kutools para Excel, puedes usar su función avanzada Combinar filas para contar rápidamente cada serie de valores duplicados en la columna especificada.

Notas: La función Combinar filas avanzado modificará el rango seleccionado y eliminará filas basadas en valores duplicados en la columna clave especificada. Para proteger tus datos, se recomienda hacer una copia de seguridad antes de realizar estas operaciones.

1. Añade una columna en blanco a la derecha del rango de datos original y nómbrala como Contar.

2. Selecciona el rango de datos original y la nueva columna juntos, y haz clic en Kutools > Combinar y dividir > Combinar filas avanzado.

3En el cuadro de diálogo Combinar filas avanzado,
(1) Haz clic para seleccionar la columna especificada que contarás y haz clic en Columna clave.
(2) Haz clic para seleccionar la nueva columna (Contar) y luego haz clic en Calcular > Contar.
(3) Especifica el tipo de combinación o cálculo para otras columnas si es necesario.
(4) Haz clic en el Ok botón.

Verás que cada serie de valores duplicados en la columna especificada se cuenta en bloque. Consulta la imagen:

Supón que tienes una lista de frutas en una columna. Algunas frutas aparecen varias veces. Ahora necesitas marcar cada fruta duplicada según el orden en que aparece. ¿Cómo hacerlo? Aquí te mostramos una fórmula para lograrlo fácilmente en Excel.

1. Añade una columna en blanco a la derecha de los datos originales.

2. Introduce la fórmula =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") en la primera celda de la columna añadida.

3. Arrastra el controlador de AutoRelleno de esta celda para copiar la fórmula al resto de celdas.

Notas: En la fórmula anterior,
(1) $C$3:$C$14 es la columna donde quieres contar los valores duplicados en orden.
(2) C3 es la primera celda de la columna especificada.
(3) Si el valor correspondiente es duplicado, esta fórmula devolverá el número de secuencia1,2,3… según el orden de aparición; si es único, devolverá vacío.

Puedes usar la función Eliminar duplicados para eliminar todos los valores duplicados excepto la primera aparición directamente.

1. Selecciona la columna donde deseas eliminar todos los valores duplicados excepto la primera aparición.

2. Haz clic en Datos > Eliminar duplicados.

3. En el cuadro de advertencia de Eliminar duplicados, marca la opción Continuar con la selección actual y haz clic en el botón Eliminar duplicados.

Consejos: Para eliminar filas basadas en los valores duplicados de la selección, marca la opción Expandir la selección .

4. En el cuadro de diálogo Eliminar duplicados, marca solo la columna especificada y haz clic en OK.

Consejos: Si marcaste la opción Expandir la selección en el paso anterior, aquí aparecerán todas las columnas. Aun así, debes marcar solo la columna especificada.

5. Aparecerá un cuadro de diálogo mostrando cuántos valores duplicados se han eliminado. Haz clic en OK para cerrarlo.

También puedes usar la función Filtro avanzado para eliminar todos los valores duplicados de la columna especificada fácilmente.

1. Haz clic en Datos > Avanzado.

2En el cuadro de diálogo Filtro avanzado,
(1) Marca la opción Copiar a otro lugar ;
(2) En el Rango de lista , selecciona la columna especificada de la que eliminarás duplicados;
(3) En el Copiar a , especifica el rango donde pegarás la columna;
(4) Marca la opción Solo registros únicos ;
(5) Haz clic en el OK botón.

Verás que la columna especificada se pega en el rango indicado con todos los duplicados eliminados excepto la primera aparición. Consulta la imagen:

También puedes usar un VBA para eliminar rápidamente los valores duplicados excepto la primera aparición de una columna en Excel.

1. Pulsa Alt + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haz clic en Insertar > Módulo y pega el siguiente código VBA en el nuevo módulo.

VBA: Eliminar valores duplicados excepto la primera aparición

Sub ExtendOffice_RemoveAllDeplicate()
Dim xRg As Range
Dim xURg, xFRg, xFFRg As Range
Dim xI, xFNum, xFFNum As Integer
Dim xDc As Object
Dim xDc_keys
Dim xBol As Boolean
Dim xStr As String
Dim xWs As Worksheet
Dim xURgAddress As String
On Error Resume Next
Set xRg = Application.InputBox("Select range:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Set xWs = xURg.Worksheet
Set xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Then
If xFRg.Value <> "" And (Not IsError(xFRg)) Then
For xFFNum = xFNum + 1 To xURg.Count
Set xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Then
If xFFRg.Value = xFRg.Value Then
xDc(xFFRg.Address) = ""
End If
End If
Next
End If
End If
Next
xStr = ""
xDc_keys = xDc.Keys

For xI = 1 To UBound(xDc_keys)
If xStr = "" Then
xStr = xDc_keys(xI)
Set xURg = xWs.Range(xStr)
Else
xStr = xStr & "," & xDc_keys(xI)
Set xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
End If
Next
Debug.Print xStr
xWs.Activate
xURg.Select
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3. Pulsa F5 o haz clic en el botón Ejecutar para ejecutar este VBA.

4. En el cuadro de diálogo emergente, especifica el rango del que eliminarás duplicados y haz clic en OK.

Todos los valores duplicados, excepto la primera aparición, se eliminan inmediatamente del rango especificado.

Nota: Este código VBA distingue mayúsculas y minúsculas.

Podemos resaltar todos los valores duplicados, incluyendo la primera aparición, en una columna o lista con una regla de formato condicional y luego filtrar todos los duplicados por color de resaltado. Después, seleccionamos todas las celdas filtradas y las eliminamos en bloque.

1. Aplica formato condicional para resaltar valores duplicados. (Haz clic para ver cómo)

2. Selecciona la columna de la que eliminarás duplicados (incluyendo la primera aparición) y haz clic en Datos > Filtro.

3Haz clic en el icono de filtro en el encabezado de la columna especificada. En el menú desplegable, selecciona Filtrar por colory luego especifica el color de resaltado en el submenú.

Ahora, todos los valores duplicados están filtrados.

4. Selecciona todas las celdas filtradas, haz clic derecho y selecciona Eliminar fila en el menú contextual. En el cuadro de diálogo de confirmación, haz clic en OK para continuar.

5. Todos los valores duplicados se eliminan en bloque. Mantén la lista filtrada seleccionada y haz clic en Filtro > Datos de nuevo para quitar el filtro.

Ahora verás que todas las celdas duplicadas, incluyendo la primera aparición, se han eliminado en bloque y solo quedan los valores únicos.

Notas: Este método eliminará filas basadas en los valores duplicados de la columna especificada.

También podemos usar una fórmula para identificar valores duplicados, incluyendo la primera aparición, en una columna auxiliar, luego filtrar los duplicados por el resultado de la fórmula y finalmente eliminar estos valores filtrados en bloque.

1. Añade una columna auxiliar junto a la columna especificada, introduce la fórmula =COUNTIF($B$3:$B$11,B3) en la primera celda de la columna auxiliar y arrastra el controlador de AutoRelleno hacia abajo. Consulta la imagen:

Nota: En la fórmula anterior, $B$3:$B$11 es la columna de la que eliminarás duplicados y B3 es la primera celda de la columna.

2. Selecciona la columna auxiliar y haz clic en Datos > Filtro.

3Haz clic en el icono de filtro en el encabezado de la columna auxiliar y en el menú desplegable marca todos los valores excepto 1y haz clic en el OK botón. Consulta la imagen:

4. Ahora, todos los valores duplicados están filtrados. Selecciona las celdas filtradas en la columna auxiliar, haz clic derecho y selecciona Eliminar fila en el menú contextual.

5. En el cuadro de diálogo de confirmación, haz clic en OK para continuar.

6. Ahora, todos los valores duplicados y sus filas se eliminan en bloque. Haz clic en Datos > Filtro de nuevo para quitar el filtro.

Verás que todos los valores duplicados, incluyendo la primera aparición, se han eliminado en bloque.

Si tienes instalado Kutools para Excel, también puedes usar su función Seleccionar celdas duplicadas y únicas para seleccionar y eliminar rápidamente los valores duplicados, incluyendo o excluyendo la primera aparición, en Excel.

1. Selecciona la columna de la que eliminarás duplicados.

2. Haz clic en Kutools > Seleccionar > Seleccionar celdas duplicadas y únicas.

3. En el cuadro de diálogo Seleccionar celdas duplicadas y únicas, marca la opción Valores Duplicados (Incluir la primera coincidencia) y haz clic en Ok.

Notas:
(1) Para seleccionar y eliminar duplicados excluyendo la primera aparición, marca la opción Valores Duplicados (Excluir la primera coincidencia) ;
(2) Para seleccionar y eliminar filas basadas en duplicados en la columna especificada, marca la opción Seleccionar fila completa ;
(3) Para seleccionar y eliminar duplicados distinguiendo mayúsculas y minúsculas, marca la opción Distinguir mayúsculas y minúsculas ;
(4) Para seleccionar, resaltar y eliminar celdas o filas duplicadas, marca las opciones Color de fondo de relleno o Color de fuente de relleno y especifica los colores de relleno o fuente según necesites.

4. Aparecerá un cuadro de diálogo mostrando cuántas celdas se han seleccionado, haz clic en OK para cerrarlo.

5. Haz clic derecho en las celdas seleccionadas y selecciona Eliminar en el menú contextual.

6. En el cuadro de diálogo Eliminar, marca la opción Desplazar celdas hacia arriba y haz clic en OK.

Ahora, todos los valores duplicados, incluyendo la primera aparición, se han eliminado en bloque.

En la mayoría de los casos, identificamos valores duplicados en una columna y luego eliminamos las filas completas por esos duplicados. Esta operación es muy similar a eliminar duplicados de una sola columna. Por tanto, podemos usar soluciones similares para eliminar filas basadas en duplicados en la columna especificada.

El primer método es usar la función integrada Eliminar duplicados para eliminar filas por duplicados en la columna especificada. Solo selecciona el rango que eliminarás, haz clic en Datos > Eliminar duplicados para activar la función, marca solo la columna especificada en el cuadro de diálogo Eliminar duplicados y haz clic en OK para finalizar la operación.

También podemos usar las funciones Formato condicional y Filtro para eliminar filas basadas en duplicados en la columna especificada. Primero, resalta las filas basadas en duplicados en una columna mediante una regla de formato condicional (haz clic para ver cómo). Luego, filtra el rango por color. Después, elimina todas las filas filtradas fácilmente. Por último, borra o quita el filtro y verás que solo quedan las filas con valores únicos en la columna especificada.

Como alternativa, puedes añadir una columna auxiliar y usar la fórmula =COUNTIF($C$3:$C$21,C3) para identificar duplicados en la columna especificada. Luego, filtra los números mayores que1 en la columna auxiliar y elimina todas las filas filtradas fácilmente. Tras quitar el filtro, verás que solo quedan las filas con valores únicos en la columna especificada.

El complemento de terceros Kutools para Excel también ofrece una función muy útil, Seleccionar celdas duplicadas y únicas, para seleccionar rápidamente filas basadas en duplicados en la columna especificada y luego eliminarlas fácilmente desde el menú contextual.

La función Combinar filas avanzado de Kutools para Excel también puede eliminar rápidamente filas basadas en duplicados en la columna clave especificada.

Podemos añadir una columna auxiliar y usar una fórmula para identificar los valores duplicados entre dos columnas, y luego filtrar y eliminar fácilmente los duplicados.

1. Añade una columna en blanco junto a la columna especificada de la que eliminarás duplicados.

2. En la primera celda de la columna auxiliar (excepto el encabezado), escribe la fórmula =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Único","Duplicado") y arrastra el controlador de AutoRelleno hacia abajo.

Notas: En la fórmula anterior,
(1) C2 es la primera celda de la columna especificada de la que eliminarás duplicados;
(2) $A$2:$A$13 es la otra columna con la que compararás.
(3) Esta fórmula devolverá Duplicado si el valor correspondiente es duplicado respecto a la otra columna, y devolverá Único si es diferente respecto a la otra columna.

3. Selecciona la columna auxiliar y haz clic en Datos > Filtro.

4Haz clic en el icono de filtro en el encabezado de la columna auxiliar y en el menú desplegable marca solo Duplicadoy haz clic en el OK botón.

5. Ahora, todos los valores duplicados están filtrados. Selecciona las celdas filtradas, haz clic derecho y selecciona Eliminar fila en el menú contextual. Luego haz clic en OK en el cuadro de diálogo de confirmación.

6. Todos los valores duplicados se eliminan de la columna especificada. Haz clic en Datos > Filtro de nuevo para quitar el filtro.

Verás que solo quedan valores únicos en la columna especificada. Puedes eliminar la columna auxiliar si lo deseas.

Notas: Este método eliminará las filas completas basadas en los valores duplicados de la columna especificada.

Si tienes instalado Kutools para Excel, puedes usar su excelente función Seleccionar celdas iguales y diferentes para seleccionar rápidamente los valores duplicados entre dos columnas y luego eliminarlos fácilmente.

1. Haz clic en Kutools > Seleccionar > Seleccionar celdas iguales y diferentes para activar esta función.

2. En el cuadro de diálogo Seleccionar celdas iguales y diferentes, especifica ambas columnas en los cuadros Buscar valores en y Según por separado, marca las opciones Por celda y Valor igual y haz clic en Ok. Consulta la imagen:

3. Todos los valores duplicados entre las dos columnas se seleccionan en la primera columna (la que especificaste en el cuadro Buscar valor en). Haz clic en OK en el cuadro de diálogo emergente.

4. Puedes pulsar la tecla Supr para eliminar estos valores duplicados directamente o hacer clic derecho y seleccionar Eliminar en el menú contextual.