Bedingte Formatierung in Excel: Regeln, Formeln und Beispiele für den Dienstplan

Was ist bedingte Formatierung?

Eine normale Formatierung gehört zur Zelle: Wer sie gelb färbt, bleibt sie gelb, egal was darin steht. Eine bedingte Formatierung gehört dagegen zu einer Regel. Excel prüft bei jeder Änderung, ob die Bedingung zutrifft, und blendet das Format ein oder aus. Steht in einer Planzelle heute ein „F“ und morgen ein „N“, wechselt die Farbe mit – ohne dass jemand nachfärben muss.

Für Dienstpläne ist das mehr als Kosmetik. Ein Monatsplan mit zehn Personen und 31 Tagen hat über 300 Zellen. Ob am Dienstag genug Frühdienste besetzt sind oder ob jemand nach einer Nachtschicht direkt in den Frühdienst rutscht, lässt sich in einer Wand aus Buchstaben kaum erkennen. Als farbiges Muster sieht man es auf einen Blick.

Wichtig für das Verständnis: Die bedingte Formatierung verändert keine Werte. Sie rechnet nichts aus, sperrt nichts und verhindert keine Eingabe. Sie macht nur sichtbar, was in den Zellen steht. Wer falsche Eingaben verhindern will, braucht zusätzlich eine Datenüberprüfung – wie das mit einer Auswahlliste geht, zeigt Excel Dropdown erstellen.

Die Regeltypen im Überblick

Das Menü Start › Bedingte Formatierung bietet fünf Gruppen fertiger Regeln und den Weg zur eigenen Formel. Für den Dienstplan sind nicht alle gleich nützlich:

Die fertigen Regeln reichen, solange sich die Bedingung auf den Wert der Zelle selbst bezieht. Sobald eine Zelle wegen eines anderen Werts gefärbt werden soll – die Planzelle wegen des Datums in der Kopfzeile, die ganze Zeile wegen der Stundenspalte –, führt kein Weg an der Formelregel vorbei. Sie ist das eigentliche Werkzeug für Dienstpläne.

Eine Regel anlegen: der Ablauf in vier Schritten

Am Beispiel einer Formelregel, die Nachtdienste blau färbt. Der Plan steht in C5:AG14, Zeile 4 enthält die Datumswerte, Spalte B die Namen.

1. Den Bereich C5:AG14 markieren – von links oben beginnend, damit C5 die aktive Zelle ist.
2. Start › Bedingte Formatierung › Neue Regel wählen und den Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden anklicken.
3. In das Feld Werte formatieren, für die diese Formel wahr ist eintragen: =C5="N". Die Formel beginnt mit einem Gleichheitszeichen und muss WAHR oder FALSCH ergeben.
4. Über Formatieren… eine Füllfarbe wählen, mit OK bestätigen.

Excel schreibt die Formel für die aktive Zelle und überträgt sie relativ auf alle anderen Zellen des Bereichs: D5 prüft =D5="N", C6 prüft =C6="N". Genau deshalb ist Schritt 1 so wichtig. Wer den Bereich von rechts unten nach links oben markiert, macht AG14 zur aktiven Zelle – dann verschiebt sich jeder Bezug, und die Farben landen neben den Nachtdiensten statt darauf.

Ein Detail aus der Praxis: Der Vergleich =C5="N" unterscheidet nicht zwischen Groß- und Kleinschreibung, ein „n“ wird also ebenfalls blau. Ein Leerzeichen hinter dem Kürzel dagegen verhindert die Farbe – auch das spricht für feste Kürzel per Dropdown.

Relative, absolute und gemischte Bezüge

Die meisten Fehler in Formelregeln sind Dollarzeichenfehler. Die Regel ist immer dieselbe: Ein $ vor der Spalte oder Zeile hält diesen Teil fest, während Excel die Formel über den Bereich verteilt.

Beim Eintippen hilft die Taste F4: Sie schaltet einen markierten Bezug nacheinander durch $C$4, C$4, $C4 und C4. Vorsicht beim Klicken statt Tippen: Wer im Regeldialog mit der Maus auf eine Zelle klickt, bekommt laut Microsoft einen absoluten Bezug eingefügt. Das ist die häufigste Ursache dafür, dass eine Regel entweder den ganzen Plan oder gar nichts einfärbt.

Sechs Regeln für den Dienstplan

Die folgende Tabelle fasst die Regeln zusammen, die in einem Monatsplan am häufigsten gebraucht werden. Grundlage ist derselbe Aufbau wie oben: Datumszeile 4, Plan C5:AG14, Stundensumme je Person in AH5:AH14, das Monatssoll als Zahl in AI2.

Schichtkürzel F, S, N, U und K

Für reine Kürzelfarben genügt auch die fertige Regel Regeln zum Hervorheben von Zellen › Gleich… – fünf Kürzel, fünf Regeln. Die Formelvariante hat zwei Vorteile: Mehrere Kürzel lassen sich mit ODER zu einer Farbe zusammenfassen, und die Formel ist im Regel-Manager lesbar. Vorsicht mit Textinhalt…: Diese Regel prüft, ob ein Text enthalten ist. Eine Regel für „F“ färbt dann auch „FB“ für Fortbildung. Wie die Kürzel überhaupt sauber in die Zellen kommen, beschreibt Excel Dropdown erstellen.

Wochenenden mit WOCHENTAG

WOCHENTAG mit dem Rückgabetyp 2 zählt Montag als 1 und Sonntag als 7. Alles über 5 ist damit Samstag oder Sonntag. Das Dollarzeichen vor der 4 fixiert die Datumszeile, sodass die ganze Spalte grau wird. Voraussetzung: In Zeile 4 stehen echte Datumswerte, nicht der Text „Mo“. Wie die Kopfzeile aus einem einzigen Monatsdatum entsteht und welche Rückgabetypen es gibt, steht in Excel: Wochentag aus Datum anzeigen.

Feiertage mit ZÄHLENWENN

Excel kennt keine Feiertage. Die Regel zählt deshalb, wie oft das Datum der Spalte in einer gepflegten Liste vorkommt; ab einem Treffer ist die Bedingung wahr. Feiertage ist ein benannter Bereich auf einem eigenen Blatt. Wie die Liste je Bundesland aufgebaut wird und warum der Umweg über einen Namen nötig ist, beschreibt ausführlich der Beitrag Feiertage in Excel markieren.

Ruhezeitkonflikt nach Nachtdienst

Die Regel =UND(C5="N";D5="F") wird auf D5:AG14 angewendet, also eine Spalte nach rechts versetzt: Jede Zelle prüft sich selbst und ihren linken Nachbarn. Ein Frühdienst am Morgen nach einer Nachtschicht lässt die elfstündige Ruhezeit nach § 5 ArbZG in der Regel nicht zu – solche Zellen sollten kräftig rot erscheinen.

Mehr als 10 Stunden am Tag hervorheben

Nach § 3 ArbZG darf die werktägliche Arbeitszeit acht Stunden nicht überschreiten; eine Verlängerung auf bis zu zehn Stunden ist nur zulässig, wenn innerhalb von sechs Kalendermonaten oder 24 Wochen im Durchschnitt acht Stunden werktäglich nicht überschritten werden. Zehn Stunden sind also die Obergrenze für den einzelnen Tag – ein sinnvoller Schwellenwert für eine Warnfarbe.

Im Kürzelplan stehen keine Stunden, die Regel gehört deshalb in die Zeiterfassung oder in ein Stundenblatt. Angenommen, jede Zeile ist ein Arbeitstag und Spalte F enthält die Netto-Arbeitszeit:

Das Dollarzeichen vor dem F sorgt dafür, dass alle Zellen der Zeile auf die Stundenspalte schauen. Damit färbt sich der ganze Tag, nicht nur die Summe. Der Faktor 24 ist der Punkt, an dem die meisten Regeln scheitern: =$F5>10 ist bei einem Uhrzeitwert nie wahr, weil 10:30 intern 0,4375 ist. Wie Netto-Zeiten über Mitternacht korrekt entstehen, zeigt Arbeitszeiterfassung in Excel.

Unterbesetzung sichtbar machen

Die Frage „ist der Tag ausreichend besetzt?“ beantwortet eine Regel auf der Kopfzeile. In $B$2 steht die Mindestzahl an Frühdiensten, etwa 3. Auf den Bereich C4:AG4 kommt die Regel:

=ZÄHLENWENN(C$5:C$14;"F")<$B$2

Jede Datumszelle zählt die „F“ in ihrer eigenen Spalte und färbt sich rot, wenn es weniger als die Mindestbesetzung sind. Der Bereich C$5:C$14 ist bewusst gemischt: Die Zeilen bleiben fest, die Spalte wandert mit. Für Spät- und Nachtdienst entsteht dieselbe Regel mit „S“ und „N“ – entweder in weiteren Kopfzeilen oder in einer Auswertungszeile unter dem Plan, in der =ZÄHLENWENN(C5:C14;"F") die Zahl sichtbar ausgibt und die Regel nur noch die Farbe setzt. Die zweite Variante ist leichter zu prüfen, weil die Zahl neben der Farbe steht.

Datenbalken und Farbskalen für Stundenkonten

Für Salden und Summen ist eine Farbe pro Schwellenwert oft zu grob. Hier helfen die grafischen Regeltypen:

• Datenbalken auf der Saldo-Spalte, etwa AJ5:AJ14: Plusstunden erscheinen als Balken nach rechts, Minusstunden nach links. Unter Regeln verwalten › Regel bearbeiten › Negativer Wert und Achse lassen sich Farbe und Lage der Achse einstellen; Nur Balken anzeigen blendet die Zahl aus, was für eine Übersicht reicht, für die Abrechnung aber nicht.
• Dreifarbige Farbskala auf der Monatssumme: Minimum, Mittelpunkt und Maximum lassen sich als feste Zahl statt als Perzentil festlegen, etwa 0 für den Mittelpunkt. Dann bedeutet Weiß „ausgeglichen“, unabhängig davon, wie die anderen Konten stehen.

Voraussetzung für beide: Die Werte müssen Zahlen sein. Ein Saldo im Format [h]:mm kann nicht negativ angezeigt werden und erscheint als Rautenfolge; Datenbalken und Farbskalen rechnen dann mit falschen oder fehlenden Werten. Deshalb wird der Saldo besser in Dezimalstunden geführt – die Begründung steht im Beitrag Arbeitszeitkonto in Excel führen.

Regel-Manager, Reihenfolge und „Anhalten“

Alle Regeln eines Blatts stehen unter Start › Bedingte Formatierung › Regeln verwalten im Dialog Manager für Regeln zur bedingten Formatierung. Im Listenfeld Formatierungsregeln anzeigen für sollte statt der aktuellen Auswahl das ganze Arbeitsblatt eingestellt sein – erst dann sieht man wirklich alle Regeln. Dort werden Regeln neu angelegt, bearbeitet, dupliziert, gelöscht und mit den Pfeilen verschoben.

Die Reihenfolge entscheidet, was passiert, wenn mehrere Regeln dieselbe Zelle treffen. Microsoft beschreibt zwei Fälle:

• Kein Konflikt: Eine Regel setzt fette Schrift, die andere eine rote Füllung – die Zelle wird fett und rot.
• Konflikt: Beide Regeln setzen eine Füllfarbe – es gilt die Regel, die in der Liste weiter oben steht.

Das Kontrollkästchen Anhalten rechts in der Regelzeile beendet die Auswertung, sobald diese Regel zutrifft: Die Regeln darunter werden für diese Zelle nicht mehr geprüft. Für Datenbalken, Farbskalen und Symbolsätze lässt es sich laut Microsoft nicht setzen.

Ob Feiertag und Wochenende über oder unter den Kürzelfarben stehen, ist eine Gestaltungsfrage. Stehen sie oben und setzen eine Füllung, überdecken sie die Schichtfarbe an diesen Tagen. Eine Alternative ohne Konflikt: Feiertag und Wochenende färben nur die Kopfzeile oder setzen statt einer Füllung einen Rahmen – dann kombiniert Excel beide Formate.

Typische Fehler

Der Fehlerwert-Punkt betrifft vor allem Regeln, die auf Nachschlageformeln zeigen: Eine Stundenspalte mit SVERWEIS liefert an freien Tagen #NV, und Microsoft weist ausdrücklich darauf hin, dass die bedingte Formatierung auf Zellen mit Fehlerwerten nicht angewendet wird. Wie der SVERWEIS im Plan mit WENNNV sauber bleibt, steht in SVERWEIS in Excel.

Gegen zerfallene Regeln hilft eine Arbeitsgewohnheit: Werte mit Inhalte einfügen › Werte statt mit einfachem Einfügen übernehmen, und neue Zeilen innerhalb des Plans einfügen, nicht am Rand. Fertige Formate auf einen neuen Bereich überträgt Start › Format übertragen (Pinsel).

Excel 365, Excel für das Web und Google Sheets

Für gemeinsam genutzte Dateien heißt das: Ein Plan, der in Excel gebaut und in Google Sheets geöffnet wird, behält einfache Kürzelregeln meist, verliert aber Feinheiten bei der Kombination mehrerer Regeln. Die Feiertagsregel muss in Google Sheets etwa so lauten: =ZÄHLENWENN(INDIREKT("Feiertage!A2:A40");C$4)>0. Wer die Datei über OneDrive im Team teilt und im Browser bearbeitet, bleibt mit Excel für das Web näher am Original.

Weiterlesen

Die Feiertagsliste und die Reihenfolge von Feiertags- und Wochenendregel behandelt Feiertage in Excel markieren im Detail. Die Datumskopfzeile, auf die sich die Wochenendregel stützt, entsteht in Excel: Wochentag aus Datum anzeigen. Die Eingabeseite mit festen Kürzeln liefert Excel Dropdown erstellen, die Übersetzung der Kürzel in Uhrzeiten und Stunden SVERWEIS in Excel. Fertige Arbeitsmappen mit Schichtfarben gibt es in der Vorlagenübersicht.