Excel – Bedingte Formatierung richtig angewendet
3. Januar 2022 in Excel
Bei großen Datenmengen in Excel den Überblick zu behalten ist ein Problem! Das denken zumindest viele, die einen kurzen Blick darauf werfen. Nutzt man aber die bedingte Formatierung in Excel, ist es leicht, die Daten im Blick zu behalten. Denn die bedingte Formatierung ist ein geeignetes Excel-Tool, zu farblichen Darstellung von Excel-Werten, die z.B. bestimmte Sollwerte über- oder unterschreiten. Sie eignet sich aber auch hervorragend für Erkennung von Duplikaten in Adress- oder Artikellisten.
Der Vorteil gegenüber der Sortier- und Filterfunktion im Excel ist die sich ständig aktualisierende Darstellung der Werte, da sich die Formatierung immer auf einen Zellbereich bezieht.
In diesem Tutorial erklären wir anhand kleiner Beispiele den Funktionsumfang der bedingten Formatierung.
REGELN ZUM HERVORHEBEN VON ZELLEN
In diesem Beispiel betrachten wir die Artikelliste eines Lebensmittelgeschäftes. Diese Liste enthält den Bestand und die Absatzzahlen der einzelnen Produkte einer Woche.
Die Inhaber beschäftigen nun die Frage, welche Artikel müssen ggf. neu bestellt werden bzw. sind mit weniger als 100 Stück noch im Bestand?
Vorgehensweise: Markieren Sie den Datenbereich der Spalte B.
- Wählen Sie aus dem Register START – bedingte Formatierung – Kleiner als…aus.
- Geben Sie in dem Fenster den Grenzwert – in unserem Beispiel 100 – ein. Die hellrote Füllung ist standardmäßig hinterlegt. Sie können aber aus dem Dropdown-Feld aus einer Vielzahl anderer Farben auswählen.
Mit OK wird nun der Zellbereich farbig hinterlegt.
REGELN FÜR OBERE / UNTERE WERTE
Nun interessiert die Inhaber des Geschäftes auch noch, welche Artikel am meisten verkauft wurden – also wer sind die Top 5 der Woche?
Vorgehensweise: Welche Artikel wurden am meisten verkauft – wer sind die Top 5 der Woche?
- Dazu markieren Sie den Datenbereich der Spalte C.
- Wählen Sie aus dem Register START – bedingte Formatierung – Regeln für obere / untere Werte – obere 10 Elemente aus.
In der Dropdown-Liste reduzieren Sie den Wert auf 5, wählen eine geeignete Füllfarbe aus und bestätigen mit OK.
FARBEN UND FARBVERLÄUFE
Farben und Farbverläufe veranschaulichen die unterschiedliche Bedeutung einzelner Werte. Dabei werden alle Zellen eines ausgewählten Zellbereichs farbig hervorgehoben. Dadurch können Trends, Schwankungen und Tendenzen dargestellt werden. Für unser Lebensmittelbeispiel heißt das, welche Getränke liefen im Vergleich zu den anderen besser oder schlechter.
Vorgehensweise
- Markieren Sie den Datenbereich für den Verkauf der lfd. Woche.
- Wählen Sie aus dem Register START – bedingte Formatierung – Datenbalken aus.
- Wählen Sie eine Farbe Ihrer Wahl und dann wird der gesamte Datenbereich farblich markiert.
Ändern sich die Daten in der nächsten Woche, passen sich die Datenbalken dynamisch an. D.h., ausgehend vom kleinsten und höchsten Wert innerhalb des Datenbereiches werden die Datenbalken proportional angepasst.
Anker Werbeblock
FORMATIERUNGEN MIT SYMBOLEN
Spätestens seit Emoticons ihren Siegeszug in unseren SMS- und What’sApp-Alltag angetreten haben, weiß wohl jeder über die Aussagekraft von Symbolbildern Bescheid. Solche passenden Symbole können auch den Werten einer Excel-Tabelle hinzugefügt werden, wenn es zum Beispiel um die Kundenzufriedenheit in den einzelnen Filialen einer Kaufhauskette geht.
Vorgehensweise
- Markieren Sie den Datenbereich B3:B10.
- Wählen Sie aus dem Register START – bedingte Formatierung – Symbolsätze aus.
- Wählen Sie einen Formatstil Ihrer Wahl aus.
Das Ergebnis ist leider nicht zufriedenstellend. Excel bewertet – wie bei jeder anderen Art der bedingten Formatierung – hohe Werte als gut und niedrige Werte als schlecht.
Die Filiale 1 mit 12 Beschwerden pro Woche wird in Excel positiv bewertet und die Filiale 5 mit nur einer Beschwerde als negativ.
Um die Logik „auf den Kopf zu stellen“ müssen wir deshalb eine Änderung vornehmen.
- Markieren Sie den Datenbereich B3:B10.
- Wählen Sie aus dem Register START – bedingte Formatierung – Regeln verwalten – aus.
- Klicken Sie auf Regel bearbeiten. In der darauffolgenden Abfrage wählen Sie den Eintrag „Symbolreihenfolge umkehren“ und bestätigen mit OK.
Jetzt steht die Filiale 5 mit einem grünen Symbolpfeil in der Liste.
ADRESSDUPLIKATE ERKENNEN
In diesem Beispiel handelt es sich um eine Adressliste die Duplikate enthält. Um die Duplikate zu erkennen, nutzen wir die bedingte Formatierung, die doppelte Werte in einer Referenzspalte zu kennzeichnen.
Hier dient die Kundennummer zur Identifizierung der Duplikate.
Vorgehensweise
- Markieren Sie den Datenbereich in der Spalte A.
- Wählen Sie aus dem Register START – bedingte Formatierung – Regeln zum Hervorheben von Zellen – Doppelte Werte – aus. Mit Auswahl einer geeigneten Farbe bestätigen Sie mit OK.
- Ist die Liste sehr groß, kann es vorkommen, dass im Monitorausschnitt die doppelten Werte nicht sichtbar sind. Wenden wir aber die Sortierung nach Farbe in de Spalte A an, werden die Werte nach oben geschoben.
Stellen Sie dazu den Cursor innerhalb des Datenbereiches in die Spalte A und wählen sie unter START – Sortieren und Filtern – benutzerdefiniertes Sortieren – aus.
- Sortieren Sie nach: Kundennummer – Zellenfarbe – Reihenfolge: Farbe nach oben
- Abschließend kontrollieren Sie, ob es sich tatsächlich um Duplikate handelt und löschen die doppelten Datensätze.
FAZIT
Grundsätzlich lässt sich mit der bedingten Formatierung noch viel mehr aus Ihren Excel-Tabellen herausholen. Viele Funktionen sind selbsterklärend und mit ein bisschen Routine lassen sich Ergebnisse erzielen, die die Arbeit mit Excel erleichtern und große Datenmengen schnell und auf einfache Weise veranschaulichen.
Wenn Sie erfahren wollen, wie Sie die bedingte Formatierung in Verbindung mit Formeln einsetzen können, dann schauen Sie sich unseren Beitrag „Die bedingte Formatierung mit Formeln“ an.
