AutoFilter: Einfach alle aktiven Filter farblich hervorheben bzw. zurücksetzen (MS EXCEL)
Vielnutzer umfangreicher Tabellen werden sicherlich die Funktion „AutoFilter“ von MS Excel kennen und — genauso wie ich — zu schätzen wissen. Obwohl äußerst praktisch, hat diese Funktion dennoch zwei lästige Macken. Macken, die gerade bei Tabellen mit vielen Spalten zum Tragen kommen: zum einen lässt sich unter gewissen Umständen nur schwer erkennen, wo ein AutoFilter überhaupt aktiv ist. Und zum anderen kann es unnötig umständlich werden, mehrere aktive Filter zurückzusetzen.
Wenn der AutoFilter bei euch ein Thema ist, dann lest weiter und erfahrt in diesem Beitrag, wie ihr ab jetzt in all euren Tabellen jeden aktiven Filter farblich hervorheben und mit nur einem Klick alle Filter auf ein mal zurücksetzen könnt.
Für Eilige: gleich weiter zur Lösung oder zur Installationsanleitung.
Immer up to date: Aboniere den RSS-Feed für die automatische Benachrichtigung über neue Artikel.
Inhaltsverzeichnis
Die AutoFilter-Funktion und ihre Macken
Mit dem AutoFilter können Tabellen nach bestimmten Kriterien gefiltert werden, sodass nur jene Zeilen angezeigt werden, die diesen Kriterien entsprechen. Durch die parallele Anwendung mehrerer Filter können unterschiedliche Kriterien miteinander kombiniert werden — soviel zur allgemeinen Funktion1.
Jedoch sehe ich — wie eingangs bereits erwähnt — in der AutoFilter-Funktion auch zwei lästige Macken. Diese Macken und meine Workarounds dafür möchte ich euch im Folgenden vorstellen.
Aktive AutoFilter sind nicht immer deutlich zu erkennen
Um erst mal zu erkennen, DASS ein Filter aktiv ist, bietet MS Excel (zumindest) zwei Möglichkeiten: Zum einen findet sich in der Statusleiste der Hinweis, dass x von y Datensätzen gefunden wurden. Und zum anderen werden alle Zeilennummern im gefilterten Bereich blau dargestellt:
Aber welche Filter konkret aktiviert sind, erkennt man nur an den jeweiligen Symbolen des Autofilters (Pfeil im Bild oben). Und diese können beispielsweise in Tabellen mit vielen schmalen Spalten oder auf kleinen Notebookmonitoren mit hohen Auflösungen schon mal untergehen.
Mir jedenfalls würde es besser gefallen, wenn sich aktive AutoFilter deutlicher hervorheben — beispielsweise durch ein Umfärben der Spaltenüberschrift.
Gleich weiter zur Lösung oder zur Installationsanleitung.
Das Zurücksetzen ALLER aktiven Filter ist unnötig umständlich
Auch das Deaktivieren aller aktiven Filter bzw. das Einblenden aller versteckten Spalten und Zeilen könnte einfacher sein.
Beruflich habe ich jeden Tag mit recht großen Tabellen (180 Spalten und mehr) zu tun. In diesen Tabellen nutze ich nicht nur die Filterfunktion sehr intensiv sondern blende — der Übersichtlichkeit wegen — auch immer wieder einzelne Spalten aus.
Diese meist verstreuten Filter und ausgeblendeten Spalten nach jeder Änderung händisch zurück zu setzen ist auf Dauer schon etwas umständlich2. Hier wäre ein eigener Befehl oder Button interessant, der das alles auf ein mal erledigt.
Gleich weiter zur Lösung oder zur Installationsanleitung.
Meine Workarounds
Als Workaround habe ich mir — nach einigen Recherchen im Internet — kurzerhand zwei benutzerdefinierte Funktionen geschrieben und in ein Excel-Add-In gepackt.
Lösung 1: Aktive AutoFilter farblich hervorheben
Die benutzerdefinierte Funktion AF_KRIT(ZELLE) gibt „WAHR“ zurück, falls in der angegebenen Zelle ein AutoFilter aktiv ist. Diese Funktion kann in einer bedingten Formatierung dazu verwendet werden, um — abhängig vom Ergebnis — beispielsweise die Hintergrundfarbe der betroffenen Zelle zu verändern:
Die Idee und die Code-Basis kommen von Thomas Ramel.
Lösung 2: One-Button-Lösung zum Zurücksetzen aller aktiven Filter
Die benutzerdefinierte Funktion ResetAnzeige() setzt in der aktuellen Tabelle alle aktiven AutoFilter zurück, blendet alle versteckten Spalten und Zeilen ein und setzt den Cursor in die Zelle A1.
Wird diese Funktion mit einer eigenen Schaltfläche in die Schnellstartleiste verknüpft, lässt sich diese Aktion jederzeit mit nur eimem Mausklick ausführen:
Installationsanleitung
Download
Das Excel-Add-In myFunctions.xlam steht im Downloadbereich in einem 7zip-Archiv samt Beispieldatei bereit. Dieses kann nach dem Herunterladen an beliebiger Stelle entpackt werden.
Installation
Damit das Add-In myFunctions.xlam verwendet werden kann, muss Excel erst einmal wissen wo dieses gespeichert ist. Die folgende Schritt-für-Schritt-Anleitung zeigt, wie zusätzliche Add-Ins in Excel verfügbar gemacht werden.
- Zum Verfügbar machen zusätzlicher Add-Ins, muss die Excel-Add-Ins-Verwaltung geöffnet werden:
Aufruf der Excel-Add-Ins-Verwaltung. - In der Verwaltung wird über den Button
Durchsuchen...der Standardordner für die Microsoft Add-Ins geöffnet. Dorthin wird per Drag and Drop das entpackte Add-InmyFunctions.xlamkopiert und ausgewählt (Add-In markieren und Öffnen anklicken):
Auswählen und Verfügbar machen eines neuen Excel-Add-Ins. - Das neue Add-In sollte jetzt in der Verwaltung aufscheinen. Mit dem Haken davor wird es aktiviert. Die Verwaltung kann jetzt mit OK geschlossen werden:
Das neue Excel-Add-In aktivieren. - Zum Test kann in einer beliebigen Zelle mit
=myGetVersion()die Versionsnummer des Add-Ins ausgegeben werden:
Funktionstest des gerade installierten Add-Ins: Ausgabe der Versionsnummer.
1. Entfernen des Hakens vor dem Add-In und
2. Bestätigung mit OK (Schließen der Add-Ins-Verwaltung).
Anschließend kann mit Schritt 1 dieser Anleitung fortgefahren werden.
Einrichten der Funktion „Filter farblich hervorheben“
=AF_KRIT() in der bedingten Formatierung nicht verwendet werden kann, wenn sie in einem Add-In definiert wurde. Warum das nicht geht versuche ich gerade herauszufinden. Deswegen habe ich diese Funktion bis auf Weiteres in ein VBA-Modul ausgelagert. Dieses Modul (myAfKrit.bas) muss in jedes Dokument, in welchem die Hervorhebung genutzt werden soll, importiert werden.- Um die Hervorhebung nutzen zu können, muss ein Arbeitsblatt mit AutoFiltern geöffnet sein:
Ausgangslage für die Einrichtung und Nutzung der farblichen Hervorhebung von aktiven Autofiltern. - In diesem werden alle Zellen markiert, die einen AutoFilter enthalten.
- Danach wird der Manager für Regeln zur bedingten Formatierung geöffnet und mit dem Button „
Neue Regel...“ eine neue Regel erstellt:
Beschriftung - Als Regeltyp Formel zur Ermittlung der zu formatierenden Zellen verwenden auswählen:
Beschriftung - Und folgende Formel eingeben:
=AF_KRIT(A1)=WAHR. „A1“ ist als die erste Zelle des markierten Bereiches mit AutoFilter entsprechend anzupassen. - Auf „Formatieren…“ klicken und das gewünschte Format für die Darstellung eines aktiven AutoFilters einstellen.
Einrichten der Funktion „Alle Filter zurücksetzen“
Soll die neue Funktion zum Zurücksetzen aller Filter möglichst ohne Umwege erreichbar sein, ist der beste Platz dafür wohl eine Schaltfläche in der Symbolleiste für den Schnellzugriff:
Diese Symbolleiste befindet sich entweder über oder unterhalb des Menübandes und zeigt ständig die wichtigsten Befehle an.
Neue Befehle werden über den Anpassungsdialog hinzugefügt. Dieser lässt sich am einfachsten über das Kontextmenü der Symbolleiste (Rechtsklick auf die Symbolleiste) aufrufen:
Mit den folgenden Schritten wird eine neue Schaltfläche zur Symbolleiste hinzugefügt und mit der benutzerdefinierten Funktion ResetAnzeige verknüpft:
- Unter „Befehle auswählen“ den Eintrag
Makrosauswählen, - und den Befehl
ResetAnzeigemitHinzufügen >>zur Symbolleiste hinzufügen. - Mit den Pfeiltasten wird die Position der neuen Schaltflächen verändert
- und über
Ändern...die Schaltfläche mit einem besseren Symbol belegt.
AutoReset.Fußnoten
One-Button-Sicherungskopie von Dateien inkl. Zeitstempel
Neue Kategorie in Literature & Links: Finanzen
Der Filterbau – Ein unentbehrliches Handwerkszeug
About Author
Ing. Gerald Mayer
Konstrukteur seit 2000, mit den Tätigkeitsschwerpunkten: Konstruktion von Schaltschränken für Umwelttechnikanlagen, Administration von EPLAN, Stammdatenpflege, dem optimieren bzw. automatisieren von Standardabläufen (Scripting, VBA, ...), technische Redaktion, Maschinenüberprüfungen, und vielem mehr.
Hallo Gerald,
nettes Feature, genau, was ich suche.
Nur leider funktioniert das Einfärben der Filter-Zelle nicht wie beschrieben.
Ich verwende Excel 2013, muss als odie Datei als .xlsm speichern, das habe ich getan.
Ist die VBA-Funktion mit Excel 2013 kompatibel?
Muss ich ggf. etwas ändern, damit der Code ausgeführt wird?
Danke!
Harald
Hallo Gerald,
ich habe zusätzlich zum VBA-ode in der Tabelle selbst noch das Add-In installiert, dann hat der Färben funktioniert.
Da ich das nicht glauben konnte, habe ich das Add-In wieder deaktiviert, Färben funktionierte weiter.
Daraufhin habe ich das Add-In gelöscht und das Färben funktioniert weiter.
Also mission accomplished, auch wenn ich nicht verstehe, weshalb 🙂
Danke nochmal!
Harald
Hallo Harald, es freut mich wenn du es doch noch hinbekommen hast. So wie ich das getestet habe, brauchst du beide (das Makro und das Add-In) damit das Einfärben funktioniert.
LG Gerald
Moin Gerald,
bin bis gestern glücklicher Nutzer des AddIns gewesen. Aber leider läuft es seit heute nicht mehr.
es kommt immer zur Fehlermeldung, dass das Objekt verschoben/umbenannt/gelöscht wurde, was aber nicht der Fall ist. Ich habe mehrer Ordner probiert.
Hallo Stefan,
es freut mich zu lesen, dass du das AddIn gut gebrauchen kannst. Hast du schon versucht, es neu zu installieren? Ich habe das gerade ausprobiert – es läuft einwandfrei bei mir. Du kannst mir gerne eine E-Mail mit Screenshots oder Beispieldateien schicken.
LG Gerald
kann sein das es an OneDrive liegt. Vorhin dachte ich hätte es wieder zum Laufen gebracht. Konnte die VErsion auslesen, beim nächsten Öffnen war es wieder kaputt. 🙁
jetzt läuft es wieder. Kurios. 🙂
Hallo Herr Mayer,
Ich bin an dem Punkt angelangt, an dem das Add-In myFunktions.xlam verwendet werden kann. Es fehlt wahrscheinlich das von Ihnen erwähnte Modul (myAfKrit.bas). Könnten Sie mich aufklären, wo dieses Modul verfügbar ist?
Hallo Herr Szeltner, das Modul „myAfKrit.bas“ finden Sie im Downloadbereich, Abschnitt „Sammlung von benutzerdefinierten Funktionen (MS Excel)“. Laden Sie die Datei „myFunctions_2.0.0.7z“ herunter. LG Gerald
Hallo Gerald, eigentlich genau das, was ich brauche.
Die Reset-Anzeige funktioniert.
Das Einfärben der gefilterten Zelle funktioniert leider nicht. Ich weiß nicht, ob hier die Sache case sensitive ist, weil nur das K ist groß geschrieben, der Rest klein, auch wenn es groß ist bei der Abspeicherung. Mach ich was falsch? Danke!
Reinhard
Hallo Gerald,
vielen Dank, jetzt funktioniert es. Das Geheimnis war die korrekte Installation der .bas Datei.
An dieser Herausforderung bin ich gewachsen 😉
Möglichweise ist es sinnvoll, so wie bei der bedingten Formatierung einen step-by-step Wegweiser einzubauen für so DAUs wie mich… 😉
Hallo Gerald,
die Funktion zur besseren Darstellung des (Auto)Filters ist wunderbar.
Allerdings beginnt die Tabellenüberschrift nicht immer in der gleichen Zeile, sodass die bedingte Formatierung immer manuell angepasst werden muss.
Mir schwebt eine Lösung über InputBox vor, wo man die Zeile der Überschrift auswählt und diese Variable dann automatisch für die Zeile bei der Bedingten Formatierung benutzt.
Leider kann ich so etwas nicht programmieren; ich kann nur ein wenig VBA lesen; ansonsten anwenden 😉
Kannst du eine geänderte Datei zur Verfügung stellen?
Grüße – Erwin
Hallo Gerald,
ich hatte vor ein paar Tagen hier schon einmal einen Kommentar hinterlassen, sehe aber den post leider nicht.
Das Programm ist super, aber m.E. zu unflexibel, da AF_KRIT immer nur fix eine bestimmte Zeile berücksichtigt.
Meine Tabellen beginnen manchmal in Zeile 4, 8, 10, … und dann muss man immer in der bedingten Formatierung rumfummeln.
Kann man das so vereinfachen, dass man über einen Button eine input box aufruft, die Zeile der Überschrift markiert und die Eingabe an das Programm / die Bedingte Formatierung für AF_KRIT übergibt?
Grüße – Erwin
Hallo Gerald,
das einfärben bei aktivem Filter klappt super!
Mein gesetzter Filter ist jetzt über die bedingte Formatierung rot eingefärbt. Ich würde gerne mit einer weiteren Regel die gesamte Zeile orange einfärben wenn irgendwo ein Filter gesetzt ist, ich dachte es geht mit =AF_KRIT(1:1)=WAHR. Das klappt aber leider nicht. Gibt es eine Möglichkeit das umzusetzen?
Vielen Dank!