Applicare le regole della Formattazione condizionale di Excel

La Formattazione Condizionale di Excel consente di mettere in risalto specifici valori in un foglio di lavoro basandosi sul risultato di una o più condizioni logiche predefinite.
Quando le condizioni impostate sono soddisfatte, Excel applica automaticamente la formattazione desiderata alle celle, evidenziandole. Se invece le condizioni non vengono rispettate, le celle rimangono formattate nel loro stato originale.
Questo strumento può essere utilizzato in modo flessibile, sia per formattare una singola cella sia per applicarlo a interi intervalli di dati.
Le possibilità di personalizzazione sono molteplici: è possibile modificare il tipo e la dimensione del carattere, cambiare il colore del testo o dello sfondo, aggiungere bordi o persino applicare icone e barre di colore che visualizzano graficamente l'andamento dei dati. Q
ueste opzioni permettono di evidenziare rapidamente trend, anomalie o relazioni importanti all'interno di un set di dati.

Il presente tutorial illustra in modo dettagliato e progressivo come utilizzare la Formattazione Condizionale in Excel, guidando l’utente passo dopo passo nella creazione di regole personalizzate per l'analisi dei dati. Anche se il tutorial è piuttosto esteso, si è preferito non dividerlo in sezioni separate per mantenere una visione coerente e unitaria dell'argomento, garantendo così una comprensione completa e fluida del processo.

Contenuti del tutorial: la Formattazione condizionale di Excel

Applicazione e regole della Formattazione condizionale
[01] - Introduzione alla Formattazione condizionale di Excel
[02] - Formattazione condizionale di Excel: Regola evidenziazione celle (1)
[03] - Esempi di applicazione della Formattazione condizionale: Regola evidenziazione celle
[04] - Formattazione condizionale di Excel: Regole Primi Ultimi (2)
[05] - Esempi di applicazione della Formattazione condizionale: Regole Primi / Ultimi
[06] - Formattazione condizionale di Excel: (3) Barre dei dati
[07] - Esempio applicazione: Creare una formattazione Barre dei dati nascondendo i valori presenti nelle celle
[08] - Formattazione condizionale di Excel: (4) Scale colori (esempi applicazione)
[09] - Formattazione condizionale di Excel: (5) Set icone
Gestire - organizzare la Formattazione condizionale
[10] - Individuare dove sono presenti formattazioni condizionali sul foglio Excel  
[11] - La Finestra di dialogo Gestisci regole formattazione automatica
[12] - Gestisci regole formattazione condizionale: opzione interrompi se vera
[13] - Rimuovere la formattazione condizionale
[14] - Approfondimento: La Formattazione condizionale applicate alle date

Introduzione alla Formattazione condizionale di Excel

La Formattazione condizionale di Excel permette di attribuire ai valori presenti sul Foglio di lavoro una serie di proprietà fra cui il tipo di font, la dimensione, il colore, il bordo, lo sfondo etc.
A differenza della normale formattazione di una cella la formattazione condizionale consente di formattare solo i dati che soddisfano determinati criteri.

La Formattazione Condizionale di Excel, dopo aver verificato un test logico, applica al contenuto di una cella speciali attributi di testo oppure include nella cella degli elementi grafici (quali ad esempio barre o icone).
La Formattazione condizionale rappresenta pertanto un vero e proprio strumento di analisi dati che può essere applicato ad intervalli di dati e tabelle, incluse le tabelle Pivot.
Il programma Excel mette a disposizione diverse condizioni di Formattazione condizionale predefinite. È possibile modificare le condizioni predefinite offerte dal programma oppure crearne di personalizzate applicando delle Formula comparative logiche che restituiscano come risultato vero oppure falso. Se il risultato restituito è vero, viene applicata la formattazione.

Le opzioni della Formattazione condizionale sono contenuti nel gruppo pulsanti Stili della scheda Home.
Una volta cliccato sul Pulsante Formattazione condizionale, compare un elenco a discesa nel quale vengono proposte 5 diverse impostazioni:
[1] Regola evidenziazione celle
[2] Regole Primi/Ultimi
[3] Barre dei dati
[4] Scale dei colori
[5] Set di icone
L'ultima sezione del menu della Formattazione condizionale è dedicata alle operazioni di Gestione delle regole, Cancellazione e Creazione regole personalizzate.

Formattazione condizionale di Excel: (1) Regola evidenziazione celle

Le impostazioni disponibili per la Regole evidenziazione celle, sono soprattutto legate agli operari logici Maggiore di, Minore di, e Uguale a.
Molto utile anche l'applicazione dell'opzione Valori Duplicati.

 

Nello specifico le opzioni di formattazione condizionale offerte da menu Regole evidenziazione celle sono le seguenti.
Maggiore di… Permette di applicare la formattazione alle celle contenenti valori superiori ad un determinato numero.
Minore di… Permette di applicare la formattazione alle celle contenenti valori inferiori ad un determinato numero.
Tra… Definisce un intervallo (range di valori) entro il quale applicare la formattazione.
Uguale a… Applica la formattazione alle celle con valori uguali a un determinato valore testuale o numerico.
Testo contenente… Applica la formattazione alle celle con valori contenenti una determinata stringa di testo.
Data corrispondente a… Applica la formattazione alle celle con date di ieri, oggi, domani, ultimi 7 giorni, settimana scorsa, questa settimana, settimana prossima, mese scorso, questo mese, mese prossimo. (Vedi immagine sottostante).
Valori duplicati… Applica la formattazione a celle contenenti valori duplicati oppure valori univoci.
Altre regole... È possibile accedere alla Finestra di dialogo Nuova regola di formattazione.

Applicazione di una stile di formattazione condizionale

Una volta scelto il tipo di azione, Excel mette a disposizione alcuni stili predefiniti per la formattazione (vedi immagine sottostante).
Qualora questi non risultassero sufficienti è possibile fare clic su Formato personalizzato... per accedere ad un menu che permette di impostare tutti i possibili attributi di formattazione font, dimensione, colore, bordo, sfondo etc.

Esempi applicazione della Formattazione condizionale: Regola evidenziazione celle

Nella tabella sottostante l'obiettivo è mettere in evidenza le celle del campo stipendio il cui valore sia superiore a 1.300 euro.
Per iniziare è conveniente selezionare tutti i campi sui quali si intende impostare la formattazione, e dalla scheda Home, nel gruppo pulsante Stili: cliccare sul pulsante Formattazione condizionale e scegliere 'l'opzione Maggiore di....

Nella successiva Finestra di dialogo Maggiore di, Excel propone automaticamente quale criterio di formattazione la media. Ovviamente è possibile impostare diversamente questo valore ad esempio digitando 1300.
Successivamente, scegliendo la formattazione Riempimento rosso chiaro con testo rosso scuro le celle contenenti un valore superiore a 1300 verranno evidenziate con tale colorazione.

Nelle Regole evidenziazione celle è possibile usufruire anche dei criteri: Testo contenente... Data corrispondente a... Valori duplicati...
La prima funzione permette l'evidenziazione delle celle contenenti una determinata stringa testo (anche un solo carattere).
Nel Caso della funzione Data corrispondente a... è possibile applicare la formattazione condizionale all'evidenziazione di una specifica data. Per un approfondimento sulla formattazione delle date si rimanda a: Applicare la Formattazione condizionale di Excel al formato data

Applicazione della Formattazione Valori duplicati 

La Formattazione condizionale Valori duplicati risulta molto utile per monitorare l'eventuale presenza di codici doppi presenti nelle tabelle.
Per i campi di tipo testo che devono contenere dei valori univoci risulta davvero utile applicare questo tipo di formattazione.
Un esempio potrebbe essere un campo che deve contenere la matricola di un dipende, piuttosto che il codice fiscale oppure il codice di un prodotto.
Se per errore venisse inserito un codice doppio, le celle verrebbero immediatamente messe in evidenza con la Formattazione (vedi immagine sottostante).

 

Formattazione condizionale di Excel: (2) Regole Primi Ultimi

Questa formattazione risulta particolarmente utile nel caso in cui si voglia mettere in evidenza valori particolarmente alti o bassi presenti all'interno di una serie (anche espressi percentualmente rispetto al totale), oppure valori che siano superiori o inferiori rispetto la media della serie.
Una volta cliccato su Regole Primi/Ultimi vengono messe a disposizione numerose impostazioni: Primi 10 elementi; Primi 10%; Ultimi dieci elementi; Ultimi 10%; sopra e sotto alla media.

Nello specifico le opzioni di formattazione condizionale offerte da menu sono Regole Primi/Ultimi sono le seguenti:
Primi 10 elementi… Permette di  formattare le celle con gli "n" valori più alti della serie.
Primi 10%... Permette di formattare le celle contenente gli "n" percento valori più alti della serie.
Ultimi 10 elementi… Permette di  formattare le celle con gli "n" valori più bassi della serie.
Ultimi 10%... Permette di formattare le celle contenente gli "n" percento valori più bassi della serie.
Sopra la media… Applica la formattazione alle celle contenete i valori sopra alla media.
Sotto la media… Applica la formattazione alle celle contenete i valori sotto alla media.
Altre regole… È possibile accedere alla Finestra di dialogo Nuova regola di formattazione.

Esempi applicazione della formattazione condizionale: Regole Primi / Ultimi

Primo esempio. Si vogliono evidenziare i tre valori più alti di una serie di fatture di vendita.
Una volta selezionati i dati del fatturato (vedi immagine sottostante) nella scheda Home scegliere Formattazione condizionale Regole Primi / Ultimi e successivamente primi 10 elementi...

Nella successiva Finestra di dialogo Primi 10 elementi impostare il contatore sul numero tre, scegliere il tipo di formattazione e confermare cliccando su OK.
Il risultato della formattazione sarà la colorazione dei 3 valori più alti della serie.

 

Secondo esempio. Relativamente ad una serie di fatture, si vogliono colorare rispettivamente di verde e di rosso i valori superiori e inferiori alla media. Una volta selezionati i dati del fatturato (vedi immagine sottostante) nella scheda Home scegliere Formattazione condizionale valori Sopra alla media... e applicare il formato Riempimento verde chiaro con testo verde rosso scuro.
Successivamente, ripetere l'operazione scegliendo Sotto alla media... applicando quale colore di riempimento rosso chiaro con testo rosso scuro.
Il risultato dell'operazione è illustrato nella successiva immagine

 

Formattazione condizionale di Excel: (3) Barre dei dati

Questa Formattazione condizionale è molto interessante in quanto risulta una valida alternativa alla costruzione di un grafico a barre, con il vantaggio di poter applicare l'analisi grafica a grandi serie di dati.
Barre dei dati permette di formattare il contenuto delle celle con un grafico a barre colorate (come rappresentato nell'immagine sottostante)
Una volta selezionato i dati nella scheda Home fare clic su Formattazione condizionale e scegliere Barre dei dati.
Quale risultato della formattazione, Excel riempie completamente con una barra colorata la cella che presenta il valore più alto della serie creando delle barre proporzionalmente più piccole per i valori inferiori.
Qualora le celle dovessero contenere un valore negativo, viene creata una barra di colore rosso nella parte sinistra della cella (vedi immagine successiva).

 

Creare una formattazione Barre dei dati nascondendo i valori presenti nelle celle

La sovrapposizione delle barre con i valori presenti nella cella a volte rappresenta un limite sia di lettura sia di stampa dei dati.
Una interessante alternativa è quella di produrre a fianco dei valori una seconda colonna che copia dalla colonna a sinistra i numeri che si vogliono formattare con le barre dei dati.
Esempio nell'immagine sottostante nella cella F2 del Foglio Excel è stata inserita, e successivamente trascinata, la Formula =E2

 

Una volta duplicati i dati nella seconda colonna, selezionare i valori e applicare alla Formattazione condizionale. Pulsante Formattazione condizionale opzione Barre dei dati.
Create le barre dei dati. Si torna in Formattazione condizionale barre dei dati e si scegli l'opzione Altre regole...

Altre regole... permette di accedere alla Finestra di dialogo  Nuova regola di formattazione, nella quale bisogna aggiungere il simbolo di spunta alla opzione Mostra solo la barra.
Con questa impostazione (vedi immagine sottostante) nella seconda colonna verranno nascosti i dati e rimarrà evidente la sola barra creando a tutti gli effetti un grafico a barre orizzontali.

 

Formattazione condizionale di Excel: (4) Scale colori

Anche queste tipologia di Formattazione condizionale, assieme al successivo Set icone, permette di evidenziare i rapporti fra i dati, le loro variazioni e la loro distribuzione.
Nello specifico Scala colori: consente di applicare una sfumatura di colore a intervalli di celle.
È possibile personalizzare questa formattazione andando sulla opzione Gestisci regole... e nella finestra di dialogo Gestisci regole formattazione condizionale, cambiare colori e valori di riferimento.

Applicazione della formattazione condizionale scale colori

Un esempio classico dell'applicazione delle scale colore nella formattazione condizionale, è quello della creazione di una scala termica.
Nel caso del sottostante esempio, nella tabella sono riepilogate le temperature medie (minime e massime) rilevate nel 2020 nella provincia di Verona.
Una volta selezionate i dati, nella scheda Home, scegliere Formattazione condizionale e successivamente Scale di colori.

Per ipostazione, Barre colore propone il colore rosso per i valori più bassi e il verde per quelli più altri.
Sempre nella Finestra di dialogo Modifica regola di formattazione. nella parte inferiore della scheda sono disponibile dei menu di scelta colore che permettono di decidere quale colore attribuire ai valori minimi, intermedi e massimi. 
Nel caso dell'esempio è stato attribuito il colore verde alle temperature più basse, il giallo per quelle intermedie ed infine il rosso per quelle più alte.

Formattazione condizionale di Excel: (5) Set icone

Il Set di icone permette di rappresentare i dati attraverso dei simboli grafici quali frecce; simboli di spunta; stelle etc. oppure attraverso forme che richiamano l'immagine di un semaforo.
Le tipologie di icone sono classificate in Direzionali; Forme; Indicatori e Classificazioni.
Al di la della forma è importante considerare il numero di icone che caratterizza i vari tipi di set, ovvero tre, quattro o cinque simboli.
A seconda del numero di icone la distribuzione dati dati viene suddivisa in tre, quattro oppure cinque sezioni, assegnando ad ognuna di esse una specifica icona.

Esempio di set a tre icone: La Formattazione condizionale potrà utilizzerà un'icona per evidenziare tutti i valori superiori o uguali al 50 percento di una distribuzione di dati, un'altra icona per i valori inferiori compresi fra 50 percento e superiori o uguali al 25 e infine una terza icona per i valori inferiori al 25 percento.

Una volta scelto il tipo di icona entrando nell'opzione Modifica regola di formattazione sarà possibile modificare l'impostazione percentuale espressa da Excel e personalizzarla.
È possibile invertire l'applicazione dei colori delle icone, ovvero anzichè usare il verde per la formattazione del valore più alti e attribuire questo colore ai valori più basso.

Per modificare tutte queste impostazione accedere alla Finestra di dialogo Nuova regola di formattazione (vedi immagine sottostante) dopo aver scelto Set di icone e cliccato su Altre regole...

Impostazioni e personalizzazione del set icone

[1] Stile icone si può scegliere fra diversi formati di icone. Indipendentemente dalla forma è molto importante scegliere fra set composti di tre, quattro oppure cinque icone.
[2] Per invertire l'ordine del colore (rosso o verde) assegnato ai valori più alti o bassi fare clic sul pulsante: Inverti ordine icone.
[3] Per visualizzare solo le icone assegnate e nascondere i valori presenti in una cella seleziona la Checkbox: Mostra solo icone.
[4] Per attribuire una Icona ad un valore presente in una cella, è possibile impostare la percentuale (esempio posizionamento di un valore nel 33 percento inferiore di una distribuzione di valori, dal 33 a 67 percento, oppure superiore al 67%).
In alternativa ad una definizione percentuale possono essere impostati dei numeri esempio valori inferiori a 1000, superiori o uguali a 1000 o superiori uguali a 3000.
E' altresì possibile definire l'icona da attribuire attraverso la definizione di una formula oppure un percentile.

Individuare dove sono presenti formattazioni condizionali sul Foglio Excel  

La presenza della Formattazione condizionale su un Foglio di lavoro si individua con facilità nel momento in cui si realizza la condizione Vero del test logico. Potrebbe però essere necessario conoscere in quali parti del Foglio di lavoro sono presenti regole di Formattazioni condizionali.
Per procedere, nella scheda Home, tutto a destra sulla barra barra multifunzione, fare clic sul pulsante Trova e selezione e nel menu selezionare Formattazione condizionale.

 

A seguito di questa azione, tutte le celle del Foglio Excel contenenti Formattazioni condizionali verranno selezionate.

La Finestra di dialogo Gestisci regole di formattazione condizionale

Per conoscere le regole di Formattazione condizionale applicate su un Foglio di lavoro una volta selezionato l'intervallo celle interessate, nella scheda Home fare clic sul pulsante Formattazione condizionale e scegliere l'opzione Gestisci regole...

Si apre la finestra di dialogo Gestisci regole formattazione condizionale la quale è un vero e proprio cruscotto di controllo con il quale gestire le proprietà della Formattazione condizionale.
In questa finestra è possibile Modificare una regola, creare una Nuova regola oppure Eliminare una regola.
Nel caso dell'esempio sottostante, rispetto all'intervallo delle celle selezionate, la Finestra di dialogo permette di verificare quali regole, e quali formattazioni sono applicati in determinati celle del Foglio di lavoro.
L'ultima regola definita, è quella che viene descritta nella prima riga della finestra di dialogo, ed è quella gerarchicamente più importante.
Nel caso dell'esempio illustrato nella sottostante immagine se il valore contenuto in una cella è superiore a 2000 viene applicato il colore giallo, se però il valore fosse superore a 4000 (regola gerarchicamente più importante) viene applicato il colore verde.

Gestisci regole formattazione condizionale: l'opzione Interrompi se Vera

Spesso poco conosciuta o poco chiara, l'opzione Interrompi se Vera presente nella Finestra di dialogo Gestione regole formattazione condizionale, permette di interrompere l'esecuzione delle successive regole qualora un precedente criterio risultasse vero.
Un caso classico di applicazione di questa opzione è quello di evitare la Formattazione condizionale delle celle vuote del Foglio di lavoro.

Come evitare la formattazione condizionale delle celle vuote

Esempio. Nella sottostante immagine, nella colonna fatturato, è stata impostata una Formattazione condizionale che applica un riempimento di colore rosso chiaro se il valore della cella è minore di 100.
Quale risultato di questa impostazione, anche le celle vuote vengono formattate con il colore rosso.

Per correggere questa impostazione, fare clic sul pulsante Formattazione condizionale e selezionare Nuova regola...
Nel successiva Finestra di dialogo Nuova regola di formattazione (vedi immagine sottostante) scegliere l'opzione Formatta solo le celle che contengono e impostare la regola  Formatta solo le celle con: Valore vuoto.

 

Una volta impostata la formattazione celle vuote, cliccare su Formattazione condizionale e selezionare Gestisci regole.
Nella Finestra di dialogo Gestisci regole formattazione condizionale in corrispondenza della voce La cella contiene un valore vuoto  inserire il segno di spunta su Interrompi se vera.

Il risultato di questa impostazione, è che quando viene rilevata una cella vuota (se vero che una cella è vuota) Excel interrompe l'applicazione delle regole di formattazione e non valuta la successiva impostazione (se il valore è minore di 1000 applica il colore rosso).

 

Rimuovere le regole della Formattazione condizionale

Sono disponibili più livelli di cancellazione delle regole della formattazione. Per procedere, fare clic su Formattazione condizionale e selezionare Cancella regole.
Nella successiva tendina sono disponibili quattro diversi azioni di cui due specifiche per le tabelle.
La prima opzione serve per eliminare le regole di formattazione condizionale presenti solo all'interno di una area selezionata del Foglio di lavoro.
La seconda opzione elimina tutte le regole presenti sull'intero Foglio di lavoro.
La terza opzioni cancella le regole dall'eventuale tabella selezionata.
La quarta opzione elimina le eventuali regole presenti all'interno di una tabella Pivot selezionata.

 

Approfondimento: La Formattazione condizionale applicate alle date

Nei fogli Excel, può tornare utile monitorare scadenze (consegne, fine lavori, pagamenti etc.) applicando delle Formattazioni condizionali che permettano di rilevare facilmente l'avvicinarsi di una scadenza oppure un'avvenuta scadenza.
Per quanto concerne il formato data Excel permette di applicare sia delle regole predefinite, sia di crearle attraverso l'applicazione di alcune Funzioni.
L'argomento Formattazione condizionale delle date è stato sviluppato in una specifica sezione: Applicare Formattazione condizionale di Excel al formato data

Indice Generale Argomenti: EXCEL

[Valter Borsato: Ottobre - 2016 | Ultimo aggiornamento 12/12/2023]