Benvenuto ad una nuova lezione di MasterExcel.it. In questa lezione vedremo un caso di Formattazione Condizionale Avanzata, quindi applicheremo la formattazione su tutta l’intera riga, non solo sulla cella.
Ti ricordo che abbiamo già visto la Formattazione Condizionale sia nella lezione Excel precedente, sia nel corso Excel base di MasterExcel.it
Guarda il video della Formattazione Condizionale Avanzata
Come sempre utilizzeremo un file di esempio, visibile nell’immagine sottostante:
Abbiamo un enorme archivio, contenente articoli per una tesi di laurea. Il nostro scopo è colorare ogni articolo in base all’anno di pubblicazione, quindi l’intera riga.
Per prima cosa selezioniamo tutto il nostro file tramite il triangolino che puoi trovare nell’angolo in alto a sinistra tra la riga “1” e la colonna “A”.
Ora andiamo su Formattazione condizionale > Nuova regola. Nel menù che compare selezioniamo “utilizza una formula per determinare le celle da formattare”.
Nel box “formatta i valori per cui questa formula restituisce vero”, scriviamo:
=$h1 =2016
Su “formato” e “riempimento” selezioniamo il colore giallo.
Il risultato è questo:
Ripetiamo lo stesso procedimento anche per l’anno 2015. Per farlo dovremo solamente modificare la formula con “=$h1 =2015” e selezionare un colore diverso, magari il verde.
Questo metodo è dinamico. Se modifichiamo l’anno, la formattazione si adeguerà di conseguenza. In questo caso, se modificassimo l’anno indicato per un file pubblicato, ad esempio, nel 2016 e scrivessimo 2015, il file verrebbe riformattato passando dal colore giallo al verde.
Un altro Esempio
Facciamo un altro esempio, ma prima di tutto cancelliamo le regole inserite poco prima così da tornare alla formattazione originaria. Quindi andiamo su Formattazione Condizionale > Cancella Regole > Cancella Regole dal foglio intero.
Ora selezioniamo tutto il file, andiamo su Formattazione Condizionale > Nuova regola. Selezioniamo ancora “utilizza una formula..” e formattiamo i valori utilizzando la formula:
=$f1 =”N”
In questo caso è necessario utilizzare le virgolette per il valore “N” perché non si tratta di un valore numerico, come nell’esempio precedente. Cambiamo il formato e selezioniamo un riempimento rosso.
Il risultato è visibile nell’immagine sottostante:
Conclusioni
Come vedi, possiamo applicare questa formattazione condizionale in maniera molto versatile. In questo esempio possiamo colorare le celle in base ai vari valori specificati in una colonna in particolare. Per esempio potremo colorare di rosso in maniera automatica tutte le righe con N in colonna F e di verde tutte le righe con I in colonna F.
Inoltre, in questo esempio abbiamo i vari valori possibili convalidati. Quindi non si possono inserire valori che non sono disponibili negli elenchi. Questo ovviamente ci risparmia tantissimi possibili errori nella formattazione condizionale perché si possono sempre e soltanto inserire dei valori precisi che andranno proprio ad essere utilizzati come criteri nelle nostre formattazioni condizionali.
Salve, ho un quesito. Ho un elenco con 2 colonne: Colonna A, data. Colonna B, nome. Devo fare in modo di colorare di rosso tutte le righe che hanno 2017 alla colonna A (questo è facile), ma devo colorare di rosso anche le righe che in colonna B hanno lo stesso nome di quelle del 2017 già colorate di rosso, ma hanno data diversa.
Spero di essermi spiegata!
Grazie
Erica
Ciao Erica,
ho capito, la puoi risolvere in vari modi. Il metodo più facile credo sia quello di utilizzare una terza colonna con la funzione SE nidificata e mettere la formattazione condizionale basandoti sul risultato della SE, questo esempio dovrebbe esserti utile.
In alternativa, dovresti scrivere una funzione se analoga però all’interno della formattazione condizionale personalizzata, il che è lievemente più complicato, questo esempio dovrebbe far al caso tuo
Ciao,
nel video sopra riportato spieghi benissimo come colorare l’intera RIGA tramte la formattazione condizionale… Bene, ti chiedo come devo fare per colorare l’intera COLONNA calcolando che all’inizio della colonna cè la data odierna… in attesa di un tuo riscontro ti ringrazio anticipatamente.
Enrico
Ciao Enrico,
ti ringrazio per avermi scritto. Esattamente come con la riga ma di traverso 😉
Buongiorno,
ho seguito le vostre indicazioni ma purtroppo non ottengo il risultato sperato. Ho una tabella dove nella colonna B ho inserito una convalida dati ad elenco da cui scegliere l’esito di un preventivo “ACCETTATO-SOSPESO-RIFIUTATO”, in base alla scelta vorrei che la riga si colorasse, rispettivamente, di verde, giallo o rosso. Ho selezionato l’intero foglio, dal menu Formattazione Condizionale ho inserito una nuova regola, utilizzando una formula per determinare il valore e ho inserito la dicitura =$b1 =”SOSPESO”, in riempimento ho scelto il giallo, in carattere ho scelto il nero. Dato l’ok le celle non subiscono modifiche. Vi prego aiutatemi!
Ciao Barbara,
Senza avere il foglio non saprei dire dove sia l’errore. Ti consiglio di controllare che non ci siano errori di battitura e spazzini iniziali e finali
Buongiorno, ho un foglio excell con dei dati relativi a dei pazienti devo fare in modo che dalla data inserita nella colonna dopo 21 settimane si evidenzi in rosso la riga intera.ho provato con la formattazione condizionale ma forse sbaglio qualcosa nella formula io ho scritto:
=$ER:$ER+147>OGGI()
grazie
Ciao Silvia,
beh dipende. Le settimane come le vuoi conteggiare? vuoi fare 21*7 oppure vuoi contare le settimane vere e proprie? Inoltre, in colonna ER hai una data espressa in formato data?
Salve , in un colendario mensile in colonna A e orari in colonna B-C-D , vorrei colorare le righe A-B-C-D riferenti alle domeniche e magari cancellando pure gli orari
Grazie
Ciao Alessio,
ci sono vari modi. Quello che ti consiglio è di prendere una colla vuota, ed inserirvi il giorno della settimana con una formula del tipo:
=TESTO(A:A;”gggg”)
oppure:
=TESTO(@A:A;”gggg”)
da qui puoi impostare facilmente una formattazione condizionale, per il testo “domenica”
Buongiorno.
Ho un foglio idi lavoro excell con una serie di date.
vorrei che le celle contenenti date più vecchie di un anno diventino rosse.
non riesco a impostare la formattazione condizionale.
grazie in antivipo
ciao Mario
il controllo lo hai impostato per anno? Prova a fare un controllo per +365 giorni
ho seguito alla lettera il vostro video ma inconsciamente ho incollato dati da un’altro file e mi ha tolto la formattazione e non riesco più a rimetterla. AIUTO …………..
Ciao Daniele,
non saprei. Riprova a fare tutto da capo
Buonasera,
Ho un quesito.
In una tabella ho 3 colonne. Colonna A (numero ordine), Colonna B (Data), Colonna C (Ente).
Nella colonna C inserisco via via l’ente che svolgerà l’ordine. L’ente si suddivide in 3 categorie, es: POP+città; ALFA+città, CIP+città.
Il mio obbiettivo è di colorare in 3 colori differenti le varie righe degli enti.
Grazie in anticipo!
Ciao Matteo, problema comune, devi solo inserire una formattazione condizionale con 3 regole diverse quindi 3 colori diversi
Ciao Matteo,
pur essendo il tuo esempio chiarissimo e pur seguendolo precisamente le tue indicazioni, non riesco a capire per quale motivo mi si colori la riga precedente rispetto a quella dove è contenuto il valore significativo/indicato nella formula tra virgolette.
Non trovo l’errore!!
Grazie in anticipo.
Ciao Giulia,
che riga hai messo? la 2? prova la 1
Buonasera Matteo, complimenti per il video!
E’ possibile impostare la formattazione condizionale su tutta la riga in base alla presenza di un testo (es. “TOTALE”) contenuto su una cella (es. “VINI TOTALE”)?
Grazie mille!
Ciao Giacomo, grazie per avermi scritto. Si, è possibile, devi solo creare una nuova regola> formata solo celle che contengono> (togli Valore, e metti) Testo specifico. Oppure puoi usare una formattazione personalizzata, ma è più complicato
Ciao Matteo,
una domanda: se creo una formattazione che comprende le celle A1:A10 e succesivamente copio e incollo una nuova riga prima della 5, perchè la regola di formattazione automaticamente mi esclude quest’ultima e il mio intervallo che inizialmente era A1:A10 ora è diventato A1:A4 + A6:A11?
Questo non avviene se invece di copiare e incollare, inserisco una nuova riga.
Esiste però un modo per “forzare” la formattazione in modo che essa venga applicata sia inserendo una nuova riga che copiandola e incollandola?
Gino, ottima osservazione, ma cosa vuoi che ti risponda? che non c’è questa feature? che non funziona così? non so, io che ci posso fare? Non inserire intervalli di cella ma imposta fin da subito la colonna A:A. Oppure imposta la formattazione condizionale solo dopo aver inserito tutto
Ciao
non riesco a colorare le righe intere riferite per es. ai 2 anni più inferiori (i due anni più “piccoli” usando la funzione PICCOLO) o per es. a una ipotetica colonna IMPORTI colorando le righe con i 2 importi più inferiori. Ho provato a usare dentro la formula nella formattazione condizionale le funzioni SE e PICCOLO ma con scarso successo.
Grazie. Saluti
Gianni, cosa intendi con “calcolare le righe intere”? che calcolo? sommare? contare? moltiplicare?
Salve,
Ho bisogno di aiuto, please.
Ho 2 colonne acquisto A e vendita B.
Colonna A sono targhe auto acquistate e colonna B sono targhe auto vendute, quando vendo voglio che targa (cella) su colonna A si cancelli con colore rosso e barrato.
Sono riuscita a farlo solo per cella B1 con formattazione condizionale:
formatta solo celle con testo specifico contiene =$B$1 Si applica a =$A$1:$AB$200
Se scrivo =B1 non funziona.
Come posso fare per riportarlo su tutta la colonna?
Grazie in anticipo, saluti
Salve,
CORREZIONE
Sono riuscita a farlo solo per cella B1 con formattazione condizionale:
formatta solo celle con testo specifico contiene =$B$1 Si applica a =$A$1:$AB$200
Se scrivo =B1 non funziona.
Come posso fare per riportarlo su tutta la colonna?
Grazie in anticipo, saluti
ottima domanda, la formattazione dovresti saperla impostare, ti occorre una formula personalizzata di questo tipo:
=CONTA.SE(B:B;A1)=1
su tutta la colonna A
salve. come posso colorare di un colore qualsiasi una cella che contenga un commento? a volte il tringolino rosso non basta. grazie mille
Ciao Francesco, ottima domanda. Che io sappia l’unico modo è il colorarla a mano
Ciao!
Come faccio se voglio colorare le celle quando il loro valore non è uguale a UNA cella ma è uguale a UNA TRA una serie di celle? Posso usare la funzione logica or? In che modo?
Grazie!
Ciao Marco,
hai varie scelte, una è ripetere la formattazione per i vari valori, se sono tanti non è praticabile.
Se sono molti puoi usare una conta se come funzione personalizzata, la scorsa settimana ho aggiornato il corso Excel avanzato inserendo proprio un esempio analogo
Buonasera avrei un quesito…Ho due colonne A e B.Sulla cella B1 ho impostato di divenire rossa se fosse minore di A1.Devo copiare la seguente formattazione condizionale su tutte le celle della colonna B nel seguente modo(B2 rossa se inferiore A2,B3 rossa se inferiore a C3,….).Con copia formato non ci sono riuscita perché?Chi mi aiuta?Grazie!
Ciao Cecilia, non ho capito cosa tu voglia fare. Non mi pare nulla di difficile, hai provato a fare il semplice copia incolla?
buongiorno
mi scuso se la domanda è ingenua….
perché se seleziono un intera colonna che contiene testo e numeri quando applico una formattazione condizionale per colorare lo sfondo delle celle contenenti numeri maggiori di un certo valore, si colorano anche le celle contenenti il testo?
grazie mille
ciao Ezio, ottima domanda, perché bugga
Prova con una formula personalizzata
Ciao Matteo,
ho un file exel nel quale inserendo una data qualsiasi in una cella deve colorarsi tutte la riga.
Grazie
Buongiorno,
Mi chiedo se sia possibile gestire con semplicità una mia necessità legata alla formattazione condizionale con regole personalizzate.
Parto spiegando la costruzione del file.
Si tratta di un foglio in cui vengono inseriti sia informazioni (testi) che valori numerici.
La singola riga è quindi costituita da “informazione” (es. colonna A) e poi vari dati numerici (es. colonne C, D, E, ecc., ma non con intervallo continuo). Il tutto ripetuto su più righe.
La formattazione che cerco di inserire dovrebbe seguire la seguente logica:
Se viene indicato una dicitura specifica nella colonna A, e i valori delle colonne B, C, D (ecc.) é (esempio) >= di 25, allora Testo colore Verde, se = e una per =25) _e indico la formattazione “colore testo verde”
=SE(A1=”parola”; B1<25) _e indico la formattazione "colore testo rosso"
Fino a qui, nessun problema!
Ora però, vorrei applicare la medesima formattazione anche anche ad altre celle, sia appartenenti alla stessa riga, ma colonne diverse, sia a righe e colonne diverse.
Dove, il primo valore di "convalida" (cella A1) è sempre posizionato nella medesima Colonna (A), e si riferisce, per ogni riga, ai valori della colonna riferiti alla riga stessa (A1 per tutti i valori di riga 1 nelle colonne es. B, C, D. ecc.), mentre i valori (che sono soggetti alla formattazione condizionale), è riporto in diverse colonne, ma sempre riferendosi ovviamente alla medesima riga di cui il valore "convalida".
Trattandosi di un file di oltre 800 righe, e circa 250 valori (differenti colonne) interessati per singola riga, vorrei capire se c'è la possibilità di applicare automaticamente la formattazione condizionale, o se dovrebbe eventualmente essere fatta a mano per singola cella.
Spero di essere stato chiaro (e che esista una soluzione che non preveda l'inserimento manuale di oltre 400k formattazioni.. )
Grazie mille per il supporto!