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.

Weiterlesen: Excel: Wochenende und Wochentage optisch unterscheiden.

33 Gedanken zu “Excel: Bedingte Formatierung mit entferntem Bezug

  1. Hallo,

    ich brauche Unterstützung.

    Eine leere Zelle soll eine bestimmte Farbe bekommen, wenn eine andere Zelle (bestim. Datum) in einem Bereich-Datum z.B. von 01.04.2018 bis 27.04.2018 liegt.

    Vielen Dank!
    LG Ksenia

    • Nehmen wir an, die erste Zelle mit dem Datum wäre A1 ganz links oben. Dann tragen Sie als Formel für die Zelle, deren Farbe sich ändern soll bei der bedingten Formatierung ein:
      =UND(A1>=DATUM(2018;4;1);A1<=DATUM(2018;4;27)) Es müssen also beide Bedingungen erfüllt sein: das Datum ist größer/gleich dem 1.4.18 und kleiner/gleich dem 27.4.18

  2. Hallo Zusammen,

    ich grübel schon eine Weile über folgende Problematik. Es geht um einen Schichtplan.
    Ich habe ein Tabellenblatt mit einer Spalte mit Dienstkürzeln. Jedes Kürzel hat eine eigene Zellfarbe und Schriftfarbe.
    Nun habe ich auf einem weiteren Tabellenblatt einen Kalender erstellt.
    Trage ich nun in diesen Kalender das Dienstkürzel ein, so soll automatisch die Formatierung übernommen werden für dieses Kürzel aus dem Tabellenblatt mit den hinterlegten Dienstkürzeln.

    Ich möchte nicht für jedes Dienstkürzel eine bedingte Formatierung anlegen.

    Könnt ihr mir helfen?

    Grüße
    Thomas

    • Ich befürchte, dass das nicht so funktioniert, wie Sie wünschen. Man könnte zwar relativ leicht eine Funktion schreiben, die eine Zelle in Ihrem ersten Arbeitsblatt findet, wo das Kürzel identisch zum Inhalt einer Zelle im zweiten Arbeitsblatt ist, aber die Extraktion der Zellformatierung aus Blatt 1 ist nicht möglich. Denn die Bedingte Formatierung sieht als Ergebnis jeder Regel nur eine feste Formatierung vor und nicht so etwas wie “Kopiere das Format von Zelle XY”. Es wäre wahrscheinlich möglich, das per Makro/VB Script zu lösen, aber auch nur mit einem gewissen Aufwand.

  3. Hallo zusammen,

    ich habe folgedes Anliegen:

    Spalte A soll die Bedingte formatierung von Spalte B übernehemen. Die Spalte B ist variabel und wechselt von gelb, grün zu rot.

    Da ich ganz veiele davon habe (linke spalte soll immer so sein wie rechte) suche ich nach einer einfachen Formel, die sich nicht auf bestimmte Spalten bezeht. Ich habe mir das so vorgestellt:

    =A übernehme formatierung=rechte spalte

    Freue mich über Ideen. Habe leider nichts finden können im Netz.

    Danke vorab

  4. Hallo.

    Bitte um Hilfe. Mein Hirn raucht schon.

    Habe eine Tabelle in der verschiedene Rohstoffe eingetragen werden. Jeder Rohstoff hat einen eigenen Grenzwert. was ich schon mit einer wenn funktion geregelt habe. d.h. wenn in zelle A4 Kalk steht, schreib mir den Grenzwert von 0,3 in Zelle E4.
    Jetzt wird aber der gemessene Wert in Zelle D4 eingegeben. Und wenn dieser über dem Grenzwert ist hätt ich ihn gerne rot markiert. Was generell ja auch kein Problem ist, aber kann man das für die ganze Spalte übernehmen? Sozusagen dass sich die bedingte Formatierung immer auf die Zelle daneben bezieht?

    Danke und lg Carmen

    • Hallo Carmen,
      das ist ganz einfach: Markieren Sie zuerst den Bereich, der gefärbt werden, ich nehme fürs Beispiel mal an, dass der bei Ihnen bei D4 beginnt und weiter unten endet. Dann schreiben Sie als Formel für die Bedingte Formatierung: =D4>E4. Die Anwendung dieser Regel für die Zellen D5, D6, usw passiert automatisch, weil Sie keine einzelne Zelle, sondern einen Bereich markiert haben. Dabei passt Excel die Adressen automatisch an. Ihr Messwert in D5 wird also z.B. gegen den Grenzwert in E5 verglichen.

  5. Hallo
    vielen Dank für die Hilfe Gestern. Hat prima geklappt.
    Andere Frage: ich möchte in einer Liste jeweils eine farblich markierte Umbruchzeile in der die Summe aller darunter liegenden Zeilen gebildet wird. Und pro Gruppe aller z.B. grauen Zeilen soll in der blauen Gruppenumbruchzeile die Summe aller untergruppen gebildet werden.
    Wenn ich nun Zeilen in die Liste einfüge oder Lösche werden die Summen in der Gruppen oder Untergruppen Kopfzeilen nicht mit angepasst. Wie kann ich das erzwingen?
    Vielen Dank für die Hilfe

  6. herzlichen Dank für die Unterstützung gestern. Hat prima geklapp.
    Nun noch eine Frage:
    Die Tabelle ist in Form einer Liste aufgebaut und auf den jeweiligen Umbrüchen sollen die Zwischensummen gebildet werden.
    Wenn nun Listenzeilen eingefügt werden, werden die neuen Zeilen in den Summenzeilen nicht automatisch nachgeführt.
    Kann man das?

    Header = Summe SubHeader1+ Subheader2 + Subheader3 etc.
    Suchender = Summe Zeile1:Zeilex

    Herzlichen Dank für Ihre Unterstützung

  7. Hallo
    wie kann ich eine bedingte Formatierung verwenden wenn die Formel abhängig von 2 Zellen sein soll?
    also wenn J$32″E” UND T$32″E” soll U$32 rot sein.
    Vielen Dank für Hilfe

    • Dazu setzen Sie die Funktion UND() ein, die dann “wahr” zurückgibt, wenn alle innerhalb der Klammern genannten Bedingungen zutreffen, also in Ihrem Fall: =UND(J$32=”E”;T$32=”E”)

  8. Hallo!
    Ich habe eine Messauswertung mit einem 3 Spalten: SOLL, Toleranzen und dem IST. Wenn der IST -Wert nicht in der Toleranz ist färbt sich dieser natürlich rot. Zusätzlich habe ich dann noch zwei Spalten in denen mit einem “X” in denen jeweils Differenz oder akzepiert angekreuzt wird. Also wenn der ISTwert schlecht ist dann soll ein Kreuz in Differenz und wenn ok dann in Akzeptiert.

    Können sie mir dabei helfen? Ich habe schon ünerall gesucht, aber scheinbar finde ich nicht das passende.

    • Hallo, das ist kein Fall für die bed. Formatierung, sondern für die Funktion WENN(): Angenommen, Sie haben die Messwerte in Zeile 2 mit A2=SOLL, B2=Toleranz, C2=IST, D2=Anzeige “Differenz” E2=Anzeige “Akzeptiert”. Dann verwenden Sie in D2 die Formel =WENN(ABS(C2-A2)>B2;”X”;””). Wenn also die absolute Abweichung größer als die Toleranz ist, soll hier ein “X” erscheinen, ansonsten nichts. In E2 tragen Sie das Gegenstück ein: =WENN(ABS(C2-A2)>B2;””;”X”).

  9. 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.

  10. 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

  11. 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.

  12. 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.

  13. 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

  14. 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.

  15. 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


Hier finden Sie noch mehr Excel-Tipps. Zum Beispiel zum Arbeiten mit Kalenderwochen oder unseren großen Ratgeber zu Dropdown-Listen.