#Excel formattazione condizionale
Creare regole di formattazione condizionale in #Excel
Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 12 Gennaio 2023
Categoria: Microsoft Office Excel Formattazione condizionale
La formattazione condizionale in Excel è utile per formattare diversamente solo i dati che soddisfano certe condizioni, allo scopo di evidenziare informazioni importanti dei fogli di lavoro con colori o stili particolari. Tale formattazione si può applicare a celle, righe, colonne o a tabelle intere attraverso specifiche regole (condizioni). E allora non aspettiamo altro tempo e impariamo a creare regole di formattazione condizionale in Excel
Sommario #Excel Formattazione Condizionale
- Excel Regole di Formattazione condizionale
- Rimuovere la formattazione condizionale dalle celle vuote
- Formattare in base al valore di un’altra cella
- Formattare celle vuote e celle piene
- Formattare valori testuali
- Formattare le date
- Copia formato e aggiunta di nuove righe
- Excel Formattazione condizionale delle righe (formattazione condizionale orizzontale con video)
- Conclusioni
Excel Regole di Formattazione condizionale
I comandi per impostare la formattazione condizionale in Excel si trovano nella scheda Home all’interno del gruppo Stili cliccando l’icona Formattazione Condizionale
Una volta selezionata ad esempio una colonna, si può aprire la suddetta icona e scegliere tra:
- Regole evidenziazione celle
- Maggiore di… formatta celle con valori maggiori di un valore specificato
- Minore di… formatta celle con valori minori di un valore specificato
- Tra… formatta celle con valori tra due estremi specificati
- Uguale a… formatta celle con valori uguali a un valore specificato
- Testo contenente… formatta celle con valori contenenti un testo specificato
- Data corrispondente a… formatta celle con date di ieri, oggi, domani, ultimi 7 giorni, settimana scorsa, questa settimana, settimana prossima, mese scorso, questo mese, mese prossimo. Molto limitato perché funziona solo per le celle selezionate e non in base ad altri riferimenti ed è sempre applicata costruendosi sulla data corrente. Più avanti c’è quindi una sezione dedicata solo alle date
- Valori duplicati… formatta celle con doppioni o valori univoci
- Altre regole…
- Regole Primi/Ultimi
- Primi 10 elementi… formatta celle con gli n elementi più alti
- Primi 10%... formatta celle con gli n% elementi più alti, utile per quantificare le incidenze percentuali. Ad esempio nell’intervallo A1:A20, Primi 10%... formatterebbe i due valori più alti, Primi 20% i quattro valori più alti e così via. Vediamo un esempio con i manuali informatici che vendo su Amazon per capire quali libri rappresentano il primo 25% delle vendite totali
- Ultimi 10 elementi… formatta celle con gli n elementi più bassi
- Ultimi 10%... formatta celle con gli n% elementi più bassi
- Sopra la media… formatta celle con i valori sopra la media
- Sotto la media… formatta celle con i valori sotto la media
- Altre regole…
- Barre dei dati inseriscono una barra orizzontale nella cella tanto più lunga quanto più il valore è alto, ciò permette di localizzare facilmente i valori più alti e bassi in un foglio denso di informazioni
- Riempimento sfumato
- Tinta unita
- Altre regole… permette di applicare un proprio stile alla barra dei dati scegliendo le opzioni desiderate
- Spuntare la casella di controllo Mostra solo la barra per nascondere il valore nella cella e visualizzare solo le barre
- Per selezionare il minimo e il massimo tipo di dati, cliccare l’elenco a discesa a destra di Tipo.
- Scale di colori permettono la formattazione con due o tre sfumature di colore, le diverse tonalità rappresentano i valori delle celle
- Altre regole… si possono scegliere 2 o 3 colori e assegnargli al minimo, massimo e valore intermedio
- Set di icone rappresentano i dati con frecce, forme, segni di spunta, bandiere, stelle ed altre immagini
- Direzionale
- Forme
- Indicatori
- Classificazioni
- Altre regole… permette di scegliere altre icone da assegnare a valori e tipi diversi
- Applicare un set di icone in base ad altre celle
Caso 1 Per aggiungere una spunta nella colonna A, se tutte le celle della riga sono piene, un punto esclamativo se alcune sono vuote e una croce se sono tutte vuote:- Inserire una colonna vuota A all’inizio della propria tabella (ad esempio B1:F10) e contare il numero di celle vuote in ciascuna riga copiandoci la formula =CONTA.VUOTE(B2:F2) e tirandola giù fino ad A10
- Da Altre regole… applicare il set di icone desiderato alla colonna A
- Cliccare il pulsante Inverti ordine icone per modificare l’ordine delle icone
- Spuntare la casella di controllo Mostra solo icona
- Per la croce impostare >= 5 (5 sono le colonne nella tabella B1:F10), per il punto esclamativo >= 1
- Impostate il tipo Numero per entrambe le icone
Excel Formattazione condizionale con Set di icone
Per avere una bandiera verde nella colonna A quando c’è una data nella cella corrispondente della colonna D (Data di spedizione) e una bandiera rossa se non c’è data in D:- Aggiungere la formula =SE($D2 <> “”;3;1) alla colonna A e tirare giù (essa inserirà un 3 in A, se la cella in D è vuota, altrimenti inserirà 1)
- Selezionare tutte le celle di A, eccetto l’intestazione e da Altre regole… applicare il set di icone alla colonna A
- Cliccare il pulsante Inverti ordine icone per modificare l’ordine delle icone
- Spuntare la casella di controllo Mostra solo icona
- Per la bandiera verde impostare >=3; per la gialla >2. Non vogliamo davvero una bandiera gialla, quindi impostiamo una condizione insoddisfabile, cioè un valore >2 e <3;
- Impostate il tipo Numero per entrambe le icone
Excel Formattazione condizionale con Set di icone
Per avere in B2 una freccia verde verso l'alto quando il valore in B2 stesso è maggiore di quello in A1 e rossa verso il basso quando è minore- Applicare a B2 il seguente set di icone
Excel Formattazione condizionale con Set di icone
- Nuova regola… permette di creare una regola da 0, in caso quelle già presenti non fossero adatte alle proprie esigenze, aprendo una finestra con varie regole impostabili che in realtà sono le stesse che si trovano cliccando la voce Altre regole… dei punti precedenti; queste 6 nuove possibilità replicano anche alcune possibilità già presenti nei punti precedenti, ma anche cose molto utili che man mano si vedranno
- Formatta tutte le celle in base ai relativi valori
- Formatta solo le celle che contengono
- Formatta solo i primi o gli ultimi valori
- Formatta solo i valori superiori o inferiori alla media
- Formatta solo i valori univoci o duplicati
- Utilizza una formula per determinare le celle da formattare
Excel Formattazione condizionale con Formule
- Cancella regole
- Cancella regole dalle celle selezionate
- Cancella regole dal foglio intero
- Cancella regole da questa tabella
- Cancella regole da questa tabella pivot
- Gestisci regole… permette di creare una nuova regola o modificare, eliminare, ordinare, interrompere una regola già impostata; supponendo che un intervallo abbia 2 formattazioni impostate, mettendo il segno di spunta su Interrompi se vera della prima regola si fa in modo che la seconda regola non venga applicata quando la prima condizione è soddisfatta. Se non si vede la regola da modificare, selezionare Questo foglio di lavoro dall’elenco a discesa Visualizza regole di formattazione per
Rimuovere la formattazione condizionale dalle celle vuote
Supponiamo di aver creato una regola “Minore di…” per evidenziare i valori minori di 10; per evitare che vengano evidenziate anche le celle vuote occorre creare una Nuova regola del tipo Formatta solo le celle che contengono, Formatta solo celle con:, Valore vuoto e fare clic su OK senza impostare nessun formato.
Infine da Gestisci regole… aprire la finestra Gestione regole formattazione condizionale e spuntare la casella di controllo Interrompi se vera a destra di La cella contiene un valore vuoto.
Formattare in base al valore di un’altra cella
Per basare la formattazione sul valore di una particolare cella, ad esempio colorare i valori di A1:A10 più alti del valore in A1 (o per esempio di C10), Regole evidenziazione celle, Maggiore di…, scrivere nella casella di testo =$A$1, così cambiando il valore in A1, la regola resterà dinamica. Ovviamente si potrebbe anche scegliere Minore di…, Tra…, Uguale a…
Da Regole evidenziazione celle, Altre regole…, Formatta solo le celle che contengono si può trovare anche:
Non compreso tra, Diverso da.
Se per esempio si vogliono formattare i dati in A1:A10 se il valore di C1>D1 (ma si possono usare tutti gli operatore relazionali), dopo aver selezionato l’intervallo suddetto, andare su Formattazione condizionale, Nuova regola… e nella casella di testo inserire la formula:
- =$C$1>$D$1
- =E($C$1>$D$1;$C$2<$D$2) per formattare in base a più condizioni da soddisfare contemporaneamente
- =E($C$1>$D$1;$C$2<$D$2) per formattare in base a più condizioni da soddisfare non necessariamente in maniera contemporanea
Formattare celle vuote e celle piene
Formattazione condizionale, Nuova regola…, Formatta solo le celle che contengono…, Formatta solo le celle con: e scegliere Valore vuoto o Valore non vuoto.
Per formattare le celle di una colonna, ad esempio A1:A10, quando una cella corrispondente di un’altra colonna, ad esempio B1:B10, è vuota o non vuota occorre una formula da applicare ad A1:A10 partendo da Formattazione condizionale, Nuova regola…:
- per celle vuote: =B1="" formatta se la cella corrispondente in B è vuota
- per celle non vuote: =B1<>"" formatta se la cella corrispondente in B non è piena
Chi usa qualcosa del tipo =SE(FALSO; “OK”; “”) per restituire una stringa vuota e non si vuole che tali celle siano trattate come vuote, deve usare al suo posto =VAL.VUOTO(A1)=VERO o =VAL.VUOTO(A1)=FALSO per formattare nell’ordine le celle vuote o piene.
Formattare valori testuali
Per formattare l’intervallo A1:A10 quando un’altra cella nella stessa riga ha un certo valore testuale:
- =D1=”Andrea” corrispondenza esatta
- =RICERCA(“Andrea”;D1)>0 corrispondenza non necessariamente esatta
Formattare le date
Formattare i giorni della settimana: in tal caso si sappia che se si usa la formula GIORNO.SETTIMANA, Excel considera Domenica = 1, Lunedì = 2 e così via fino a Sabato = 7.
Per formattare tutti lunedì, nell’intervallo A1:A10, basterà selezionare l’intervallo e andare su Formattazione Condizionale, Nuova regola…, Utilizza una formula per determinare le celle da formattare, e nella casella di testo inserire la formula =GIORNO.SETTIMANA(A1)=2
Per formattare martedì e giovedì: =O(GIORNO.SETTIMANA(A1)=3;GIORNO.SETTIMANA(A1)=5)
Per formattare più giorni consecutivi si può utilizzare anche =GIORNO.SETTIMANA(A1)>2 con tutte le possibili varianti fornite dagli operatori relazionali: >, <; >=, <=, =, <>.
Formattare le festività: scrivere nell’intervallo A1:A10 le date che comprenderanno anche le feste, e ad esempio in ($C$1:$C$5) solo le feste, andare in Utilizza una formula per determinare le celle da formattare, inserire la formula =CONTA.SE($C$1:$C$5;A1)>0
Per formattare le festività è anche utile leggere il post denominato Excel: formattare giorni festivi.
Formattare le date uguali, maggiori o inferiori alla data corrente: scrivere nell’intervallo A1:A10 le date, andare in Utilizza una formula per determinare le celle da formattare, inserire una delle seguenti formule
- Uguale a oggi: =A1=OGGI()
- Maggiore di oggi: =A1>OGGI()
- Minore di oggi: =A1<OGGI()
Formattare le date superiori o inferiori di x giorni alla data corrente: scrivere nell’intervallo A1:A10 le date, andare in Utilizza una formula per determinare le celle da formattare, inserire una delle seguenti formule
- =A1=OGGI()+3 Tra 3 giorni
- =A1=OGGI()-3 3 gironi fa
- =E(OGGI()-A1>=2;OGGI()-A1<=4) da 2 a 4 giorni fa
- =E(A1-OGGI()>=2;A1-OGGI()<=4) tra 2 a 4 giorni
Copia formato e aggiunta di nuove righe
- Il Copia formato vale anche per la formattazione condizionale. Cliccare una cella con la formattazione condizionale da copiare, cliccare la scheda Home e poi l’icona Copia formato (facendo doppio clic su Copia formato si può incollare la stessa formattazione condizionale in celle anche non adiacenti, senza dover ogni volta ripetere la procedura da zero; al termine, premere Esc (se si è creata la regola usando una formula, potrebbe servire modificare i riferimenti di cella nella formula dopo aver applicato la formattazione condizionale).
- Se prevedete di aggiungere altre righe di dati in futuro e desiderate che la regola di formattazione condizionale venga applicata anche alle nuove voci automaticamente, è possibile:
- Convertire l’intervallo di celle in una tabella (scheda Inserisci, icona Tabella). In questo modo, la formattazione condizionale verrà applicata automaticamente a tutti i nuovi record
- Selezionare alcune righe vuote sotto i vostri dati, ad esempio 100 righe vuote, in modo che la regola venga applicata anche ad esse.
Excel Formattazione condizionale delle righe (formattazione condizionale orizzontale)
Spesso si cerca di effettuare la formattazione condizionale orizzontale ovvere la formattazione condizionale delle righe, vediamo allora come formattare una riga in base al valore di una cella.
Supponiamo di avere nell’intervallo A1:F5 gli stipendi del primo trimestre di una serie di impiegati (colonna A nome impiegati, B stipendio gennaio, C stipendio febbraio, D stipendio marzo, E stipendio aprile, F totali) e di voler formattare l’intera riga se la somma degli stipendi di ogni singolo impiegato è maggiore o uguale a 5.300 euro. Il valore 5.300 è stato inserito nella cella H1, in modo da poter modificare il valore senza modificare la regola.
Selezionare l’intervallo A2:F5. Andare su Formattazione condizionale e poi su Nuova regola.
Seleziona Utilizza una formula per determinare le celle da formattare.
La regola da usare sarà =$F2>=$H$1 ovvero il valore della cella contenente la somma degli stipendi del primo impiegato >= la cella contenente il valore 5.300.
Una volta decisa la formattazione si vedranno colorate tutte le righe il cui totale è maggiore o uguale a 5.300 euro.
Vediamo un video esplicativo
Conclusioni
Da Excel 2007, l’applicazione della formattazione condizionale è molto più semplice rispetto a versioni più vecchie e offre molte opzioni pronte all’uso, ma attraverso l'uso delle formule e funzioni Excel le combinazioni diventano infinite. L’interfaccia fornita dall’icona Formattazione condizionale dà ampia versatilità nel modo di presentare i dati. Personalmente, specie se ti è piaciuto creare regole di formattazione condizionale in Excel, non posso far altro che consigliarla fortemente per accrescere l'impatto visivo che ha sul lettore. Leggi anche come contare e sommare celle in base al colore.
Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: