Zellen, Werte, Formeln, Regeln
© Tony Alexson - Shutterstock

Bedingte Formatierungen in Excel: Das ist möglich

Mit diesen Anleitungen erstellen Sie verschiedene Formatierungen

Bedingte Formatierungen in Microsoft Excel sind ein sinnvolles Werkzeug, Tabellen, Berichte oder Zellen markanter zu gestalten, einen Bereich oder mehrere Spalten hervorzuheben, Daten zu analysieren, Unterschiede zu verdeutlichen oder Trends zu ermitteln. Dazu dienen Datenbalken, Symbolsätze oder Farbskalen. Entsprechend der festgelegten Bedingungen wird die Darstellung von Zellen geändert oder der Zellbereich formatiert. Zu den zahlreichen integrierten Bedingungen können Sie individuell Ihre eigenen erstellen.

Bedingte Formatierungen sind sinnvoll und unterstützen Sie

Bedingte Formatierungen in Excel lassen sich vielseitig anwenden, ob auf eine Excel-Tabelle, einen bestimmten Zellbereich oder einen PivotTable-Bericht. Verschiedene Aktionen und Formatvorlagen stehen Ihnen dabei zur Verfügung. Der entscheidende Vorteil für Excel-Nutzer ist, dass besondere Werte oder Ergebnisse durch farbliche Kennzeichnung hervorgehoben werden können. Wenn sich ein Wert ändert, wird die Formatierung – je nach Bedingungen – automatisch angepasst.

Info: Die PivotTable („Pivot-Tabelle“) ist eine besondere Tabelle, mit der eine große Menge an Daten berechnet, zusammengefasst und analysiert werden können. Zusätzlich lassen sich schnell Vergleiche ziehen und Trends in den Untersuchungsdaten erkennen.

Bedingte Formatierung mit bestehenden Regeln bei Zahlen, Textinhalten oder Datumsangaben

Formatieren Sie mit einem Vergleichsoperator, lassen sich Zellen einfacher finden. Sie können Werte besser sichtbar machen, zum Beispiel größer oder kleiner als ein definierter Wert oder bestimmte Werte, die zwischen einer Ober- und Untergrenze liegen. Damit werden die gewünschten oder gesuchten Daten farblich dargestellt. Zur Bearbeitung stehen die Schnellformatierung oder die erweiterte Formatierung zur Verfügung. Bei der erweiterten Formatierung kann mit Formeln gearbeitet werden, um beispielsweise eine Excel-Spalte automatisch farbig zu markieren.

Beispiele:

  • Ein Lagerist möchte alle Bestände unter 15 Stück optisch durch eine farbliche Hinterlegung hervorheben.
  • In einer Tabelle sollen alle Städte, die mehr als 150.000 Einwohner haben, markiert werden.
  • Ein Produktionsunternehmen möchte alle Güter, die zwischen dem 1. Januar 2020 und dem 31. Januar 2020 gefertigt wurden, in roter Farbe herausstellen.

Erweiterte Formatierung:

  1. Eine oder mehrere Zellen in einer Tabelle oder einem PivotTable-Bericht auswählen.

  2. Auf Registerkarte "Start" gehen, in Gruppe Formatvorlagen auf den Pfeil neben "Bedingte Formatierung" klicken.

  3. Auf "Regeln verwalten" klicken: Es erscheint das Dialogfeld "Manager für Regeln zur bedingten Formatierung".

  4. Aktion auswählen:

    • Auf "Neue Regel" klicken: Das Dialogfeld "Neue Formatierungsregel" erscheint.
    • Um ein Format zu ändern, vergewissern Sie sich vorab, dass im Listenfeld "Formatierungsregel anzeigen für" das richtige Arbeitsblatt oder die richtige Tabelle ausgewählt wurde.
    • Im Feld "Wird angewendet auf "auf "Dialogfeld reduzieren" gehen; danach den neuen Zellbereich auf dem Arbeitsblatt auswählen.
    • Schaltfläche "Dialogfeld erweitern" anklicken.
    • Regel auswählen und auf "Regel bearbeiten" klicken: Es erscheint das Dialogfeld "Formatierungsregel bearbeiten".

  5. Um den Bereich für Felder im Wertebereich eines PivotTable-Berichts unter "Regel anwenden auf" zu ändern, verfahren Sie folgendermaßen:

    • Auswahl: Auf "Nur diese Zellen" klicken
    • Entsprechendes Feld: Auf "Alle <Wertefeld>-Zellen mit den gleichen Feldern" klicken
    • Wertefeld: Auf "Alle <Wertefeld>-Zellen" klicken

  6. Unter "Regeltyp auswählen" auf "Nur Zellen formatieren, die enthalten" klicken

Nun können Sie unter "Regelbeschreibung bearbeiten" im Listenfeld "Nur Zellen formatieren mit" Aktionen ausführen:

Formatieren nach Datum:

  • Datum und Vergleichsdatum auswählen, z. B. Gestern, Nächste Woche, oder Diesen Monat

Dann: Um ein Format anzugeben auf "Formatieren" klicken, es erscheint das Dialogfeld "Zellen formatieren". Das Gewünschtes Zahlenformat, Füllformat, Rahmen oder Schriftart auswählen, auf "OK" klicken.

Es ist möglich, mehrere Formate auszuwählen. Sie können im Feld "Vorschau" sehen, welche Formate ausgewählt wurden.

Bedingte Formatierung mit Datenbanken, Farbskalen und Symbolsätzen

Inhalte und Werte in Zellen lassen sich mit Datenbanken, Farbskalen oder Symbolsätzen visuell aufwerten und attraktiver sowie übersichtlicher gestalten. Sollen Werte in einem Zellbereich miteinander verglichen werden, gelingt das mit diesen bedingten Formatierungen einfacher.

Zellen mit Datenbalken formatieren

Mit Datenbalken lassen sich Zellen einfacher miteinander vergleichen. Der Wert in der Zelle wird durch die Länge des Balkens angezeigt. Je höher der Wert, desto länger der Datenbalken, je niedriger der Wert, desto kürzer ist der Balken. Auf diese Weise können hohe oder niedrige Werte schnell ermittelt werden.

Zellen mit Farbskalen formatieren

Farbskalen helfen dabei, unterschiedliche Dateninformationen leichter einzuordnen und deren Inhalt rasch miteinander vergleichen zu können. Je nach Farbschattierung werden hohe oder niedrige Werte angezeigt. Sie können Zellen mit einer 2- oder 3-Farbskala formatieren.

Finden Sie die Option nicht, die Sie für Ihre eigene Formatierungsregel brauchen, kann eine logische Formel weiterhelfen. Mithilfe einer Formel können Sie beispielsweise Excel-Zellen automatisch einfärben, sobald Inhalte vorhanden sind. Umgekehrt können Zellen bedingt formatiert werden, die leer sind.

Beispiel: Durch die Anwendung einer Formel können Sie Daten außerhalb des ausgewählten Bereichs vergleichen. Dazu gehören sogar Daten, die sich auf einem anderen Arbeitsblatt der gleichen Arbeitsmappe befinden. Oder sie haben die Möglichkeit, Werte mit einem von einer Funktion zurückgegebenen Ergebnis zu vergleichen. Die Formel muss logisch sein und „wahr“ oder „falsch“ zurückgeben.

Indem Sie Zellen direkt auf dem Arbeitsblatt markieren, können Sie Zellbezüge in eine Formel eingeben. Es handelt sich dabei um absolute Zellbezüge. Excel kann allerdings die Zellbezüge auch für jede Zelle im markierten Bereich anpassen. Wünschen Sie dies, wählen Sie relative Zellbezüge.

Schritt-für-Schritt-Anleitung zur Erstellung einer Regel

  1. Auf Registerkarte "Start", Gruppe "Formatvorlagen", auf den Pfeil neben "Bedingte Formatierungen" gehen und "Regeln verwalten" anklicken. Es erscheint das Dialogfeld "Manager für Regeln zur bedingten Formatierung".

  2. Folgende Aktionen sind möglich:

    • Wollen Sie ein bedingtes Format hinzufügen, gehen Sie auf "Neue Regel".
    • Ändern eines bedingten Formats:
      • Überprüfen Sie, ob Sie sich auf dem richtigen Arbeitsblatt befinden.
      • Zunächst in "Wird angewendet auf" auf "Dialogfeld reduzieren" klicken. Danach den neuen Zellbereich auswählen und auf "Dialogfeld erweitern" gehen.
      • Die gewünschte Regel auswählen, auf "Regel bearbeiten" klicken. Es erscheint "Formatierungsregel bearbeiten".

  3. Unter "Regeltyp auswählen" klicken Sie auf "Formel für die Ermittlung der zur formatierenden Zellen verwenden".

    • Unter "Regelbeschreibung bearbeiten" geben Sie eine Formel in "Werte formatieren, für die diese Regel wahr ist" ein. Die Formel beginnt mit (=) und muss „wahr“ (1) oder „falsch“ (0) zurückgeben.
    • Auf "Format" können Sie das Dialogfeld "Zellen formatieren" anzeigen.
    • Nun Zahl, Schriftart, Füllformat usw. auswählen, das angewendet werden soll, wenn der Zellwert wahr ist und mit "OK" bestätigen. Die ausgewählten Formate werden in "Vorschau" angezeigt.

Die Formel ZÄHLENWENN gibt zum Beispiel Wahrheitswerte zurück. Wenn diese Formel verwendet wird, können doppelt vorkommende Werte ermittelt oder Inhalte, die in einer bestimmten Häufigkeit auftreten, farblich markiert werden. So lassen sich auch Unikate finden und farbig hervorheben. Weitere Formeln, die für die bedingte Formatierung genutzt werden können, sind

Beispiel zur Erstellung einer Regel zur UND- und ODER-Prüfung:

Für dieses Beispiel werden zwei Werte zur bedingten Formatierung verwendet. Gilt die erste Regel nicht, gilt automatisch die zweite Regel.

Erste Regel: Ein Hauskäufer möchte prüfen, ob der geplante Immobilienerwerb seinem Budget entspricht. Er kann an Eigenmitteln bis zu 80.000 € als Anzahlung einbringen und eine monatliche Darlehensrate in Höhe von maximal 1.200 € bedienen. Entsprechen sowohl Anzahlung als auch Darlehensrate diesen Voraussetzungen, werden die entsprechenden Zellen grün markiert.

Zweite Regel: Entsprechen entweder Anzahlung oder Rate nicht den Voraussetzungen, werden die entsprechenden Zellen rot markiert.

Die Formel zur Erstellung der ersten Regel unter Verwendung der Farbe Grün lautet folgendermaßen:

=UND(WENN($B$4<=80000;1);WENN(ABS($B$5)<=1200;1))

 

Die Formel zur Erstellung der zweiten Regel unter Verwendung der Farbe Rot lautet folgendermaßen:

=ODER(WENN($B$4<=80000;1);WENN(ABS($B$5>=1200;1))

Übertragen und löschen bedingter Formatierungen

Bedingte Formatierungen können kopiert und auf anderen oder neuen Daten des Arbeitsblattes angewendet oder wieder gelöscht werden.

Alternativ kann die bedingte Formatierung mit den Tastenkombinationen kopiert und in eine andere Excel-Zelle eingefügt werden.

Bedingte Formatierung löschen

Man unterscheidet zwischen einem Löschvorgang auf einem Arbeitsblatt oder einem Löschvorgang in einem Zellbereich.

Bedingte Formatierungen im PivotTable-Bericht

Mit einem PivotTable-Bericht haben Nutzer die Möglichkeit, große Datenmengen rasch zusammenzufassen. Zellbereich, Excel-Tabelle oder PivotTable-Bericht – bedingte Formatierungen funktionieren im Grunde überall gleich. Lediglich im PivotTable Bericht gibt es einige Besonderheiten, die Sie kennen sollten:

  • Einige bedingte Formate funktionieren nicht auf Feldern im Wertebereich eines PivotTable-Berichts.
  • Ändern Sie das Layout des PivotTable-Berichts auf irgendeine Weise, besteht die bedingte Formatierung weiter, bis die Felder in den zugrunde liegenden Daten entfernt werden.
  • Bedingte Formatierungen für Felder im Wertebereich basieren möglicherweise auf der Datenhierarchie. Der Bereich dieser bedingten Formatierungen wird durch die nächst niedrigere oder nächst höhere Ebene der Hierarchie in Zeilen für eine oder mehrere Spalten oder in Spalten für eine oder mehrere Zeilen bestimmt.
  • Der Bereich der bedingten Formatierung für Felder im Wertebereich lässt sich durch drei Methoden definieren:

    • Auswahl
    • Entsprechendes Feld
    • Wertfeld

Die Standartmethode ist die Auswahl.

Diese Methode eignet sich sowohl für eine zusammenhängende Reihe von Feldern im Wertebereich, z. B. Erhebungsdaten für eine Region, als auch für eine nicht zusammenhängende Reihe von Feldern im Wertebereich, wie Erhebungsdaten für verschiedene Regionen auf unterschiedlichen Ebenen der Datenhierarchie.

Bereichsdefinition durch ein entsprechendes Feld

Diese Methode eignet sich, wenn Sie

  • es vermeiden wollen, viele nicht zusammenhängende Elemente auswählen zu müssen
  • mehrere Felder im Wertebereich für eine Datenhierarchieebene bedingt formatieren wollen
  • Teilergebnisse ausschließen wollen

Bereichsdefinition durch ein Wertfeld

Diese Methode eignet sich, wenn Sie

  • es vermeiden wollen, viele nicht zusammenhängende Elemente auswählen zu müssen
  • mehrere Felder im Wertebereich für sämtliche Hierarchieebenen bedingt formatieren wollen
  • Gesamtsummen und Teilergebnisse mit einbeziehen wollen.

Fazit: Bedingte Formatierungen in Excel erleichtern den Überblick über Daten

Wenn Sie in Ihrer Excel-Tabelle bestimmte Datenwerte farblich hervorheben wollen, ist die bedingte Formatierung in Excel ideal geeignet. Ihnen stehen dabei zwei Möglichkeiten zur Verfügung, um die bedingte Formatierung anzuwenden. Entweder Sie verwenden von Excel vorgegebene Regeln, um Textinhalte, Zahlenwerte oder Datumsangaben farblich zu markieren. Oder Sie legen selbst eine Regel mit verschiedenen Bedingungen fest, unter der die relevanten Werte gekennzeichnet werden sollen. Mithilfe von Datenbalken, Farbskalen und Symbolsätzen können Sie Daten leicht und effektiv miteinander vergleichen. Falls die Gestaltungsoptionen nicht mehr gebraucht werden, können Sie die mit ein paar Klicks entfernen.