Bedingte Formatierung aufgebohrt 13

Eigene Symbolsätze in der bedingten Formatierung geht leider nicht. Doch! Wenn man weiß, wie!
 

Viele Excel-Anwender wissen die tollen Möglichkeiten der bedingten Formatierung zu schätzen. Neben der dynamischen Anwendung von Hintergrund- und Schriftfarben abhängig von bestimmten Regeln können insbesondere die Symbolsätze langweilige Zahlenwüsten auflockern.

Auch wenn es hier für die gängigsten Anwendungsfälle entsprechende Symbole gibt (Ampeln, Pfeile, Sterne etc.), ist die Auswahl doch einigermaßen eingeschränkt. Zudem lassen sich keine individuellen Symbole verwenden, sondern man ist auf das vorhandene Repertoire beschränkt.

Mit etwas Wissen, Fantasie und ein wenig Fleißarbeit kann man die bedingte Formatierung aber doch gehörig aufbohren.

Und so geht’s:

Vorhandene Symbolsätze

Zunächst ein kleiner Überblick, was Excel standardmäßig an Symbolen in der bedingten Formatierung bietet:

Vorhandene Symbolsätze

Vorhandene Symbolsätze

Grundsätzlich schon gar nicht so schlecht. Wenn man aber an die beliebten Sternebewertungen denkt, die man überall im Web findet, stößt man in Excel schon sehr schnell an Grenzen. Hier gibt es einfach nur maximal drei Sterne:

Es gibt nur maximal drei Sterne

Es gibt nur maximal drei Sterne


Falls man also für die eigene Auswertung eine fünfstufige Bewertung braucht, bleiben nicht viele Möglichkeiten:
Symbolsätze mit 5 Abstufungen

Symbolsätze mit 5 Abstufungen

Wie lässt sich diese Einschränkung umgehen?

Bedingte Formatierung aufgebohrt

Eine Möglichkeit hatte ich vor längerer Zeit schon in dem Artikel Bedingte Formatierung mit eigenen Symbolen vorgestellt. Der kleine Nachteil, den die dort gezeigte Methode hatte: Es war eine Hilfsspalte notwendig, in der über eine spezielle Schriftart (z.B. Wingdings) das betreffende Symbol dargestellt wurde.

Der heutige Tipp kommt ohne solche Hilfsspalten aus, sondern schreibt die Symbole direkt in die Zellen, in der sich auch der Zahlenwert befindet. Und das könnte beispielsweise so aussehen:

Individuelle Bewertungen

Symbolsätze mit 5 Abstufungen

Was man dazu braucht?

  • Die UNIZEICHEN-Funktion
  • benutzerdefinierte Zahlenformate
  • und natürlich die bedingte Formatierung

Aber der Reihe nach.

Die UNIZEICHEN-Funktion

Zunächst müssen wir uns überlegen, welche Symbole wir gerne hätten. Im Artikel Excel für Kreative: Die UNIZEICHEN-Funktion hatte ich dafür die UNIZEICHEN-Funktion vorgestellt. Damit lassen sich für nahezu jeden erdenklichen Bereich die passenden Symbole erstellen.

Hier sind ein paar Beispiele dafür:

Die UNIZEICHEN-Funktion

Die UNIZEICHEN-Funktion

Hinweis:
Die UNIZEICHEN-Funktion ist erst ab Excel 2013 verfügbar.

Wenn du jetzt die für die passenden Symbole über die UNIZEICHEN-Funktion erstellt hast, musst du die Formel noch durch die Werte ersetzen, so dass in der Zelle tatsächlich das Symbol steht:
Kopieren | Inhalte einfügen | Werte

Die Formel durch die Werte ersetzen

Die Formel durch die Werte ersetzen

Für unsere spätere Sternebewertung brauchst du also die Symbole für einen gefüllten und einen leeren Stern. Als nächstes erstellst du in jeweils einer eigenen Zelle alle möglichen 5 Bewertungen, in dem du die zuvor generierten Symbole einfach in der entsprechenden Reihenfolge und Häufigkeit in die Bearbeitungszeile kopierst:

Vorarbeiten: 5 verschiedene Bewertungen

Vorarbeiten: 5 verschiedene Bewertungen

Benutzerdefinierte Zahlenformate

Aus diesen 5 vorbereiteten Zellen basteln wir jeweils ein eigenes benutzerdefiniertes Zahlenformat. Markiere dazu für die erste Zelle die 5 Sterne direkt in der Bearbeitungszeile und kopiere sie mit Strg+C in die Zwischenablage:

Symbole markieren und kopieren

Symbole markieren und kopieren

Jetzt markieren wir eine beliebige Zelle und rufen mit Strg+1 das Fenster „Zelle formatieren“ auf (geht natürlich auch mit einem Rechtsklick in die Zelle) und wählen aus der Liste der Zahlenkategorien den Eintrag „Benutzerdefiniert“. In das Eingabefeld „Typ“ tippen wir erst ein Anführungszeichen, fügen dann mit Strg+V die Sterne aus der Zwischenablage ein und schließen das Ganze wieder mit einem Anführungszeichen ab:

Die kopierten Sterne einfügen

Die kopierten Sterne einfügen

Das ganze Spiel wiederholen wir dann für die anderen 4 Bewertungsmöglichkeiten:
Symbole in die Zwischenablage kopieren, neues benutzerdefiniertes Zahlenformat erstellen, Symbole aus der Zwischenablage einfügen.
Am Ende solltest du also 5 verschiedene benutzerdefinierte Zahlenformate in deinem Auswahlfenster haben:

Übersicht der benutzerdefinierten Formate

Übersicht der benutzerdefinierten Formate

Die bedingte Formatierung

Jetzt brauchen wir noch ein paar bedingte Formatierungsregeln:
Start | Bedingte Formatierung | Neue Regel.
Als Regeltyp wählen wir „Nur Zellen formatieren, die enthalten“. Im Bereich der Regelbeschreibung geben wir an, „Zellwert gleich 5“ und klicken dann auf die Formatieren-Schaltfläche. Für das Zahlenformat wählen wir jetzt das benutzerdefinierte Format mit den 5 gefüllten Sternen aus.

Benutzerdefiniertes Format anlegen

Benutzerdefiniertes Format anlegen


Im Register „Schrift“ habe ich als Schriftfarbe noch ein sattes Orange eingestellt.

Dann werden 4 weitere Regeln für die Zellwerte 1, 2, 3 und 4 angelegt, wobei wir immer das passende benutzerdefinierte Zahlenformat wählen. Am Ende dieser Fleißaufgabe sollte der Regelmanager so aussehen:

Alle bedingten Formatierungsregeln

Benutzerdefiniertes Format anlegen

Und jetzt kannst du die Regeln testen. Wenn du alles richtig gemacht hast, sollte jetzt für die betreffende Zelle abhängig von dem eingegebenen Wert die entsprechende Sternebewertung angezeigt werden:

Die neuen Bewertungssymbole im Einsatz

Die neuen Bewertungssymbole im Einsatz

Ja, es ist zugegebenermaßen etwas Fleißarbeit notwendig, die sicherlich nicht für jeden Anwendungsfall gerechtfertigt ist. Wenn du aber beispielsweise ein schönes Dashboard erstellen und damit beim Chef Eindruck schinden möchtest, dann kann man ja durchaus mal darüber nachdenken, oder?

(Wenn du möchtest, kannst du dir die Beispieldatei hier herunterladen)
 

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

13 Gedanken zu “Bedingte Formatierung aufgebohrt

  • Avatar-Foto
    Andreas Unkelbach

    Guten Morgen Martin,

    ich freue mich jedes Mal aufs Neue, dass sich durch solche Artikel immer wieder zeigt, dass Excel einfach das 5 Sterne Tool im Berichtswesen ist ?.

    Die Idee mit benutzerdefinierten Format ist tatsächlich klasse und hat den Vorteil, dass dieses auch direkt in der Arbeitsmappe gespeichert ist.

    Vielen Dank dafür und dir eine gute Woche.
    Andreas

  • Avatar-Foto
    Lutz Blumensaat

    Hallo,
    ich habe diese bedingte Formatierung gerade ausprobiert:
    In Excel 2013 klappts problemlos. Dann habe ich die neue Datei unter Excel 2010 geöffnet:
    Seltsamer Effekt:
    Die Bedingt Formatierten Zellen zeigen nur das Hash – Zeichen. (Okay – 2010 kennt es wohl nicht)
    Ein Blick in die Zahlenformate: Dort stehen Sterne !
    Ich habe eine der Zellen bearbeitet (F2 & Enter), danach funktionit die bedingte Formatierung ???

    Seltsam oder ?

    • Avatar-Foto
      Martin Weiß

      Hallo Lutz,

      ja, ich kann dieses merkwürdige Verhalten unter Excel 2010 bestätigen. Auch bei mir erscheint zunächst nur das Hashzeichen und erst nach der erstmaligen Eingabe oder Änderung des Werts werden die Sterne angezeigt. Eine Erklärung habe ich dafür leider auch nicht.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Heinz

    Hallo Martin habe heute dein Artikel über Bedingte Formatierung aufgebohrt umgesetzt: Hat alles prima geklappt. Ganz großes Lob auch an die hervorragende und ausführliche Beschreibung.Eine Frage habe ich noch. Kennen Sie eine Möglichkeit auch halbe Sterne darzustellen?
    Schöne Grüße
    Heinz

    • Avatar-Foto
      Martin Weiß

      Hallo Heinz,

      vielen Dank für das Lob!
      In den Unicode-Tabellen konnte ich zumindest bisher noch keine halben Sterne finden, die dafür notwendig wären. Bleibt noch die Möglichkeit über den regulären Sterne-Symbolsatz in der bedingten Formatierung, denn dort gibt es auch einen halben Stern. Allerdings ist man hier wieder auf maximal 3 Sterne beschränkt. Eine andere Lösung kenne ich leider auch nicht.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Konstantin

    Hallo Martin,

    wollte deine richtig tolle Lösung für meine Tabelle anwenden, aber komme nicht weiter. Ich kann pro Zelle maximal nur einen Stern einfügen, sobald ich versuche einen weiteren Stern einzufügen ist das Einfügen nicht mehr verfügbar (ausgegraut).

    Ich verwende Office 365

    Muss ich etwas an meinen Einstellungen umstellen?

    Danke
    Konstantin

    • Avatar-Foto
      Martin Weiß

      Hallo Konstantin,

      nein, da gibt es nichts einzustellen. Du musst nur den ersten Stern mit Strg+C in die Zwischenablage kopieren und dann direkt oben in der Bearbeitungszeile mit Strg+V wieder einfügen.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Konstantin

        Hallo Martin,

        danke für deine Rückmeldung.
        Aber genau das funktioniert bei mir nicht, bei Betätigung von Strg+V passiert nichts, die Bearbeitungszeile bleibt leer.