Farbenwechsel durch bedingte Formatierung in MS Excel

Maria Drießen

In der Praxis erhält man häufig in einer Excel-Datei eine große Anzahl an Daten. Diese Daten erscheinen zuerst einmal unstrukturiert. In vielen Fällen fehlt eine Struktur bzw. ein Muster, um die Daten lesbar zu machen.

Bunte Regenschirme aus der Froschperspektive

Hierfür stehen in MS Excel zahlreiche Möglichkeiten zur Verfügung. Eine Methode möchte ich Ihnen im Folgenden näher erläutern. Sie ermöglicht einen Farbwechsel innerhalb der Zeile, sobald sich in einer bestimmten Spalte der Inhalt ändert. Dadurch können Blöcke besser erfasst werden. In unserem Beispiel wäre dies der Sprung von einer Kundennummer zur nächsten.

Screenshot: Excel-Spalten mit Kundennummern, Buchungsbelegen und Rechnungsbeträgen

Formatierung

Die Funktion „Bedingte Formatierung“ kann über den Reiter Start unter dem Bereich „Formatvorlagen“ erreicht werden. Es können neben der Auswahl von vordefinierten Regeln auch eigene Regeln erstellt werden. Für den Farbwechsel muss eine „Neue Regel“ erstellt werden. Der Regeltyp ist eine „Formel zur Ermittlung der zu formatierenden Zeile“. Diese Formel muss wahr sein, damit die Formatierungsregel angewendet wird. In unserem Beispiel möchten wir, dass abwechselnd zwei Farben angezeigt werden, sobald sich die Kundennummer ändert. Dafür müssen zwei Regeln erstellt werden.

Screenshot: Neue Formatierungsregel in MS Excel anlegen

Formel

Um einen Farbwechsel zu ermöglich, müssen insgesamt drei verschiedene Formeln angewendet werden.

Screenshot: Excel-Formel Rest

Zur Erklärung werden die drei Formeln kurz einzeln betrachtet.

Screenshot: Excel-Formel N-Wert

Die erste Formel „=N(Wert)“ wird verwendet, um einen nicht-numerischen Wert in eine Zahl oder ein Datum in eine serielle Zahl umzuwandeln. Daneben gibt sie aber auch, sobald es sich um einen wahren Ausdruck handelt, eine „1“ wieder.

Diese Formel wird genutzt, um zwei Werte zu vergleichen. Es wird abgefragt, ob die beiden Werte unterschiedlich sind -> A2 <> A1. Wenn die beiden Werte gleich sind, gibt die Formel also eine „0“ wieder, da die Frage, ob die Werte ungleich sind, mit Nein beantwortet wird. Ansonsten gibt sie eine „1“ wieder. In unserem Beispiel wären das zwei übereinanderliegende Werte in der Spalte „Kundennummer“.

Wichtig ist, dass der Wert mit dem darüberliegenden Wert verglichen wird und nicht mit dem nachfolgenden -> A2 wird mit A1 verglichen.

Screenshot: Excel-Spalten mit Kundennummern, Buchungsbelegen und Rechnungsbeträgen mit der Formel N-Wert

Es sollen pro Zeile immer die aktuelle Zeile und alle darüberliegenden Zeilen mit der vorherigen Zeile und allen darüber liegenden Zeilen verglichen werden. Somit entsteht ein Bereich der miteinander verglichen wird. Innerhalb der Formel werden beide Bereiche wie folgt dargestellt:

Screenshot: Excel-Formel N-Wert

Die zweite Formel „=SUMMENPRODUKT(Bereich1; Bereich2;…)“ addiert alle Ergebnisse der ersten Formel zusammen.

Screenshot: Excel-Formel SummenProdukt

In unserem Beispiel erkennt man, dass die Summe immer eine Zahl höher springt, sobald die Kundennummer sich verändert.

Screenshot: Excel-Spalten mit Kundennummern, Buchungsbelegen und Rechnungsbeträgen mit der Formel N-Wert und SummeProdukt

Als letzten Schritt benötigen wir noch eine Bedingung, die zwei Antworten haben kann, damit wir zwei unterschiedliche Farben nutzen können.

Hierfür verwenden wir die Formel „=REST(Zahl; Divisor)“. Die Ergebnisse aus den beiden ersten Formeln werden durch zwei geteilt. Der Rest ergibt somit entweder eine „1“ bei einer ungeraden Zahl und eine „0“ bei einer geraden Zahl. So erhalten wir beide „WAHR“-Aussagen unserer gesuchten Formel. Entweder ergibt unsere Formel eine „1“, dann wird in unserem Beispiel die Zeile grau gefärbt, oder sie ergibt eine „0“ und dann wird die Zeile in unserem Beispiel grün gefärbt.

Screenshot: Excel-Formel Rest
Screenshot: Formatierungsregel in MS Excel bearbeiten
Screenshot: Formatierungsregel in MS Excel bearbeiten

Arbeitsvorlage

Damit Ihnen die Anwendung meines beschriebenen Bespiels leichter fällt, stelle ich Ihnen unter dem folgenden Download eine Arbeitsvorlage zur Verfügung. Probieren Sie es aus, mit ein wenig Übung wird die Formatierung in MS Excel problemlos klappen.

Newsletter mit interessanten Beiträgen

Was gibt es Neues? Profitieren Sie von aktuellen und kostenfreien Arbeitsvorlagen, Buchartikeln, Seminarempfehlungen, Best-Practice-Ansätzen uvm. – so können Sie Ihre Arbeitsabläufe optimieren und neues Wissen in der Praxis einsetzen.

Hier geht es zur Anmeldung.

Zur Übersicht