Jetzt gibt’s eins auf’s Auge… 6

Mit bedingter Formatierung Ergebnisse beeindruckend präsentieren!
 

Im heutigen Artikel geht es darum, die erstellten Zahlenfriedhöfe ein wenig „aufzuhübschen“.

Dabei wollen wir jedoch keine separaten Charts und Grafiken erstellen, sondern die eigentliche Datentabelle so gestalten, dass wichtige Informationen auf einen Blick ersichtlich sind.

Excel bietet dazu das Konzept der bedingten Formatierung. Das heißt, wenn der Wert in einer Zelle eine vorher definierte Bedingung erfüllt, dann erhält er automatisch ein bestimmtes Format.

Und das Allerschönste ist: Die bedingte Formatierung passt sich automatisch an, wenn sich ein Wert in einer Zelle wieder ändert.

Und so geht’s:

Bedingte Formatierung

Als Beispieltabelle habe ich eine Übersicht mit der Feinstaubbelastung verschiedener Messpunkte in Nordrhein-Westfalen erstellt:

Tabelle Feinstaubbelastung

Ausgangstabelle: Feinstaubbelastung NRW

Die gewünschte Funktion befindet sich im Menü „Start“ unter „Bedingte Formatierung“:

Bedingte Formatierung

Menü: Bedingte Formatierung

Markieren wir also zunächst den Tabellenbereich mit den zu formatierenden Zellen und rufen dann nochmal das Menü mit der bedingten Formatierung auf. Sobald wir mit der Maus im Menü eine bestimmte vordefinierte Formatierung überfahren, sieht man sofort das Ergebnis in seiner Tabelle:

Datenbalken

Bedingte Formatierung: Datenbalken

Oder mit Farbskalen …

Farbskalen

Bedingte Formatierung: Farbskalen

… oder mit verschiedenen Symbolsätzen, wie z.B. Ampeln:

Symbolsätze

Bedingte Formatierung: Symbolsätze

Bleiben wir bei den Ampel-Symbolen: Hier sehen wir, dass momentan die hohen Werte in Grün und die niedrigen Werte in Rot dargestellt werden. Was eigentlich das genaue Gegenteil von dem ist, was wir in unserer Tabelle mit Feinstaubbelastungen zeigen wollen.

Außerdem möchten wir einen vierten Ampelwert einführen und die Schwellenwerte für die Farben selbst definieren. Also passen wir die Regeln einfach an.

Dazu rufen wir noch einmal die Symbolsätze auf und wählen jetzt den Punkt „Weitere Regeln…“:

Weitere Regeln

Weitere Regeln definieren

Hier sieht man zunächst, wie die aktuellen Ampelfarben zustande kommen. Es wird also standardmäßig eine prozentuale Verteilung vorgenommen:

Prozentuale Verteilung

Prozentuale Verteilung

Passen wir die Einstellungen also einfach unseren Wünschen an. Im Feld „Formatstil“ sollte der Eintrag „Symbolsätze“ stehen. Danach wählen wir unten die Symbolart „4 Ampeln“ aus und setzen den Haken im Feld „Symbolreihenfolge umkehren“. Jetzt können wir die verschiedenen Schwellwerte und Typen eingeben:

Regeln anpassen

Regeln anpassen

Und so sieht dann das neue Ergebnis aus:

Neu formatierte Tabelle

Neu formatierte Tabelle

Das ist doch gleich deutlich besser, oder?

Jetzt wollen wir noch einen Schritt weitergehen. Es lassen sich nämlich auch mehrere Formatierungsregeln miteinander kombinieren. Löschen wir dazu zunächst die vorhandene bedingte Formatierung:

Regeln löschen

Regeln löschen

Anschließend wählen wir den Menüpunkt „Regeln verwalten…“ und fügen dort eine neue Regel ein:

Neue Regel einfügen

Neue Regel einfügen

Mit der ersten Regel wollen wir als Formatstil „Datenbalken“ darstellen. Die restlichen Einstellungen übernehmen wir unverändert und klicken auf „OK“.

Regel 1: Datenbalken

Regel 1: Datenbalken

Wir sehen im Regeln-Manager unsere erste Regel mit den Datenbalken und fügen jetzt eine weitere Regel hinzu:

Regel-Manager

Regel-Manager

Für die zweite Regel ändern wir als erstes den Regeltyp und markieren dazu den Eintrag „Nur obere oder untere Werte formatieren“. Dann tragen wir z.B. den Wert 5 ein, um in unserer Tabelle die fünf höchsten Werte besonders hervorzuheben. Und schließlich legen wir noch fest, wie diese Werte formatiert werden sollen:

Regel 2: Obere Werte

Regel 2: Obere Werte

Zum Beispiel mit einem roten Rahmen …

Format: Rahmen

Format: Rahmen

… und zusätzlich mit fetter, roter Schrift:

Format: Schrift

Format: Schrift

Im Regeln-Manager sehen wir nun unsere beiden Regeln. Gut zu wissen: Über die Pfeiltasten lässt sich die Reihenfolge der Regeln verändern. Sollte einmal der Fall auftreten, dass zwei widersprüchliche Regeln anzuwenden wären, dann greift immer nur die erste Regel in der Liste:

Reihenfolge ändern

Reihenfolge ändern

Und so sieht unsere neue Tabelle aus: Zusätzlich zu den Zahlenangaben werden blaue Balken angezeigt und die fünf höchsten Werte werden außerdem noch rot hervorgehoben:

Ergebnis: Mehrere Regeln

Ergebnis: Mehrere Regeln

Und wie ich in der Einleitung schon erwähnt habe, passen sich die Farben und Formate dynamisch an. Das heißt, sobald sich jetzt ein Wert in der Tabelle ändert, wird automatisch auch die Formatierung angepasst.

Perfekt!

Excel bis Version 2003:
In den älteren Excel-Versionen findet man diese Funktion unter dem Menü „Format – Bedingte Formatierung“. Allerdings beschränken sich die Möglichkeiten im Wesentlichen auf Zellenfarben und Schriften. Die schönen Balken und Symbole gibt es hier leider nicht.

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

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

6 Gedanken zu “Jetzt gibt’s eins auf’s Auge…

  • Avatar-Foto
    Horst Schulte

    Danke für den ausführlichen und informativen Artikel. Vielleicht hast du eine Idee, wie man zwei Werte miteinander vergleichen kann (BUDGET vs. IST) und die positive oder negative Abweichung (ohne Extraspalte) mithilfe der Symbolsätze entsprechend kennzeichnen kann? Ich finde dafür einfach keine Lösung. Aber das muss doch gehen…

    LG Horst

    • Avatar-Foto
      Martin Weiß

      Hallo Horst,

      wenn Du sagst „ohne Extraspalte“ meinst Du dann wahrscheinlich etwas in dieser Art:
      Ergebnistabelle

      Im Verbindung mit Symbolsätzen geht das zwar, ist aber relativ umständlich. Warum? Du musst für jede einzelne Zelle, in der Du ein Symbol haben willst, eine eigene Regel nach folgendem Schema definieren:
      Neue Regel definieren

      Bei vier Tabellenzeilen hast Du somit vier Regeln, bei größeren Tabellen entsprechend mehr:
      Regelliste

      Das Problem dabei ist, dass man bei einer Regel mit Symbolsätzen leider nicht mit relativen Zellbezügen arbeiten kann. Für eine Handvoll Zeilen ist das vielleicht noch praktikabel, bei größeren Tabellen kommst Du um eine Extraspalte wohl nicht herum…

      Aber vielleicht hat ja ein anderer Leser noch einen besseren Vorschlag.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Horst Schulte

    Jetzt weiß ich wenigstens, woran ich dabei immer gescheitert bin. Ich hatte das mit den absoluten Bezügen nämlich nicht beachtet bzw. nicht kapiert. Danke für die Erläuterungen.

  • Avatar-Foto
    Natalie

    Hallo Martin,

    ich habe in Zelle G37 folgende Formel stehen:

    =SVERWEIS(G35&G36;WAHL({1.2};WVQ2:WVQ291&WVR2:WVR291;WVS2:WVS291);2;FALSCH)

    Ich möchte die Zelle G37 so formatieren das zum einen das Ergebnis aufs der SVERWEIS Formel als Währung angezeigt wird und dass die Zelle grün wird. Es funktioniert aber nicht.

    Genau dasselbe habe ich in Zelle B37 und da funktioniert es.

    HILFEEE!!!

    Vielen Dank
    Grüße Natalie

    • Avatar-Foto
      Martin Weiß

      Hallo Natalie,

      Du bist wirklich ganz sicher, dass in B37 exakt die gleiche Formel steht? Und dort auch exakt das gleiche Format eingetragen ist?
      In jedem Fall funktioniert das Währungsformat nur, wenn als Ergebnis auch eine echte Zahl zurückgeliefert wird. Falls der SVERWEIS jedoch als Text formatierte Zahlen zurückliefert, wird es nicht klappen. Hängt einfach davon ab, was bei Dir in WVS2:WVS291 für Daten stehen.

      Schöne Grüße,
      Martin