La formattazione condizionale cambia l'aspetto di una cella in base al suo contenuto: rosso se un valore è negativo, verde se un obiettivo è raggiunto. Le regole predefinite (maggiore di, tra, primi dieci, barre dei dati) bastano per i casi semplici. Quando però vuoi colorare un'intera riga in base a una sola colonna, o confrontare valori su colonne diverse, serve l'opzione basata su formula. È lì che la formattazione condizionale diventa davvero utile.
In Excel la trovi in Home, Formattazione condizionale, Nuova regola, «Utilizza una formula per determinare le celle da formattare». In LibreOffice Calc in Formato, Condizionale, con la condizione «La formula è». In Google Sheets in Formato, Formattazione condizionale, scegliendo «La formula personalizzata è».
Come ragiona il programma
La formula di una regola deve restituire VERO o FALSO. Il programma la valuta per ogni cella dell'intervallo selezionato, adattando i riferimenti esattamente come se copiasse la formula in ciascuna cella. Quando il risultato è VERO, applica il formato.
Ne deriva la regola d'oro: scrivi la formula pensando alla prima cella in alto a sinistra della selezione. Se selezioni A2:F200, la formula deve essere corretta per A2; il programma penserà al resto.
Evidenziare righe intere
Supponi di avere un elenco di fatture in A2:F200, con lo stato nella colonna E. Vuoi colorare l'intera riga quando lo stato è «Insoluta».
- Seleziona A2:F200, partendo da A2.
- Crea una regola con formula:
=$E2="Insoluta". - Scegli il formato, per esempio sfondo rosso chiaro.
Il dollaro davanti alla E è il punto chiave. Blocca la colonna, così ogni cella della riga, da A a F, guarda sempre la colonna E. Il numero di riga invece resta relativo, così la riga 3 guarda E3, la riga 4 guarda E4 e così via. Senza il dollaro, la cella B2 guarderebbe F2, la cella C2 guarderebbe G2, e la regola funzionerebbe solo nella prima colonna.
Esempi utili
Scadenze
Con la data di scadenza in D e una colonna di stato in E:
- scadute e non pagate:
=E($D2<OGGI(); $E2<>"Pagata"); - in scadenza nei prossimi sette giorni:
=E($D2>=OGGI(); $D2<=OGGI()+7).
Poiché OGGI() si aggiorna ogni giorno, il foglio si ricolora da solo senza interventi.
Duplicati su più colonne
La regola predefinita per i duplicati considera una colonna sola. Per segnalare le righe in cui la combinazione di nome e data di nascita si ripete:
=CONTA.PIÙ.SE($A$2:$A$500; $A2; $B$2:$B$500; $B2)>1
Righe alternate
Per una lettura più comoda delle tabelle lunghe, una riga sì e una no: =RESTO(RIF.RIGA(); 2)=0. A differenza dello sfondo applicato a mano, resta corretto anche quando ordini o filtri i dati.
Confronto tra due colonne
Per evidenziare i valori della colonna B che differiscono dalla colonna C sulla stessa riga: =$B2<>$C2. È un modo rapido per controllare due versioni di un listino affiancate.
Valore assente in un altro elenco
Per segnalare i codici di A che non compaiono nell'elenco di un altro foglio:
=CONTA.SE(Anagrafica!$A:$A; $A2)=0
In alcune versioni di Excel le regole non accettano riferimenti diretti ad altri fogli; in quel caso si assegna un nome all'intervallo e si usa il nome nella formula.
L'ordine delle regole
Una cella può essere soggetta a più regole. Il programma le valuta nell'ordine in cui compaiono nel gestore delle regole e, se due regole impostano lo stesso attributo (per esempio il colore di sfondo), vince quella più in alto. In Excel è disponibile anche l'opzione «Interrompi se vera», che evita di valutare le regole successive.
Un ordine sensato è dal più grave al meno grave: prima «scaduta», poi «in scadenza», poi le righe alternate.
Errori frequenti
- Selezione che non parte dalla riga della formula. Se selezioni da A1 ma scrivi la formula per la riga 2, tutto risulta sfasato di una riga.
- Dollaro nel posto sbagliato.
=E$2="Insoluta"blocca la riga anziché la colonna: tutte le righe guardano E2. - Virgolette e maiuscole. Il confronto
="Insoluta"non distingue maiuscole e minuscole, ma è sensibile agli spazi: «Insoluta » con lo spazio finale non viene riconosciuta. - Troppe regole sovrapposte. Copiare e incollare celle formattate moltiplica le regole e rallenta i file grandi. Ogni tanto apri il gestore delle regole e consolida.
Non esagerare con i colori
Nel mio lavoro uso la formattazione condizionale per richiamare l'attenzione su ciò che richiede un'azione, non per decorare. Due o tre colori con un significato chiaro funzionano meglio di un arcobaleno: se tutto è evidenziato, niente lo è davvero. Ricorda anche che non tutti distinguono bene il rosso dal verde: abbinare al colore un'icona o il grassetto rende il messaggio leggibile per chiunque.
In sintesi
- La regola con formula deve restituire VERO o FALSO e va scritta per la prima cella della selezione.
- Per colorare righe intere blocca la colonna di controllo con il dollaro:
=$E2="Insoluta". - OGGI() rende dinamiche le regole sulle scadenze.
- Con CONTA.SE e CONTA.PIÙ.SE segnali duplicati e valori mancanti.
- Controlla l'ordine delle regole e usa pochi colori con un significato preciso.
