La formattazione condizionale viene utilizzata da qualsiasi utente di Excel, che sia un neofita o un esperto dello strumento. Nello specifico, la formattazione condizionale ci permette di modificare il layout di determinate parti del foglio a seconda di determinate condizioni dinamiche, rendendo più facile la presentazione del foglio a terzi e migliorandone la leggibilità.
In questo articolo vediamo alcuni fra i metodi più comuni per l'utilizzo della formattazione condizionale tramite degli esempi concreti. Il nostro file di partenza avrà una lista di diversi agenti con relative vendite, valori che vogliamo mettere in ordine e visualizzare diversamente utilizzando proprio la formattazione condizionale.
Formattazione condizionale Excel: sezione regole evidenziazione celle
La prima sezione della formattazione condizionale ha diverse sezioni utili per visualizzare al meglio i valori del foglio:
- Maggiore / Minore di: definiamo una soglia e la formattazione evidenzia tutti i valori sopra / sotto la soglia definita
- Tra: definiamo un minimo e un massimo. Qualsiasi valore fra questi due sarà evidenziato
- Uguale a: questa parte funziona sia con testi sia con numeri, evidenziandoli se sono uguali al valore che inseriamo
- Testo contiene: solo per celle testuali, se questa contiene il testo da noi inserito allora sarà evidenziata
- Data corrispondente a: in questo caso dobbiamo stare attenti perchè Excel, di default, considera domenica come l'inizio della settimana. Qualsiasi valore corrispondente a Ieri, Settimana prossima etc ( a seconda di quale scegliamo) sarà evidenziato
- Valori duplicati: selezioniamo un intervallo, se ci sono valori duplicati questi saranno formattati
Come evidenziare numeri maggiori di con la formattazione condizionale
Poniamo ad esempio di voler evidenziare le celle superiori a un certo numero (1'000). Clicchiamo su Home -> Formattazione Condizionale -> Regole evidenziazione celle -> Maggiore di:
Di default Excel proverà a visualizzare questi valori con un Riempimento rosso chiaro con testo rosso scuro, ma possiamo modificare il formato cliccando su Formato personalizzato:
Modifichiamo il riempimento inserendo un colore verde:
Modifichiamo anche il carattere inserendo il colore bianco per visualizzare meglio i valori:
Abbiamo finito! Ora visualizziamo i numeri superiori a 1'000 che sono evidenziati in verde e con il font bianco come avevamo specificato.
Come evidenziare celle utilizzando i valori di un'altra cella tramite la formattazione condizionale
Come notiamo, la parte di visualizzazione potrebbe essere migliorata dato che non è chiarissimo associare i nomi ai singoli valori. Se volessimo modificare la formattazione inserendo anche i nomi, possiamo farlo selezionando il range da A2 a A11 per poi cliccare su Home -> Formattazione condizionale -> Regole evidenziazione celle -> Altre regole:
In questo caso dobbiamo utilizzare una formula per arrivare allo stesso risultato. Ci capiterà spesso di doverlo fare per replicare funzionalità della formattazione condizionale su valori testuali - stavolta sarà semplice, dato che basterà inserire =b2>1000. Non è necessario inserire il SE iniziale perchè Excel formatta i valori per cui la formula restituisce vero, ma dovremo ricordarci di inserire b2 senza dollari per permettere a Excel di trascinare in basso le formule (che diventeranno b3>1000; b4>1000 e così via).
Nel risultato finale vediamo come anche i testi siano ora con la stessa formattazione della parte numerica, garantendo un'ottima leggibilità.
Come colorare righe alternate utilizzando la formattazione condizionale
Può capitare di voler visualizzare diversamente le righe, alternandone il riempimento. Il suggerimento è quello di non farlo riempiendo le singole righe perchè qualsiasi modifica rovinerebbe la formattazione. Con la formattazione condizionale, possiamo invece ottenere un ottimo risultato utilizzando le formule.
Il punto di partenza è sempre lo stesso foglio, ma in questo caso clicchiamo su Home -> Formattazione Condizionale -> Nuova Regola:
La formula che inseriamo è una combinazione di RESTO e RIF.RIGA:
=RESTO(RIF.RIGA();2)
La logica della formula è di dividere il RIF.RIGA (per A1 sarebbe 1, per A2 sarebbe 2) per due. Se questa divisione ha un RESTO, allora la condizione è vera - se non c'è resto, allora la regola non si applica. La stessa logica si applicherebbe per una riga ogni 3 oppure ogni 5, basterebbe modificare il divisore da 2 a 3 o 5.
Modifichiamo anche la formattazione inserendo un giallo di riempimento:
Ecco il risultato finale con le righe alternate.
Come formattare celle su Excel a seconda del testo
Facciamo sempre un esempio con l'elenco precedente. In questo caso vogliamo vedere quali nomi abbiano la vocale O all'interno. Clicchiamo su Formattazione Condizione -> Regole Evidenziazione Celle -> Testo contiene, inserendo il valore O e il riempimento verde con testo verde scuro:
Nel risultato finale, notiamo che si siano evidenziate solamente le celle che abbiano una O all'interno. In questo caso l'evidenziazione non è case-sensitive (sia O sia o sono condizioni vere per il riempimento).
Come evidenziare i duplicati con la formattazione condizionale
In alcuni casi potremmo voler evidenziare i duplicati, in particolare per evitare errori per eventuali calcoli successivi. Aggiungiamo quindi un'ulteriore riga di valori, dove troviamo Luka Doncic e 650 come valore di vendite.
Cliccando su Home -> Formattazione Condizionale -> Regole evidenziazione celle -> Valori duplicati troviamo già il duplicato, dato che anche la riga 9 con Wes Unseld ha sempre 650.
Come nel caso precedente, vogliamo però visualizzare diversamente anche i nomi e non solo i numeri. Clicchiamo quindi su Home -> Formattazione Condizionale -> Nuova regola:
La formula in questo caso sfrutta la funzione CONTA.SE:
=CONTA.SE($B$2:$B$12;b2)>1
Notiamo che l'intervallo è bloccato, mentre invece b2 si muoverà in basso per variare a seconda del valore corrispondente di vendita. Modifichiamo anche la formattazione, inserendo prima il riempimento nero:
Successivamente, inseriamo anche il colore del font che sarà bianco:
Il risultato finale ci aiuterà molto nel nostro lavoro, permettendoci di differenziare sia il nome sia il numero per verificare quali agenti abbiano fatto le stesse vendite.
Clicca qui se stai cercando un articolo per eliminare i duplicati.
Formattazione condizionale Excel: sezione primi/ultimi valori
In questa sezione abbiamo queste possibilità:
- Primi / Ultimi elementi: possiamo visualizzare rapidamente, ad esempio, i top 3 o ultimi 8 valori di un intervallo
- Primo 10% / Ultimo 10%: vedi sopra, ma utilizzando le percentuali
- Sopra / sotto la media: evidenziare i valori inferiori o superiori alla media dell'intervallo
Formattazione condizionale Excel: evidenziare i primi 5 valori
La formattazione condizionale viene anche utilizzata per visualizzare i valori più grandi di un certo intervallo. In questo caso vogliamo evidenziare i primi 5 valori del nostro intervallo, quindi clicchiamo su Home -> Formattazione Condizionale -> Regole Primi / Ultimi valori:
Selezionando 5 e modificando la formattazione (seguendo le stesse indicazioni precedenti, cambia solo il colore) possiamo già visualizzare con facilità i 5 valori che ci interessano.
Come negli altri casi, è ideale visualizzare anche i nomi collegati con i valori per meglio presentare il nostro lavoro. Selezioniamo quindi i valori della colonna A e clicchiamo su Home -> Formattazione Condizionale -> Nuova regola:
La formula sfrutta la funzione GRANDE per permetterci di visualizzare i valori dal primo al quinto:
=B2>=GRANDE($B$2:$B$11;5)
La formula sta chiedendo a Excel di colorare unicamente le celle se il valore corrispondente è almeno il quinto più grande (ultimo elemento della funzione GRANDE), rendendola dinamica visto che B2 non è bloccato con il dollaro.
Il risultato finale permette di avere la stessa visualizzazione per entrambe le parti del foglio.
Formattazione condizionale Excel: che cosa sono le barre dati
Un altro elemento da utilizzare per migliorare la visualizzazione di un singolo foglio sono sicuramente le barre dati. In questo caso sembra quasi di avere un grafico all'interno di una cella, creato cliccando su Home -> Formattazione Condizionale -> Barre dei dati e selezionando il colore preferito:
La formattazione a volte non è ideale ma si riesce comunque a capire, a colpo d'occhio, che Billy Cunningham e Oscar Robertson hanno venduto sicuramente più di Charles Barkley o Scottie Pippen.
Proviamo a modificare la regola di formattazione, basta cliccare su Home -> Formattazione Condizionale -> Gestisci regole:
Ad esempio, vogliamo visualizzare unicamente la barra senza vedere i numeri e mettere un bordo nero. Basta spuntare "Mostra solo la barra" e modificare il colore del bordo:
Il risultato finale potrebbe già essere inserito su una slide come grafico e può essere utile per fare ragionamenti più strategici e meno mirati sui singoli numeri, come vediamo dall'immagine successiva.
Un altro trucco, sempre per non visualizzare i numeri, sarebbe quello di selezionare l'intervallo e inserire come Formato personalizzato 3 punti e virgola di fila (;;;). Anche in questo caso, i valori non vengono cancellati all'interno ma non sono visualizzati.
Come implementare le scale di colori nella formattazione condizionale
Se le barre dei dati non ci convincono, possiamo utilizzare anche le scale di colori per ottenere lo stesso risultato. Queste rappresentano un modo rapido per visualizzare i valori senza andare troppo nel concreto, possiamo iniziare cliccando su Home -> Formattazione Condizionale -> Scale di colori -> Altre regole. Il suggerimento è di passare ad "Altre regole" perchè permette maggiore flessibilità e meno colori "forti", a volte un po' fastidiosi per il layout.
Selezioniamo le condizioni: vogliamo che il punto intermedio sia il 50' percentile e che i colori siano più leggeri rispetto a quelli standard.
Modifichiamo il font inserendo il colore bianco e il grassetto, così troviamo un risultato finale simile a quella dell'immagine seguente.
Chiaramente pure in questo caso potremmo utilizzare il trucco del formato con ;;; per non visualizzare i valori ma solamente i colori.
Formattazione condizionale Excel: set di icone
I set di icone sono utilizzati spesso essendo un buon compromesso rispetto alle scale di colori (molto invasive) e alle barre dati, che rimangono uno dei modi migliori per avere un'idea generale senza riempire il foglio di informazioni confusionarie. In questo caso dobbiamo cliccare su Home -> Formattazione Condizionale -> Set di icone-> Altre regole, dove selezioneremo l'icona verde se il valore è superiore o uguale al 67% percentuale, giallo se fra il 33% e il 67% e rosso per tutti gli altri:
Come notiamo, anche in questo caso possiamo visualizzare con facilità i valori e come differenziarli. Se non volessimo vedere i valori numerici, basterebbe cliccare su "Mostra solo icona". L'unico accorgimento è che per visualizzare le icone correttamente (altrimenti sono sgranate) dobbiamo accertarci che la grandezza del font in File -> Opzioni -> Generale sia almeno quella della cella corrente (in questo caso 16).
Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel.
Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, IMI Orton, Primadonna e SISAL, oltre a PMI e startup di diverso genere.
Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.
Vuoi saperne di più? Prenota una call gratuita di 15′!
