Zellen, die sich selbst einfärben, wenn die Regel zutrifft
Eine Regel per Formel prüft eine beliebige Bedingung und färbt auch Zellen ein, in denen diese Bedingung gar nicht steht, etwa eine ganze Zeile anhand ihres Status.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Von Hand gesetzte Farben halten das erste Sortieren nicht aus
In fast jeder Statusliste steckt eine Farbschicht, die jemand einmal von Hand gesetzt hat: gelb für in Arbeit, rot für überfällig, grün für erledigt. Solange dieselbe Person die Liste pflegt, geht das gut. Sobald jemand sortiert, filtert oder Zeilen einfügt, klebt die Farbe an der Zelle und nicht mehr am Sachverhalt.
Der zweite Bruch kommt beim Auswerten. Eine Farbe ist kein Zellinhalt, keine Formel liest sie aus, und nach Farbe filtern hilft nur so lange, wie jemand die Farben gewissenhaft nachträgt. Fällt diese Person aus, ist die Liste optisch gepflegt und inhaltlich unbrauchbar.
Der Schaden ist am Ende meist eine verpasste Frist. Wer sich darauf verlässt, dass eine überfällige Zeile rot leuchtet, sieht nicht nach, wenn sie es einmal nicht tut. Eine Regel, die bei jeder Berechnung neu prüft, kennt diesen Ausfall nicht.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Formel im Detail
Aufbau der Formel
=UND($E2<>""; $E2-HEUTE()<=7; $E2>=HEUTE()) - 01 Alle Bedingungen zugleich
UNDDie Regel greift nur, wenn der gesamte Ausdruck WAHR ergibt. Soll schon eine von mehreren Bedingungen reichen, nimmst du ODER an dieser Stelle.
- 02 Gemischter Bezug
$E2Das Dollarzeichen hält die Spalte E fest, die fehlende Fixierung vor der 2 lässt die Regel Zeile für Zeile mitwandern. Geschrieben wird sie immer für die linke obere Zelle des Geltungsbereichs.
- 03 Leere ausschließen
<>""Ohne diese Bedingung leuchtet jede Zeile ohne Datum mit, denn eine leere Zelle wird als 0 gelesen und liegt damit rechnerisch im Januar 1900.
- 04 Heutiger Tag
HEUTE()Bestimmt sich bei jeder Neuberechnung neu, die Regel altert also von allein mit. HEUTE() ist volatil, deshalb begrenzt du den Geltungsbereich auf die belegten Zeilen.
- 05 Untere Grenze
>=HEUTE()Schließt bereits abgelaufene Termine aus, sodass eine zweite Regel darüber die überfälligen Zeilen in einer anderen Farbe kennzeichnen kann.
Wenn es nicht funktioniert
Das siehst du
Entweder färbt sich der ganze Bereich oder gar nichts.
Warum
In der Regelformel steht ein absoluter Bezug wie $D$2, deshalb prüft Excel für jede Zelle immer dieselbe Zelle.
Was hilft
Nur die Spalte fixieren, also =$D2 schreiben, und die Formel für die linke obere Zelle des markierten Bereichs formulieren.
Das siehst du
Beim Tippen der Regelformel springen ungewollt Zellbezüge in den Text.
Warum
Das Eingabefeld steht im Zeigemodus, in dem die Pfeiltasten Bezüge einfügen statt den Cursor zu bewegen.
Was hilft
F2 drücken, dann wechselt das Feld in den Bearbeitenmodus und die Pfeiltasten bewegen wieder den Cursor.
Das siehst du
In der Fristenliste leuchten auch alle leeren Zeilen mit.
Warum
Eine leere Zelle wird als 0 gelesen und liegt damit rechnerisch im Jahr 1900, also weit in der Vergangenheit.
Was hilft
Die Bedingung $E2<>"" als erstes Argument in UND ergänzen, dann bleiben unbefüllte Zeilen unberührt.
Das siehst du
Die Datei reagiert träge, und in Regeln verwalten stehen zwanzig fast gleiche Einträge.
Warum
Kopieren und Einfügen überträgt Regeln mit und legt jedes Mal einen neuen Eintrag mit zerfledderten Geltungsbereichen an.
Was hilft
Über Regeln löschen alle Regeln des Blatts entfernen und die zwei oder drei nötigen neu aufbauen. Künftig beim Einfügen Inhalte einfügen, Werte nehmen.
Das siehst du
Eine Farbe ist da, aber keine Regel scheint dazuzugehören.
Warum
Regeln verwalten zeigt in der Voreinstellung nur die Regeln der aktuellen Auswahl.
Was hilft
Oben von Aktuelle Auswahl auf Dieses Arbeitsblatt umstellen, dann tauchen auch Regeln aus anderen Bereichen und verwaiste Regeln ohne gültigen Geltungsbereich auf.
Fünf Schritte vor der ersten Regel
- 01 Klär, ob nur die Zelle selbst oder die ganze Zeile reagieren soll.
- 02 Markier den Geltungsbereich, bevor du die Formel schreibst.
- 03 Schreib die Formel für die linke obere Zelle dieses Bereichs.
- 04 Setz die Dollarzeichen dort, wo der Bezug nicht wandern darf.
- 05 Sortier die Regeln und entscheide, wo Anhalten gesetzt wird.
Was du danach mit Regeln steuerst
Bedingte Formatierung ist kein Dekorwerkzeug, sondern eine kleine Logikschicht über deinen Daten. Der Unterschied zwischen einer nützlichen und einer lästigen Regel liegt fast immer bei den Dollarzeichen und beim Geltungsbereich.
Ganze Zeilen steuern
Mit einem gemischten Bezug wie =$D2="offen" reagiert die ganze Zeile auf einen Wert in einer einzigen Spalte, statt nur die Statuszelle einzufärben.
Fristen mit Fenster
Du kombinierst mehrere Bedingungen in UND und schließt leere Zellen aus, damit nicht jede unbefüllte Zeile mitleuchtet und die Warnung wertlos wird.
Fertige Regeln korrigieren
Du stellst Datenbalken auf ein Minimum von null und Symbolsätze auf echte Schwellenwerte, damit der optische Eindruck zur Größenordnung der Zahlen passt.
Reihenfolge entscheiden
In Regeln verwalten sortierst du die Regeln und setzt das Häkchen Anhalten dort, wo eine Ausnahme alle folgenden Regeln überstimmen soll.
Geltungsbereich sauber halten
Du pflegst den Bereich unter Wird angewendet auf als zusammenhängenden Block, statt Regeln mit dem Pinsel über die Tabelle zu vervielfältigen.
Dateien schnell halten
Du vermeidest Regeln über ganze Spalten und volatile Funktionen in Regelformeln, weil beides Excel bei jeder Änderung zur Neuberechnung über riesige Bereiche zwingt.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die fertigen Regeln und was sie taugen
Unter Start, Formatvorlagen, Bedingte Formatierung liegen fünf Gruppen. Die Gruppe Regeln zum Hervorheben von Zellen deckt Größer als, Kleiner als, Zwischen, Textinhalt, Datum und Doppelte Werte ab. Die Gruppe Obere und untere Regeln greift Ranglisten ab, etwa die zehn größten Werte oder alles über dem Mittelwert. Beide arbeiten pro Zelle und beurteilen ausschließlich deren eigenen Inhalt.
Datenbalken, Farbskalen und Symbolsätze zeigen dagegen Verhältnisse innerhalb des markierten Bereichs. Ein Datenbalken skaliert in der Voreinstellung auf das Minimum und Maximum der Auswahl, nicht auf null. Wenn deine Werte zwischen 95 und 100 liegen, sieht der Unterschied dramatisch aus, obwohl er es nicht ist. Über Regel bearbeiten stellst du das Minimum auf den Typ Zahl mit dem Wert 0, dann stimmt der optische Eindruck wieder.
Bei Symbolsätzen steht die Voreinstellung ebenfalls auf Prozent, und Prozent meint hier die Position im Wertebereich, nicht den Anteil an einer Gesamtsumme. Für eine Ampel nach echten Schwellen stellst du den Typ auf Zahl und trägst die Grenzen ein, die in eurem Bericht gelten. Über Symbolreihenfolge umkehren drehst du die Logik, wenn ein kleiner Wert der gute ist, etwa bei Durchlaufzeiten.
Die Regel per Formel
Über Neue Regel, Formel zur Ermittlung der zu formatierenden Zellen verwenden gibst du eine Formel ein, die WAHR oder FALSCH ergibt. Excel wertet sie für jede Zelle des Geltungsbereichs aus, du schreibst sie aber so, als gälte sie nur für die linke obere Zelle dieses Bereichs. Alles Weitere ergibt sich aus den Bezügen.
Genau hier entscheiden die Dollarzeichen. Damit eine ganze Zeile eingefärbt wird, sobald in Spalte D das Wort offen steht, markierst du zuerst A2:G200 und schreibst =$D2="offen". Das Dollarzeichen vor dem D hält die Spalte fest, während die fehlende Fixierung vor der 2 die Regel Zeile für Zeile mitwandern lässt. Schreibst du =$D$2="offen", färbt sich entweder alles oder nichts.
Fristen markierst du nach demselben Muster, aber mit einer Bedingung, die leere Zellen ausschließt: =UND($E2<>"";$E2-HEUTE()<=7;$E2>=HEUTE()) hebt alles hervor, was in den nächsten sieben Tagen fällig ist. Ohne den ersten Teil würde jede leere Zeile mitleuchten, weil eine leere Zelle als 0 gelesen wird und damit rechnerisch im Jahr 1900 liegt.
Reihenfolge, Geltungsbereich und Anhalten
Bedingte Formatierung, Regeln verwalten zeigt die Regeln des ganzen Blatts, sobald du oben von Aktuelle Auswahl auf Dieses Arbeitsblatt umstellst. In der Voreinstellung siehst du nur die Regeln der markierten Zellen, weshalb viele Regeln erst hier auftauchen und man vorher rätselt, woher eine Farbe kommt.
Die Liste wird von oben nach unten abgearbeitet, und bei einem Konflikt gewinnt die weiter oben stehende Regel für die jeweilige Eigenschaft. Regeln, die sich nicht widersprechen, wirken zusammen, eine setzt die Schriftfarbe, die nächste den Hintergrund. Das Häkchen Anhalten bricht die Auswertung ab, sobald die Regel greift, und ist der Hebel, wenn eine Ausnahme alle folgenden Regeln überstimmen soll.
Der Geltungsbereich steht in der Spalte Wird angewendet auf und lässt sich dort direkt bearbeiten. Das ist der richtige Ort, um eine Regel auf die tatsächliche Ausdehnung der Daten zu ziehen, statt sie mit dem Pinsel über die Tabelle zu vervielfältigen. Liegen die Daten in einer als Tabelle formatierten Liste, erben neue Zeilen die Regel von selbst.
Die Stolperfallen, die Dateien langsam machen
Kopieren und Einfügen überträgt die Regel mit und legt dabei jedes Mal einen neuen Eintrag an. Nach ein paar Wochen Pflege steht in Regeln verwalten dieselbe Regel zwanzigmal, jede mit einem zerfledderten Geltungsbereich wie $C$2:$C$5;$C$7:$C$9;$C$14. Die Datei wird spürbar träge, und einzelne Zeilen fallen aus der Formatierung heraus, ohne dass jemand merkt, warum.
Dagegen hilft, beim Einfügen bewusst Inhalte einfügen, Werte zu nehmen, damit keine Formate mitreisen. Ist es schon passiert, löschst du über Bedingte Formatierung, Regeln löschen, Regeln in gesamtem Blatt löschen alles und baust die zwei oder drei Regeln neu auf. Das geht schneller, als die Fragmente einzeln zusammenzuführen.
Vermeide außerdem Regeln über ganze Spalten wie $A:$A sowie volatile Funktionen wie INDIREKT, JETZT oder ZUFALLSZAHL in Regelformeln. Beides zwingt Excel bei jeder Änderung zur Neuberechnung über eine Million Zellen. HEUTE() ist ebenfalls volatil, in einer Fristenliste aber kaum zu vermeiden, dort begrenzt du wenigstens den Geltungsbereich auf die belegten Zeilen.
Dazu passende Kurse
Wer solche Regeln nicht nur nachbaut, sondern für die eigenen Berichte selbst entwerfen will, kann Formatierungsregeln im Excel-Seminar aufbauen .
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Der passende Lernpfad
Wenn du nicht nur ein Thema abhaken, sondern eine Rolle ausfüllen willst, zeigt dir der Lernpfad die Kurse in der Reihenfolge, in der sie aufeinander aufbauen.
Excel: Visualisierungsexperte
Wer Daten nur berechnet, bleibt austauschbar. Wer Zusammenhänge sichtbar macht und Erkenntisse ableiten kann, wird gehört.
Dieser Lernpfad ist dein Schritt raus aus endlosen Tabellen und hin zu klaren Botschaften: Dashboards, die sofort verstanden werden, Visualisierungen, die Entscheidungen ermöglichen, Ergebnisse, die fundiert sind. Warte nicht, bis andere deine Zahlen besser erklären als du selbst – mach Excel jetzt zu deinem Werkzeug für einen echten Vorsprung!
Wo du genau das übst
Sehr kompetenter Dozent mit einem intensiven Training welches anwendungsorientiert ist.
Das doch recht "langweilige" Thema Excel, wurde einem sehr gut präsentiert. Durch direktes mitarbeiten wurde es einem nie langweilig.
Sehr gut vorbereitetes Seminar mit hilfreichen Informationen und anschaulicher Vermittlung. Vielen Dank!
Häufige Fragen
Kann ich Zellen zählen oder summieren, die durch eine Regel eingefärbt sind?
Warum sitzt meine Regel nach dem Sortieren auf den falschen Zeilen?
Wie finde ich heraus, welche Zellen überhaupt eine Regel tragen?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Excel-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Listen, die selbst melden, was dringend ist
Welche Regel welchen Fall abdeckt und wie du sie über eine wachsende Liste ziehst, ohne Fragmente zu hinterlassen, kannst du in den Excel-Kursen von cmt am eigenen Datenbestand nachbauen.