Was kann man mit der bedingten Formatierung machen?

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.

Schnellformatierung:

Auswahl einer oder mehrerer Zellen.

Auf der Registerkarte auf "Start" klicken.

In "Formatvorlage" auf den Pfeil neben der Schaltfläche "Bedingte Formatierung" gehen.

Klick auf "Regeln zum Hervorheben von Zellen​​​​​​​".

Gewünschten Befehl auswählen, z. B. größer als, kleiner als, zwischen, Datum etc.

Werte eingeben und Format auswählen.

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 Zahl, Datum oder Uhrzeit:

Dazu einen Zellwert und Vergleichsoperator auswählen und Zahl, Datum oder Uhrzeit eingeben. Beispiel: Für "Zwischen" geben Sie 300 und 500 ein oder Sie wählen "Gleich" aus und geben ein Datum ein.

Per Formel können Sie einen Zahlen-, Datums- oder Uhrzeitwert zurückerhalten.

Formatieren nach Text:

"Bestimmter Text" und "Vergleichsoperator" auswählen.

Text eingeben, z. B. auf "Enthält" das Wort „Gold“ oder auf "Beginnen mit" eine Vorsilbe, aufgrund derer Sie suchen möchten.

Es kann eine Zeichenfolge von maximal 255 eingegeben werden.

Mit der Anwendung von Formeln können Werte zurückgegeben werden. Dazu muss die Formel mit einem (=) beginnen. Ungültige Formeln werden nicht formatiert. Ratsam ist, die Formel zunächst auf Fehler zu testen.

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.

Schnellformatierung:

Gewünschten Bereich (Zelle, Tabelle oder ganzes Blatt) markieren.

Auf der Registerkarte "Start" auf die Schaltfläche "Bedingte Formatierung" klicken.

Auf "Datenbalken" gehen und eine Füllung auswählen.

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.

2-Farbskala

Schnellformatierung:

Zelle, Tabelle oder ganzes Blatt markieren.

Auf der Registerkarte "Start" auf "Bedingte Formatierung" klicken.

Auf "Farbskalen" gehen und das gewünschte 2-Farbskalenformat anklicken. Die obere Farbe steht für höhere Werte, die untere Farbe für niedrigere.

3-Farbskala

Die dritte Farbe ermöglicht die Visualisierung von mittleren Werten – neben der farblichen Darstellung von hohen und niedrigen Werten wie in der 2-Farbskala.

Schnellformatierung:

Zelle, Tabelle oder ganzes Blatt markieren.

Auf der Registerkarte "Start" auf "Bedingte Formatierung" klicken.

Auf "Farbskalen" gehen und das gewünschte 3-Farbskalenformat anklicken. Die obere Farbe steht für höhere Werte, die untere Farbe für niedrigere.

Zellen mit Symbolsätzen formatieren

Mit einem Symbolsatz lassen sich Daten in getrennten Kategorien betrachten und einordnen. Ein Symbol steht für einen bestimmten Wertebereich. Die bedingte Formatierung in Excel bietet unterschiedliche, farbige Symbole: Pfeile, Punkte, Kreuze, Ausrufezeichen und vieles mehr.

Schnellformatierung:

Zelle, Tabelle oder ganzes Blatt markieren.

Auf der Registerkarte "Start" auf "Bedingte Formatierung" klicken.

Auf "Symbolsätze" gehen und den gewünschten Symbolsatz auswählen

Formatierungsregeln selbst erstellen

Wer in den von Excel angebotenen bedingten Formatierungen keine geeignete Option findet, kann individuell Regeln zur Formatierung auf einfache Weise erstellen.

Zellbereich markieren, der bedingt formatiert werden soll.

Auf der Registerkarte "Start" auf "Bedingte Formatierung" gehen.

"Neue Regel" auswählen und mit "OK" bestätigen.

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

• REST: so können automatisch abwechselnde Zeilenfarben eingestellt werden
• IDENTISCH: mit dieser Formel können gesuchte Daten in einer Excel-Tabelle markiert werden, die einem festgelegten Wert entsprechen
• ISTFEHLER: kennzeichnet leere Zellen in der Excel-Tabelle automatisch oder lässt Fehlerwerte aus der Arbeitsmappe verschwinden
• NICHT und ISTZAHL: formatiert alle Excel-Zellen, in denen sich keine Zahlen befinden
• MAX: der größte Wert der Zelle wird in der Tabelle hervorgehoben
• MIN: erlaubt die bedingte Formatierung des kleinsten Werts in der Excel-Liste, damit das Minimum auf einen Blick erkennbar ist
• LINKS: abhängig von einer am Anfang stehenden Zeichenfolge werden Excel-Zellen automatisch formatiert und erhalten eine einheitliche Textfarbe
• GROSS und LINKS: ermöglichen die farbige Kennzeichnung einer Zelle mit neuem Inhalt
• RECHTS: Zellen können abhängig vom letzten Zeichen der Zeichenfolge formatiert werden
• HEUTE: hiermit können Termine mit der Ampelmethode farbig formatiert werden oder Datumswerte hervorgehoben werden, die bald aktuell werden
• MONAT oder JAHR: ermöglichen, die Excel-Liste in Monate oder Jahre zu unterteilen; mit JAHR können außerdem Datumswerte des aktuellen Jahrs markiert werden und Jahreswerte hervorgehoben werden
• ODER und WOCHENTAG: hebt Werte nach Wochentagen farbig hervor

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.

Bedingte Formatierung übertragen

Um eine bedingte Formatierung zu kopieren und in andere Zellen oder Zellbereiche einzufügen, nutzen Sie die Schaltfläche "Format übertragen".

Wählen Sie die Zellen mit der bedingten Formatierung aus, die kopiert und in anderen Zellen eingefügt werden sollen und klicken Sie diese an.

Nun auf "Start" und anschließend "Format übertragen" gehen. Der Cursor erscheint als Pinsel.

Die bedingte Formatierung lässt sich in eine Zelle oder einen Zellbereich einfügen, indem Sie den Pinsel über die jeweilige Zelle bewegen.

Mit ESC beenden Sie die Nutzung des Pinsels.

Durch Doppelklick auf "Format" übertragen können Sie den Pinsel weiterhin zum Einfügen bedingter Formatierungen nutzen.

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.

Löschen auf einem Arbeitsblatt oder im ganzen Arbeitsblatt

Auf Registerkarte "Start" auf "bedingte Formatierung" gehen.

Danach auf "Regeln löschen"

Anschließend "Regeln in gesamtem Blatt löschen" klicken.

Alternativ können Sie über das Dialogfenster "Inhalte auswählen" die bedingte Formatierung löschen.

Löschen im Zellbereich

Zellen markieren, die die gewünschte bedingte Formatierung enthalten.

Auf Schaltfläche "Leseanalyselupe" klicken (die Schaltfläche befindet sich unten rechts neben den markierten Daten).

Auf "Format löschen" gehen.

Die gleichen bedingten Formate im ganzen Arbeitsblatt finden und löschen

Auf die Zelle mit dem entsprechenden bedingten Format klicken.

Auf Registerkarte "Start" auf Pfeil neben "Suchen und Auswählen" klicken, anschließend auf "Inhalte auswählen".

Auf "Bedingte Formate" klicken.

Unter "Datenüberprüfung" auf "Gleiche" gehen (damit werden all jene Zellen mit den gleichen bedingten Formatierungen ausgewählt).

Auf Registerkarte "Start" auf "Bedingte Formatierung" gehen, danach "Regeln löschen" und "Regeln in ausgewählten Zellen löschen".

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.

Bereichsdefinition durch Auswahl

Auf "Formatierungsregel anwenden auf" gehen.

Entweder auf Dialogfeld "Neue Formatierungsregel" oder Dialogfeld "Formatierungsregel bearbeiten" klicken.

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.

Wie funktioniert bedingte Formatierung?

Kann ich bedingte Formatierungen kopieren?

Wie geht das Einfärben einer Zelle je nach zellwert?

Wie Bedingte Formatierung auf andere Zellen übertragen?