Uso avanzato della formattazione condizionale di Excel

  Oggi torniamo a parlare di formattazione condizionale, di come possiamo usarla per rendere le nostre tabelle più comunicative e per installare allarmi e controlli, e dell’uso avanzato della formattazione condizionale in Excel. Ma prima un breve riassunto delle basi.

Cos’è la formattazione condizionale di Excel

La formattazione condizionale di Excel è uno strumento comodo ed efficiente, con cui possiamo installare una formattazione dinamica alle tabelle e alle pagine dei tuoi fogli Excel.

Cosa intendo con formattazione “dinamica”?

Intendo una formattazione che si modifica in presenza di specifiche condizioni.

Questo permette di mostrare formattazioni alternative a quelle impostate, se il valore della cella rispetta determinate condizioni secondo regole preimpostate, o se una formula restituisce valore vero.

Cosa puoi fare con la formattazione condizionale di Excel?

Come abbiamo già visto, la formattazione condizionale di Excel ti permette di evidenziare:

1. i contenuti delle celle in modo da comunicare meglio i valori contenuti, anche con oggetti grafici e in modo professionale
2. solo celle precise, il cui contenuto rispetta condizioni precise (es: che contengono valori minori di zero),
3. o secondo regole meno semplici e intuitive (valori duplicati, primi N valori, ecc),
4. ma anche celle secondo condizioni che valutano i valori di altre celle della tabella,
5. e più in generale secondo i risultati di formule e funzioni.

Quindi la formattazione condizionale di Excel permette di assegnare formattazioni dinamiche secondo regole o condizioni personalizzabili, allo scopo di evidenziare tutte o alcune delle celle in cui viene installato lo strumento.

Per quali obiettivi si usa la formattazione condizionale di Excel?

Di solito la formattazione condizionale di Excel si usa per:

1. rendere più leggibile e comunicativa una tabella (evidenziando i valori con colori ed elementi grafici e perfino aggiungendo intere colonne di elementi grafici;
2. eseguire controlli automatici evidenziando celle e record con valori o stati non desiderati o da gestire (valori duplicati, celle vuote, valori zero, errori, valori fuori range, ecc);
3. evidenziare record e valori che dobbiamo gestire per poterli individuare o filtrare velocemente (esempio: commesse aperte da seguire, oppure ordini ancora da elaborare, ecc).

Come funziona la formattazione condizionale di Excel – Sintesi

La formattazione condizionale quindi è automazione che si applica a una o più celle ed è composta da due elementi:

1. le condizioni in presenza della quali viene fatto comparire la formattazione dinamica
2. e la formattazione da applicare quando le condizioni compaiono.

Come detto in precedenza i menu offrono:

1. una serie di formattazioni preimpostate divisi in cinque sottomenu,
2. oltre ai comandi per accedere alle finestre classiche per l’impostazione “manuale”.

Dove si trovano i comandi della formattazione condizionale di Excel?

I comandi della formattazione condizionale di Excel si trovano:

1. nella scheda “Home” della barra multifunzione,
2. ma sono accessibili in modo più efficiente, direttamente sulla tabella, attraverso l’“Analisi rapida”.

Se non conosci l’analisi rapida, in sintesi è una finestra richiamabile tramite l’icona che compare alla selezione di intervalli di celle, che porta numerosi strumenti a portata di clic direttamente nella pagina, anzi sulla tabella, tra cui anche la formattazione condizionale. Per approfondire l’argomento ti invito a guardare questo tutorial.

Invece il classico menu della formattazione condizionale lo trovi nella scheda Home della barra multifunzione e presenta una serie di menu di condizioni preimpostate applicabili con qualche clic.

Ma offre anche la possibilità di personalizzare finemente sia la formattazione che le condizioni tramite le finestre dedicate.

Le formattazioni preimpostate

Questi sono i comandi preimpostati nei menu:

1. Il primo menu della formattazione “Regole evidenziazione celle” propone regole preimpostate classiche, come: “Maggiore di”, “Minore di”, “Compreso tra”, “Uguale a”, eccetera che impostano regole di attivazione della formattazione se il valore delle celle è appunto maggiore di un valore, uguale a un valore, eccetera. Regola molto utile è quella che evidenzia i duplicati o in alternativa i valori univoci.
2. Il secondo menu invece è quello “Regole primi/ultimi” che propone regole di applicazione della formattazione meno convenzionali, relativi a interi campi di valori numerici. Per esempio permette di evidenziare i “primi 10 elementi” del campo, o gli ultimi 10, come i valori del “primo 10%” come dell’ultimo. Naturalmente il numero di elementi o la percentuale possono essere personalizzati a piacere. Ma troviamo anche “Sopra la media” e “Sotto la media” che permette di evidenziare le celle con i valori sopra o sotto la media, o varianti (per esempio sopra o sotto di 2 deviazioni standard).

Barre, icone e scale dei colori

3. Il terzo menu è quello delle barre dei dati ed è forse il più utilizzato. Permette l’installazione nelle celle di barre colorate orizzontali la cui dimensione rappresenta il valore della cella. Le barre sono un ottimo modo per mostrare a colpo d’occhio i valori maggiori o minori di una serie numerica, senza dover spendere tempo a leggere i valori. È possibile usarlo in tutti i campi numerici importanti delle tabelle di elaborazione e come visualizzazione sostitutiva dei grafici a barre nascondendo i valori.
4. Invece il quarto menu propone le scale dei colori, cioè offre una serie di regole preimpostate che colorano le celle di campi e serie numeriche di colori e toni diversi a seconda dei valori relativi. Quindi abbiamo una scala di verde o rosso, come scale bicolori, verde–rosso, verde–giallo, verde–blu, e anche tricolori. Naturalmente questo permette di evidenziare i valori maggiori o minori di colori diversi e contrastanti in modo da identificarli velocemente.
5. Il quinto menu invece permette di installare set di icone configurabili che cambiano colore o orientamento a seconda dei valori. Tra le icone troviamo frecce, semafori, scale, bandiere e altro ancora. Possono essere utili in diversi modi, per esempio per mostrare a colpo d’occhio un avvenimento o uno stato, come il ritardo di una commessa in lavorazione, o il superamento di un target di vendite, i ritardi nelle consegne, eccetera.

Come impostare regole personalizzate

Le regole possono essere impostate passo passo usando il comando “nuova regola” presente nel menu. Si aprirà la finestra omonima che offre un’ampia scelta di opzioni e impostazioni con cui è possibile realizzare centinaia di regole diverse.

Nella metà superiore della finestra trovi i tipi di regole tra cui scegliere:

1. formatta tutte le celle in base ai relativi valori,
2. formatta solo le celle che contengono,
3. formatta solo i primi o gli ultimi valori,
4. formatta solo i valori superiori o inferiori alla media,
5. formatta solo i valori univoci o duplicati,
6. utilizza una formula per determinare le celle da formattare.

Buona parte delle regole corrisponde ad alcune delle regole preimpostate descritte in precedenza. Per esempio la regola 1 è quella di solito impiegata per applicare scale di colore o barre colorate autocalcolate tra il valore minimo e massimo e permette di personalizzarle in modo accurato.

La regola 3 è quella impiegata per individuare ed evidenziare i primi o gli ultimi N valori del campo selezionato. La 4 serve a evidenziare i valori superiori o inferiori alla media, soluzione usata in ambiti limitati, e la 5 viene usata per evidenziare i valori duplicati o univoci.

Scegli la formattazione e poi modifica le regole

Anche se può apparire controintuitivo, il modo più efficiente per impostare le formattazioni precedenti è quello di usare quelle preimpostate per poi modificarle.

Quindi

1. scegli la formattazione che preferisci tra quelle preimpostate che più si avvicina al tuo obiettivo,
2. crea la regola
3. e poi torna a modificarla con il comando “gestisci regole”.

Questo apre la finestra con le regole applicate alla cella o alle celle selezionate che possiamo modificare anche in modo radicale con il comando “modifica regola”.

Le regole che si usano di solito

Le regole che si usano di solito sono il tipo 2, “formatta solo le celle che contengono”, che permette di impostare la formattazione secondo il valore presente nella cella, secondo regole logiche come minore, maggiore, uguale, tra, eccetera.

La seconda regola più usata è il tipo 6, cioè una regola basata sulle formule. In cosa consiste? In breve possiamo inserire come regola una formula e quando questa restituisce un valore “vero”, allora la formattazione impostata verrà applicata.

Questa possibilità offre una gamma di possibilità impressionante.

Formattazione condizionale con le formule

L’utilizzo di una formula permette di cambiare prospettiva alla formattazione condizionale. Per esempio possiamo evidenziare l’intero record basandosi sul valore delle celle di una colonna precisa della tabella (es: lo stato di avanzamento delle commesse).

Ma una formula, attraverso l’uso dei riferimenti, permette di puntare a più celle e valori, secondo condizioni anche complesse.

Per essere più chiari:

1. permette di collegare la formattazione al valore di una o più celle dello stesso record,
2. o a una o più celle di record diversi, o di tutti i record della tabella,
3. come a celle al di fuori della tabella, anche in altre tabelle.

Queste celle esterne possono essere compilate da formule e funzioni anche complesse, come da query o dall’inserimento diretto o indiretto di altri dati. Possono essere anche celle di maschere e controlli su pagina impostabili dall’utente.

E quest’ultima possibilità permette di controllare la formattazione condizionale di celle, record, interi campi, intervalli o tabelle, attraverso valori impostati su pagina senza dover modificare le formattazioni installate.

E molto altro ancora. Il vero potenziale della formattazione condizionale di Excel emerge quando viene combinata con formule e funzioni.

Come si usa la formattazione condizionale di Excel in pratica

In generale la formattazione condizionale si usa in questo modo:

1. si definisce il bisogno e l’obiettivo,
2. se possibile si applica la formattazione preimpostata che corrisponde, o più si avvicina all’obiettivo,

2a. oppure si imposta la formattazione condizionale da zero, usando le regole adatto o usando le formule,

3. se necessario si modifica per ottenere il risultato voluto,
4. si testa ed eventualmente si revisiona.

Esempi basilari di formattazioni condizionali con le formule

Per esempio, se vogliamo evidenziare le date che corrispondono alla domenica possiamo usare formule come “=Giorno.settimana(RIF, 2)=7”, dove RIF è la cella con la data, due è il conteggio dei giorni della settimana e la comparazione “=7” serve a restituire Vero o Falso.

Oppure se vogliamo individuare assenza di valori o presenza di valori testuali in un campo numerico, possiamo usare la funzione =Val.Testo(RIF), che restituisce vero se il valore indicato è un testo, oppure la funzione =Val.Vuoto(RIF) che restituisce vero se la cella indicata non ha valore.

Altrimenti, se vuoi evidenziare i valori duplicati di una colonna con una formula, puoi farlo in questo modo: “=CONTA.SE($A:$A;$A2)>1”. In pratica la formula restituisce vero se nella colonna c’è uno o più valori uguali.

E se invece vuoi evidenziare i valori unici? Quelli che compaiono una sola volta? Puoi usare questa formula “=CONTA.SE($B:$B;$B2)=1”.

Formattazioni condizionali multiple

Un aspetto che è importante chiarire è che possiamo impostare più formattazioni condizionali sulla stessa cella o intervallo, cioè possiamo aggiungere più regole che controllano formattazioni diverse o alternative. Possiamo farlo usando regole di qualsiasi tipo, anche basate su formule, semplice ma anche complesse, cioè che per esempio controllano più condizioni per restituire un valore vero.

Perché usare più regole?

Può essere molto utile in diverse situazioni, per esempio per:

1. evidenziare stati diversi,
2. controlli multipli esclusivi o alternativi,
3. evidenziazioni e controlli insieme,
4. visualizzazioni dinamiche e controlli.

Un esempio classico è impostare nel campo Fatturato della nostra tabella, una formattazione con le barre per evidenziare gli importi più grandi e più piccoli, e alcune per evidenziare con il colore di fondo le fatture non pagate, in scadenza e scadute.

Un altro è impostare nel campo “Quantità” della base dati controlli multipli su: valori nulli, formati diversi (es: testi), valori eccessivamente grandi (es: maggiori della media di 3 volte la deviazione standard), eccetera.

Un altro esempio è l’uso della formattazione condizionale in un gantt per evidenziare le celle del calendario con formattazioni diverse a seconda di stato e avanzamento (programmazione, esecuzione, ritardi, sospensioni, eccetera).

Formattazioni condizionali con condizioni multiple

Negli esempi di formule per la formattazione condizionale visti sopra, abbiamo visto singole condizioni, ma nella maggior parte dei casi in cui vengono impiegate, formule, è comune dover usare condizioni multiple correlate.

Per esempio per controllare che la formattazione si attivi se il record è compilato, o controlli che ci sia l’importo pagato, eccetera.

Come si impostano condizioni multiple?

Per impostare formule a condizioni multiple di solito si usano le funzioni E(), O(), per connettere le condizioni tra di loro.

Un esempio semplice è “=O(VAL.ERRORE(RIF); RIF=0)”. La formula risulta vera se il valore della cella RIF contiene un errore o contiene un valore 0.

Nello stesso modo possiamo concatenare numerose condizioni multiple anche alternative e quindi realizzare formule molto complesse che verificano molte condizioni per restituire la formattazione impostata.

Progettare formattazioni condizionali avanzate

Quando l’obiettivo è complesso, per esempio perché riguarda valori in celle esterne, condizioni multiple o alternative, eccetera, allora è necessario progettare con cura le regole che dobbiamo impostare e le formule necessarie, oltre che la formattazione da impiegare.

Tutto parte dall’obiettivo, che ti invito a scrivere o a esprimere a parole, per poter valutare con precisione i suoi elementi:

1. le celle in cui verrà installata la formattazione condizionale,
2. le condizioni delle regole,
3. a quali celle si appoggeranno le regole
4. se è necessario o opportuno aggiungere campi calcolati,
5. se è necessario usare una formula, quale formula, quali funzioni e come devono essere impostate,
6. quale deve essere la formattazione condizionale da aggiungere per raggiungere l’obiettivo.

A seconda dello scopo, questa procedura può richiedere decine di secondi come decine di minuti.

L’uso della formattazione condizionale avanzata per rendere più leggibili e comunicative le tabelle.

Quando si deve rendere comunicativa una tabella è raro aver bisogno di usare formattazioni condizionali avanzate, perché di solito le formattazioni preimpostate, come barre colorate, icone e scale di colori, sono sufficienti a evidenziare valori e campi per migliorarne la leggibilità.

Ti ricordo che rendere leggibile una tabella non significa evidenziare valori precisi che devono essere oggetto di controllo.

Quando la complessità è nei numeri, per esempio per visualizzare l’avanzamento di una commessa, di solito si preferisce installare campi calcolati per restituire percentuali a cui possiamo collegare icone come semafori per evidenziare i risultati ottenuti e i risultati inattesi o non desiderati.

Talvolta è opportuno basarsi non solo sul valore della cella oggetto della formattazione, ma anche su altri valori di altri campi della tabella o su valori esterni o di altre tabelle, oppure talvolta è opportuno evidenziare l’intero record della tabella. In questi casi e in altri è necessario installare formattazioni condizionali basate su una formula come anticipato in precedenza.

Come si evidenziano più celle o l’intero record della tabella?

Evidenziare le celle della colonna che contiene il valore a cui la formattazione condizionale di deve riferire è semplice, basta utilizzare le regole base, ma per tutte le celle delle altre colonne è necessario riferirsi alle celle della colonna con il valore. Quindi possiamo usare una sola regola per tutte.

Il primo passo è definire la regola, per esempio: “se il valore della colonna “importo” supera i centomila”, che puoi scrivere subito anche in una cella della pagina di excel come: “=$G7>100000”. In questo caso la regola è semplice e la difficoltà consiste nell’indicazione corretta del riferimento “=$G7”.

Perché ho scritto “$G7”? Perché G è la colonna con i valori di riferimento da controllare, mentre 7 è la prima riga delle celle che selezionerò e la prima riga della tabella.

Ti faccio notare il $ davanti a G, che dice all’automazione di excel di usare sempre la colonna G nei controlli di tutte le celle di tutte le colonne selezionate. Se non metti il $, le celle di ogni colonna punteranno alla colonna relativa rispetto alla selezione, quindi probabilmente verrà evidenziata solo una colonna della selezione o solo alcune colonne che puntano a colonne con importi.

Ora imposta la formattazione con la formula

Il passo successivo è impostare la formattazione condizionale:

1. seleziona le colonne della tabella che vuoi evidenziare, dalla prima riga all’ultima,

(in alternativa puoi selezionare alcuni record e solo dopo vai a modificare il riferimento che indica alla formattazione su quali celle deve essere installata l’automazione)

2. vai alla scheda “home” della barra multifunzione, clicca su “formattazione condizionale” per aprire il menu e clicca sul comando “nuova regola” per aprire la finestra omonima,
3. nella metà superiore della finestra seleziona “utilizza una formula …” per richiamare nella metà inferiori i controlli relativi
4. inserisci la formula nella casella di testo,
5. clicca sul pulsante “formato” per aprire la finestra “formato celle” e imposta la formattazione che vuoi compaia se la regola viene individuata (es: fondo giallo e testo grassetto)
6. conferma con “ok” fino a chiudere la finestra e controlla il risultato.

Esempi più complessi

Se la regola per l’evidenziazione dell’intero record è complessa, per esempio perché le condizioni sono molteplici, è necessario impostare la formula di conseguenza.

Per esempio: “=O(E($C2<OGGI();$D2<>”Evaso”);$E2>0))” serve a evidenziare gli ordini scaduti non evasi, o gli ordini con quantità di prodotti non evase.

Oppure: “=O($F2=”Critico”;$F2=”Bloccato”)”, che evidenzia gli stati degli ordini di vendita.

O:  “=$G2>MEDIA($G:$G)*1,3” evidenzia i valori superiori al 30% della media dei dati registrati.

Altrimenti se vogliamo evidenziare i record con colori diversi a seconda dello stato di avanzamento dei progetti che vogliamo gestire, possiamo usare formule come questa: “=E($B7<>””;$F7>0;O($H7<Oggi();$L7>0))” che assegnano un colore alle celle controllando la presenza di dati nei campi B ed F, la data del campo H, tipicamente una scadenza, e l’inserimento di una data nel campo L.

Più regole e più formattazioni

Queste formule naturalmente valgono per una regola e un colore, ma se hai più regole e quindi il bisogno di far comparire sulle stesse celle più colori, allora devi impostare più formattazioni condizionali.

Il modo più semplice per farlo è questo:

1. moltiplicare le regole,
2. modificare le regole duplicate,
3. e modificare le formattazioni che vuoi inserire.

Se hai le idee chiare, puoi fare questo in modo semplice e veloce:

1. seleziona le celle,
2. richiama la finestra tramite comando “gestisci regole”,
3. duplica la regola con il comando apposito della finestra di gestione delle regole,
4. seleziona la regola duplicata e cliccare sul pulsante “modifica” per richiamare la finestra di impostazione
5. con cui puoi modificare la regola o sostituirla con quella alternativa e modificare la formattazione selezionando un colore diverso o quello che hai deciso.

Come si progettano le formattazioni condizionali multiple

Abbiamo già visto in precedenza che se usiamo le formule è opportuno progettare e impostarle su pagine per poi copiarle come regole. Quando le formattazioni sono multiple è necessario che tu ci ragioni con calma e le progetti prima di installare alcunché.

Questo significa definire un obiettivo preciso e particolareggiato, per poi tradurlo in regole e formattazioni.

Per esempio, vogliamo evidenziare le righe delle commesse per comunicarne lo stato a colpo d’occhio, per questo:

1. se lo stato è uguale a “Da iniziare”, lo sfondo deve essere giallo, regola 1,
2. se lo stato è uguale a “In lavorazione”, lo sfondo deve essere azzurro, regola 2,
3. se lo stato è uguale a “In ritardo”, lo sfondo deve essere rosso e il testo bianco, regola 3,
4. se lo stato è uguale a “Conclusa”, lo sfondo deve essere verde, regola 4.

Campi calcolati e formattazione condizionale

Se le regole sono molte e le condizioni complesse, spesso si preferisce inserire un campo calcolato nella tabella, di solito in fondo a destra, con una formula che attraverso la funzione SE() o PIU’.SE() esegua i controlli necessari per restituire “stati” precisi o valori precisi su cui impostare formattazioni condizionali più semplici.

Intendo formattazioni condizionali che si basano su formule tipo “=$AF7=“InizioProgettazione””, dove cambia la stringa secondo quello che il campo calcolato in precedenza restituisce.

Priorità delle regole

Le regole vengono controllate in modo gerarchico, dall’alto verso il basso, e se le condizioni tra 2 o più regole sono sovrapponibili, possiamo posizionare le regole più importanti in cima alla lista. Le regole più in alto verranno applicate per prime.

Inoltre avrai notato che la finestra di gestione della formattazione condizionale offre la possibilità di spuntare “Interrompi se vero”, allo scopo di bloccare l’esecuzione delle regole sottostanti. Questo è utile se non necessario se vogliamo che le formattazioni vengono eseguite in modo gerarchico, cioè le formattazioni sottostanti vengono considerate solo se quelle superiori non risultano vere.

Questo consente per esempio di modellare allarmi o controlli a livelli, dove la criticità maggiore annulla gli altri allarmi.

L’uso della formattazione condizionale avanzata per eseguire controlli automatici

Laddove abbiamo bisogno di usare la formattazione condizionale come controllo per evidenziare valori e record che richiedono la nostra attenzione, il metodo è analogo. Quello che cambia di solito è la complessità delle formule che deve sempre restituire vero o falso.

Per esempio possiamo usare una formula come questa: “=E(O($J7<$L$1-3*$K$1; $J7>$L$1+3*$K$1); CONTA.VALORI($B7:$B1000)>$M$1)” per individuare i valori anomali oltre 3 volte la deviazione standard dalla media. La formula in questione si appoggia a celle esterne alla tabella ($L$1; $K$1; $M$1) che contengono formule che calcolano media, deviazione standard e il numero minimo di record ammissibili.

Come già visto in precedenza, le formule della formattazione condizionale possono essere semplificate se si installa un campo calcolato con una formula condizionale che restituisce uno o più valori a seconda del controllo.

Soprattutto se la tabella è grande, si preferisce accompagnare la formattazione a un campo calcolato per facilitare la consultazione dei dati, per esempio per installare filtri su pagina (slicers) con cui filtrare i record da verificare con uno o pochi clic, ma anche per posizionare allarmi nell’intestazione fissa della pagina o in un report dedicato tramite funzioni come conta.se e similiari.

Riferimenti a controlli su pagina

Usare le formule nella formattazione condizionale ci dà la possibilità di modificare i parametri e i valori previsti nelle formule senza dover modificare la formattazione condizionale, ma inserendo valori diversi nelle celle indicate, magari tramite elenchi o controlli su pagina.

Questo è utile per una gestione “furba” e più efficiente delle formattazioni, in molti casi, soprattutto quando usiamo la formattazione condizionale come allarme o controllo.

Per esempio, possiamo impostare velocemente “soglie” numeriche diverse, allo scopo di colorare celle o record da controllare. Oppure escludere o includere dalla formattazione stati o categorie in modo veloce e semplice.

Esempi di formattazioni condizionali su date e scadenze

Spesso le formattazioni condizionali sono usate per evidenziare ritardi o scadenze, per esempio con la formula “=$G7<OGGI()” si possono evidenziare date passate. Di solito però è opportuno impostare la formula con 2 o più condizioni:

1. una per il controllo effettivo rispetto a una data precisa come OGGI() o OGGI()-Ngiorni, il trigger della formattazione,
2. una che disattiva la formattazione, per esempio dopo il pagamento dell’importo o dopo un certo numero di giorni (es: G7<OGGI()+Ngiorni), il blocco della formattazione,
3. ed eventualmente uno o più condizioni accessorie come la presenza di uno stato, valore o data che devono far parte del trigger o indicano condizioni alternative.

Detto questo quando si inseriscono formattazioni legate a date e scadenze è opportuno chiarire subito le soglie e gli intervalli che vogliamo applicare. Per esempio, se vogliamo segnalare l’incombere di una scadenza è opportuno definire quanti giorni prima inizia deve iniziare a segnalare e quale deve essere il termine della segnalazione (es: G7<OGGI+1)

Esempio pratico: controllo ordini di acquisto

Per controllare gli ordini di acquisto della tua attività, puoi usare una tabella pivot che mostra i dati rilevanti dell’ordine e del relativo avanzamento. E puoi usare la formattazione condizionale per evidenziare gli stati dell’ordine, in particolare quelli indesiderati (ritardi, resi, errori, ecc).

Per esempio, puoi impostare un sistema di formattazione condizionale avanzato che evidenzia le righe della pivot:

1. in rosso e testo bianco se la consegna è in ritardo o se non tutto il materiale è stato consegnato entro la data prevista;
2. in arancio per le consegne con problemi (errori, resi, rimborsi, nuove consegne, ecc);
3. in giallo se mancano 3 giorni o meno alla scadenza;
4. in verde se lo stato restituito è “Completo”;
5. oltre che barre colorate in azzurro chiaro per evidenziare gli importi;
6. e icone a semaforo in un duplicato della colonna stato senza i valori per indicare a colpo d’occhio la situazione dell’ordine.

Questo impiego della formattazione rende la tabella di controllo dell’avanzamento degli ordini più parlante e leggibile, aggiungendo un livello visuale che semplifica l’individuazione dei record da gestire.

Esempio pratico: CRM

Negli strumenti per la gestione dei contatti con i clienti, la formattazione condizionale aiuta a gestire priorità e follow-up senza perdersi tra le centinaia di righe registrate ogni giorno.

Per controllare l’avanzamento delle opportunità oltre che le attività da svolgere, si usano tabelle pivot filtrate e formattazioni condizionali che evidenziano le priorità. Ma anche stati indesiderati da gestire come:

1. ritardi nei contatti previsti con lead ad alta priorità, evidenziabili facilmente con formattazioni condizioni multicondizione (es: “=E($D2=”Alta”; O($F2=”Aperto”;$F2=”In corso”); $E2<OGGI()-7)”)
2. clienti senza attività recente, da ricontattare, evidenziati tramite formattazione condizionale nella colonna “giorni dall’ultima attività”.

Esempio pratico: Controllo vendite

Un altro esempio classico di applicazione profittevole della formattazione condizionale è nel controllo delle vendite, in cui è necessario tenere sotto controllo performance e scostamenti rispetto al budget.

Anche in questo caso una tabella pivot filtrata sul periodo è la soluzione consigliata, per esempio per evidenziare i commerciali con vendite sotto il 90% del target (es: =$E2<$F2*0,9).

Ed eventualmente aggiungere icone a semaforo nell’icona della differenza % che mostra il rosso per valori sotto il 90%, giallo tra il 90 e il 100% e verde se è maggiore del 100%.

Consigli utili sull’uso della formattazione condizionale

1. definisci e usa standard di formattazione

Il primo consiglio è definire e usare standard per le formattazioni soprattutto se più persone lavorano sugli stessi file o li consultano. Questo per non dar adito a incomprensioni o errate interpretazioni.

In pratica questo significa usare:

1. gli stessi tipi di formattazione (es: barre per gli importi, icone per le %, colore di fondo e colore di testo per stati ed allarmi, ecc),
2. sempre gli stessi colori per lo stesso significato (es. rosso = ritardo, giallo = attenzione, verde = tutto bene); puoi usare il grigio per rendere meno leggibili valori secondari e far risaltare gli altri colori.
3. Evita di colorare “troppo”

Se i colori sono troppi, niente è davvero in evidenza. Evidenzia solo ciò che è necessario, se anche in questo caso ci sono troppi colori, c’è qualcosa che non va nei processi o nelle procedure.

3. rendi chiaro il significato della formattazione

Questo significa di solito inserire una legenda, di solito in alto nel foglio, preferibilmente in intestazione fissa, che spiega i colori presenti nella tabella.

Se hai difficoltà a inserire la legenda nelle celle della pagina, puoi crearne una basculante usando forme rettangolari e caselle di testo. Puoi anche inserirla nelle istruzioni d’uso se sono presenti.

L’obiettivo è che un utente, consultando la tabella, capisca in pochi secondi il significato dei colori e dove guardare: righe rosse significa intervento immediato, righe gialle significa controllo con priorità.

4. Formattazione condizionale e prestazioni

La formattazione condizionale è automazione che installiamo nelle celle e richiede risorse per poter restituire quanto desideri, quindi se la utilizzi in tabelle di dati di centinaia di migliaia di celle, inizierà a pesare sulle prestazioni del tuo foglio.

Questo in particolare se usi formule complesse e regole multiple per le stesse celle. Quindi presta attenzione a come la usi, non abusarne, ma applicala solo dove necessario. E se revisioni i tuoi fogli dedica qualche minuto a rimuovere le formattazioni inutilizzate e revisionare quelle utili.

Conclusioni

Abbiamo visto che la formattazione condizionale di Excel è uno strumento utile che ci permette di evidenziare campi, celle o tabelle in modo dinamico ed automatico. O di eseguire controlli automatici a responso visivo con cui possiamo individuare o sottolineare valori specifici o anomali. O creare visualizzazioni dinamiche che cambiano a seconda dei valori delle cello o di celle esterne.

Quindi la formattazione condizionale di Excel diventa uno strumento fondamentale in molte situazioni dove l’aspetto di report e tabelle è importante per comunicare meglio le informazioni (oltre che la professionalità dell’autore o dell’azienda).

Come per esempio in report, dashboard o negli strumenti di gestione che visualizzano stati, eseguono controlli, comunicano traguardi e allarmi.

Chi usa Excel deve conoscere questo strumento, ma chi usa Excel in ambito aziendale o professionale deve saperlo padroneggiare per ottenere le soluzioni migliori nel modo più efficiente e comodo.

PS: Se il post ti è piaciuto, condividi questa pagina con gli amici e sui social.

Puoi farlo attraverso i pulsanti qui di seguito. Grazie