Bedingte Formatierung in Excel mit 17 Symbolsätzen

Bedingte Formatierung ist immer dann sinnvoll, wenn Werte, die bestimmte Bedingungen erfüllen oder nicht erfüllen, hervorgehoben werden sollen.

Bisher habe ich in Beispielen gezeigt, wie sich dann die Schriftfarbe ändert oder wie sich der Zellhintergrund anders gefärbt wird.

In diesem Beitrag geht es um die Symbolsätze, von denen Excel 2007 exakt 17 Varianten anbietet.

Am Ende des Beitrags findest Du eine Ergänzung aufgrund neuer Erkenntnisse, geschrieben am 17.11.2018.

Rufst Du über das Menü Start die bedingte Formatierung auf und wählst dann Symbolsätze, wirst Du dieses Bild sehen:

Symbole1

Das ist die Auswahl, auf die Du zugreifen kannst.

Liegt Dir eine Tabelle vor, in der ein Bereich eben mit einem der Symbolsätze formatiert wurde, gehe zu „Regeln verwalten“ und Du wirst standardmäßig mit Prozenten (67% bzw. 33%) konfrontiert.

Was diese 67% bzw. 33% bedeuten und worauf sich die Werte beziehen, ist oft eine Fragestellung in Foren. Ich will die Frage in diesem Beitrag beantworten. Doch nun zu ein paar Beispielen:

1. Eine Zahlenreihe von 1 bis 10

Eine ganz einfache Zahlenreihe soll zuerst betrachtet werden:

Symbole2

Den Bereich B32:B41 habe ich markiert, bin dann im Menü Start zur bedingten Formatierung gegangen und habe Symbolsätze aufgerufen. Dort habe ich mich für „3 Symbole (ohne Kreis)“ entschieden. Dieses Symbol zählt zu den Indiaktorsymbolen.

Um zu ergründen, warum bei einigen Werte das rote Kreuz, bei einigen das gelbe Ausrufungszeichen und bei einigen der grüne Haken erscheint, habe ich „Regeln verwalten“ aufgrufen und sehe dieses Bild:

Symbole3

Was bedeuten jetzt die Prozente?

Die Reihe hat 10 Werte, 67% davon sind 6,7, gerundet 7. D.h., in einer aufsteigend sortierten Liste bekommen alle Werte nach dem 7.Wert von oben den grünen Haken.

Alles nach 33%, also 3,3 Werte, bekommt das gelbe Zeichen. Alles was unter 33% liegt, bekommt schließlich rot.

Hat die Datenreihe 100 Werte, erhalten alle Werte, die anzahlmäßig > 67% sind, den grünen Haken.

Im Übrigen kann die Symbolreihenfolge auch umgekehrt werden.

Verändere einfach mal die 67% in 50%, die bedingte Formatierung wird sich ändern.

Sind die Werte z.B. Plan-Ist-Abweichungen, sagt diese bedingte Formatierung nichts darüber aus, ob die Abweichung zu groß oder in Ordnung ist.

2. Die gleiche Zahlenreihe von 1 bis 10

Jetzt wähle ich statt Prozente den Typ Zahl aus und bestimme, dass

>=5 -grün
>=2 – gelb
<2 – rot

sein soll.

Symbole5

Die Zahlenreihe sieht nach der Formatierung so aus:

Symbole4

Aber auch hierbei erkenne ich, dass ich Plan-Ist-Abweichungen  nicht richtig bewerten kann. Deshalb mache ich einen weiteren Versuch:

3. Wieder die gleiche Zahlenreihe von 1 bis 10

Als Typ wähle ich diesmal das Quantil aus und treffe diese Festlegungen:

=85 – grün
>=35 – gelb
<35 – rot

Symbole7

Die Zahlenreihe ist so formatiert:

Symbole6

Nein, auch diese Variante bringt mich nicht weiter.

4. Plan, Ist und Abweichung

Mein Wunsch ist es immer noch, die Abweichungen zwischen Ist- und Planwerten vernünftig beding zu formatieren. Im Klartext heißt das, wenn eine Abweichung eine bestimmte Größe überschreitet, soll sie das rote Kreuz erhalten.

Ich bringe deshalb eine neue Datentabelle ins Spiel:

Symbole8

Die Abweichung abs. wurde so formatiert:

Symbole9

Mit dem Typ Formel wird ausgedrückt, dass der Wert das Ergebnis einer Berechnung ist, also, wenn in Zeile 134 Ist 2599 – Plan 2500 > oder = 20 ist, dann setze den grünen Haken. 48 ist >= 20.

Die Abweichung rel. wurde so formatiert:

Symbole10

Hier geht es darum, dass der Wert >= 2% sein muss, um den grünen Haken zu erhalten. In Zeile 134 ist das der Fall.

Dieser Typ kann z.B. im Risikomangement genuzt werden. Die Indikatoren können auf künftig positive Enwicklungen (Chancen) oder künftig negative Entwicklungen (Risiken) hinweisen.

Die Symbolsätze (hier: Indikatoren) werden zu Frühwarnindikatoren. Am nächsten Beispiel wird das recht deutlich.

5. Netzabgabe eines Wasserversorgers: Plan, Ist und Abweichung

Gezeigt wird die Netzabgabe eines Wasserversorgers an die Verbraucher.

Symbole11

Die bedingte Formatierung der Abweichung erfolgt auch hier über den Typ Formel.

Symbole12

Deutlich zu sehen ist, dass ab Monat August die Abweichung von Ist zu Plan unter dem gewählten Grenzwert liegt.

Die negative Entwicklung der Netzabgabe auf Dauer kann für den Versorger existenzbedrohend sein.

Daran hängen die technische Auslegung der Anlage, die Kalkulation und damit die Verbraucherpreise.

Das rote Kreuz ist hier ein Frühwarnindikator für ein mögliches kommendes Risiko.

Mit dem Typ Formel habe ich nun endlich die Aussagen, die ich über die bedingte Formatierung haben will.

Fazit

Meine Schlussfolgerung aus diesem Beitrag ist, dass ich mir stets überlegen muss, welche Aussage ich mit einer bedingten Formatierung erhalten will.

Ein Symbolsatz ist schnell ausgewählt. Wenn die Regel nicht vernünftig ausgewählt ist, habe ich zwar schöne bunte Zeichen, aber keine Antwort auf meine Fragen.

In den Beispielen habe ich mich auf einen Symbolsatz beschränkt. Die Regelverwaltung ist aber bei allen anderen ähnlich. Liegen fünf Symbole in einem Satz vor, geht die Standardeinstellung über 80%, 60%, 40% und 20%. Aber auch das kann entsprechend den obigen Ausführungen verändert werden.

Viel Spaß beim Ausprobieren.

Ergänzung (17.11.2018)

Kürzlich brachte ein Leser Zweifel an der grundsätzlichen Richtigkeit der Aussagen in Abschnitt 1 an. Dort hatte ich eine Zahlenreihe von 1 bis 10  mit einer bedingten Formatierung (Symbolsätze / 3 Symbole (ohne Kreis)) versehen. Bei dieser Art der Formatierung geht es zunächst nicht um Werte, sondern um die Anzahl der Werte. Werte >=67% sollten grün, >=33% gelb und alle anderen rot gefärbt werden.

Das Ergebnis sah so aus:

Symbolsätze1

Alle Werte >=67%, gerundet 70%, also ab dem 8.Wert sind grün gekennzeichnet.

Alle Werte >=33%, gerundet 40%, und <67%, als 4. bis 7. Wert sind gelb gekennzeichnet.

Schließlich sind alle Werte <33%, also die Werte 1 bis 3 rot gekennzeichnet.

Der Leser brachte nun eine andere, sortierte Wertereihe ins Spiel und wendete die Regel wie oben an:

Symbolsätze2

Das Ergebnis war ein anderes als in meinem Beispiel. Berechtigterweise entstanden so seine Zweifel. Seine Zweifel war für mich eine Aufforderung, der Sache nachzugehen. Warum wurden seine Werte anders bedingt formatiert als meine?

Irgendwann kam mir in den Sinn, seine Wertereihe doch einmal komplett abzubilden, also von 10 bis 33 und dann die bedingte Formatierung vorzunehmen.

Dies war das Ergebnis:

Symbolsätze3

Die im Beispiel des Lesers sichtbaren Werte sind alle richtig bedingt formatiert, die 10 ist rot, 18 bis 25 sind gelb und die 33 ist grün.

Offenbar bildet Excel intern eine geschlossene Reihe vom kleinsten bis zu größten Wert, formatiert sie mit Symbolsätzen und zeigt schließlich nur die gewünschten Werte an. Ob das immer so ist, habe ich allerdings nicht weiter untersucht.

14 Kommentare zu „Bedingte Formatierung in Excel mit 17 Symbolsätzen“

  1. Guten Morgen Gerhard,

    auch von mir ein Lob für die gelungenen Beispiele. Gerade bei Frühwarnindikatoren gefällt es mir besonders gut, dass du hier nicht auf die absoluten sondern die relativen Veränderungen eingegangen bist :-).

    Dein Fazit „dass du dir stets überlegen musst, welche Aussage du mit einer bedingten Formatierung erhalten wilst“ würde ich beinahe als Credo für jeden Bericht nehmen und empfinde ich als ein universell einsetzbares Fazit, dass dir aber auch bei deinen übrigen Beispielen immer wieder gut gelingt.

    Leider hat es Excel, soweit mir bekannt, noch nicht geschafft, dass man sich eigene Symbolsätze definieren kann. Ich hatte damals eine Rückfrage zur bedingten Formatierung, wie man einen roten Pfeil nach oben und grünen nach unten erhalten kann (ging um Energieverbrauch) und konnte damals auch nur auf die Verwendung von eingefärbten Unicodezeichen ▲ und ▼ verweisen… ist dir bei einer aktuelleren (> Excel 2013) bekannt, ob mittlerweile die Symbole in der bedingten Formatierung ebenfalls ausgetauscht werden können?

    Viele Grüße
    Andreas

  2. Guten Morgen Andreas,
    vielen Dank für dein positives Feedback, es tut gut, Reaktionen auf das eigene Tun zu erfahren. Microsoft hat sicherlich keinen Kummerkasten, in den man seine Vorschläge und Ideen hinein packen kann. Es wäre aber gut.
    Gut wäre auch eine Formatierungsmöglichkeit

  3. Hallo Andreas, es ist einfach kein gutes Arbeiten auf dem Smartphone, ich war noch gar nicht fertig mit der Antwort, da wurde schon gesendet. Ich wollte noch sagen, eine Formatierungsmöglichkeit wie bei den Formen könnte schon helfen

  4. Hallo Gerhard, suche schon im gesamten Netz eine Formel für bedingte Formatierung Symbole. In Punkt:
    4. Plan, Ist und Abweichung hast du einen Wert(Formel) eingestellt.
    Zeile 134 Plan 2.500 IST 2.599
    Du hast hier ein Wert =20 Formel bzw. Wert=-70 Formel eingestellt.
    Meine Frage? warum hast du hier keine Formel, bezogen auf die Zellen eingestellt. Ich verstehe das nicht(Excel Dumm)
    Ich möchte eine Tabelle in der Art formatieren. Kannst du mir helfen

  5. Hallo Günter, danke für deinen Kommentar.
    Mein Beitrag enthält eine falsche Abbildung, wollte ich schon längst korrigieren.
    Zu deinem Anliegen:
    Wenn du Symbolsätze zeigen willst, kannst du keine Formeln verwenden, das geht einfach nicht.
    Wählst du in in der Maske „Formatierungsregel bearbeiten“ den Typ Formel, musst du als Wert „=20“ bzw. „=-70“ eingeben.
    Willst du den Typ Zahl, gibst du 20 bzw. -70 ein.
    Willst du einen Bezug auf die Datenzellen in den Spalten A und B,kannst du nur die Regel „Formel zur Ermittlung der zu formatierenden Zellen verwenden. Dann schreibst du:
    =B134-A134>=20
    und eine zweite Regel mit >=-70.
    Dann kannst du aber keine Symbolsätze, sondern nur Farben verwenden.
    Wenn du magst, kannst du mir gern eine Beispieldatei mit deinen Wünschen senden, ich will gern helfen.
    Viele Grüße
    Gerhard

  6. Hallo Gerhard,
    ich habe das Problem gelöst, hätte dir gerne ein Teil der Tabelle geschickt, nur leider sehe ich keine Möglichkeit die Datei hier einzufügen.
    Trotzdem vielen Dank für deine Bemühungen..
    nochmals. merci

  7. Hallo Gerhard
    Danke für diesen Beitrag. Das Beispiel unter Kapital 1 ist aber nur theoretisch. Wenn du die Zahlenreihe 10, 18, 19, 20, 21, 22, 23, 24, 25, 33 nimmst, ist plötzlich nur 1 Zahl rot (10) und nur eine Zahl grün (33). Alle andere Zahlen (d.h. 8 Zahlen) sind gelb. Anscheinend wird durch Excel eine andere/zusätzliche Gewichtung vorgenommen?
    Beste Grüsse
    Christophe

  8. Hallo Christophe,
    tatsächlich eine interessante Fragestellung, die Du da aufwirft. Die 18 müsste rot sein. 24 und 25 müssten grün sein. Warum das nicht so ist, weiß ich im Moment nicht.
    Ehrlich gesagt, arbeite ich mit den Prozenten nicht so gern, Zahlen finde ich besser.
    Ich bleib dran.
    Viele Grüße
    Gerhard

  9. Hallo Christophe,

    ich glaube, hinter das Problem gestiegen zu sein.
    Erstelle mal eine durchgängige Zahlenreihe von 10 bis 33 und wähle die bedingte Formatierung wie gehabt, Symbolsätze und Prozent. Lege die Prozente so fest: >67, >33 <=67, <=33.
    Die Werte von 10 bis 17 werden rot, 18 bis 25 gelb und 26 bis 33 grün. Deine 10 Werte enthalten nicht die ganze Reihe , deshalb werden nur die richtig markiert, die auch zu sehen sind.
    Ich werde meinen Beitrag am Wochenende ergänzen.

    Viele Grüße
    Gerhard

  10. Cool, danke für diesen Hinweis! Ich hatte lange – erfolglos – nach einer Lösung gesucht, und habe auch im Netz nichts gefunden. Nun wirst Du wohl der Erste sein, der es klar formulieren wird… 🙂
    Vielen Dank und beste Grüsse
    Christophe

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s