Formattazione condizionale in Excel: la guida definitiva con esempi

La formattazione condizionale è una delle funzionalità più semplici ma potenti dei fogli di calcolo Excel.

Come suggerisce il nome, puoi utilizzare la formattazione condizionale in Excel quando vuoi evidenziare le celle che soddisfano una condizione specificata.

Ti dà la possibilità di aggiungere rapidamente un livello di analisi visiva sul tuo set di dati. Puoi creare mappe di calore, mostrare icone in aumento/diminuzione, bolle di Harvey e molto altro ancora utilizzando la formattazione condizionale in Excel.

Utilizzo della formattazione condizionale in Excel (esempi)

In questo tutorial, ti mostrerò sette fantastici esempi di utilizzo della formattazione condizionale in Excel:

• Identifica rapidamente i duplicati utilizzando la formattazione condizionale in Excel.
• Evidenzia le celle con valore maggiore/minore di un numero in un set di dati.
• Evidenziare i 10 valori superiori/inferiori (o 10%) in un set di dati.
• Evidenziazione di errori/spazi vuoti utilizzando la formattazione condizionale in Excel.
• Creazione di mappe di calore utilizzando la formattazione condizionale in Excel.
• Evidenzia ogni ennesima riga/colonna utilizzando la formattazione condizionale.
• Cerca ed evidenzia utilizzando la formattazione condizionale in Excel.

1. Identifica rapidamente i duplicati

La formattazione condizionale in Excel può essere utilizzata per identificare i duplicati in un set di dati.

Ecco come puoi farlo:

• Seleziona il set di dati in cui desideri evidenziare i duplicati.
• Vai a Home -> Formattazione condizionale -> Evidenzia regole cella -> Valori duplicati.
• Nella finestra di dialogo Duplica valori, assicurati che Duplica sia selezionato nel menu a discesa a sinistra. È possibile specificare il formato da applicare utilizzando il menu a discesa a destra. Esistono alcuni formati esistenti che è possibile utilizzare o specificare il proprio formato utilizzando l'opzione Formato personalizzato.
• Fare clic su OK.

Ciò evidenzierebbe immediatamente tutte le celle che hanno un duplicato nel set di dati selezionato. Il tuo set di dati può trovarsi in una singola colonna, più colonne o in un intervallo di celle non contigue.

Guarda anche: La guida definitiva per trovare e rimuovere i duplicati in Excel.

2. Evidenzia celle con valore maggiore/minore di un numero

È possibile utilizzare la formattazione condizionale in Excel per evidenziare rapidamente le celle che contengono valori maggiori/minori di un valore specificato. Ad esempio, evidenziando tutte le celle con un valore di vendita inferiore a 100 milioni o evidenziando le celle con contrassegni inferiori alla soglia di superamento.

Ecco i passaggi per farlo:

• Seleziona l'intero set di dati.
• Vai a Home -> Formattazione condizionale -> Evidenzia regole cella -> Maggiore di… / Minore di…
• In base all'opzione selezionata (maggiore o minore di), si aprirà una finestra di dialogo. Diciamo che selezioni l'opzione "Maggiore di". Nella finestra di dialogo, inserisci il numero nel campo a sinistra. L'intento è quello di evidenziare le celle che hanno un numero maggiore di questo numero specificato.

• Specificare il formato da applicare alle celle che soddisfano la condizione utilizzando il menu a discesa a destra. Esistono alcuni formati esistenti che è possibile utilizzare o specificare il proprio formato utilizzando l'opzione Formato personalizzato.
• Fare clic su OK.

Ciò evidenzierebbe immediatamente tutte le celle con valori maggiori di 5 in un set di dati.Nota: se desideri evidenziare valori maggiori di uguale a 5, dovresti applicare nuovamente la formattazione condizionale con i criteri "Uguale a".

Lo stesso processo può essere seguito per evidenziare le celle con un valore inferiore a un valore specificato.

3. Evidenziare Top/Bottom 10 (o 10%)

La formattazione condizionale in Excel può identificare rapidamente i primi 10 elementi o il 10% superiore da un set di dati. Ciò potrebbe essere utile in situazioni in cui si desidera visualizzare rapidamente i migliori candidati in base ai punteggi o ai valori delle migliori offerte nei dati di vendita.

Allo stesso modo, puoi anche identificare rapidamente gli ultimi 10 elementi o il 10% più basso in un set di dati.

Ecco i passaggi per farlo:

• Seleziona l'intero set di dati.
• Vai a Home -> Formattazione condizionale -> Regole primi/sotto -> Primi 10 elementi (o %) / Ultimi 10 elementi (o %).
• In base a ciò che selezioni, si aprirà la finestra di dialogo. Supponiamo che tu abbia selezionato i primi 10 elementi, quindi si aprirà una finestra di dialogo come mostrato di seguito:
• Specificare il formato da applicare alle celle che soddisfano la condizione utilizzando il menu a discesa a destra. Esistono alcuni formati esistenti che è possibile utilizzare o specificare il proprio formato utilizzando l'opzione Formato personalizzato.
• Fare clic su OK.

Ciò evidenzierebbe immediatamente i primi 10 elementi nel set di dati selezionato. Nota che funziona solo per le celle che contengono un valore numerico.

Inoltre, se nel set di dati sono presenti meno di 10 celle e si selezionano le opzioni per evidenziare gli elementi 10 principali/elementi inferiori 10, tutte le celle verranno evidenziate.

Ecco alcuni esempi di come funzionerebbe la formattazione condizionale:

4. Evidenziazione di errori/spazi vuoti

Se lavori con molti dati numerici e calcoli in Excel, conoscerai l'importanza di identificare e trattare le celle che contengono errori o sono vuote. Se queste celle vengono utilizzate in ulteriori calcoli, potrebbe portare a risultati errati.

La formattazione condizionale in Excel può aiutarti a identificare ed evidenziare rapidamente le celle che contengono errori o sono vuote.

Supponiamo di avere un set di dati come mostrato di seguito:

Questo set di dati ha una cella vuota (A4) ed errori (A5 e A6).

Ecco i passaggi per evidenziare le celle vuote o che contengono errori:

• Seleziona il set di dati in cui desideri evidenziare le celle vuote e le celle con errori.
• Vai a Home -> Formattazione condizionale -> Nuova regola.
• Nella finestra di dialogo Nuova regola di formattazione selezionare Usa una formula per determinare quali celle formattare.
• Inserisci la seguente formula nel campo della sezione "Modifica la descrizione della regola":
  =OR(VAL.VUOTO(A1),VAL.ERRORE(A1))
  • La formula sopra controlla tutte le celle per due condizioni: se è vuota o meno e se ha un errore o meno. Se una delle condizioni è TRUE, restituisce TRUE.
• Imposta il formato che desideri applicare alle celle vuote o con errori. Per fare ciò, fare clic sul pulsante Formato. Si aprirà la finestra di dialogo "Formato celle", in cui è possibile specificare il formato.
• Fare clic su OK.

Ciò evidenzierebbe immediatamente tutte le celle che sono vuote o contengono errori.

Nota: Non è necessario utilizzare l'intero intervallo A1: A7 nella formula in formattazione condizionale. La formula sopra menzionata utilizza solo A1. Quando applichi questa formula all'intero intervallo, Excel controlla una cella alla volta e regola il riferimento. Ad esempio, quando controlla A1, utilizza la formula =OR(ISBLANK(A1),ISERROR(A1)). Quando controlla la cella A2, utilizza la formula =OR(ISBLANK(A2),ISERROR(A2)). Regola automaticamente il riferimento (dato che si tratta di riferimenti relativi) a seconda di quale cella viene analizzata. Quindi non è necessario scrivere una formula separata per ogni cella. Excel è abbastanza intelligente da cambiare il riferimento di cella da solo 🙂

Guarda anche: Utilizzo di IFERROR e ISERROR per gestire gli errori in Excel.

5. Creazione di mappe di calore

Una mappa termica è una rappresentazione visiva dei dati in cui il colore rappresenta il valore in una cella. Ad esempio, puoi creare una mappa termica in cui una cella con il valore più alto è colorata in verde e c'è uno spostamento verso il colore rosso quando il valore diminuisce.

Qualcosa come mostrato di seguito:

Il set di dati sopra ha valori compresi tra 1 e 100. Le celle vengono evidenziate in base al valore in esse contenuto. 100 ottiene il colore verde, 1 ottiene il colore rosso.

Ecco i passaggi per creare mappe di calore utilizzando la formattazione condizionale in Excel.

• Seleziona il set di dati.
• Vai a Home -> Formattazione condizionale -> Scale di colore e scegli una delle combinazioni di colori.

Non appena si fa clic sull'icona della mappa di calore, verrà applicata la formattazione al set di dati. Ci sono più sfumature di colore tra cui puoi scegliere. Se non sei soddisfatto delle opzioni di colore esistenti, puoi selezionare più regole e specificare il colore che desideri.

Nota: in modo simile, puoi anche applicare i set di barre dati e icone.

6. Evidenzia ogni altra riga/colonna

Potresti voler evidenziare righe alternative per aumentare la leggibilità dei dati.

Queste sono chiamate linee zebra e potrebbero essere particolarmente utili se si stampano i dati.

Ora ci sono due modi per creare queste linee zebra. Il modo più veloce è convertire i dati tabulari in una tabella Excel. Applicava automaticamente un colore a righe alternate. Puoi leggere di più a riguardo qui.

Un altro modo è usare la formattazione condizionale.

Supponiamo di avere un set di dati come mostrato di seguito:

Ecco i passaggi per evidenziare le righe alternative utilizzando la formattazione condizionale in Excel.

• Seleziona il set di dati. Nell'esempio sopra, seleziona A2:C13 (che esclude l'intestazione). Se desideri includere anche l'intestazione, seleziona l'intero set di dati.
• Apri la finestra di dialogo Formattazione condizionale (Home-> Formattazione condizionale-> Nuova regola) [Scorciatoia da tastiera - Alt + O + D].
• Nella finestra di dialogo, seleziona la finestra di dialogo "Usa una formula per determinare quali celle formattare".
• Inserisci la seguente formula nel campo della sezione "Modifica la descrizione della regola":
  =ISODD(RIGA())

• La formula sopra controlla tutte le celle e se il numero di RIGA di una cella è dispari, restituisce VERO. Il formato condizionale specificato verrebbe applicato a tutte le celle che restituiscono VERO.
• Imposta il formato che desideri applicare alle celle vuote o con errori. Per fare ciò, fare clic sul pulsante Formato. Si aprirà la finestra di dialogo "Formato celle", in cui è possibile specificare il formato.
• Fare clic su OK.

Questo è tutto! Le righe alternative nel set di dati verranno evidenziate.

Puoi usare la stessa tecnica in molti casi. Tutto quello che devi fare è utilizzare la formula pertinente nella formattazione condizionale. Ecco alcuni esempi:

• Evidenzia righe pari alternate: =ISEVEN(ROW())
• Evidenzia righe di aggiunta alternative: =ISODD(ROW())
• Evidenzia ogni 3a riga: =MOD(ROW(),3)=0

7. Cerca ed evidenzia i dati utilizzando la formattazione condizionale

Questo è un uso un po' avanzato della formattazione condizionale. Ti farebbe sembrare una rockstar di Excel.

Supponiamo di avere un set di dati come mostrato di seguito, con Product Name, Sales Rep e Geography. L'idea è di digitare una stringa nella cella C2 e se corrisponde ai dati in qualsiasi cella, allora dovrebbe essere evidenziata. Qualcosa come mostrato di seguito:

Ecco i passaggi per creare questa funzionalità di ricerca ed evidenziazione:

• Seleziona il set di dati.
• Vai a Home -> Formattazione condizionale -> Nuova regola (Scorciatoia da tastiera - Alt + O + D).
• Nella finestra di dialogo Nuova regola di formattazione, seleziona l'opzione "Utilizza una formula per determinare quali celle formattare".
• Inserisci la seguente formula nel campo della sezione "Modifica la descrizione della regola":
  =E($C$2””,$C$2=B5)
• Imposta il formato che desideri applicare alle celle vuote o con errori. Per fare ciò, fare clic sul pulsante Formato. Si aprirà la finestra di dialogo "Formato celle", in cui è possibile specificare il formato.
• Fare clic su OK.

Questo è tutto! Ora quando inserisci qualcosa nella cella C2 e premi invio, evidenzierà tutte le celle corrispondenti.

Come funziona?

La formula utilizzata nella formattazione condizionale valuta tutte le celle nel set di dati. Supponiamo che tu entri in Giappone nella cella C2. Ora Excel valuterebbe la formula per ogni cella.

La formula restituirebbe TRUE per una cella quando sono soddisfatte due condizioni:

• La cella C2 non è vuota.
• Il contenuto della cella C2 corrisponde esattamente al contenuto della cella nel set di dati.

Quindi, tutte le celle che contengono il testo Giappone vengono evidenziate.

Scarica il file di esempio

Puoi usare la stessa logica, per creare variazioni come:

• Evidenzia l'intera riga anziché una cella.
• Evidenzia anche quando c'è una corrispondenza parziale.
• Evidenzia le celle/righe durante la digitazione (dinamica) [Adorerai questo trucco :)].

Come rimuovere la formattazione condizionale in Excel

Una volta applicata, la formattazione condizionale rimane in vigore a meno che non venga rimossa manualmente. Come best practice, mantieni la formattazione condizionale applicata solo alle celle in cui ne hai bisogno.

Poiché è volatile, potrebbe portare a una cartella di lavoro di Excel lenta.

Per rimuovere la formattazione condizionale:

• Seleziona le celle da cui desideri rimuovere la formattazione condizionale.
• Vai a Home -> Formattazione condizionale -> Cancella regole -> Cancella regole dalle celle selezionate.
  • Se desideri rimuovere la formattazione condizionale dall'intero foglio di lavoro, seleziona Cancella regole da tutto il foglio.

Cose importanti da sapere sulla formattazione condizionale in Excel

• Formattazione condizionale in volatile. Può portare a una cartella di lavoro lenta. Usalo solo quando necessario.
• Quando copi le celle incollate che contengono la formattazione condizionale, viene copiata anche la formattazione condizionale.
• Se applichi più regole allo stesso insieme di celle, tutte le regole rimangono attive. In caso di sovrapposizione, viene data preferenza alla regola applicata per ultima. Tuttavia, puoi modificare l'ordine modificando l'ordine dalla finestra di dialogo Gestisci regole.