L'aggiunta della formattazione condizionale in Excel consente di applicare diverse opzioni di formattazione a una cella, o intervallo di celle, che soddisfano condizioni specifiche impostate. L'impostazione di tali condizioni può aiutare a organizzare il foglio di calcolo e semplificare la scansione. Le opzioni di formattazione che puoi utilizzare includono modifiche ai caratteri e al colore dello sfondo, stili dei caratteri, bordi delle celle e aggiunta di formattazione numerica ai dati.
Excel ha opzioni integrate per le condizioni comunemente utilizzate come trovare numeri maggiori o minori di un valore particolare o trovare numeri al di sopra o al di sotto del valore medio. Oltre a queste opzioni preimpostate, puoi anche creare regole di formattazione condizionale personalizzate utilizzando le formule di Excel.
Queste istruzioni si applicano a Excel 2019, 2016, 2013, 2010 ed Excel per Microsoft 365.
Applicazione di più condizioni in Excel
Puoi applicare più di una regola agli stessi dati per testare condizioni diverse. Ad esempio, i dati di budget potrebbero avere condizioni impostate che applicano modifiche alla formattazione quando vengono raggiunti determinati livelli di spesa, come il 50%, 75% e 100%, del budget totale.
In tali circostanze, Excel determina prima se le varie regole sono in conflitto e, in tal caso, il programma segue un determinato ordine di precedenza per determinare quale regola di formattazione condizionale applicare ai dati.
Trovare dati che superano il 25% e gli aumenti del 50%
Nell'esempio seguente, verranno applicate due regole di formattazione condizionale personalizzate all'intervallo di celle da B2 a B5.
- La prima regola controlla se i dati nelle celle A2:A5 sono maggiori del valore corrispondente in B2:B5 di più del 25%.
- La seconda regola controlla se gli stessi dati in LA2:LA5 superano il valore corrispondente in B2:B5 di oltre 50%.
Come si può vedere nell'immagine sopra, se una delle condizioni di cui sopra è vera, il colore di sfondo della cella o delle celle nell'intervallo B1:B4 cambierà.
- Per i dati in cui la differenza è superiore al 25%, il colore di sfondo della cella cambierà in verde.
- Se la differenza è maggiore del 50%, il colore di sfondo della cella cambierà in rosso.
Le regole utilizzate per portare a termine questa attività verranno immesse utilizzando la finestra di dialogo Nuova regola di formattazione. Inizia inserendo i dati di esempio nelle celle da A1 a C5 come mostrato nell'immagine sopra.
Nella parte finale del tutorial aggiungeremo formule alle celle C2:C4 che mostrano l'esatta differenza percentuale tra i valori nelle celle A2:A5 e B2:B5; questo ci consentirà di verificare l'accuratezza delle regole di formattazione condizionale.
Impostazione delle regole di formattazione condizionale
In primo luogo, applicheremo la formattazione condizionale per trovare un aumento del 25% o più significativo.
La funzione sarà simile a questa:
=(A2-B2)/A2>25%
- Evidenzia celle da B2 a B5 nel foglio di lavoro.
- Fai clic sulla scheda Home del nastro.
- Fai clic sull'icona Formattazione condizionale nel ribbon per aprire il menu a discesa.
-
Scegli Nuova regola per aprire la finestra di dialogo Nuova regola di formattazione.
- In Seleziona un tipo di regola, fai clic sull'ultima opzione: Utilizza una formula per determinare quali celle formattare.
- Digita la formula annotata sopra nello spazio sotto Formatta i valori in cui questa formula è vera:
- Fai clic sul pulsante Formatta per aprire la finestra di dialogo. Fai clic sulla scheda Riempi e scegli un colore.
- Fai clic su OK per chiudere le finestre di dialogo e tornare al foglio di lavoro.
- Il colore di sfondo delle celle B3 e B5 dovrebbe cambiare nel colore selezionato.
Ora applicheremo la formattazione condizionale per trovare un aumento del 50 percento o superiore. La formula sarà simile a questa:
- Ripeti i primi cinque passaggi sopra.
-
Digita la formula fornita sopra nello spazio sotto Formatta i valori in cui questa formula è vera:
- Fai clic sul pulsante Formatta per aprire la finestra di dialogo. Fai clic sulla scheda Riempi e scegli un colore diverso da quello che hai fatto nella serie di passaggi precedente.
- Fai clic su OK per chiudere le finestre di dialogo e tornare al foglio di lavoro.
Il colore di sfondo della cella B3 dovrebbe rimanere lo stesso indicando che la differenza percentuale tra i numeri nelle celle A3 eB3 è maggiore del 25 percento ma minore o uguale al 50 percento. Il colore di sfondo della cella B5 dovrebbe cambiare nel nuovo colore selezionato indicando che la differenza percentuale tra i numeri nelle celle A5 e B5 è maggiore del 50 percento.
Controllo delle regole di formattazione condizionale
Per verificare che le regole di formattazione condizionale inserite siano corrette, possiamo inserire formule nelle celle C2:C5 che calcoleranno l'esatta differenza percentuale tra i numeri negli intervalliLA2:LA5 e SI2:LA5.
La formula nella cella C2 è simile a questa:
=(LA2-B2)/LA2
- Fai clic su cella C2 per renderla la cella attiva.
- Digita la formula sopra e premi il tasto Invio sulla tastiera.
- La risposta 10% dovrebbe apparire nella cella C2, indicando che il numero nella cella A2 è il 10% più grande del numero nella cella B2.
- Potrebbe essere necessario modificare la formattazione sulla cella C2 per visualizzare la risposta in percentuale.
- Usa punto di riempimento per copiare la formula da cella C2 a celle C3 a C5.
- Le risposte per celle da C3 a C5 dovrebbero essere 30%, 25% e 60%.
Le risposte in queste celle mostrano che le regole di formattazione condizionale sono accurate poiché la differenza tra celle A3 e B3 è maggiore di 25 percento e la differenza tra celle A5 e B5 è maggiore del 50 percento.
La cella B4 non ha cambiato colore perché la differenza tra celle A4 e B4 è uguale 25 percento e la nostra regola di formattazione condizionale specificava che era necessaria una percentuale maggiore del 25 percento per cambiare il colore di sfondo.
Ordine di precedenza per la formattazione condizionale
Quando applichi più regole allo stesso intervallo di dati, Excel determina prima se le regole sono in conflitto. Le regole in conflitto sono quelle in cui le opzioni di formattazione non possono essere applicate entrambe agli stessi dati.
Nel nostro esempio, le regole sono in conflitto poiché entrambe utilizzano la stessa opzione di formattazione, modificando il colore della cella di sfondo.
Nella situazione in cui la seconda regola è vera (la differenza di valore è superiore al 50 percento tra due celle), vale anche la prima regola (la differenza di valore è maggiore del 25 percento).
Poiché una cella non può avere contemporaneamente due diversi colori di sfondo, Excel deve sapere quale regola di formattazione condizionale dovrebbe applicare.
L'ordine di precedenza di Excel afferma che la regola che è più in alto nell'elenco nella finestra di dialogo Gestione regole di formattazione condizionale viene applicata per prima.
Come mostrato nell'immagine sopra, la seconda regola utilizzata in questo tutorial è più in alto nell'elenco e, quindi, ha la precedenza sulla prima regola. Di conseguenza, il colore di sfondo della cella B5 è verde.
Per impostazione predefinita, le nuove regole vanno in cima alla lista; per modificare l'ordine, utilizzare i pulsanti freccia Su e Giù nella finestra di dialogo.
Applicazione di regole non contrastanti
Se due o più regole di formattazione condizionale non sono in conflitto, vengono applicate entrambe quando la condizione che ciascuna regola sta verificando diventa vera.
Se la prima regola di formattazione condizionale nel nostro esempio formatta l'intervallo di celle B2:B5 con un bordo arancione invece di un colore di sfondo arancione, le due regole di formattazione condizionale non lo sarebbero conflitto poiché entrambi i formati possono essere applicati senza interferire con l' altro.
Formattazione condizionale vs. formattazione normale
In caso di conflitto tra le regole di formattazione condizionale e le opzioni di formattazione applicate manualmente, la regola di formattazione condizionale ha sempre la precedenza e verrà applicata al posto di qualsiasi opzione di formattazione aggiunta manualmente.
