Ganze Zeile einfärben (bedingte Formatierung)

Bedingte Formatierungen bieten die Möglichkeit, Zellen basierend auf einem Wert zu formatieren (beispielsweise farblich hervorheben). Im Normalfall wird dies auf eine einzelne Zelle angewandt, um darzustellen, dass ein bestimmter Parameter unter- oder überschritten wurde. Ein konkretes Beispiel zeigt der Beitrag "Einen eingefärbten Kalender erstellen". Symbolsätze bieten die Möglichkeit, Trends und Entwicklungen (Pfeile) oder Ampeln und Indikatoren für den - oder gar anstelle des - entsprechenden Wert anzuzeigen. Dies kann in Übersichten und Berichten spannend sein, um auf einen Blick relevante Gegebenheiten erkennen zu können. Dies sind die Symbolsätze, die man unter dem Menüpunkt "Start" → "Bedingte Formatierung" findet:

bedingteFormatierungSymbols

In diesem Blog zeige ich Dir einen Weg, wie Du nicht nur eine einzelne Zelle hervorheben kannst, sondern sämtliche dazugehörigen Informationen in derselben Zeile werden ebenfalls formatiert. Somit werden erfasste Einträge besser ersichtlich und Du kannst Deine Daten beispielsweise nach Farbe filtern (siehe unten). Für unser Beispiel haben wir einen übersichtlichen Datensatz mit Kundeninformationen:

bedingteFormatierungRohdaten

Wir beabsichtigen nun, sämtliche Kunden farblich hervorzuheben, die über eine Kundenkarte verfügen (Spalte G). Um ein erstes Resultat zu erzielen, klicke in die Zelle "G2" und gehe via "Start" → "Bedingte Formatierung" auf "Regeln zum Hervorheben von Zellen" und dort auf "Textinhalt...":

bedingteFormatierungTextinhalt

Damit wird der bestehende Inhalt ("Ja") standardmässig rot hervorgehoben. Mit "OK" lässt sich die Auswahl bestätigen. Man kann jedoch die Formatierung beliebig anpassen, indem man in der Auswahl auf der rechten Seite "benutzerdefiniertem Format..." auswählt. Die Schritte da sind gleich, wie wenn man eine normale Zelle formatiert. Wenn Du die Auswahl bestätigt hast, gehe erneut auf "bedingte Formatierung" im Menüband und wähle dieses Mal zu unterst "Regeln verwalten...". Du siehst diese Option auch im Screenshot oben, wo wir "Regeln zum Hervorheben von Zellen" gefolgt von "Textinhalt..." angeklickt haben. Nun siehst Du die eine Regel, die Du erstellt hast. Gehe oberhalb des einzelnen Eintrags auf "Regel bearbeiten" und wähle anstelle von "Nur Zellen formatieren, die enthalten" die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kannst Du weiter unten eine Formel/Kondition eingeben, um bedingte Formatierung auszulösen. Verwende nachfolgende Formel:

=$G2="Ja"

(Das Dollarzeichen ist notwendig, um die Formatierung auf andere Spalten zu übertragen, aber dass die Spalte G und der Wert darin nach wie vor die Bedingung vorgibt.)

bedingteFormatierungFormatierungsregel

Bestätige die Formel und folglich die neue Formatierungsregel mit "OK". In der Übersicht passe nun noch den Bereich der Regel an -  dies ist die Spalte mit der Überschrift "Wird angewendet auf". Wie erweitern da den Bereich auf den gesamten Datenbereich (A2 bis G5). Dies führt dazu, dass alle Werte darin eingefärbt werden, sofern der jeweilige Wert in Spalte G "Ja" entspricht. Weitere Spalten rechts davon (z.B. H und fortfolgende) sind davon nicht betroffen. Damit der Bereich erweitert wird, gib folgende Formel an (die Dollarzeichen sind auch hier zwingend notwendig!):

=$A$2:$G$5

bedingteFormatierungRegelManager

Du solltest die Änderungen bereits aktiv sehen, wenn Du rechts unten auf "Übernehmen" klickst. Du kannst aber auch direkt auf "OK" drücken, um den Regel-Manager zu schliessen. Nun hast Du zwei Einträge komplett eingefärbt, weil der jeweilige Kunde über eine Kundenkarte verfügt:

bedingteFormatierungResultat

Resultate filtern (Farbfilter)

Einleitend habe ich erwähnt, dass das Resultat nun aufgrund der bedingten Formatierung deutlich besser ersichtlich ist. Das Ergebnis ist auch ansprechender, als wenn nur die Zelle in der Spalte G eingefärbt wäre. In jeder beliebigen Spalte kann nun nach der Farbe gefiltert werden, um nur die eingefärbten Zeilen anzuzeigen:

bedingteFormatierungFarbfilter

Einen eingefärbten Kalender erstellen

Wer kennt das nicht: Es beginnt ein neues Kalenderjahr und sämtliche Dateien wie Personaleinsatzplanung, Ferienpläne, Projektübersichten, Terminlisten, etc. müssen für das neue Jahr aufgesetzt werden. In diesem Blog zeige ich Dir, wie Du dies mithilfe von vereinzelten Formeln sowie bedingten Formatierungen ganz schnell hinbringst!

Du kannst Dir dafür gerne direkt diese Datei (Farbiger_Kalender.xlsx) herunterladen. Alternativ kannst Du nachfolgendes Bild selbst nachkonstruieren (Beschreibungen dazu folgen unmittelbar):

In der Zelle "B1" gibst Du das gewünschte Jahr ein, in "B2" den entsprechenden Monat. Unterhalb wird Dir mittels der Formel "DATUM" die Kombination aus Tag (fortlaufende Nummer im Bereich  "D3:D33") sowie dem Monat und dem Jahr in "B2" respektive "B1" erstellt. Daraus können wir in der Spalte "E" mittels der Formel "TEXT" den jeweiligen Wochentag ermitteln (im Beitrag Dynamische Titel (Formel "TEXT") haben wir bereits angeschaut, wie man den Wochentag von einem beliebigen Datum auslesen kann).

Hier zusammengefasst noch einmal die bereits erwähnten Formeln:

[In der Zelle "B3" und dann entsprechend angewendet auf "B3:B33"]
=DATUM($B$1;C3;D3)
[In der Zelle "C3" und dann entsprechend angewendet auf "C3:C33"]
=MONAT(1&$B$2)
[In der Zelle "E3" und dann entsprechend angewendet auf "E3:E33"]
=TEXT(B3;"TTTT")

Die bedingte Formatierung zur farblichen Hervorhebung von Samstagen und Sonntagen

Das einzige, was jetzt noch fehlt, ist die Markierung von den Wochenenden. Hierzu verwenden wir bedingte Formatierungen. Falls Du die oben bereitgestellte Datei verwendest, klicke beispielsweise die Zelle "E3" an und gehe dann via "Start" in der Menüleiste auf "Bedingte Formatierung" und dort auf "Regeln verwalten...". Auf den Bereich "$D$3:$K$33" wenden wir zwei Formeln an. Falls der jeweilige Wert in der Spalte "E" ein Samstag respektive Sonntag ist, werden die Zellen von "D" bis "K" grau eingefärbt. Weitere Informationen findest Du, wenn Du auf "Regel bearbeiten" klickst.

Wie erstellt man man so eine Formel selbst? Falls Du das gerne nachkonstruieren möchtest, gehst Du wie folgt vor: Selektiere zum Beispiel die Zelle "E3" und gehe dann via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Nimm dann die Einstellungen in der Grafik unten vor. Diese sind: Wähle einerseits "Formel zur Ermittlung der zu formatierenden Zellen verwenden" als Regeltyp aus und gebe dann folgende Formel ein:

=$E3="Samstag"

Danach kannst Du via dem Button "Formatieren..." dein gewünschtes Format auswählen.

Formatierungsregel

Wichtig zu erwähnen ist an dieser Stelle, dass der Bezug unbedingt "$E3" sein muss. Wir setzen dadurch die Spalte "E" absolut. Dies bedeutet, dass die Regel in beispielsweise der Zelle "K3" ebenfalls überprüft, ob eben in dieser Zelle "E3" "Samstag" steht. Die Zeilennummer ist nicht fixiert, was bedeutet, dass in der Zelle "K6" zu "E6" referenziert wird. Nach dem Erstellen dieser Regel können wir via "Start" → "Bedingte Formatierung" → "Regeln verwalten..." bei "Wird angewendet auf" den Bereich =$D$3:$K$33 eintragen. Diese Schritte wiederholst Du dann für Sonntag, indem Du eine weitere Regel anlegst.

Falls Du weniger oder mehr Spalten eingefärbt haben möchtest (da Du beispielsweise mehrere Mitarbeitende oder Projekte erfassen möchtest), kannst Du einfach Deine Regeln verwalten und den Bereich von =$D$3:$K$33 auf =$X$3:$X$33 erweitern!

... und nun kannst Du in den Zellen "B1" und "B2" andere Monate und Jahre eingeben und hast direkt die Wochenende eingefärbt!

Wie kann ich das nun verwenden?

Diese Frage ist natürlich abhängig davon, was Du genau für eine Anwendung dafür hast. Falls Du nicht alle Informationen von diesem Tabellenblatt beibehalten möchtest, kannst Du entweder vereinzelte Zellen/Spalten ausblenden oder wie folgt vorgehen: Der einfachste Weg, damit Du diese Datei verwenden kannst, ist, den gewünschten Monat auszuwählen und dann mit einem Rechtsklick auf das Tabellenblatt via "Verschieben oder kopieren..." eine Kopie zu erstellen. Wenn Du nun den Bereich "B3:E33" auswählst und kopierst und im Anschluss darüber nur "Werte" einfügst, bleiben die Formatierungen erhalten! Du kannst die übrigen Spalten "A" bis "C" sowie die Zeilen eins und zwei löschen. Danach wiederholst Du den Vorgang für weitere Monate/Jahre. Zum Beispiel für den Monat Februar wirst Du zusätzliche vorige Zeilen unterhalb haben, diese kannst Du natürlich ebenfalls entfernen.

P.S.: Falls Du eine englische Version von Excel verwendest, müsstest Du selbstverständlich die Monate in "B2" auf Englisch eingeben, die Formel "TEXT" leicht anpassen ("TTTT" wird zu "DDDD") und die bedingte Formatierung von "Samstag" auf "Saturday" und von "Sonntag" auf "Sunday" ändern.