Bedingte Formatierung mit eigenen Symbolen 21

Artikelbild-168
Statt den vordefinierten Symbolsätzen in der bedingten Formatierung eigene Symbole verwenden
 

Die bedingte Formatierung ist eine geniale Funktion in Excel, um seine Daten mit minimalem Aufwand optisch aufzuwerten. Und das Ganze eben – wie der Name schon sagt – abhängig von bestimmten Bedingungen.

Dabei lassen sich z.B. mit Ampel-Symbolen oder Trendpfeilen einzelne Werte oder Entwicklungen hervorheben. Wem diese vordefinierten Symbolsätze jedoch nicht ausreichen, kann mit Hilfe eines kleinen Tricks eigene Symbole für die Formatierung einsetzen.

Und so geht’s:

Dass mit Hilfe von bedingter Formatierung extrem coole Dinge möglich sind, hatte ich vor längerer Zeit in diesem Artikel gezeigt. Etwas mehr Praxisbezug hat der heutige Tipp, den ich vor Kurzem bei meinem großen Excel-Vorbild Chandoo entdeckt hatte und den ich dir nicht vorenthalten möchte.

Symbolsätze in der bedingten Formatierung

Meine kleine Beispieltabelle zeigt die Umsatzveränderungen bei drei Produkten von einem auf den nächsten Monat:

Die "nackte" Ausgangstabelle

Die „nackte“ Ausgangstabelle

Mit Hilfe der Bedingten Formatierung lassen sich diese Veränderung noch plakativer darstellen. Dazu werden die drei Werte markiert und aus dem Menüband im Register „Start“ über die Schaltfläche „Bedingte Formatierung“ z.B. die vordefinierten Symbolsätze aufgerufen:

Vordefinierte Symbolsätze

Vordefinierte Symbolsätze

Ampelsymbole per Bedingter Formatierung

Ampelsymbole per Bedingter Formatierung

Wie man sieht, bringt hier Excel schon einiges an Bordmitteln mit. Wie bekommt man es aber hin, wenn man stattdessen vielleicht ein paar Smileys oder „Daumen hoch“- und „Daumen runter“-Symbole einsetzen möchte?

Da geht doch noch mehr!

Da geht doch noch mehr!

Und die Einfärbung in Rot, Gelb und Grün soll selbstverständlich ebenfalls automatisch erfolgen?

Eigene Symbole verwenden

Zunächst benötigen wir die passenden Symbole. Über die Schaltfläche „Symbole“ in der Registerkarte „Einfügen“ lässt sich ein Überblick über die vorhandenen Sonderzeichen verschaffen. Dabei ist die Schriftart „Wingdings“ ein dankbarer Kandidat:

Symbole einfügen

Symbole einfügen

Aber auch andere Schriftarten, wie z.B. Webdings oder Symbol bieten sich an. Hier heißt es einfach ein bisschen Stöbern.

Wenn ein geeignetes Symbol gefunden wurde, markiert man es und kann es dann mit einem Klick auf „Einfügen“ in die aktuelle Zelle übernehmen. Ein Blick in die Bearbeitungszeile zeigt, welcher Buchstabe oder welches Zeichen sich in der normalen Schriftart dahinter verbirgt. Ein paar Beispiele habe ich in der folgenden Tabelle aufgelistet:

Wingdings vs. normaler Text

Wingdings vs. normaler Text

Das richtige Symbol zuordnen

Damit die Zuordnung der Symbole zu den Werten auch automatisch funktioniert, setzen wir eine kleine WENN-Funktion ein. In meinem Beispiel also:
=WENN(K3>=1000;"J";WENN(K3>=0;"K";"L"))

Symbol per WENN-Funktion zuordnen

Symbol per WENN-Funktion zuordnen

Der Buchstabe „J“ wird mit der Schriftart „Wingdings“ dabei zu einem lachenden Smiley, sofern der Wert in K3 größer oder gleich 1000 ist. Bei einem Wert größer oder gleich Null wird der Buchstabe „K“ ausgegeben, welches in „Wingdings“ dem neutralen Smiley entspricht. Und das „L“ kommt für alle anderen Fälle zum Einsatz, sprich: Wenn der Wert kleiner als Null ist.

Wichtig ist hier nur, dass die Schriftart in diesen Zellen auf Wingdings geändert wird (oder auf die Schriftart, aus der du deine Symbole ausgewählt hast)!

Die automatische Einfärbung

Jetzt kommt die bedingte Formatierung ins Spiel: Um die drei verschiedenen Smileys rot, gelb oder grün einzufärben, legen wir drei Formatierungsregeln an:

Neue Formatierungsregel anlegen

Neue Formatierungsregel anlegen

Die Logik ist denkbar einfach: Wenn in der Zelle der Buchstabe J steht, soll die Schriftfarbe auf Grün geändert werden:

Farbe abhängig vom Buchstaben setzen

Farbe abhängig vom Buchstaben setzen

Analog verfahre ich mit den anderen beiden Buchstaben, so dass ich am Ende drei einfache Regeln im Einsatz habe (verwendest du andere Symbole, musst du die Buchstaben natürlich entsprechend anpassen):

Drei Buchstaben, drei Regeln

Drei Buchstaben, drei Regeln

Feinschliff

Jetzt stehen die Werte und die Symbole aber noch in zwei verschiedenen Spalten. Wie bekommt man es also hin, alles in eine einzige Zelle zu packen?

Die Antwort: Gar nicht 🙂

Vielmehr habe ich einen kleinen optischen Trick eingesetzt und vor der Werte-Spalte eine zusätzliche Spalte für die Symbole eingefügt. Lediglich die Rahmen sind um beide Zellen gezogen, so dass es wie eine einzige Zelle aussieht, wenn man zusätzlich die Gitternetzlinien im Hintergrund abschaltet:

Ein letzter optischer Trick

Ein letzter optischer Trick

Die fertige Beispieltabelle kannst du dir übrigens hier herunterladen.

Und wieder einmal zeigt sich, dass auch für Excel (meistens) gilt: Geht nicht gibt’s nicht. Auch wenn manchmal ein wenig kreativ nachgeholfen werden muss…

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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 zu Wolfgang Lazar Antworten abbrechen

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

21 Gedanken zu “Bedingte Formatierung mit eigenen Symbolen

  • Avatar-Foto
    Andreas Unkelbach

    Hallo Martin,

    ich hatte damals die Anforderung einen roten Pfeil nach oben und einen grünen Pfeil nach unten ebenfalls über die Unicodezeichen ▲ und ▼ umgesetzt, bin aber von deiner Lösung der Kombination von Symbol und Wert optisch in einer Zelle sehr angetan und empfinde dieses als wesentlich angenehmer als per & oder Verknüpfung Symbol und Wert miteinander in Verbindung zu bekommen.

    Da ich deine Anleitung als wesentlich zugängiger empfinde (als meine kurze Anmerkung) verweise ich hier gerne auf deinen Artikel und freue mich darüber bei künftigen Fragen in dieser Richtung direkt auf deine Ausführung verweisen zu können.. 🙂

    Viele Grüße und einen guten Start in die Woche
    Andreas

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      freut mich sehr, wenn Dir die Anleitung gefällt – auch wenn die ursprüngliche Idee nicht von mir stammt. Die Idee mit der Verknüpfung ist aber auch nicht schlecht.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Joe

    Hallo Martin, danke für deine tollen Ideen, habe es ausprobiert und hat super geklappt. Hab da zufällig noch etwas gefunden was in diese Richtung geht (im November 15) hast du etwas über Kommentare geschrieben und diese rechteckigen Kästen fand ich immer langweilig. Habe dann mal in den Einstellungen gesucht und auch gefunden, nennt sich ( Form ändern ) nach der Übernahme der Funktion konnte ich aus den Kästen schöne Wolken, Banner etc erstellen. Simpel aber optisch toll anzusehen. Ich hoffe ich habe verständlich genug geschrieben. Gruß Joe

  • Avatar-Foto
    Seba

    Hallo Martin,
    Ich hab mich sehr gefreut, dass ich mich versuchen funktionieren. hab mich alles gut geklappt.
    Danke schön. ich bin leider Gehörlos. ich hab mich sehr genaue Ihre Text gelesen.
    5 tage hab mich über symbolsätze im excel versuchen. klappt
    wünsch ihnen ein schöne Tag.
    Gruss seba

    • Avatar-Foto
      Martin Weiß

      Hallo Seba,

      vielen Dank für das nette Feedback. Es freut mich sehr, wenn der Artikel hilfreich war.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    WErner Flörl

    Hallo miteinander, ich habe folgendes Problem. ich möchte in meiner Tabelle werte wie z.B.: 1 2 oder 3 durch meine eigenen Graphiksymbole in jeweils Spalten daneben verwenden. Das heißt in Spalte A steht 1 dann sollte in Spalte B das entsprechende Symbole erscheinen. Nur kann ich keine Graphiksymbole in Zellen verwenden? Wie kann ich das Problem lösen.

    Danke

    Werner

    • Avatar-Foto
      Martin Weiß

      Hallo Werner,

      die Symbole in den Zellen lassen sich über eine geänderte Schriftart darstellen. Du muss die betreffenden Zellen nur auf beispielsweise Wingdings oder Webdings umstellen. Am einfachsten geht das übrigens über das Menü Einfügen | Symbole. Dort eine der genannten Schriftarten auswählen und das gewünschte Symbol einfügen. Dann wird die Schriftart in der Zelle automatisch umgestellt.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas Kuntz

    Hallo Martin,
    mit Begeisterung habe ich die Vorlage für die Bedingte Formatierung mit eigenen Symbolen gelesen.
    Für meine tägl. Arbeit benötige möchte ich eine zusätzliche Datei erstellen, die auch solche Handsympole aufzigen sollen, allerdings stoße ich an meine Grenzen und schaffe es nicht dies so um zusetzen, daher erlaube ich mir dich zu kontaktieren in der Hoffnung das ich mit einer Formel geholfen bekommen.
    Die benötigte Bedingte Formatierung:
    in Spalte I3 steht ein Datum zb. 20.9.19 und in Spalte F3 steht das aktuelle Datum. dann sollte in B3 eine Grüne Hand gezeigt werden.
    Wenn aber dann die Spalte F3 10 Tage vor dem 20.9.19 steht müsste die Hand auf gelb schalten, und so wenn das Datum über den 20.9.19 steht rot erscheinen.

    Ich hoffe meine Anfrage wird nicht als unverschämt eingestuft!!
    Grüße
    Andreas

  • Avatar-Foto
    Tobias Bollin

    Hallo Martin,
    ich möchte einen Wert z.B. „10“ einem Symbol z.B. „&“ hinterlegen, dass wenn ich das Symbol eintippe es zwar angezeigt wird aber in einer Formel mit dem hinterlegten wert 10 berechnet wird.
    Denke das ist sicher möglich, ich habe nur den Weg nicht gefunden

    • Avatar-Foto
      Martin Weiß

      Hallo Tobias,

      nein, das ist so leider nicht möglich. Du könntest höchstens eine Referenztabelle anlegen, in der die Zuordnung zwischen Symbol und Zahl gepflegt wird und dann in deinen Formeln per SVERWEIS aus dieser Zuordnungstabelle den entsprechenden Wert herausholen und damit weiterrechnen.
      Beispiel

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Wolfgang Lazar

    Hallöchen,
    Ich möchte in nur 2mm hohen Zellen einen senkrechten Strich (Höhe ebenfalls 2mm) mittig (d. h. in der horizontalen Ausrichtung) platzieren. In anderen Zellen, ebenfalls nur 2mm Höhe sollte ein einfacher Punkt plaziert werden.
    Geht das??? Oder ist die Zellenhöhe von nur 2mm ein Problem???
    Viele Grüsse und Danke

    • Avatar-Foto
      Martin Weiß

      Hallo Wolfgang,

      mit exakten mm-Angaben ist das immer so ein Problem, denn so exakt lässt sich das in Excel nicht lösen. Dazu kommt, dass Zellinhalte nicht bis an die Zellränder heranreichen. Wenn also der senkrechte Strich 2mm hoch ist, dann wird die Zelle automatisch noch etwas höher. Einfach mal ausprobieren, einen Buchstaben in eine Zelle eintippen und dann die Schriftgröße ändern, dann siehst du, was ich meine.

      Ansonsten lassen sich aber Zeichen grundsätzlich über die vertikale Zellausrichtung schon mittig positionieren (wenn auch nicht millimetergenau)

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Laura

    Hallo Martin,
    inzwischen kann man mit der Tastenkombi „Windows-Taste“ + „.“ auch alle Möglichen Emojis in die WENN Formel einbauen.
    So muss man nicht mehr mit unterschiedlichen Schriftarten arbeiten.
    Beispiel:
    =WENN(A1=1;“?“;WENN(A1=2;“?“;WENN(A1=3;“☹️“;““)))
    Viele Grüße
    Laura

    • Avatar-Foto
      Martin Weiß

      Hallo Laura,

      ja das stimmt, damit geht ziemlich viel und diese Tastenkombination nutze ich auch sehr gerne 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Sebastian

    Hallo Martin, ich hab mich echt in deinen Blog verliebt der hat mir schon so so weiter geholfen, aber auch die anderen Leser die Fragen gestellt haben oder auf eine eigene Lösung gekommen sind waren echt hilfreich.

    Ich sitze gerade bei Excel und habe das Problem dass Problem dass er bei der Bedingten Formatierung den Ausgabebereich nicht Dynamisch gestaltet. Ich habe mir gedacht Mit B&8&# und B8# (weil ich mich auf eine Matrix Formel beziehe) dass es funktioniert, aber leider nach den ersten mal ändern der breite der Liste Schaltet er auf fix Position um.

    Gibt es da eine Lösung?

    Vielen Dank für deine Hilfe

    Alles Gute
    Sebastian

    • Avatar-Foto
      Martin Weiß

      Hallo Sebastian,

      vielen Dank, es freut mich wirklich immer sehr, wenn meine Blogartikel weiterhelfen.
      Du möchtest vermutlich auf den Ausgabebereich einer der neuen dynamischen Arrayfunktionen zugreifen, richtig? Ja, leider ist es wirklich so, dass diese Bezüge immer in statische Zellbezüge umgewandelt werden. Im Augenblick wäre der einzige Weg, um das zu umgehen, folgender:
      Du weist dem dynamischen Ausgabebereich über den Namensmanager einen Namen zu. Dort kannst du das #-Zeichen im Feld „Bezieht sich auf“ verwenden. Und in der bedingten Formatierung nutzt du dann den vergebenen Namen.
      Das ist zwar etwas umständlicher, sollte aber funktionieren.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Sebastian

        Hallo Martin,

        Vielen Dank für deine Antwort!

        Leider funktioniert es bei mir nicht, wenn ich der Formel einen Name zuweise und den dann bei der Bedingten Formatierung im Bereich „wird angebe auf“ den Name eingebe, wird der Sofort in einen Statische Zellenbezug umgewandelt.

        Hab ich irgendwas falsch gemacht? Zur Info ich benutze Office 365

        Schöne Grüße
        Sebastian

        • Avatar-Foto
          Martin Weiß

          Hallo Sebastian,

          vermutlich habe ich dich falsch verstanden. In der bedingten Formatierung wird im Feld „wird angewendet auf“ tatsächlich alles in einen statischen Zellenbezug umgewandelt, das lässt sich auch nicht ändern. Lediglich in der Formatierungsregel bleiben die definierten Namen erhalten.

          Schöne Grüße,
          Martin