Autofilter - Grundlagen

Mit dem Autofilter können Listen nach bestimmten Kriterien gefiltert werden, so dass die Zeilen, die diese Kriterien nicht erfüllen, ausgeblendet werden. Sie können in unbegrenzt vielen Spalten auch gleichzeitig filtern. Interessant sind die Möglichkeiten des benutzerdefinierten Filterns sowie des Top-10-Autofilters. Beides wird nachstehend erklärt.




Hier sehen Sie eine Tabelle, die in der Spalte Artikel den Autofilter aufgeklappt hat. Und an diesem Muster werde ich meine nachstehenden Erklärungen aufhängen:



Wie sie sehen, zeigt das Autofilterdropdown die Kriterienauswahl aufsteigend sortiert an. Wobei Zahlen vor Buchstaben angezeigt werden. Diese Reihenfolge kann man nicht ändern.

Ist die Kriterienzahl sehr groß, erscheint rechts eine Bildlaufleiste, über die Sie zum gewünschten Kriterium scrollen können.

In gefilterten Spalten verändert der Autofilterpfeil seine Farbe und wird blau. Daran können wir erkennen, welche Spalten einen aktiven Filter haben. In der Statuszeile unten links wird uns angezeigt, wieviele von wieviel Datensätzen z.Zt. angezeigt werden. Also auch ein Hinweis auf gesetzten Filter; ist die gefilterte Tabelle sehr umfangreich, dann erscheint das jedoch nicht in der Statusleiste. Wenn Sie ohne Prüfung sicher gehen möchten, dass die Liste ungefiltert ist, dann heben Sie alle gesetzten Autofilter gleichzeitig auf.


Der Autofilter hat Beschränkungen, die über Umwege ausgehebelt werden können. Diese Beschränkungen sind:

• es können max. 2 UND/ODER Kriterien pro Spalte gefiltert werden. Zur Problemlösung
• im Autofilterdropdown werden nur bis zu 1.000 verschiedene Kriterien angezeigt. Zur Problemlösung

Eine Möglichkeit, schnell immer nach gleichen einmal gespeicherten Kriterien zu filtern, bietet der Einsatz des Autofilters als benutzerdefinierte Ansicht.


Wissenswertes zum Autofilter:

• Einbau des Autofilters
• Autofilter "normal"
• Autofilter benutzerdefiniert mit einem Kriterium
• Autofilter benutzerdefiniert mit zwei Kriterien UND/ODER
• Autofilter Top 10 - kann noch mehr
• Umgang mit gefilterten Listen: Kopieren und nach unten ausfüllen
• Autofilter aufheben oder entfernen
• Autofilter auswerten mit der Funktion TEILERGEBNIS
• Zeilen der Tabelle abwechselnd einfärben - mit Wirkung auf gefilterte Liste
• Ist das Autofiltern für Excel ein Ereignis, mit dem ein Makro gestartet werden kann?

Wem das nicht reicht, sollte sich den Spezialfilter näher betrachten:

Spezialfilter (1) Duplikate wegfiltern Spezialfilter (2) Filtern nach Kriterien Spezialfilter (3) Filtern mit Funkionen Spezialfilter (4) Filterergebnis in ein anderes Tabellenblatt

Zum Blattanfang

Autofilter "normal"

Klicken Sie in der Spalte, die Sie filtern möchten, auf den Dropdownpfeil und die Kriterien werden Ihnen angezeigt. Klicken Sie das gewünschte Kriterium an, damit startet der Filtervorgang. Alle Zeilen, die dem Kriterium in der Spalte nicht entsprechen, werden ausgeblendet. Sie können nun die Auswahl weiter einschränken, indem Sie auch noch andere Spalten filtern.

Beispiel: Hier wurde die Spalte Artikel nach dem Kriterium "Platte" gefiltert:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 5 Platte lila 70 13 Platte orange 58 37

Zum Blattanfang

Autofilter benutzerdefiniert mit einem Kriterium

Wie Sie oben am aufgeklappten Autofilter erkennen können, bietet Excel auch die Möglichkeit, benutzerdefiniert zu filtern. Diese Möglichkeit wird gewählt, wenn man nicht einen einzigen bestimmten Wert sucht, sondern z.B. Werte die größer sind als ein bestimmter Wert oder Zellen die bestimmten Text enthalten. Wählen Sie im Autofilterdropdown benutzerdefiniert aus und das Dialogfenster benutzerdefinierter Autofilter springt auf. Es werden uns folgende Möglichkeiten dort wieder per Dropdown angeboten:

entspricht ist kleiner als Endet mit entspricht nicht ist kleiner oder gleich Endet nicht mit ist größer als Beginnt mit Enthält ist größer oder gleich Beginnt nicht mit Enthält nicht

Im rechten Dropdownfeld können wir nun den Wert aussuchen, auf den diese Bedingung angewandt werden soll. Sie können auch direkt in dieses Textfeld hineinschreiben und dabei zusätzlich die Platzhalter * und ? verwenden.

Beispiel: Hier wurde die Spalte Preiskategorie nach dem Kriterium "ist größer als: 60" gefiltert:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 4 Becher weiß 75 5 Platte lila 70 8 Becher lila 66 12 Tasse rose 76 14 Tasse gelb 68 37

Beispiel: Hier wurde die Spalte Artikel nach dem Kriterium "entspricht: ?a*" gefiltert, damit sollen alle Artikel ausgewiesen werden, die an 2. Stelle ein "a" haben:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 7 Tasse orange 60 10 Kanne weiß 19 11 Tasse schwarz 36 12 Tasse rose 76 14 Tasse gelb 68 15 Kanne weiß 39 37

Zum Blattanfang

Autofilter benutzerdefiniert mit zwei Kriterien UND/ODER

Bevor Sie hier einsteigen, sollten Sie die Grundlagen zum benutzerdefierten Einsatz des Autofilters kennen.

Sie können eine Spalte nach einem Kriterium ausfiltern oder auch zwei Kriterien, da wiederum können Sie variieren ob Sie nur Ergebnisse zulassen wollen, die beide (UND) oder nur ein Kriterium (ODER) erfüllen.

Ob Sie sich für UND oder ODER entscheiden, teilen Sie Excel durch Klick auf das entsprechende Optionsfeld im Dialogfenster benutzerdefinierter Autofilter mit.

Beispiel: Hier wurde die Spalte Preiskategorie nach dem Kriterium "entspricht: 70" ODER "ist kleiner als: 30" gefiltert:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 5 Platte lila 70 9 Korb gelb 7 10 Kanne weiß 19 37

Hätten wir hier UND gewählt, hätte das Filtern keine Treffer gebracht, weil keine der Zellen beiden Kriterien entpricht.

Zum Blattanfang

Autofilter Top 10 - kann noch mehr

Vorab: Diese Filtermöglichkeit kann nur auf Spalten angewandt werden, die Werte enthalten.

Wir haben im Autofilterdropdown die Möglichkeit, die Top 10 auszuwählen. Dann springt das Dialogfenster Top-10-Autofilter auf.

Diese Bezeichnung ist eher irritierend. Weil viel mehr Möglichkeiten bestehen!

Im Dialogfenster Top-10-Autofilter können wir an 3 Stellen über Dropdown wählen:

• ob wir die obersten oder untersten Werte der Spalte ausfiltern möchten
• auch an die Zahl 10 sind wir nicht gebunden, die können wir erhöhen oder reduzieren
• und wir können wählen, ob uns z.B. die obersten/untersten x Werte oder alle Zeilen, die zu den obersten/untersten x % gehören, angezeigt werden sollen

Beispiel: Hier wurde die Spalte Preiskategorie nach den obersten 5 Werten als Element gefiltert:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 4 Becher weiß 75 5 Platte lila 70 8 Becher lila 66 12 Tasse rose 76 14 Tasse gelb 68 37

Beispiel: Hier wurde die Spalte Preiskategorie nach den obersten 5 % gefiltert:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 12 Tasse rose 76 37

Sie sehen an diesen beiden Beispielen, welch großen Unterschied die Einstellungen im Filterergebnis machen.

Zum Blattanfang

Umgang mit gefilterten Listen: Kopieren und nach unten ausfüllen

Das Kopieren gefilterter Listen ist denkbar einfach; markieren Sie mit der Maus den gewünschten Bereich und fügen ihn an anderer Stelle ein. Es werden nur die im Filter sichtbaren Zeilen berücksichtigt.

Auch wenn Sie Zellen in gefilterten Listen markieren und nach unten ausfüllen, werden nur die sichtbaren Zellen durch den Inhalt der obersten markierten Zelle überschrieben. Die ausgeblendeten Zellen werden hiervon nicht berührt.

Zum Blattanfang

Autofilter aufheben oder entfernen

Autofilter in einer Spalte aufheben: klicken Sie auf den Dropdownpfeil der betreffenden Spalte in der Überschriftenzeile und wählen im Dropdown Alle aus alle gesetzten Autofilter gleichzeitig aufheben: gehen Sie über Menü > Daten > Filter > alle anzeigen Autofilter komplett entfernen: gehen Sie über Menü > Daten > Filter und nehmen den Haken vor Autofilter weg. Sollte die Liste gerade gefiltert sein, werden damit auch alle Zeilen wieder eingeblendet

Zum Blattanfang

Autofilter auswerten mit der Funktion TEILERGEBNIS

Eine wichtige Funktion zur Auswertung von Autofilterergebnissen ist TEILERGEBNIS. Diese Funktion kann man durch Einsatz bestimmter Kennziffern zu bestimmten Auswertungen der eingeblendeten Zeilen (= Kriterientreffer) heranziehen.

Die gebräuchlisten TEILERGEBNIS-Unterfunktionen sind in Verbindung mit Autofilter:

• um die Anzahl der Filterergebnisse festzustellen: =TEILERGEBNIS(3;Bereich)
• um die Summe der Filterergebnisse festzustellen: =TEILERGEBNIS(9;Bereich)

Beispiel: Hier wurde die Spalte Artikel die Anzahl der Filterergebnisse ausgewertet und in der Spalte Preiskategorie die Summe der Filterergebnisse:

A B C D E 1 2 Anzahl: Summe: 3 2 118 4 Artikel Farbe Preis-Kategorie 9 Tasse orange 60 15 Platte orange 58 18 Formeln der Tabelle B3 : =TEILERGEBNIS(3;B5:B17) D3 : =TEILERGEBNIS(9;D5:D17)

Zum Blattanfang

Zeilen der Tabelle abwechselnd einfärben - mit Wirkung auf gefilterte Liste

Über bedingte Formatierung können Sie erreichen, dass die Zeilen der Liste im gefilterten wie im ungefilterten Zustand abwechselnd farbig unterlegt sind. Das hilft, gerade bei großen Listen, die Zeilen besser im Auge halten zu können.

• markieren Sie dazu die Datensätze Ihrer Liste und gehen Sie über Menü > Format > bedingte Formatierung...
• im Dialogfenster wählen Sie im Dropdown unter Bedingung1 Formel ist aus
• und im Textfeld daneben geben Sie folgende Formel ein: =REST(TEILERGEBNIS(3;$B$3:$B3);2)=0
• die in der Formel genannte Zelle muss die aktive Zelle Ihres markierten Datenbereichs sein (sie wird Ihnen im Namenfeld angezeigt) - achten Sie auch auf die korrekte Absolutierung
• wählen Sie über die Schaltfläche Format im Registerblatt Muster die gewünschte Hintergrundfarbe aus, die dann in jeder zweiten sichtbaren Zeile erscheinen wird

Beispiel: Hier sehen Sie unsere Beispieltabelle so aufgerüstet im gefilterten Zustand - die Zeilen abwechselnd gefärbt:

A B C D E 1 2 Artikel Farbe Preis-Kategorie 7 Tasse orange 60 11 Tasse schwarz 36 12 Tasse rose 76 14 Tasse gelb 68 16 Bedingte Formatierungen der Tabelle Zelle Nr.: / Bedingung Format B7 1. / Formel ist =REST(TEILERGEBNIS(3;$B$3:$B7);2)=0 Abc

Zum Blattanfang

Ist das Autofiltern für Excel ein Ereignis, mit dem ein Makro gestartet werden kann?

Diese Frage kann mit einem klaren NEIN beantwortet werden. Sie können aber z.B. durch den Einbau der TEILERGEBNIS-Funktion durch das Filtern eine Neuberechnung auslösen und ein Worksheet_Calculate Makro dadurch starten und auf diesem Weg Ihr Ziel erreichen.