Bedingte Formatierung mit Formeln: Fristen automatisch markieren

Die eingebauten Regeln der bedingten Formatierung decken viel ab: Werte über einem Grenzwert, Duplikate, Datumsangaben aus der letzten Woche. Sobald eine Markierung aber von zwei Spalten gleichzeitig abhängt, reichen sie nicht mehr. Eine Rechnung ist nicht einfach deshalb überfällig, weil ihr Fälligkeitsdatum in der Vergangenheit liegt, sondern nur, wenn sie außerdem noch nicht bezahlt wurde. Für solche Bedingungen gibt es den Regeltyp mit eigener Formel.

Dieser Artikel zeigt an einer Rechnungsliste, wie Sie solche Regeln aufbauen, warum die Dollarzeichen in der Formel über richtig oder falsch entscheiden und wie mehrere Regeln zusammenspielen. Die beschriebenen Befehle gelten laut Microsoft für Excel für Microsoft 365 sowie Excel 2016 bis 2024.

Das Beispiel: zehn Rechnungen und ein Stichtag

Beispieldatei: Ausgangsrechnungen eines fiktiven Grafikbüros. Spalte D enthält das Fälligkeitsdatum, Spalte E das Zahlungsdatum. Ist E leer, ist die Rechnung offen. In Zelle H1 steht der Stichtag, hier Freitag, der 30.10.2026.

Die Zielsetzung: Offene Rechnungen, deren Fälligkeit vor dem Stichtag liegt, werden rot. Offene Rechnungen, die am Stichtag oder in den sieben Tagen danach fällig werden, also bis einschließlich 06.11.2026, werden gelb. Bezahlte Rechnungen werden grau, unabhängig vom Datum. Am Ende sind zwei Rechnungen über zusammen 2.140,00 € überfällig und drei Rechnungen über 4.295,25 € bald fällig. Offen sind insgesamt sieben Rechnungen über 7.693,65 €.

Ein eigener Stichtag in H1 statt der Funktion HEUTE (TODAY) hat einen praktischen Grund: Sie können damit nachprüfen, ob die Regeln an einem bestimmten Datum das Richtige tun. Für den Alltag schreiben Sie in H1 einfach =HEUTE(). Laut Microsoft gibt HEUTE die fortlaufende Zahl des aktuellen Datums zurück und wird beim Neuberechnen der Mappe aktualisiert. Aktualisiert sich das Datum nicht, sollte unter Datei > Optionen > Formeln die Berechnung auf Automatisch stehen.

Eine Formelregel anlegen

Markieren Sie den Bereich, der eingefärbt werden soll, hier A2:E11. Achten Sie darauf, die Markierung in Zeile 2 zu beginnen, sodass A2 die aktive Zelle ist. Dann:

1. Start > Bedingte Formatierung > Neue Regel.
2. Als Regeltyp Formel für die Ermittlung der zu formatierenden Zellen verwenden wählen.
3. In das Feld Werte formatieren, für die diese Formel wahr ist die Formel eintragen.
4. Über Formatieren die Füllfarbe festlegen und mit OK bestätigen.

Für die überfälligen Rechnungen lautet die Formel:

=UND($D2<>"";$E2="";$D2<$H$1)

Microsoft nennt zwei Bedingungen für solche Formeln: Sie müssen mit einem Gleichheitszeichen beginnen und den Wahrheitswert WAHR oder FALSCH liefern. Die Formel prüft drei Dinge, die alle zutreffen müssen. In D steht überhaupt ein Datum, E ist leer, und das Fälligkeitsdatum liegt vor dem Stichtag. Warum der erste Teil nötig ist, zeigt der Abschnitt über leere Zeilen weiter unten.

Die gelbe Regel für bald fällige Rechnungen:

=UND($D2<>"";$E2="";$D2<=$H$1+7)

Und die graue Regel für bezahlte Rechnungen:

=$E2<>""

Warum die Dollarzeichen entscheiden

Die Formel wird so geschrieben, als stünde sie in der aktiven Zelle A2. Excel wendet sie danach auf jede Zelle des Bereichs an und verschiebt dabei die Bezüge, genau wie beim Kopieren einer Formel. Für Zelle C7 wird aus $D2 also $D7. Wie sich die drei Bezugsarten beim Kopieren verhalten, beschreibt Microsoft so:

Die Faustregel für zeilenweise Markierungen: Dollarzeichen vor den Spaltenbuchstaben, keines vor der Zeilennummer. Feste Einzelwerte wie der Stichtag bekommen zwei Dollarzeichen. Beim Eintippen können Sie mit F4 zwischen den Bezugsarten wechseln.

Mehrere Regeln: Reihenfolge und Konflikte

Rechnung R-2026-104 vom 23.10.2026 erfüllt zwei Regeln zugleich. Sie ist überfällig, und ihr Datum liegt auch vor dem 06.11.2026, die gelbe Regel trifft also ebenfalls zu. Was dann geschieht, regelt Microsoft eindeutig: Widersprechen sich zwei Formate, etwa zwei verschiedene Füllfarben, gilt die Regel, die im Dialog Manager für Regeln zur bedingten Formatierung weiter oben steht. Widersprechen sie sich nicht, zum Beispiel Fettschrift aus der einen und eine Füllfarbe aus der anderen Regel, werden beide angewendet.

Für das Beispiel ergibt sich daraus diese Reihenfolge, von oben nach unten:

1. grau: =$E2<>"" (bezahlt)
2. rot: überfällig
3. gelb: bald fällig

Steht Gelb über Rot, werden auch die beiden überfälligen Rechnungen gelb, denn beide erfüllen die gelbe Bedingung. Den Manager öffnen Sie über Start > Bedingte Formatierung > Regeln verwalten. Achten Sie darauf, dass oben im Listenfeld Formatierungsregeln anzeigen für das richtige Arbeitsblatt ausgewählt ist. Mit den Schaltflächen Nach oben und Nach unten im Dialog ändern Sie die Rangfolge.

Ein Detail, das häufig zu Verwirrung führt: Laut Microsoft werden neue Regeln standardmäßig ganz oben in die Liste eingefügt. Wer erst Rot, dann Gelb anlegt, hat Gelb also automatisch über Rot und muss umsortieren.

Die graue Regel macht die Bedingung „nicht bezahlt“ in den anderen beiden Regeln streng genommen überflüssig, solange sie ganz oben steht. Nach unserer Einschätzung lohnt es sich trotzdem, $E2="" in jeder Regel zu behalten. Dann bleibt das Ergebnis richtig, auch wenn jemand die Reihenfolge ändert oder die graue Regel löscht.

Typische Fehler mit Ursache und Lösung

Leere Zeilen am Ende werden rot

Viele Listen werden für spätere Einträge großzügiger formatiert, etwa A2:E50. Ohne den Teil $D2<>"" werden dann alle noch leeren Zeilen rot: E ist leer, und eine leere Zelle in D behandelt Excel in einem Größer-kleiner-Vergleich wie die Zahl 0. Null ist kleiner als jedes Datum. Die zusätzliche Bedingung, dass in D etwas stehen muss, verhindert das.

Bezahlte Rechnungen erscheinen als überfällig

Die naheliegende erste Version der Regel lautet =$D2<$H$1. Im Beispiel markiert sie vier Rechnungen rot, darunter R-2026-101 und R-2026-103, die längst bezahlt sind. Eine Fälligkeit allein sagt nichts über den Status. Die Prüfung der Zahlungsspalte gehört in die Regel.

Die Markierung ist um eine Zeile verrutscht

Beginnt die Markierung in Zeile 2, die Formel bezieht sich aber auf $D3, prüft jede Zeile die Zeile darunter. Im Beispiel werden dann ausgerechnet die bezahlten Rechnungen R-2026-101 und R-2026-103 rot, weil jeweils die nächste Zeile überfällig ist. Ursache ist häufig eine Markierung, die mit der Überschriftenzeile begann oder von unten nach oben aufgezogen wurde. Die Formel muss sich auf die Zeile der aktiven Zelle beziehen.

Nach Kopieren und Einfügen gibt es doppelte Regeln

Laut Microsoft legt Excel beim Einfügen, Ausfüllen oder bei Format übertragen von Zellen mit bedingter Formatierung eine neue Regel für die Zielzellen an. Nach einigen Wochen Pflege stehen so oft viele gleichartige Regeln mit zerstückelten Bereichen im Manager. Abhilfe: überzählige Regeln löschen und bei der verbleibenden Regel das Feld Wird angewendet auf auf den ganzen Bereich setzen. Noch einfacher wird es, wenn die Liste als Excel-Tabelle (Start > Als Tabelle formatieren) angelegt ist. Bedingte Formatierung lässt sich laut Microsoft auch auf Tabellen anwenden.

Zeilen mit Fehlerwerten bleiben ungefärbt

Liefert eine Formel in der geprüften Zelle einen Fehler wie #WERT!, wendet Excel laut Microsoft die bedingte Formatierung auf diese Zelle nicht an. Microsoft empfiehlt, den Fehler mit WENNFEHLER (IFERROR) oder einer IST-Funktion in einen festgelegten Wert umzuwandeln. Wird das Fälligkeitsdatum zum Beispiel mit =G2+30 aus einem Rechnungsdatum in Spalte G berechnet, das versehentlich als Text vorliegt, hilft =WENNFEHLER(G2+30;""). Die leere Zeichenfolge wird dann von der Bedingung $D2<>"" abgefangen. Wie Sie Textdaten erkennen, beschreibt der Artikel Datumswerte in Excel.

Die Regel verweist auf eine andere Datei

Steht der Stichtag in einer anderen Arbeitsmappe, funktioniert die Regel nicht: Für externe Bezüge auf andere Arbeitsmappen kann laut Microsoft keine bedingte Formatierung angewendet werden. Holen Sie den Wert per normaler Zellformel in die eigene Mappe und verweisen Sie in der Regel auf diese Zelle.

Formeln zuerst in einer Prüfspalte ausprobieren

Im Feld der Regel sehen Sie nicht, was die Formel für jede Zeile ergibt. Bevor wir eine Regel anlegen, empfehlen wir deshalb eine Hilfsspalte. Schreiben Sie in F2 genau die Formel der Regel, ohne Änderung:

=UND($D2<>"";$E2="";$D2<$H$1)

Ziehen Sie sie bis F11 nach unten. In der Beispielliste steht dann in genau zwei Zeilen WAHR, nämlich in den Zeilen 3 und 5. Erst wenn die Spalte das erwartete Muster zeigt, kopieren Sie die Formel aus F2 in die Regel und löschen die Hilfsspalte. Fehler bei den Dollarzeichen fallen so sofort auf, weil die Spalte dasselbe Verschieben der Bezüge durchläuft wie die Regel.

Eine Farbe ist außerdem kein Wert, mit dem sich weiterrechnen lässt. Brauchen Sie die Summe der überfälligen Beträge, gehört sie in eine eigene Formel mit denselben Bedingungen:

=SUMMEWENNS(C2:C11;E2:E11;"";D2:D11;"<"&H1)

Im Beispiel ergibt sie 2.140,00 €, also die Beträge von R-2026-102 und R-2026-104. Wenn Farbe und Summe auf denselben Bedingungen beruhen, widersprechen sie sich nicht. Damit Einträge wie das Zahlungsdatum überhaupt in einheitlicher Form ankommen, lohnt sich zusätzlich eine Datenüberprüfung für die Eingabespalten.

Bedingte und manuelle Formate

Trifft eine Regel zu, hat sie laut Microsoft Vorrang vor manuell gesetzten Formaten derselben Zellen. Haben Sie eine Zeile von Hand gelb markiert und die rote Regel greift, wird die Zeile rot. Wird die Regel gelöscht, kommt das manuelle Format wieder zum Vorschein. Manuelle Formate tauchen im Regel-Manager nicht auf. Wer also wissen will, warum eine Zelle eine bestimmte Farbe hat, prüft zuerst den Manager und danach das normale Zellformat.

Auch für Duplikate gibt es eine eingebaute Regel, die ganz ohne Formel auskommt. Wo ihre Grenzen liegen und welche Alternativen es gibt, beschreibt der Artikel Duplikate finden und entfernen.

Checkliste für die nächste Formelregel

• Bereich ab der ersten Datenzeile markieren, nicht ab der Überschrift. Die aktive Zelle bestimmt, auf welche Zeile sich die Formel bezieht.
• Dollarzeichen vor die Spalte, nicht vor die Zeile: $D2. Feste Werte wie der Stichtag bekommen zwei: $H$1.
• Leere Zeilen ausschließen: $D2<>"" in die Bedingung aufnehmen.
• Den Status prüfen, nicht nur das Datum: bezahlt, erledigt, storniert.
• Die Reihenfolge im Regel-Manager kontrollieren. Neue Regeln landen oben, die obere gewinnt bei gleicher Formatart.
• Die Formel vorher in einer Prüfspalte testen und das Ergebnis mit einer Stichprobe vergleichen.
• Summen und Anzahlen per Formel mit denselben Bedingungen berechnen, nicht nach Farbe zählen.
• Nach Kopieraktionen im Manager auf doppelte Regeln achten.