Tipps & Tricks 10.10.2016, 06:00 Uhr

Excel: Ampelfarben kombiniert mit Dropdownliste

Sie möchten per Ausklappliste in Excel aus «OK/prüfen/ungültig» einen Wert auswählen können, worauf sich die Zeile grün, gelb oder rot färbt.
Lösung: Das ist eigentlich ganz einfach. Kombinieren Sie die Tipps aus dem meistgelesenen PCtipp-Artikel «Dropdown-Liste in Excel erstellen» mit der einfachen Funktion der bedingten Formatierung.
Zuerst zur Dropdownliste: Erstellen Sie in einem separaten Tabellenblatt untereinander die Einträge für die Dropdownliste in der gewünschten Reihenfolge, z.B. OK, prüfen, ungültig. Markieren Sie die Zellen mit den Einträgen, wechseln Sie zum Reiter Formeln und klicken Sie auf Namen definieren. Tippen Sie als Namen etwas passendes ein, z.B. Status, und klicken Sie auf OK. Wechseln Sie zum Tabellenblatt, in welchem Sie die Werte jeweils auswählen wollen. Markieren Sie die betroffene Spalte, gehen Sie zum Reiter Daten und klicken da bei den Datentools auf Datenüberprüfung. Bei Zulassen wählen Sie Liste, lassen «Leere Zellen ignorieren» und «Zellendropdown» angehakt und tragen bei Quelle hinter einem Gleichzeichen den vorhin definierten Namen ein. Das könnte zum Beispiel =Status heissen. Klicken Sie auf OK, dann haben Sie Ihre Dropdownliste.
Sollen jetzt auch noch die Zellen je nach gewähltem Eintrag in Grün, Gelb oder Rot erscheinen? 
Erst Grundsätzliches: Wir bearbeiten zuerst nur eine Zeile und weisen ihr alle drei möglichen bedingten Formatierungen zu. Erst danach wird dieser Formatierungssatz in die restlichen Zeilen kopiert. 
Los gehts: Markieren Sie die Zellen in der ersten Zeile, die allenfalls einzufärbende Daten enthalten, zum Beispiel die Zellen A2 bis C2. Im Reiter Start gehts via Bedingte Formatierung zu Neue Regel. Wählen Sie beim Regeltyp den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden. Klicken Sie ins leere Feld «Werte formatieren, für die diese Formel wahr ist». Klicken Sie daraufhin in die Zelle, in welcher der erste Statuseintrag steht bzw. stehen könnte, z.B. C2. Im Feld erscheint zunächst =$C$2
Wichtig: Falls Excel auch bei Ihnen ein $-Zeichen vor die Ziffer 2 setzt, entfernen Sie das $-Zeichen! Sonst wird die Formel nicht funktionieren.
Dahinter tippen Sie die Zeichenfolge ="OK" ein, damit die Formel etwa =$C2="OK" lautet. Jetzt gehts zu Formatieren. Wählen Sie hier aus, in welcher Weise die Zellen im Falle des Wertes «OK» formatiert werden sollen. Greifen Sie beispielsweise unter Ausfüllen zur Hintergrundfarbe Grün. Klicken Sie auf OK.
Bleiben Sie gleich in der ersten Zeile. Öffnen Sie Bedingte Formatierung/Regeln verwalten. Klicken Sie auf Neue Regel/Formel zur Ermittlung der zu formatierenden Zellen verwenden. Wieder ein Klick ins Formelfeld, gefolgt vom Klick ins Feld mit dem ersten Statuswert (z.B.) C2. Diesmal ergänzen Sie die Formel anders. Sie lautet jetzt z.B. =$C2="prüfen". Via Formatieren gehts nun etwa zur Hintergrundfarbe Gelb. Klicken Sie auf OK.
Auf genau diesem Weg verpassen Sie hier noch eine dritte Regel: Neue Regel/Formel zur Ermittlung der zu formatierenden Zellen verwenden, ein Klick ins Formelfeld und ins erste Statusfeld, diesmal ergänzen Sie die Formel mit dem Eintrag für Rot: =$C2="ungültig". Formatieren Sie auch diese Variante passend, eben zum Beispiel in Rot. Klicken Sie auf OK. Das sieht nun etwa so aus.
Die drei Einfärberegeln
Hässliches Beispiel für bedingte Formatierung
Diese Regeln gelten zunächst nur für die erste Zeile bzw. in unserem Beispiel für die Zellen A2 bis C2. Ist die bereits formatierte Zeile noch markiert? Falls nicht, markieren Sie die Zellen. Klicken Sie danach im Reiter Start gleich am Anfang beim Bereich «Zwischenablage» aufs Pinselsymbol für Format übertragen. Damit lässt sich nämlich auch eine bedingte Formatierung übertragen. Markieren Sie die darunter liegenden Zellen, dann werden diese gemäss Wert in der Spalte C eingefärbt.
Falls die Zellen alle mit derselben Farbe erscheinen, haben Sie vergessen, das $-Zeichen an der richtigen Stelle zu löschen. 
Sie werden vermutlich auch keine hässlichen Kompletteinfärbungen mögen, siehe das obere der beiden Bilder links. Anstelle einer Hintergrundfarbe lässt sich auch eine Schriftfarbe wählen, allenfalls zusammen mit einer Schriftauszeichnung wie «fett». Das tut in den Augen weniger weh, siehe das untere der beiden Bilder.


Kommentare

Avatar
Maedly
14.07.2017
Ampelfarben Lieber Autor, warum klappt die beschriebene Vorgehensweise nicht bei 7 Werten, die in einer Spalte untereinander stehen? Die ersten 3 Werte werden farbig angezeigt und die anderen 4 nicht. Es geht um eine Statusliste mit 7 Status, die farbig unterschiedlich dargestellt werden sollen. LG Maedly

Avatar
Turakos
14.07.2017
7 Status Bedingte Formatierung Hallo Maedly Das ist ein Thema vom Oktober 2016. Den drei Kriterien sind je bedingtes Format (Test-Farbe) zugeordnet. Du hast 7 Kriterien (7 Status) dementsprechend sind 7 Bedingungen zu formulieren, jede mit einer anderen Farbe. Versuch dein Problem zu lösen mit diesem Beitrag: http://www.pctipp.ch/tipps-tricks/kummerkasten/office/artikel/excel-ampelfarben-kombiniert-mit-dropdownliste-86218/

Avatar
Maedly
14.07.2017
Link zum Thema Hallo Turakos, vielen Dank für die schnelle Antwort. Nach der von dir verlinkten Beschreibung bin ich vorgegangen. Liste untereinander erstellt, -Namen vergeben, Spalte markiert, Dropdown angelegt, erstes Feld markiert und 7 verschiedene Farben mit 7 verschiedenen Bedingungen, in dem von dir genannten Format erstellt. Resultat: bei den ersten 3 Farben funktioniert es, bei den anderen nicht. Hast du noch eine Idee? Beste Grüße Maedly

Avatar
Turakos
14.07.2017
Guten Abend Maedly In Beispiel findest du bitte im Anhang. Schau dir das Beispiel an und versuche es umzusetzen. Das Beispiel im Anhang hat 7 Status, nämlich 1 bis 7 mit jeweiliger Farbe für die Schrift und weiss für den Zellhintergrund (Ausfüllen). Die Bedingte Formatierung gilt für die hellgrauen Zellen. Die Auswahl der Status erfolgt aus einer Gültigkeitsregel.

Avatar
Maedly
17.07.2017
Fehler erkannt bei Ampelbericht Hallo Turakos, schon die erste Anleitung hätte funktioniert, wenn ich nicht vor der bedingten Formatierung einge der einzutragenden Zellen mit "Alt Return" zweizeilig formatiert hätte. Das Steuerzeichen war offensichtlich der Grund, warum die Anleitung bei mir nicht funktioniert hat. Also, am Ende alles gut und es funktioniert. vielen Dank Gruß Maedly