Excel: Bedingte Formatierung mit entferntem Bezug

In den meisten Anwendungsfälle der bedingten Formatierung in Excel beziehen sich die Bedingung und die Formatierung auf die selbe Zelle. Wenn beides auseinander läuft, müssen Sie besondere Vorkehrungen treffen, was dieser Beitrag anhand eines Schichtplans erklärt.

Die Bedingte Formatierung mit einer eigenen Formel

Angenommen, Sie haben einen in Excel gebauten Plan für eine Einteilung von Mitarbeitern in verschiedene Schichten. Der soll nun so aufgewertet werden, dass die Felder unterhalb einer Zelle mit einem Datum rot werden, wenn das Datum darüber ein Sonntag ist. Dazu kann man keine vorgefertigte Funktion nutzen, sondern muss man eine selbst geschriebene Formel in der Bedingung verwenden. Hier eine mögliche Ausgangstabelle, die nur eine Grundstruktur darstellt:

Schichtplan vor der Formatierung
Beispiel für einen Schichtplan in Excel.

Zuerst ist einmal wichtig, wie man überhaupt erkennt, dass ein Datum auf einen Sonntag fällt. Das geht einfach über die Funktion WOCHENTAG(), die für eine Zelle den Tag in numerischer Form ausspuckt. Für einen Sonntag liefert Sie dabei den Wert „1“ zurück.

So nutzen Sie WOCHENTAG() in einer Bedingten Formatierung

  1. Markieren Sie den gesamten inneren Bereich der Tabelle. In unserem Beispiel wäre das B4 bis E6.
  2. Klicken Sie im Startregister auf Bedingte Formatierung – Neue Regel – Formel zur Ermittlung….
  3. Als Formel geben Sie ein: =WOCHENTAG(B$3)=1
  4. Dann wählen Sie noch eine Formatierung, etwa das Füllen mit einem rötlichen Hintergrund.
  5. Das Ergebnis sieht dann so aus:

    Bedingte Formatierung für Schichtplan
    Schichtplan in Excel nach dem Einbau einer Bedingten Formatierung

Um auszuprobieren, ob das wirklich so klappt, könnten Sie als Datumswert über eine der noch weißen Spalten das Datum 11.6.2017 schreiben. Weil das auch ein Sonntag ist, müssen die Felder darunter rot werden, sobald Sie das neue Datum eingegeben haben.

Wollen Sie nicht nur den Sonntag farbig markieren, sondern auch den Samstag, dann ändern Sie die Formel so ab: =WOCHENTAG($B3;2) > 5. Möchten Sie wissen, wie diese Variante beide Tage erfassen kann, dann lesen Sie hier nach: Excel: Wochenende und Wochentage optisch unterscheiden.

Darum benötigt die Formel das Dollarzeichen

Wenn Sie einen Bereich für die Bedingte Formatierung markieren und in der Formel eine Referenzzelle angeben, wie hier B3, dann würde Excel diese Formel für die Zelle ganz links oben verwenden und bei jeder anderen Zelle den Bezug anpassen. In Zelle B5 etwa würde die Formatierung auf den Inhalt von B4 testen, wo gar kein Datumswert steht.

Mit dem $ erreichen Sie , dass Excel diese Anpassung in vertikaler Richtung sein lässt. In der Folge beziehen sich alle Formatierungen unterhalb von B3 also genau auf diese eine Zelle. Eine Spalte weiter rechts ändert Excel den Bezug automatisch auf C3, weil hier vor dem Adressteil kein Dollarzeichen steht. Das Ergebnis ist also genau das, was wir erreichen wollen.

Würden Sie beispielsweise als Inhalt der Formel schreiben =WOCHENTAG($B$3)=1, würde Excel alle markierten Felder nur im Bezug zu B3 formatieren. Das Dollarzeichen fixiert die entsprechende Koordinate quasi.

Wenn Sie noch mehr zum Thema lesen möchten, finden Sie hier eine Übersicht zur bedingten Formatierung mit Excel.

Bestseller Nr. 1
Excel 2016 - Formeln und Funktionen: Die schnelle Hilfe
  • Inge Baumeister, Christian Bildner
  • BILDNER Verlag
  • Auflage Nr. 1 (14.11.2016)

20 Gedanken zu “Excel: Bedingte Formatierung mit entferntem Bezug

  1. Hi lieber Markus,
    auch wenn es bestimmt anstrengend ist, all die kleinen Fragen und Sonderwünsche zu beantworten, versuche ich es trotzdem einfach mal bei dir.
    Ich habe gerade nämlich ein sehr ähnliches Problem und soll für meinen Chef eine Excel-Datei mit bestimmten Formatierungen erstellen. Dabei kenne ich mich damit kaum aus :-/
    In der Excel soll die erste Zelle vorne und auch die nachfolgenden Zellen bis Spalte H einen doppelten Strich oben an der Zelle bekommen, wenn in der ersten Zelle (Spalte A) irgendein Wert drin ist. Also wenn was drin steht, sollen die nachfolgenden Zellen nach rechts (bis H) alle diese Formatierung bekommen.
    Könntest du mir da vielleicht helfen?
    Vielen lieben Dank im Voraus und Grüße
    Andi

    • Hallo Andreas, ich nehmen für mein Beispiel mal an, dass in der ersten Zeile Überschriften stehen, der betreffende Bereich also mit A2 beginnt. Diesen Bereich markieren, dann neue Bedingte Formatierung mit Regeltyp „Formel zur Ermittlung…“ anlegen. Als Formel verwendest du =NICHT(ISTLEER($A2)) und bei „Formatieren“ suchst du dir unter „Rahmen“ das passende aus. Sollte so funktionieren.

  2. Ich habe ein Problem mit den bedingten Formatierung bei EXcel.

    Ich möchte dass in einer Spalte mit dem Datum des Zahlungsziels(SpalteB) die Daten farbig hinterlegt werden, die <= HEUTE sind und die noch nicht bezahlt sind.
    Ich habe in der Tabelle eine Spalte in der ich das jeweilige Bezahltdatum eintrage(BSP) SpalteD. Also wenn Spalte D leer ist sollen die Daten des Zahlungsziel ,die <=Heute sind farbig hinterlegt werden.
    Geht das überhaupt? Habe schon einiges probiert. Bin aber immer an der Leerspalte gescheitert. Kann mir jemand weiterhelfen

  3. Hallo!
    Können Sie mir evtl. sagen, ob ich mit „Bedingte Formatierung“ eine Spalte von X Zeilen in einer bestimmte Farbe machen kann? Z.B wenn in A1- A steht, dann von A1 nach unten alle Zellen in Spalte A gelb gefärbt sind, mit so viele Zeilen wie eine Wert von A im Voraus vergeben ist? (Z.B: Wert von A ist 17)

    • Das geht so: Komplette Spalte A markieren, neue bedingte Formatierung mit Formel anlegen, als Formel eingeben: =ZEILE()<=$A$1 und als Formatierung "Ausfüllen" mit der gewünschten Farbe wählen. Dann ändert sich die Anzahl der farbigen Zellen in Spalte A je nach dem in A1 stehenden Wert.

  4. Hallo
    Ich habe Zeilen mit Objekten. Anhand jeweils einer Zelle ( Zustand des Objektes)innerhalb dieser Zeilen möchte ich die gesamte Zeile farbig Kennzeichen. Ich habe aktuell fünf verschiedene Zustände.

    Können Sie mir helfen?

    Danke und Gruss
    Pascal

    • Ok, ich versuch´s mal: Angenommen, der Zustand steht in Spalte A. Dann markieren Sie alle Felder, die zur Objektliste gehören, legen eine neue bed. Formatierung mit eigener Formel an. Die lautet =$A1=“Topzustand“ (das wird bei Ihnen anders heißen, klar). Dieser Formel weisen Sie zum Beispiel grüne Hintergrundfarbe zu. Dann machen Sie das ganze nochmal, verwenden aber als Formel =$A1=“gut“ Die bekommt eine andere Farbe. Das wiederholen Sie noch drei mal mit jeweils anderen Zuständen und Farben.
      LG Markus Schraudolph

      • Hallo
        Besten Dank für Ihre Antwort.

        Leider bekomm ich das noch nicht hin.
        Ich kann diese bed. Formatierung mit eigener Formel nicht finden. Ich vermute, dass durch einen Versionsunterschied (Ich verwende Excel 2016) diese Option anders heisst oder ich an einem falsche Ort danach suche.
        Probiert habe ich es mit: „Bedingte Formatierung>Neue Regel>Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
        Gebe ich nun =$K4=“Defekt/Not working“ erscheint folgende Fehlermeldung: „Verweisoperatoren ( wie etwa Vereinigungen, Schnittmengen und Bereiche) oder Arraykonstanten dürfen in dem Kriterium Bedingte Formatierung nicht verwendet werden.“

        Spielt hier eventuell mit rein, dass ich den Zustand anhand einer Dropdownliste (Datenüberprüfung mit „Zulassen“ Liste, diese wiederum von einem anderen Tabellenblatt) angebe?

        Danke und Gruss
        Pascal

        • Ihr Weg für die Eingabe der Formel ist genau richtig. Ich glaube, dass der Fehler schlicht an den Anführungszeichen in der Formel liegt. Wenn ich die Formel aus Ihrem Kommentar herauskopiere, erscheint bei mir der gleiche Fehler. Tausche ich die beiden Anführungszeichen gegen die über der Taste [2] aus, dann klappt es. Ich vermute, dass unser Redaktionssystem da eigenmächtig die falschen Anführungszeichen in Kommentaren setzt.

  5. Hallo, wie muss ich denn vorgehen, wenn ich folgendes Ergebnis erhalten möchte:
    Ist im Feld AA19 nichts eingetragen, soll das Feld AG24 die Zahl „48“ in normaler Schrift bei Standard-Hintergrund haben. Sobald aber ins Feld AA19 eine „2“ eintragen wird, soll das Feld AG24 die Zahl „48“ in blauer Schrift mit Unterstrich und orangem Hintergrund haben.
    PS: Ich benutze LibreOffice unter Linux
    Danke und Gruß Robert Heim

  6. Hallo,

    ich finde nirgendwo Informationen dazu, wie die Formel aussieht, wenn ich folgende Herausforderung habe: Die Zelle soll farblich gekennzeichnet werden, wenn der Wert einer anderen Zelle nicht einer bestimmten Aussage (in meinem Fall handelt es sich um Text) entspricht.

    Wie kann mir geholfen werden?

    LG
    Agnes R.

    • Ein Beispiel: B1 soll eine bestimmte Formatierung erhalten, wenn in A1 nicht „JA“ steht. Dann verwenden Sie als Formel für die bedingte Formatierung in A1: =A1<>„JA“
      Der Operator <> ist das Gegenteil von = und bedeutet „ist ungleich“

      • Hallo,

        und wie würde die Formel bzw. bedingte Formatierung in Feld G2 in folgendem Fall aussehen:
        Ist das Feld A2 leer, dann lass die Zahl in Feld G2 Standard; steht aber in Feld AA19 z.B. eine „2“, dann soll der Inhalt von G2 in blauer Schrift auf orangem Hintergrund ausgegeben werden.
        Hinweis: Ich nutze LibreOffice unter Ubuntu-Linux.

        Danke für eure Hilfe!

        LG Robert

        • So müsste es gehen: In G2 eine bedingte Formatierung einfügen, mit „Bedingung 1“ auf die Auswahl „Formel ist gleich“ und dann im Formelfeld schreiben: A2=2
          Dann unter „Vorlage“ auf „Neue Vorlage“ gehen und unter „Schrifteffekt“ die Schriftfarbe auf blau setzen.

  7. Guten Tag

    Können Sie mir evtl. sagen, wie ich die Formel anpassen muss, dass der Samstag und der Sonntag entsprechend markiert wird?

    Vielen Dank

    • Wenn Sie möchten, dass Samstag und Sonntag in der gleichen Farbe markiert werden, dann schreiben Sie statt der genannten Formel:
      =ODER(WOCHENTAG(B$3)=1;WOCHENTAG(B$3)=7)
      Möchten Sie dagegen den Samstag in einer anderen Farbe dargestellt haben, dann legen Sie eine weitere Bedingte Formatierung mit der gewünschten anderen Farbe an und verwenden hier die Formel
      =WOCHENTAG(B$3)=7
      (Für Excel ist der Sonntag der erste Tag der Woche und der Samstag der 7. Tag)

    • Die WENN()-Funktion hat einen anderen Zweck, als man hier benötigt: Sie gibt je nachdem, was die enthaltene Bedingung ergibt, den einen oder den anderen Wert aus. Bei der Bedingten Formatierung sieht sich Excel einfach nur an, ob der genannte Ausdruck den logische Wert WAHR oder FALSCH ergibt und formatiert dann wie Sie vorgeben – oder tut eben nichts, falls FALSCH herauskommt. WAHR wird der Ausdruck, wenn WOCHENTAG() für die genannte Zelle gleich 1 ist, es sich also um einen Sonntag handelt.
      LG Markus Schraudolph

Schreibe einen Kommentar