Excel 2010: Mit Ampeln & Co. klare Zeichen setzen, aber nach welchem System werden die Farben zugeteilt?

Viele waren begeistert, als mit Excel 2007 Symbole für die Bedingte Formatierung eingeführt wurden. So lassen sich jetzt Kosten per Ampel-Symbolik, Umsatzentwicklungen mit verschiedenfarbigen Trendpfeilen bewerten. Neuerungen in Excel 2010 machen es noch leichter, Zahlenkolonnen flexibel zu visualisieren.

Excel 2010: 52 Symbole stehen zur Auswahl

Doch nach welchem System werden Ampelsymbole oder Pfeile den Zahlen zugewiesen? Das folgende Beispiel bringt Aufklärung.

Das Beispiel: Eine Besucherstatistik mit farbigen Symbolen visualisieren

Excel 2010 besitzt nun 20 verschiedene Symbolsätze aufgeteilt auf vier Kategorien. Insgesamt 52 Symbole stehen zur Verfügung.

Das Angebot an Symbolsätzen in Excel 2010

Diese Vielfalt nutze ich, um die Besucherzahlen einer Messe visuell darzustellen und dabei das Auf und Ab deutlich zu machen.

Um den in der folgenden Abbildung gezeigten Zustand zu erreichen, gehe ich wie folgt vor:

Schritt 1: Das Set aus drei farbigen Pfeilen zuweisen

Ich markiere den Bereich B2:B14 und wähle über Start – Bedingte Formatierung unter Symbolsätze in der Kategorie Direktional - 3 Pfeile (farbig) aus. Für die Verteilung der Symbole ist eine Regel hinterlegt.

Wie lässt sich beeinflussen, wo welche Pfeile erscheinen?

Über Bedingte Formatierung – Regeln verwalten gelange ich zum Manager für Regeln zur Bedingten Formatierung. Hier kann ich die Regel, die bei der Auswahl des Symbolsatzes automatisch gewählt wurde, nachträglich anpassen.

Die Regel ist im Moment eine prozentuale Auswertung der Anzahl der Zahlen, bei der die Werte der Zahlen zunächst keine Rolle spielen.

Dies ist die Standardeinstellung der Regel

Ein Beispiel:

  • Bei 10 Zahlen ist die 7te (67% von 10, gerundet) von unten die Grenze für den grünen/gelben Pfeil,
  • die 3te (33% von 10, gerundet) von unten der Grenzwert für den roten/gelben Pfeil.

Das bedeutet: Bei 10 unterschiedlichen Zahlen erhalten 3 den roten Pfeil.

Gibt es Zahlen, die mehrfach vorkommen, so kann sich die Vergabe der verschiedenen Farbpfeile verschieben. Weitere Informationen dazu im Beitrag Was bewirkt eigentlich die Regel ‘Obere 10%’?.

Mit Hilfe vom Typ kann ich die Regel auch mit festen Werten, Formeln oder Quantilen aufbauen.

Die Regel von Prozent auf feste Werte umstellen

Für das Beispiel der Besucherstatistik lege ich die Grenzen neu fest und zwar über den Typ Zahl . Ich trage die unten gezeigten Parameter ein.

Den Typ "Prozent" durch den "Zahl" ersetzen

Die Optik “zuspitzen”

Neu in Excel 2010 ist die Möglichkeit, die verschiedenen Symbolsätze zu kombinieren. Dies erledige ich – wie unten zu sehen – über die Schaltflächen der Symbole. So kann ich individuell die aus meiner Sicht besser passenden Symbole zusammenstellen.

Neu in Excel 2010: Die Symbole lassen sich flexibel kombinieren

Das macht es mir möglich, den im oberen Beispiel verwandten Pfeilsymbolsatz so zu verändern, dass anstelle des roten Pfeils clip_image001 ein rotes X clip_image002 zum Einsatz kommt. Auf diese Weise lässt sich ein kritischer Zustand deutlicher hervorheben. Hier das Ergebnis der Kombination von zwei Symbolsätzen.

Excel 2010: Pfeile und Kreuze kombinieren

Die Aussage auf den Punkt bringen

Oft wird gefordert, die Aufmerksamkeit nur auf einen bestimmten Bereich zu lenken – beispielsweise auf die kritischen Besucherzahlen.

Kein Problem, denn in Excel 2010 kann ich die nichtrelevanten Symbole ausblenden. Hierzu wähle ich über die Schaltflächen der Symbole die Option Kein Zellensymbol.

Excel 2010: Für die Aussage unwichtige Symbole einfach ausblenden

FAZIT: Die Jahre mit den kritischen Besucherzahlen springen nun sofort ins Auge.

Excel 2010: Durch das Ausblenden von Symbolen die Aussage auf den Punkt bringen

35 Kommentare zu „Excel 2010: Mit Ampeln & Co. klare Zeichen setzen, aber nach welchem System werden die Farben zugeteilt?“

  • Lisa:

    Mal eine blöde Frage, aber warum lassen sich diese Punkte/Pfeile etc. nicht drucken? *.* bei mir geht alles perfekt und es steht auch so dran wie ich es wünsche, aber auf dem Druck fehlen die Zeichen dann… :(
    Danke für eure Hilfe!

  • Eva Eutebach:

    Hallo Frau Hügemann,

    habe von meinem Vorgesetzten eine Aufgabe bekommen, wo ich eine Ampel einbauen soll. Da ich grundsätzlich mit Wenn-Formel arbeite, komme ich irgendwie nicht ganz zurecht, wie ich das machen soll.

    Kurzer Ausschnitt aus meiner Auswertung:

    Ist-Kosten Abweichung Tendenz
    Euro Euro %

    9.390,47 9.390,47 100,0%
    0,00 0,00 0,0%
    0,00 0,00 0,0%
    0,00 0,00 0,0%
    9.390,47 -5.609,53 -37,4% HIER SOLL DIE AMPEL REIN

    Unter der Tendenz soll eine Ampel eingebaut werden, die darstellen soll, dass wenn der Prozentsatz im Minusbereich ist (hier -37,4%) soll die Ampel grün sein, im Plusbereich soll rot sein und im Null soll gelb sein.
    Ich weiss es wirklich nicht weiter.
    Können Sie mir vielleicht helfen? Ich wäre Ihnen sehr dankbar!

    Viele Grüße
    Eva Eutebach

    • Hildegard Hügemann:

      Hallo Frau Eutebach,
      hier der Aufbau der Lösung:
      Von Zeile 4 bis 8 stehen in Spalte A die Werte für die Ist-Kosten, in Spalte B die für de Abweichung und in Spalte C die für die Tendenz.
      Sie markieren C4 bis C8 (dort sollen neben den Prozentwerten die Ampeln erscheinen)
      Dann wählen Sie über die Registerkarte Start > Bedingte Formatierung > Neue Regel
      Im folgenden Dialogfeld wählen Sie diese Einstellungen:
      1) Alle Zellen basierend auf ihren Werten formatieren
      2) Bei Formatstil wählen Sie Symbolsätze und darunter bei Symbolart ein Set mit 3 Ampeln
      3) Klick auf Symbolreihe umkehren
      4) unter Typ jeweils den Eintrag Zahl wählen
      5) unter Wert oben für die rote Ampel >0 einstellen und darunter für die gelbe Ampel >=0
      Klicken Sie zum Schluss auf OK. FERTIG!
      Viel Erfolg
      Hildegard Hügemann

  • FloMi:

    Habe schon im Internet alles abgegrast aber immernoch nicht die Lösung zu meinem Problem gefunden.

    Und zwar:

    Ich habe eine Prüfliste. Dort wird angezeigt das meine Aggregate jedes Jahr geprüft werden. Nach der Überprüfung soll ein grüner Haken für ein Jahr angezeigt werden. 30 Tage vor ablauf des Datums soll ein gelbes Ausrufungszeichen aufzeigen und nach ablauf des Jahres soll es ein rotes Kreuz markieren.

    In folgenden Zellen wird nur angezeigt: Zelle 1 = 22.3.2013
    Zelle 2 = 22.3.2014 (Da sollte dann in Zelle 3 ein rotes Kreuz enstehen.
    Nach Überprüfung dann ein grüner Haken. Und 30 Tage vorher ein gelbes Ausrufungszeichen).

    Kann mir da jemand helfen. Weiß nicht wie ich das machen soll????

    mit freundlichen Gruß Florian

  • Franz:

    Hallo,

    bitte entschuldigt, sollte meine Frage hier fehlplaziert sein, aber ich komm einfach nicht weiter. Ich möchte mit Ampelsymbolen Fristen darstellen.
    Datum > 90 Tage sollte grün sein
    Datum < 90 Tage sollte gelb sein
    Datum in der Vergangenheit rot sein.

    Ich möchte dezidiert die Symbole verwenden, und nicht den Text/Rahmen/Hintergrund etc. färben. Bedingte Formatierung ist mir klar, aber soweit ich gesehen habe funktioniert dies mit Datum nicht, oder muss ich hier eine Hilfstabelle verwenden? Vielen Dank für eure Hilfe!

  • Nacho:

    sorry!
    Ich wollte sagen:

    Nach oben, rot
    Nach unten, grün

    • Hallo,
      mit dem Symbolsätzen von Microsoft geht das nicht. Denn eigene Bilder lassen sich nicht in die Bedingte Formatierung von Excel einbauen.
      H. Hügemann

    • Chris:

      Ich hatte das selbe Problem mit den Pfeilfarben und habe es über die Kamerafunktion gelöst.

      Erstelle einfach über die entsprechende Symbolregel die Pfeile wie oben beschrieben und mache danach mit der in Excel implementierten Kamerafunktion vom (beispielweise rotem Pfeil ein Bild) dieses Bild kann dann beliebig manipuliert werden.

      Nachteil: Es ist sehr zeit- und performanceintensiv, hoffe dass Microsoft die Symbolfunktion noch verbessert.

      Sehe dieses Vorgehen eher als Notfalllösung.

  • Nacho:

    Hallo,
    wie kann man die Pfeile so haben?

    Nach oben, rot
    Nach unten, gelb.

    Besten Dank im Voraus!

  • Isabel:

    Guten Tag,

    ich habe eine Tabelle mit 3 Ampelfarben. Die Zelle D33 soll eine rote Ampel darstellen, sobald in D34-39 eine rote Ampel auftaucht; es soll eine gelbe Ampel darstellen, wenn es keine rote Ampel gibt, aber sobald in D34-39 eine gelbe Ampel auftaucht und nur eine grüne Ampel darstellen, wenn alle Zellen (D34-39)eine grüne Ampel haben.

    Es soll in D33 quasi der schlechteste Status von den Zellen (D34-39) dargestellt werden.

    Über Lösungsvorschläge würde ich mich sehr freuen.

    lg

  • Elisa:

    Hallo,
    leider konnte ich bisher noch nicht die richtige Lösung finden und hoffe somit auf Eure Hilfe.
    Ist es möglich, eine Ampel als Symbolisierung des aktuellen Status zu verwenden – jedoch ohne Daten, nur manuell anhand eines Dropdown-Pfeiles? Ich habe eine Tabelle, in welcher ich den Status mit Ampelfarben (z.B. grün=on track, gelb=with issues, etc.) darstellen möchte.
    Lieben Dank und Viele Grüße!!

    • Hildegard Hügemann:

      Hallo,
      leider ist eine Direktauswahl von Symbolen im Listenfeld nicht möglich. Hier ein Workaround:
      Zunächst einmal den Zellbereich markieren, in dem Symbole auswählbar sein sollen.
      Über Daten/Datenüberprüfung im Dialogfeld unter Zulassen den Eintrag Liste wählen.
      Darunter in das Eingabefeld für Quelle eingeben: 1; 2; 3. Sind es vier Zustände, dann 1; 2; 3; 4.
      Anschließend dem immer noch markierten Zellbereich über Start/Bedingte Formatierung/Symbolsätze einen anzahlmäßig passenden Symbolsatz zuweisen.
      Die Bedingte Formatierung erneut aufrufen, auf die Regel doppelklicken und folgende Einstellungen vornehmen:
      - Rechts bei Typ jeweils Zahl einstellen
      - Links daneben von oben nach unten >=4, >=3 sowie >=2 festlegen
      - Nun ganz links die Symbole auswählen
      - Oben ein Häkchen setzen bei Nur Symbo anzeigen

      Nun sind zwar keine Symbole auswählbar, aber nach Eingabe der entsprechenden Zahl erscheint das passende Symbol.

      VIEL ERFOLG!
      Hildegard Hügemann

  • Luca:

    Hallo zusammen

    Habt hier ne super Seite! jedoch finde ich mein Problem hier nicht.

    Es geht darum, dass ich das ganze mit häckchen, ausrufezeichen und kreuzchen machen möchte. Aber nicht mit Zahlen, sondern mit “ja” und “nein”.

    Das heisst:

    wenn ich in zwei nebeneinanderliegenden Felder, beides mal “ja” habe, soll das grüne häckchen im dritten feld erscheinen.

    wenn in einem feld “ja” und im anderen “nein”, dann sollte das gelbe Ausrufezeichen angezeigt werden

    und wenn in beiden “nein” steht, dann soll das rote kreuzchen kommen.

    hoffe es ist so einigermassen verständlich.

    Vielen Dank für eure Hilfe

    • Hildegard Hügemann:

      Hallo,

      diese Aufgabe lässt sich über eine geschachtelte WENN-Funktion lösen, deren Ergebnis anschließend in der Bedingten Formatierung genutzt wird.

      Angenommen, in den Spalten A und B stehen die Ja/Nein-Werte und in Spalte C soll die Auswertung per Symbol erfolgen. Eine Lösung kann dann wie folgt aussehen:

      In Spalte C (Auswertung) wird die folgende Formel erstellt
      =WENN(A2="ja";WENN(B2="ja";2;1);WENN(B2="ja";1;0))
      Diese Formel liefert
      - eine 2 zurück, wenn beide Zellen mit "ja" belegt sind,
      - eine 1, wenn nur eine Zelle ein "ja" aufweist und
      - eine 0, wenn beide Zellen auf "nein" stehen.

      Verschachtelte WENN-Formel in der Spalte Auswertung

      Die Formel kann in Spalte C nach unten kopiert werden.

      Die Spalte bleibt markiert und über Start/Bedingte Formatierung/Symbolsätze wird die Variante 3 Symbole (ohne Kreis)
      ausgewählt.
      Über Start/Bedingte Formatierung/Regeln verwalten und per Doppelklick auf die erstellte Regel werden die unten gezeigten Einstellungen vorgenommen.

      Die Einstellungen für die Regel

      Die Lösung sollte dann wie im folgenden Bild aussehen.

      Die fertige Lösung

      Viel Erfolg
      Hildegard Hügemann

      • Luca:

        Guten Tag,

        Vielen Dank für Ihre fantastische Hilfe!!! Nun habe ich eine super Tabelle, die auch schon gute Feedbacks gegeben hat!!

        Ich danke Ihnen vielmals und grüsse herzlichst

        Luca

      • Luca:

        Vielen Dank!

        Noch eine Frage: Was, wenn es noch einen “Fall C” geben würde?

        MfG

        • Hildegard Hügemann:

          Hallo,
          in dem Fall die Formel wie folgt erweitern:
          =WENN(A4=”ja”;WENN(B4=”ja”;WENN(C4=”ja”;2;1);1);WENN(B4=”ja”;1;WENN(C4=”ja”;1;0)))

          VIEL ERFOLG!
          Hildegard Hügemann

  • [...] Das Zuweisen eines bestimmten Symbolsatzes ist kein Problem (nachzulesen auch im Beitrag vom 3.10.2011). [...]

  • Milkana:

    Hi, die Seite ist wirklich klasse.

    Aber eine Lösung fehlt mir hier noch, bei der sie mir vielleicht weiterhelfen können: Ich habe eine Liste von ca 20 Daten (aka Zellen) und will den jeweils übergeordneten Wert als Hauptwert haben und von da aus soll er mir per Pfeil anzeigen, ob der jetzige Wert kleiner, gleich oder größer ist. Wie geht das?

    1:
    A1=Ausgangswert
    A2=kleiner, größer, gleich A1?

    2.
    A2=Ausgangswert
    A3=kleiner, größer, gleich A2?

    3.
    A3 … A4 … usw.

    Wie bekomme ich das hin?

    • Hildegard Hügemann:

      Hallo,

      eine Lösung ist möglich, allerdings nur über eine Hilfsspalte.

      Wenn in A1 der Ausgangswert steht und in A2 der Wert, für den ein Vergleich gewünscht wird, dann würde in B2 folgende Formel stehen:
      =WENN(A2>A1;1;WENN(A2=A1;0;-1))

      In B2 ergibt sich also 1, wenn A2 größe als A1, eine Null, wenn A2 gleich A1 und -1, wenn A2 kleiner als A1. Anhand der drei möglichen Werte 1, 0 und -1 lässt sich dann in B2 (also direkt NEBEN A2) ein passendes Symbol platzieren. Hier die Vorschau, in der alle drei Fälle zu sehen sind:

      Dazu wird B2 markiert und über Bedingte Formatierung/Symbolsätze beispielsweise die Variante 3 Dreiecke ausgewählt.

      Über Bedingte Formatierung/Regeln verwalten und per Doppelklick auf die eben definierte Regel werden im folgenden Dialogfeld die unten gezeigten Einstellungen vorgenommen.

      Der Inhalt von B2 kann dann mit Strg + C (Kopieren) auf jede der Folgezellen (B4, B6, B8 …) übertragen werden (mit Strg + V). Die Zellbezüge werden automatisch angepasst und die Bedingte Formatierung wird beim Kopieren gleich mitgeliefert. FERTIG.

      Viel Erfolg
      Hildegard Hügemann

  • Alexander:

    Hallo,
    ist es eig auch möglich, eine ampel als symbolisierung des aktuellen status zu verwenden? also mal angenommen ich habe eine tabelle z.b. von a1 bis h10 und will am ende von h1 bis h10 den status darstellen. hiebei kommen allerdings keine zahlen vor, sondern nur text.. das man sagt, in der einen zeile von a1 bis h1 ist alles ausgefüllt: ampel grün… in der anderen zeile von a2 bis h2 sind ein paar kästchen leer: ampel orange.. und in der dritten zeile von a3 bis h3 ist der status nicht mehr aktiv: ampel rot…
    oder ist es einfach nur möglich eine ampel (oder ähnliches) darzustellen und per mausklick den status einzustellen?? klickt man auf grün.. status aktiv.. das wär eig eine sehr gute möglichkeit.
    oder muss man dazu mit makros arbeiten??
    viele grüße!!

  • Dani:

    Hallo zusammen,

    prinzipiell habe ich mit der bedingten Formatierung keine Probleme. Aber einen kleinen Schönheitsfehler würde ich gern wegbringen.
    Alle Ampelstatus, welche auf der Spalte mit den Werten liegen, werden links angeordnet. Ich hätte sie aber gerne rechts, ohne eine zusätzliche Spalte.
    Hat hierfür jemand einen Tipp?

    Danke im Voraus

  • Frank Neumann:

    Hallo zusammen,

    wir arbeiten mit Excel 2010 und wollen Controllinginformationen möglichst einfach und graphisch darstellen. Wir suchen nach Ad Inns o.ä. mit dem sich leicht Tachos, Wasserfälle, Schieberegler oder Listen mit Linien einbauen lassen. Gibts hier was einfaches?

    Wir kommen leider über die schon sehr genial eingebauten bedingten Formatierungen und Sparklines sowie über die verbesserten Diagrammoptionen nicht so recht weiter.

    Bin für jeden Tipp dankbar. Viele Grüße!

  • Pascal:

    Hallo,

    ich habe folgendes Problem: ich habe 200 Zeilen mit Verkaufsstellen. Diese gehen von oben nach unten. Daneben habe ich die Tage 01.02 bis z.b jetzt 23.02.

    Ich habe hinter dem 23. Tag nun eine Sparkline eingefügt, wo ich die Verkäufe auf Tagesebene grafisch darstellen kann.

    Ich möchte nun noch einen Pfeil hinter die Sparklines setzen, der mir einen Aufschluss darüber gibt, wie die Tendenz der Verkäufe aussieht (steigen sie gerade an, flachen sie gerade ab). Leider bin ich noch nicht auf eine Lösung gekommen.

    Könnt ihr mir helfen?

    • Hildegard Hügemann:

      Hallo,
      um herauszufinden, wie die Entwicklung vom 22. zum 23. Februar ist, würde eine einfache Subtraktion reichen. Angenommen, die erste Verkaufsstelle seht in Zeile 3, die Werte für den 22. und 23. in den Spalten X und Y und die Sparklines sind in Spalte Z.
      In Zelle AA4 könnte nun folgende Formel stehen: =Y3-X3.
      Diese Formel wird nach unten kopiert.
      Der Bereich bleibt markiert. Über Bedingte Formatierung – Neue Regel wird dann die unten gezeigte Konfiguration eingestellt.
      Trend mit Pfeil
      Fertig
      Viel Erfolg

      [Aktualisiert]

  • Hrasvelgr:

    Servus, schöne Zusammenstellungen.

    Allerdings habe ich mit Symbolen ein Problem, welches google leider noch nicht lösen konnte.

    Wie kann ich die Symbole bearbeiten?
    So bräuchte ich z.b. den grünen Pfeil nach unten und den roten nach oben.
    (z.b.: etwas wird billiger, als grün, wird teurer, also rot)
    Finde leider keine Möglichkeit.

    lg

    • Hildegard Hügemann:

      Hallo, den Wunsch haben sicher viele Anwender, die intensiver mit der Bedingten Formatierung arbeiten. Aber im Moment ist er (noch) nicht erfüllbar, denn eigene Symbole lassen sich nicht importieren, vorhandene nicht anpassen. Mal schauen, was die neue Office-Version bringt, die möglicherweise noch in diesem Jahr erscheint.
      H. Hügemann

  • M. Keitemeier:

    Hallo zusammen,

    vielen Dank für die Hinweise. Ich habe aber noch eine Frage: Kann ich mir mit den Symbolen nur Trends anzeigen lassen? Oder kann ich mit dem Symbol sagen: grüner Pfeil nach oben, wenn Zelleninhalt positiv, roter Pfeil, wenn Zelleninhalt negativ?

    Vielen Dank für eine Info…
    Schöne Grüße…
    Matthias

    • Hildegard Hügemann:

      Hallo,
      mit den Symbolsätzen können nicht nur Trends veranschaulicht werden. Auch klar definierte Zustände wie beispielsweise Zellinhalt positiv ODER Zellinhalt negativ können per Symbol signalisiert werden.

      Der Klick auf diesen Link zeigt eine Beispiellösung.

      Da es ab Excel 2010 möglich ist, ‘Kein Zellensymbol’ auszuwählen, gibt es mehr Flexibilität bei der Anzahl der darzustellenden Zustände.
      Viel Erfolg
      Hildegard Hügemann

  • Gratulation zu diesen hervorragenden Tipps und guten Informationen. Wir arbeiten intensiv mit Word und haben hier einige unbekannte Hinweise gefunden. Besten Dank

Kommentieren