Excel: bedingte Formatierung

5. Juli 2020

Inhaltsverzeichnis

  1. Excel – bedingte Formatierung festlegen
  2. Excel: bedingte Formatierung mit Hervorhebung von Zellen
  3. Excel: bedingte Formatierung mit Regeln für obere oder untere Werte
  4. Individuelle Regeln für die bedingte Formatierung in Excel erstellen

 

Excel ist ein hervorragendes Tool, um auch große Datenmengen und Tabellen anschaulich unterzubringen – jedenfalls meistens. Doch manchmal ist die Zahl der ausgefüllten Felder in einer Arbeitsmappe so enorm, dass der Überblick verloren gehen kann. Für diesen Fall bietet Excel die bedingte Formatierung, die anhand optischer Effekte bestimmte Zellen hervorheben kann. Das kann zum Beispiel durch Farbgebung geschehen.

Der Clou dabei: Sie müssen die Zellen gar nicht manuell einfärben, denn genau dafür gibt es die bedingte Formatierung in Excel. Schon bei kleineren Tabellen oder Datenmenge kann diese Option dabei helfen, den Überblick zu bewahren und bestimmte Werte mit einer Signalfarbe zu besetzen.

1. Excel – bedingte Formatierung festlegen

Simplifizierung ist meist die richtige Methode, um Sachverhalte anhand von Beispielen so darzustellen, dass sie sofort verstanden werden.

Darum haben wir uns für eine einfache Tabelle entschieden: die Kostenaufstellung für Einkäufe, nach Monaten geordnet.

Wir stellen uns also einfach vor, dass wir am Ende eines jeden Kalendermonats alle Kontoauszüge und Kassenbons durchgehen und die Summe jeweils in einer Tabelle eintragen, in diesem Fall in die Spalte B.

 

excel-einfache-liste-ohne-formeln
Eine einfache Liste, ganz ohne Formeln – es geht nur um die Zahlen in Spalte B.

Das ist zunächst einmal ziemlich übersichtlich, aber stellen Sie sich einfach vor, Sie würden eine solche Tabelle über Jahre hinweg pflegen, um herauszufinden, wie sich die Haushaltskosten erhöht haben.

Dann würde der Überblick irgendwann verloren gehen. Es bietet sich daher an, die bedingte Formatierung in Excel bereits zu einem frühen Zeitpunkt einzuführen – zum Beispiel jetzt.

Dafür müssen Sie zunächst definieren, welche Kriterien Sie anlegen, ab welchem Betrag die Ausgaben also zu hoch sind für das Budget, wann sie im Rahmen liegen und wann Sie weniger Geld als geplant für Einkäufe ausgegeben haben.

Um die Farben, die zur Hervorhebung der Zahlen dienen, nicht händisch in Excel eintragen zu müssen, was besonders bei größeren Tabellen zu umständlich wäre, wenden Sie nun die Excel-bedingte Formatierung an, indem Sie zunächst die Zellen mit den Geldbeträgen komplett markieren und im Reiter Start auf „Bedingte Formatierung“ klicken.

farbskalen-passender-farbverlauf
Über „Bedingte Formatierung“ gelangen Sie zu den Farbskalen. Wählen Sie dort einen für Sie passenden Farbverlauf aus.

Im Dropdown-Menü gehen Sie auf die Option „Farbskalen“ und wählen einen der angebotenen Farbverläufe aus. Wir haben uns im Beispiel für die Rot-Gelb-Grün-Farbskala entschieden, was sich folgendermaßen darstellt:

rot-geld-gruen-skala-excel
Die Rot-Gelb-Grün-Skala färbt den höchsten Wert rot ein, den kleinsten grün und die dazwischenliegenden Werte orange und gelb.

Was aber macht Excel hier, um zu diesem Farbverlauf zu kommen? Ganz einfach: das Tool errechnet einen Mittelwert.

Dieser liegt im Zeitraum Januar bis Juni 2020 bei einem Betrag von 475,63 Euro, das können Sie im Dokument rechts unten ablesen, wenn Sie alle sechs Zellen markieren:

bedingte-formatierung-excel
Für die bedingte Formatierung berechnet Excel den Durchschnittswert aller Monate.

Alle Werte, die größer sind als 475,63 Euro, färbt Excel also rot oder orange ein (der Juni erscheint in fast reinem Gelb, weil der Betrag von 480,64 Euro fast exakt dem errechneten Mittelwert entspricht – gelb ist also die neutrale Farbe in der Rot-Gelb-Grün-Skala).

Alle Beträge, die unterhalb des Durchschnitts liegen, im Beispiel also die Werte aus dem Februar und dem April, werden gelbgrün oder grün dargestellt.

Wenn Sie am 30. Juni aber noch einen neuen DVD-Player für 100 Euro bestellt haben und den Betrag im Juni entsprechend nachbuchen müssen, dann ändert sich die Skala, da sie natürlich dynamisch:

tabelle-veraenderung
Weil der Juni durch den DVD-Player teurer geworden ist, ergeben sich für die Tabelle ebenfalls Veränderungen. Der Mittelwert der Ausgaben steigt dann auch auf gerundete 492,30 Euro.

Doch bietet Excel mit der bedingten Formatierung noch eine Menge weiterer Optionen, die wir Ihnen im nächsten Kapitel zeigen möchten.

2. Excel: bedingte Formatierung mit Hervorhebung von Zellen

Mit einem Klick auf den Button „Bedingte Formatierung“ sehen Sie rasch, dass noch vier weitere Möglichkeiten existieren, Zellen mit Bedingungen zu formatieren.

Um diese Formatierungen anzuwenden, haben wir für das Beispiel eine fiktive Einkaufsliste für das Jahr 2019 erstellt. Der Mittelwert dieser Liste beträgt 483,19 Euro:

monatliche-liste-haushaltsausgaben
Eine komplette monatliche Liste aller Haushaltsausgaben für 2019.

Wenn Sie nun auf den Button klicken, lautet die erste Option: „Regeln zum Hervorheben von Zellen“.

Hier können Sie zum Beispiel die Funktion „Größer als“ nutzen. Diese ist hilfreich, wenn Sie nicht von einem Mittelwert ausgehen wollen, der sich aus den Ausgaben ergibt, sondern aus von Ihnen vordefinierten Werten.

So können Sie festlegen, dass alle Ausgaben, die höher sind als beispielsweise 510 Euro, farblich gekennzeichnet werden sollen.

bedingte-formatierung-tabelle
Links wird gerade die bedingte Formatierung für „Größer als“ erstellt. Rechts wird – in dieselbe Tabelle – die bedingte Formatierung „Kleiner als“ hinzugefügt.

Parallel dazu können Sie für dieselbe Tabelle einen zweiten Wert einfärben – als Beispiel, s. Bild, haben wir gewählt, dass Beträge unterhalb von 420 Euro grün dargestellt werden.

So können Sie auf einen Blick erkennen, in welchen Monaten Sie weniger als 420 und mehr als 510 Euro ausgegeben haben und in welchen Monaten Sie im dazwischenliegenden Zielkorridor geblieben sind.

Möglich ist das, weil eine einmal getätigte bedingte Formatierung erhalten bleibt, auch wenn Sie für dieselbe Tabelle zusätzlich eine zweite Bedingung formulieren. Die Regeln werden nur dann rückgängig gemacht, wenn Sie über den Button „Bedingte Formatierung“ den Eintrag „Regeln löschen“ anklicken.

3. Excel: bedingte Formatierung mit Regeln für obere oder untere Werte

Der nächste Punkt im Dropdown-Menü beim Button „Bedingte Formatierung“ heißt „Obere/untere Regeln“.

Wenn Sie diese anwenden, dann werden von den markierten Feldern genau jene zehn Zellen angezeigt, die entweder die Werte der obersten oder untersten zehn Prozent darstellen, die zehn höchsten bzw. die zehn niedrigsten Werte oder alle Werte, die sich über bzw. unter dem Durchschnitt befinden.

Das erste Bild zeigt die Ausgangstabelle, in der sämtliche Beträge von Januar 2019 bis Juni 2020 angezeigt werden (ganz einfach, um mehr Daten zur Verfügung zu haben); rechts davon werden die obersten zehn Prozent in Rot sowie die untersten zehn Prozent in Grün eingefärbt.

datensaetze-oberste-unterste-werte
Bei 18 Datensätzen gibt es natürlich nur einen Wert für die obersten und einen für die untersten zehn Prozent.

Aussagekräftiger wird es, wenn Sie sich die in absoluten Zahlen zehn höchsten (linke Bildhälfte) sowie zehn niedrigsten Werte (rechts) anzeigen lassen:

letzter-monat-besonders-teuer-oder-guenstig
Hier können Sie herauslesen, welche der letzten Monate besonders teuer und welche günstig gewesen sind.

In Tabellen mit deutlich mehr Datensätzen bzw. Felder können Sie die zehn höchsten und die zehn niedrigsten Werte natürlich parallel anzeigen lassen. Das würde Sinn ergeben, wenn man etwa auch noch die Zahlen von 2018 mit aufnimmt:

datensaetze-in-einer-tabelle-anzeigen
Bei 30 Datensätzen ist es sinnvoll, die zehn höchste und die zehn kleinsten Werte in einer einzigen Tabelle anzuzeigen.

Die Zahl zehn ist übrigens excelseitig voreingestellt. Wenn Sie die Funktion ausführen, dann werden Sie feststellen, dass Sie jede beliebige Ziffer eingeben können:

zahl-gefaerbte-element-veraendern
Klicken Sie auf die Pfeile neben der Ziffer im rot markierten Kreis, um die Zahl der gefärbten Elemente zu verändern.

Tatsächlich können Sie auch alle Zellen einfärben, und zwar indem Sie die Option „Über dem Durchschnitt“ bzw. „Unter dem Durchschnitt“ anklicken.

Das haben wir für diese Tabelle – der Durchschnittswert beträgt übrigens 473,52 Euro – getan:

tabelle-rot-gruen-mittel
Eine Tabelle, die exakt anzeigt, in welchen Monaten Sie mehr (rot) und weniger (grün) als im Mittel ausgegeben haben.

Eher eine Spielerei ist die Möglichkeit, für jede Zelle einen Graphen zu generieren. Das geht über „Bedingte Formatierung“ und „Datenbalken“.

Dort, wo der höchste Wert liegt, nämlich im Juni 2020, deckt der Balken die komplette Zelle ab, in den anderen Zellen dementsprechend prozentual weniger.

dantenbalkens-optisch-schoen-selten-benoetigt
Die Anzeige eines Datenbalkens ist optisch hübsch anzusehen, wird aber eher selten benötigt.

Auch die Anwendung von Symbolsätzen (nächster Punkt im Dropdown-Menü) ist nicht unbedingt nötig.

Gerade beim Thema Ausgaben zeigt sich, dass die voreingestellte Formatierung in Excel fast ausschließlich die Farbe Grün für die höchsten Werte bereithält.

gruen-hohe-rot-niedrige-werte
Fast immer steht Grün für die größten und rot für die niedrigsten Werte.

Dabei sollten große Ausgaben eher rot gekennzeichnet werden.

Eine Ausnahme bildet die im Beispiel verwendete Formatierung (hier steht schwarz für die niedrigeren und rot für die höheren Werte), es handelt sich dabei um die vier Kreise im Bereich „Formen“, beginnend mit Rot:

rot-schwarz-farbauswahl
Die Rot/Schwarz-Farbauswahl verwendet als einziger Symbolsatz Rot für die höchsten Zahlen

4. Individuelle Regeln für die bedingte Formatierung in Excel erstellen

So ist Microsoft eben – nicht alle Voreinstellungen passen uns. Auf die bedingte Formatierung in Excel trifft das auch zu.

Allerdings können Sie, und das ist wiederum der Vorteil der Office-Tools, individuelle Regeln für die bedingte Formatierung erstellen. Hierfür bleiben wir beim Beispiel der Symbolsätze, denn eigentlich wäre eine rote Ampel als Symbol für die höchsten Werte ein sinnvolles Symbol.

neue-regel-symbole-farbgebung-aendern
Mit einer neuen Regel können Sie Symbole und Farbgebung einfach ändern.

Klicken Sie unter „Bedingte Formatierung“ auf „Neue Regel“, stellen Sie den Formatstil auf „Symbolsätze“ um, wählen Sie unter „Symbol“ die Ampel aus und vertauschen Sie dann, wie rechts im obigen Bild dargestellt, die Farben.

Die angegebenen Werte bedeuten, dass die Ampel bei einem Drittel aller 30 Werte auf Rot, bei einem weiteren Drittel auf Gelb und beim letzten Drittel auf Grün steht:

individualisierte-regel-ampel-excel
So sieht die individualisierte Regel für die Ampel aus.

Bei der Individualisierung können Sie alle möglichen Farbkombinationen und Symbole ausprobieren, und das nicht nur bei den Symbolsätzen, sondern auch bei der 2-Farben-Skala, der 3-Farben-Skala und bei den Datenbalken.

Außerdem können Sie natürlich auch die jeweiligen Regeltypen so festlegen, wie Sie das wünschen. Spielen Sie einfach ein bisschen herum, bis Sie die für Sie richtigen Einstellungen gefunden haben.

 



Abo-Team

Ihre Experten im Büro-Kaizen Blog (von links):

Patrick Kurz

Experte für modernes und agiles Arbeiten

Jürgen Kurz

Gründer und Erfinder von Büro-Kaizen

Marcel Miller

Experte für digitales Arbeiten mit Office 365

Auf der Suche nach mehr Tipps zum Thema Office 365? Auf dieser Übersichtsseite haben wir unsere wichtigsten Blogbeiträge zum Thema Office 365 für Sie zusammengestellt.


Hinterlassen Sie einen Kommentar

Ihre E-Mail Adresse wird nicht öffentlich angezeigt.


Schlagwörter: