Come evidenziare celle e righe duplicate in Excel

In questo tutorial, imparerete come mostrare i duplicati in Excel. Daremo un’occhiata da vicino a diversi metodi per ombreggiare celle duplicate, intere righe o duplicati consecutivi utilizzando la formattazione condizionale. Inoltre, vedrete come evidenziare i duplicati con colori diversi utilizzando uno strumento specializzato.

La scorsa settimana, abbiamo esplorato diversi modi per identificare i duplicati in Excel. Indubbiamente, le formule di duplicazione sono molto utili, ma evidenziare le voci duplicate con un colore definito potrebbe rendere l’analisi dei dati ancora più facile.

Il modo più veloce per trovare ed evidenziare i duplicati in Excel è usare la formattazione condizionale. Il più grande vantaggio di questo metodo è che non solo vi mostra i duplicati esistenti, ma rileva e colora i nuovi duplicati quando inserite, modificate o sovrascrivete i vostri dati.

Più avanti in questo tutorial, troverete una serie di modi per evidenziare i record duplicati a seconda del vostro compito specifico. Queste tecniche funzionano in tutte le versioni di Excel 2016, Excel 2013, Excel 2010 e inferiori.

• Evidenziazione dei duplicati in Excel con le prime occorrenze (regolain regola)
• Come evidenziare i duplicati in Excel eccetto le 1° occorrenze
• Come evidenziare i duplicati in un intervallo (colonne multiple)
• Come evidenziare l’ennesimo e successivi record
• Ombra intere righe in base ai valori duplicati in una colonna
• Come evidenziare righe duplicate in Excel
• Come visualizzare celle duplicate consecutive
• Duplicate Remover – il modo più veloce per evidenziare i duplicati in Excel

Come evidenziare i duplicati in Excel usando la regola integrata (con le prime occorrenze)

Per cominciare, in tutte le versioni di Excel, c’è una regola predefinita per evidenziare le celle duplicate. Per utilizzare questa regola nei tuoi fogli di lavoro, esegui i seguenti passi:

1. Seleziona i dati che vuoi controllare per i duplicati. Questo può essere una colonna, una riga o un intervallo di celle.
2. Nella scheda Home, nel gruppo Stili, fai clic su Formattazione condizionale > Regole per evidenziare le celle > Valori duplicati…
   
3. La finestra di dialogo Valori duplicati si apre con il formato Riempimento rosso chiaro e Testo rosso scuro selezionato per default. Per applicare il formato predefinito, basta cliccare su OK.

Oltre al riempimento rosso e alla formattazione del testo, una manciata di altri formati predefiniti sono disponibili nell’elenco a discesa. Per evidenziare i duplicati usando qualche altro colore, clicca su Formato personalizzato… (l’ultima voce nel menù a tendina) e seleziona il colore di riempimento e/o il colore del carattere di tuo gradimento.

Utilizzando la regola integrata, puoi evidenziare i duplicati in una colonna o in più colonne come mostrato nella seguente schermata:

Quando usate la regola incorporata di Excel per evidenziare i valori duplicati, tenete a mente le seguenti due cose:

• Funziona solo per celle individuali. Per evidenziare le righe duplicate, dovrai creare le tue regole basate sui valori di una colonna specifica o confrontando i valori di diverse colonne.
• Mostra le celle duplicate comprese le loro prime occorrenze. Per evidenziare tutti i duplicati eccetto le prime occorrenze, create una regola di formattazione condizionale basata su questa formula.

Come evidenziare i duplicati senza le prime occorrenze

Per evidenziare la seconda e tutte le successive occorrenze dei duplicati, seleziona le celle che vuoi colorare e crea una regola basata su una formula in questo modo:

1. Nella scheda Home, nel gruppo Stili, clicca su Formattazione condizionale > Nuova regola > Usa una formula per determinare quali celle formattare.
2. Nella casella Formatta valori dove questa formula è vera, inserisci una formula simile a questa:
   =COUNTIF($A:$A2,$A2)>1

   dove A2 è la cella più in alto dell’intervallo selezionato.

3. Fai clic sul pulsante Formato… e seleziona il colore del riempimento e/o del carattere che vuoi.
4. Finalmente, fai clic su OK per salvare e applicare la regola.

Se non hai molta esperienza con la formattazione condizionale di Excel, troverai i passi dettagliati per creare una regola basata su formula nel seguente tutorial: Formattazione condizionale di Excel basata sul valore di un’altra cella.

Come risultato, le celle duplicate escluse le prime istanze saranno evidenziate con il colore da voi scelto:

Come mostrare il 3°, 4° e tutti i record duplicati successivi

Per visualizzare i duplicati che iniziano con l’ennesima occorrenza, create una regola di formattazione condizionale basata sulla formula come nell’esempio precedente, con la sola differenza di sostituire >1 alla fine della formula con il numero richiesto. Per esempio:

Per evidenziare il 3° e tutti i successivi duplicati, create una regola di formattazione condizionale basata su questa formula:

=COUNTIF($A:$A2,$A2)>=3

Per evidenziare il 4° e tutti i successivi record duplicati, usate questa formula:

=COUNTIF($A:$A2,$A2)>=4

Per evidenziare solo determinate occorrenze, usate l’operatore uguale a (=). Per esempio, per evidenziare solo le seconde istanze, useresti questa formula:

=COUNTIF($A:$A2,$A2)=2

Come evidenziare i duplicati in un intervallo (colonne multiple)

Quando vuoi controllare i duplicati su più colonne, non confrontando le colonne tra loro, ma trovare tutte le istanze dello stesso elemento in tutte le colonne, usa una delle seguenti soluzioni.

Evidenzia i duplicati in più colonne incluse le prime occorrenze

Se la prima istanza di un elemento che appare nell’insieme di dati più di una volta è considerata un duplicato, il modo più semplice di procedere è usare la regola integrata di Excel per i duplicati.

Oppure, create una regola di formattazione condizionale con questa formula:

Ad esempio, per evidenziare i duplicati nell’intervallo A2:C8, la formula è la seguente:

=COUNTIF($A:$C, A2)>1

Si noti l’uso di riferimenti di cella assoluti per l’intervallo ($A$2:$C$8), e riferimenti relativi per la cella superiore (A2).

Evidenzia i duplicati in colonne multiple senza le prime occorrenze

La soluzione per questo scenario è molto più complicata, non c’è da stupirsi che Excel non abbia una regola integrata per questo:)

Per evidenziare le voci duplicate in più colonne ignorando le prime occorrenze, dovrai creare 2 regole con le seguenti formule:

Regola 1. Si applica alla prima colonna

Qui si usa esattamente la stessa formula che abbiamo usato per evidenziare i duplicati senza 1a ricorrenza in una colonna (i passi dettagliati si trovano qui).

In questo esempio, stiamo creando una regola per A2:A8 con questa formula:

=COUNTIF($A:$A2,$A2)>1

Come risultato, gli elementi duplicati senza 1a ricorrenza sono evidenziati nella colonna più a sinistra dell’intervallo (c’è solo un elemento di questo tipo nel nostro caso):

Regola 2. Si applica a tutte le colonne successive

Per evidenziare i duplicati nelle colonne rimanenti (B2:C8), usate questa formula:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

Nella formula precedente, la prima funzione COUNTIF conta le occorrenze di un dato elemento nella prima colonna, e la seconda COUNTIF fa lo stesso per tutte le colonne successive. Poi, si sommano questi numeri e si controlla se la somma è maggiore di 1.

Come risultato, tutti gli elementi duplicati escluse le loro prime occorrenze vengono trovati ed evidenziati:

Evidenziare i duplicati in tutte le colonne con una sola regola

Un’altra possibile soluzione è quella di aggiungere una colonna vuota a sinistra del vostro set di dati, e combinare le formule di cui sopra in una sola formula come questa:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

dove B2 è la cella superiore con dati nella seconda colonna dell’intervallo target.

Per capire meglio la formula, scomponiamola in 2 parti principali:

• Per la prima colonna (B), la condizione IF non è mai soddisfatta, quindi viene applicata solo la seconda funzione COUNTIF, che è la formula regolare che abbiamo già usato per trovare duplicati senza 1a occorrenza in una colonna.
• Per tutte le colonne successive (C2:D8), il punto chiave è l’uso intelligente dei riferimenti assoluti e relativi nelle due funzioni COUNTIF. Per rendere le cose più facili da capire, l’ho copiata nella colonna G, così potete vedere come cambia la formula quando viene applicata ad altre celle:
  

Perché la condizione IF è sempre VERA per tutte le colonne diverse dalla prima (il numero di colonne è maggiore di 1), la formula procede in questo modo:

• Conta il numero di occorrenze di un dato elemento (D5 nello screenshot sopra) in tutte le colonne a sinistra della colonna data: COUNTIF(B:$C,D5)
• Conta il numero di occorrenze di un dato elemento nella colonna dell’elemento, fino alla cella dell’elemento: COUNTIF(D:D5,D5)
• Infine, la formula aggiunge i risultati di entrambe le funzioni COUNTIF. Se il numero totale è maggiore di 1, cioè se c’è più di un’occorrenza dell’elemento, la regola viene applicata e l’elemento viene evidenziato.

Evidenziare intere righe sulla base di valori duplicati in una colonna

Se la vostra tabella contiene diverse colonne, potreste voler evidenziare intere righe sulla base di record duplicati in una colonna specifica.

Come già sapete, la regola integrata di Excel per i duplicati funziona solo a livello di cella. Ma una regola personalizzata basata su una formula non ha problemi a ombreggiare le righe. Il punto chiave è selezionare le righe intere, e poi creare una regola con una delle seguenti formule:

• Per evidenziare le righe duplicate escluse le prime occorrenze:
  =COUNTIF($A:$A2, $A2)>1
• Per evidenziare le righe duplicate incluse le prime occorrenze:
  =COUNTIF($A:$A, $A2)>1

dove A2 è la prima cella e A15 è l’ultima cella usata nella colonna che volete controllare per i duplicati. Come vedete, l’uso intelligente dei riferimenti di cella assoluti e relativi è ciò che fa la differenza.
La seguente schermata dimostra entrambe le regole in azione:

Come evidenziare le righe duplicate in Excel

L’esempio precedente ha dimostrato come colorare intere righe basate su valori duplicati in una colonna specifica. Ma cosa succede se vuoi visualizzare le righe che hanno valori identici in diverse colonne? Oppure, come evidenziare le righe duplicate assolute, quelle che hanno valori completamente uguali in tutte le colonne?

Per questo, utilizzate la funzione COUNTIFS che permette di confrontare le celle con più criteri. Per esempio, per evidenziare le righe duplicate che hanno valori identici nelle colonne A e B, usa una delle seguenti formule:

• Per evidenziare le righe duplicate tranne le prime occorrenze:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Per evidenziare le righe duplicate con le prime occorrenze:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

La seguente schermata dimostra il risultato:

Come puoi capire, l’esempio sopra è solo a scopo dimostrativo. Quando evidenziate le righe duplicate nei vostri fogli della vita reale, non siete naturalmente limitati a confrontare i valori solo in 2 colonne, la funzione COUNTIFS può elaborare fino a 127 coppie intervallo/criterio.

Evidenziazione di celle duplicate consecutive in Excel

A volte, potreste non aver bisogno di evidenziare tutti i duplicati in una colonna ma piuttosto mostrare solo le celle duplicate consecutive, cioè quelle che sono una accanto all’altra. Per fare questo, selezionate le celle con i dati (esclusa l’intestazione della colonna) e create una regola di formattazione condizionale con una delle seguenti formule:

• Per evidenziare i duplicati consecutivi senza la prima occorrenza:
  =$A1=$A2
• Per evidenziare i duplicati consecutivi con le prime occorrenze:
  =OR($A1=$A2, $A2=$A3)

La seguente schermata dimostra l’evidenziazione di testi consecutivi duplicati, ma queste regole funzionano anche per numeri e date consecutive duplicate:

Se il tuo foglio Excel può avere righe vuote e non vuoi che le celle vuote consecutive vengano evidenziate, apporta i seguenti miglioramenti alle formule:

• Per evidenziare celle consecutive duplicate senza 1a occorrenza e ignorare le celle vuote:
  =AND($A2<>"", $A1=$A2)
• Per evidenziare celle consecutive duplicate con 1a occorrenza e ignorare le celle vuote:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Come vedi, non è un grosso problema evidenziare i duplicati in Excel usando la formattazione condizionale. Tuttavia, c’è anche un modo più veloce e più facile. Per scoprirlo, leggete la prossima sezione di questo tutorial.

Come evidenziare i duplicati in Excel con Duplicate Remover

L’add-in Duplicate Remover è la soluzione all-in-one per gestire i record duplicati in Excel. Può trovare, evidenziare, selezionare, copiare o spostare celle duplicate o intere righe duplicate.

A dispetto del suo nome, l’add-in può evidenziare rapidamente i duplicati in diversi colori senza cancellarli.

The Duplicate Remover aggiunge 3 nuove funzioni alla tua barra multifunzione di Excel:

• Tabella Dedupe – per trovare ed evidenziare immediatamente i duplicati in una tabella.
• Duplicate Remover – procedura guidata passo dopo passo con opzioni avanzate per identificare ed evidenziare duplicati o valori unici in una tabella.
• Compare 2 Tables – trova ed evidenzia i duplicati confrontando due colonne o due tabelle separate.

Dopo aver installato Ultimate Suite for Excel, troverai questi strumenti nella scheda Ablebits Data nel gruppo Dedupe:

Evidenziare i duplicati in Excel in un paio di clic

Per questo esempio, ho creato la seguente tabella con alcune centinaia di righe. E il nostro scopo è quello di evidenziare le righe duplicate che hanno valori uguali in tutte e tre le colonne:

Credeteci o no, potete ottenere il risultato desiderato con solo 2 clic del mouse 🙂

1. Con qualsiasi cella della vostra tabella selezionata, cliccate sul pulsante Dedupe Table, e l’intelligente add-in prenderà tutta la tabella.
2. La finestra di dialogo Dedupe Table si aprirà con tutte le colonne selezionate automaticamente, e l’opzione Color duplicates scelta per default. Quindi, tutto quello che devi fare è cliccare su OK 🙂 Fatto!

Suggerimento. Se vuoi rilevare le righe duplicate da una o più colonne, deseleziona tutte le colonne irrilevanti e lascia selezionata solo la colonna o le colonne chiave.

E il risultato sarebbe simile a questo:

Come si vede nell’immagine qui sopra, lo strumento Tabella duplicati ha evidenziato le righe duplicate senza prime occorrenze.

Se volete evidenziare i duplicati comprese le prime occorrenze, o se volete colorare i record unici piuttosto che i duplicati, o se non vi piace il colore rosso predefinito, allora usate la procedura guidata Duplicate Remover che ha tutte queste caratteristiche e molto di più.

Evidenzia i duplicati in Excel usando una procedura guidata avanzata passo dopo passo

Paragonato allo strumento rapido Dedupe Table, la procedura guidata Duplicate Remover richiede qualche click in più, ma compensa con una serie di opzioni aggiuntive. Lascia che te lo mostri in azione:

1. Seleziona qualsiasi cella nella tua tabella dove vuoi evidenziare i duplicati e clicca sul pulsante Duplicate Remover sulla barra multifunzione. La procedura guidata verrà eseguita e l’intera tabella verrà selezionata. L’add-in suggerirà anche di creare una copia di backup della tua tabella, per sicurezza. Se non ne hai bisogno, deseleziona quella casella.

   Verifica che la tabella sia stata selezionata correttamente e clicca su Next.

   

2. Seleziona uno dei seguenti tipi di dati che vuoi trovare:
   • Duplicati eccetto 1a occorrenza
   • Duplicati con 1a occorrenza
   • Valori unici
   • Valori unici e 1a occorrenza duplicata

   Per questo esempio, troviamo Duplicati + 1a occorrenza:
   

3. Ora, selezionate le colonne per controllare i duplicati. Poiché vogliamo evidenziare le righe complete duplicate, ho selezionato tutte e 3 le colonne.
   

   Inoltre, l’add-in ti permette di specificare se la tua tabella ha intestazioni e se vuoi saltare le celle vuote. Entrambe le opzioni sono selezionate di default.

4. Infine, scegli l’azione da eseguire sui duplicati. Hai una serie di opzioni come selezionare, eliminare, copiare, spostare i duplicati o aggiungere una colonna di stato per identificare i duplicati.

Siccome oggi stiamo esplorando diversi modi per evidenziare i duplicati in Excel, la nostra scelta è ovvia 🙂 Quindi, seleziona Riempi con colore e scegli uno dei colori standard del tema, oppure clicca su Altri colori… e scegli qualsiasi colore RGB o HSL personalizzato.

Clicca il pulsante Fine e goditi il risultato 🙂

Ecco come evidenziare i duplicati in Excel utilizzando il nostro add-in Duplicate Remover. Se sei curioso di provare questo strumento sui tuoi fogli di lavoro, sei il benvenuto a scaricare una versione di prova completamente funzionale della Ultimate Suite che include tutti i nostri strumenti salva-tempo per Excel. E il tuo feedback nei commenti sarà molto apprezzato!

Se sei soddisfatto delle prestazioni di Duplicate Remover, siamo lieti di offrirti questa opportunità molto speciale:

Ti potrebbe interessare anche

• Come identificare i duplicati in Excel (in 1 tabella)
• Come confrontare 2 colonne o tabelle per trovare corrispondenze e differenze
• Come rimuovere righe duplicate in Excel