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:
Beispieldatei herunterladen
Vorhandene Symbolsätze
Zunächst ein kleiner Überblick, was Excel standardmäßig an Symbolen in der bedingten Formatierung bietet:
Vorhandene Symbolsätze
Bei diesem Artikel handelt es sich um einen zeitlosen Klassiker, der ursprünglich im Mai 2020 erschien. Ich habe den Inhalt vor der Neuveröffentlichung noch einmal überprüft und an der einen oder anderen Stelle leicht angepasst.
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
Falls man also für die eigene Auswertung eine fünfstufige Bewertung braucht, bleiben nicht viele Möglichkeiten:
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:
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 überlege ich mir, welche Symbole ich gerne hätte. 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 ist übrigens seit Excel 2013 verfügbar.
Nachdem ich für die passenden Symbole über die UNIZEICHEN-Funktion erstellt habe, muss die Formel noch durch die Werte ersetzt werden, so dass in der Zelle tatsächlich das Symbol steht. Einfach kopieren und über einen Rechtsklick die Option „Inhalte einfügen | Werte“ auswählen. Damit bleibt nur noch das eigentliche Zeichen übrig:
Die Formel durch die Werte ersetzen
Für meine spätere Sternebewertung brauche ich die Symbole für einen gefüllten und einen leeren Stern. Als nächstes erstelle ich in jeweils einer eigenen Zelle alle möglichen 5 Bewertungen, in dem ich die zuvor generierten Symbole einfach in der entsprechenden Reihenfolge und Häufigkeit in die Bearbeitungszeile kopiere:
Vorarbeiten: 5 verschiedene Bewertungen
Tipp:
Unter Windows lässt sich mit der Tastenkombination Windows-Taste + Punkt eine anwendungsübergreifende Symbol- und Emoji-Tabelle aufrufen, aus der man die gewünschten Bildchen anklicken und direkt an der aktuellen Position einfügen kann.
Benutzerdefinierte Zahlenformate
Aus diesen 5 vorbereiteten Zellen erstelle ich jeweils ein eigenes benutzerdefiniertes Zahlenformat. Dazu wähle ich die erste Zelle die 5 Sterne aus, markiere direkt in der Bearbeitungszeile alle Sterne und kopiere sie mit Strg+C in die Zwischenablage:
Symbole markieren und kopieren
Danach verlasse ich über die ESC-Taste den Bearbeitungsmodus und rufe mit Strg+1 das Fenster „Zelle formatieren“ auf (geht natürlich auch mit einem Rechtsklick in die Zelle).
Aus der Liste der Zahlenkategorien wähle ich jetzt den Eintrag „Benutzerdefiniert“. In das Eingabefeld „Typ“ tippe ich erst das doppelte Anführungszeichen, füge dann mit Strg+V die Sterne aus der Zwischenablage ein und schließe das Ganze wieder mit einem doppelten Anführungszeichen ab:
Die kopierten Sterne einfügen
Das ganze Spiel wird dann für die anderen 4 Bewertungsmöglichkeiten wiederholt:
- Symbole in die Zwischenablage kopieren,
- neues benutzerdefiniertes Zahlenformat erstellen,
- Symbole aus der Zwischenablage einfügen
Am Ende solltest du 5 verschiedene benutzerdefinierte Zahlenformate in deinem Auswahlfenster haben:
Übersicht der benutzerdefinierten Formate
Die bedingte Formatierung
Jetzt brauchen wir noch ein paar bedingte Formatierungsregeln:
Menü „Start | Bedingte Formatierung | Neue Regel“
Als Regeltyp wird der Eintrag „Nur Zellen formatieren, die enthalten“ ausgewählt. Im Bereich der Regelbeschreibung gebe ich „Zellwert gleich 5“ ein und klicke dann auf die Formatieren-Schaltfläche. Für das Zahlenformat wird jetzt das benutzerdefinierte Format mit den 5 gefüllten Sternen ausgewählt.
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.
Tipp:
Wer Excel aus Microsoft 365 nutzt, kann über die entsprechende Schaltfläche im Regelmanager eine vorhandene Formatierungsregel einfach kopieren und muss nur den Zellwert und die Formatierung anpassen.
Am Ende dieser Fleißaufgabe sollte der Regelmanager so aussehen:
Bedingte Formatierungsregel 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
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?
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.
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
Dem stimme ich voll und ganz zu! Genial die Idee!
?
Hallo Andreas,
danke für das Feedback. Ja, ein Berichtswesen ohne Excel ist tatsächlich schwer vorstellbar, da ändern auch die tollsten BI-Lösungen nur selten etwas.
Schöne Grüße,
Martin
Sehr cool 🙂
Will endlich ein neues Excelllllll 🙁
🙂
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 ?
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
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
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
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
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
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.