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
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 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
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
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
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
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
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
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
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:
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
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)
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.