Excel leicht gemacht! ist eine Tipps & Tricks Serie von Usern für User.
Sie soll den täglichen Umgang mit Excel und nachfolgenden weiteren Office-Programmen etwas erleichtern, resp. den Wissenshorizont um den unendlichen Funktions-Umfang der cleveren Office-Tools erweitern.
In diesem Blog-Beitrag widmen wir uns der Funktion «Bedingte Formatierung». Diese Funktion dient hauptsächlich dem Zweck, errechnete oder erfasste Werte in einem Excel-Dokument nach bestimmten Kriterien visuell besser darzustellen. Sei es, um verschiedene Zahlen in einer Wertung von gut bis schlecht, von hoch bis niedrig, von besser bis schlechter als der Durchschnitt oder einfach nur um Zellen hervorzuheben, die einen speziellen Text beinhalten – der Funktionsumfang der bedingten Formatierung bietet ein extrem breites Spektrum an Anwendungs- und Darstellungs-Formen.
Im nachfolgenden Beitrag zeige ich auf, für was welche bedingten Formatierungen stehen und wie man sie ganz einfach einsetzen und anpassen kann.
1. Simple Standard-Formatierungen
1.1. Farbenskalen
Für die visuelle Darstellung von errechneten Werten im Excel sind die Farbskalen eine äusserst einfache und doch übersichtliche Form der Anzeige. Im entsprechenden Untermenü gibt es bereits eine Reihe an vordefinierten Formatierungen:
Wem die Farben oder Skalen-Bereiche jedoch nicht ausreichen, kann über das erweiterte Menü «Weitere Regeln» die Darstellung nach Lust und Laune umgestalten.
Für die Basis der Farben-Skalen, sprich das Spektrum der Farben von einem zum anderen Wert, den man visuell darstellen möchte, stehen die beiden Farbverläufe in 2- oder 3-stufiger Ausführung zur Verfügung. Hiermit können die gewünschten Werte farblich so dargestellt werden, dass sie rein anhand ihrer Farbe eine entsprechende Aussage darstellen. Die anzuzeigenden Farben können nach eigenen Wünschen und Anforderungen angepasst werden und die jeweilige Schwellgrenze kann manuell ausgewählt werden.
Zu definieren ist hier übrigens auch, ob die Werte als effektive Zahlen, Prozente oder gemäss sämtlicher vorhandenen Werten basierend auf deren jeweiligen Höchst- und Tiefts-Werte ausgewertet werden sollen.
Beispiel:
Gehen wir Mal von einer 3-stufigen Werte-Skalierung von 1 bis 10 aus, wobei 1 das schlechteste Ergebnis darstellt und somit in rot dargestellt werden soll. Das Maximum der zu erreichenden Punkte liegt bei 10 und soll ein absolutes Top-Ergebnis darstellen, somit wird es grün eingefärbt. 5 steht für okay / neutral und soll somit blau dargestellt werden.
Gehen wir nun von einer Werteliste aus, welche sämtliche Zahlen von 1 – 10 als Ergebnis ausgeben kann, ist gut zu erkennen, dass Excel sämtliche Zahlen in einer anderen Farbe, sprich dem entsprechenden Skalenwert fliessend darstellt:
1.2. Datenbalken
Wer etwelche Ergebnisse lieber in Form von einem Fortschrittsbalken visualisiert, wird vorzugsweise mit der Darstellungsform der «Datenbalken» arbeiten. Auch hier gibt es schon eine vordefinierte Auswahl an Farbmustern, die unmittelbar angewendet werden können.
1.3. Symbolsätze
Die Hervorhebungsart mit den Symbolsätzen beherbergt eine Reihe von verschiedenen Icons, wie etwa Sterne, Ampelfarben, Pfeile, etc. welche für die visuelle Darstellung von Werten verwendet werden kann.
Die persönliche Anpassung dieser Vorlagen ist jedoch sehr beschränkt, da lediglich die Symbol-Art sowie deren Schwellenwerte manuell festgelegt werden kann. Das heisst, dass die jeweiligen Farben der Symbolsätze fest von Excel vorgegeben sind und nicht dem gewünschten Farbdesign angepasst werden können.
2. Erweiterte Formatierungen
2.1. Regeln für oberste / unterste Werte
Wie der Name dieser Regel-Gruppe schon besagt, können mit diesen Formatierungen, die top höchsten oder tiefsten Werte einer Zahlenreihe spezifisch ausgezeichnet werden. Es werden somit nicht alle Werte gekennzeichnet, sondern nur die jeweilige Anzahl, welche als Top-Werte definiert werden:
Funktionen der Formatierungen kurz zusammengefasst:
- Obere 10* Elemente => es werden nur jene Zellen gem. den Vorgaben formatiert, welche den höchsten Wert aufweisen
- Obere 10*%… => es werden nur jene Zellen gem. den Vorgaben formatiert, bei welchen der Wert in der oberen 10*%-Stufe der gesamten Zahlenreihe liegt
- Untere 10* Elemente => es werden nur jene Zellen gem. den Vorgaben formatiert, welche den tiefsten Wert aufweisen
- Untere 10*%… => es werden nur jene Zellen gem. den Vorgaben formatiert, bei welchen der Wert in der unteren 10*%-Stufe der gesamten Zahlenreihe liegt
- Über dem Durchschnitt => Formatierung von allen Zellen, bei welchen der Wert oberhalb vom Durchschnitt der gesamten Zahlenreihe liegt
- Unter dem Durchschnitt => Formatierung von allen Zellen, bei welchen der Wert unterhalb vom Durchschnitt der gesamten Zahlenreihe liegt
* Selbst wenn die bereits vorhandene Auswahl an Formatierungen mit «10» betitelt ist, kann die Anzahl an top Werten, die man kennzeichnen möchte, ganz einfach im erweiterten Regel-Menü angepasst werden, sprich es müssen nicht immer 10 sein, auch wenn die Formatierung «10» heisst 😉. Und das tolle an diesen Formatierungen ist, dass man sich auch hier komplett mit dem Design austoben kann, da sowohl sämtliche Füllfelder, Schriften, Rahmen, etc. nach Belieben angepasst werden können.
2.2. Regeln zum Hervorheben von Zellen
Die sogenannte «Krönchen-Disziplin» bei der bedingten Formatierung liegt im Register «Regeln zum Hervorheben von Zellen». In dieser Rubrik gibt es eine Vielzahl an Möglichkeiten, um eine Excel-Tabelle mit visuellen Darstellungen zu ergänzen.
Funktionen der vordefinierten Standard Formatierungen kurz zusammengefasst:
- Grösser als… => es werden nur jene Zellen gem. den Vorgaben formatiert, die einen bestimmten Vorgabe-Wert übersteigen
- Kleiner als… => es werden nur jene Zellen gem. den Vorgaben formatiert, die einen bestimmten Vorgabe-Wert unterschreiten
- Zwischen… => es werden sämtliche Zellen gem. den Vorgaben formatiert, die einen Wert aufweisen, der zwischen zwei definierten Limiten liegt
- Gleich… => es werden nur jene Zellen gem. den Vorgaben formatiert, die exakt einen vorbestimmten Wert aufweisen
- Textinhalt… => Formatierung sämtlicher Zellen, die einen bestimmten Text beinhalten (da diese Regel umfangreicher als die anderen ist, wird sie unten noch einmal separat vorgestellt)
- Datum… => Formatierung von Zellen, die ein gewünschtes Datum aufweisen, wie z. Bsp. gestern, heute, morgen, letzte Woche, diese Woche, letzten Monat, etc.
- Doppelte Werte… => Kennzeichnung von gleichen Inhalten innerhalb einer Datenreihe
Alle diese Funktionen werden standardmässig nur auf die jeweilige Zelle angewandt, welche man markiert, bevor man die entsprechende Formatierungs-Regel aktiviert. Die Hervorhebung kann aber problemlos auch auf eine ganze Zeile angewandt werden, mittels der erweiterten Regeldefinierung.
2.3. Nützliche erweiterte Regeln nach bestimmtem Textinhalt
Öffnet man das Menu «Weitere Regeln» findet man an zweiter Position die Möglichkeit, weitere Bedingungen für die Formatierungen von Textinhalten zu definieren durch Klicken auf „Nur Zellen formatieren, die enthalten„. Mittels der erweiterten Auswahl «Bestimmter Text» kann die Suchfunktion so eingestellt werden, dass ein bestimmter Text als Bestandteil einer Zelle sowie am Anfang oder am Ende stehen muss. Selbstverständlich können auf einer Zelle auch mehrere Formatierungen hinterlegt werden, so dass diese je nach entsprechendem Indikator eine andere Kennzeichnung ausgibt.
Beispiel:
In diesem Beispiel sollen die anwesenden Ressourcen in einem Büro dargestellt werden, wobei pro Ressource eine Farb-Richtung definiert ist, sowie die Abwesenheiten-Typen «h» für Homeoffice und «v» für allg. Absenzen (vacancies) definiert wurden.
Die entsprechenden Ressourcen können also an den jeweiligen Tagen lediglich ein «h» oder «v» im Excel eintragen, und schon ändert sich die Formatierung der entsprechenden Zelle. Auf diese Weise ist auf einen Blick erkennbar, welche Ressourcen an welchen Tagen vor Ort, im HO oder gar nicht verfügbar sind:
2.4. Nützliche erweiterte Regeln nach manuellen Definitionen
Öffnet man das erweiterte Menü erscheint ein weiterer zusätzlicher Punkt, der bislang nicht in der Vorauswahl erschienen ist. Mit der Funktion «Formel zur Ermittlung der zu formatieren Zellen» kann man nach Belieben eine Excel-Tabelle durchsuchen und mit den gewünschten Formatierungen versehen
Beispiel:
In diesem Beispiel soll folgendes Szenario entstehen: Sobald in der Spalte «Indikator» ein «x» gesetzt wird, soll die gesamte Zeile mit roter Schrift, kursiv und durchgestrichen formatiert werden.
Als erstes wurden in der Zeile 2 sämtliche Zellen markiert, auf welche sich die Umformatierung der Textgestaltung auswirken soll (A2 (Titel) bis E2 (Beschreibung).
Anschliessend wurde als «Werte formatieren, für die diese Formel wahr ist» diejenige Zelle ausgewählt, welche den Formatierungs-Auslöser enthält. In unserem Fall ist dies die Zelle «D2» (Indikator). Als Auslöser wurde das X hinzugefügt, womit die Formel wie folgt lautet: =$D2=’’x’’
Wichtig zu beachten ist hierbei, dass das Dollar-Zeichen nur vor der Spalte «D» stehen darf, nicht aber vor der Zeile «2», sonst kann die Formel nicht auf die darauffolgenden Zeilen angewendet werden und müsste für jede Zeile einzeln erstellt werden.
Nachdem die Formatierung für diese Zellen / Zeilen erstellt wurde, können die entsprechenden Zellen noch einmal alle zusammen markiert werden und die Formatierung kann mittels des kleinen Vierecks am rechten unteren Ecken der letzten Zelle soweit hinuntergezogen werden, wie die Formatierung anzuwenden ist.
Anschliessend können die Daten abgefüllt werden und die Formel wirkt sich jeweils immer auf die gesamte Zeile mit Daten aus:
3. Gespeicherte Regeln verwalten
Sämtliche manuell erstellten und hinterlegten Formatierungsregeln werden in einem zentralen Regelwerk gespeichert. Bei Bedarf können diese jederzeit über den Menü-Punkt «Regeln verwalten» aufgerufen, bearbeitet oder wieder gelöscht werden. Zu beachten gilt dabei jedoch, dass jeweils im Excel die entsprechenden Zellen zuerst markiert werden, auf welche die Mutation an den vorhandenen Regeln angewendet werden soll.
