Gegevens filteren in Excel – eenvoudig en compleet

Met de Excel-filteropdracht kun je gegevens in een bereik of tabel filteren, zodat alleen de informatie die je nodig hebt wordt weergegeven en de rest wordt verborgen. Je kunt de ingebouwde operatoren gebruiken om eenvoudig te filteren op getallen, tekst of datums, zoals alle getallen die groter dan of gelijk aan een bepaald getal zijn, tekst die begint, eindigt of een specifiek teken of woord bevat, of alleen rijen tonen waarvan de vervaldatum voor of na een bepaalde datum ligt, enzovoorts. Nadat je gegevens in een bereik of tabel hebt gefilterd, kun je, als de gegevens veranderen, het filter opnieuw toepassen om de nieuwe gegevens te tonen of het filter wissen om alle gegevens weer zichtbaar te maken.

In deze handleiding laten we zien hoe je filters toevoegt, gebruikt en verwijdert in Excel. Daarnaast geven we tips om de filterfunctie uit te breiden voor complexere Excel-vraagstukken.

1. Hoe voeg je een filter toe in Excel

Om gegevens in een bereik of tabel te filteren, moet je eerst een filter toevoegen aan je gegevens. In dit onderdeel worden3 manieren beschreven om een filter toe te voegen in Excel.

1.1 Filteropdracht op het tabblad Gegevens

Selecteer een willekeurige cel in het bereik of de tabel waar je een filter wilt toevoegen, klik op "Gegevens" > "Filter".

1.2 Filteropdracht op het tabblad Start

Selecteer een willekeurige cel in het bereik of de tabel waar je een filter wilt toevoegen, klik op "Start" > "Sorteren & Filteren" > "Filter".

1.3 Filter toevoegen met sneltoets

Selecteer een willekeurige cel in het bereik of de tabel waar je een filter wilt toevoegen en druk vervolgens op de toetsen "Ctrl" + "Shift" + "L".

Na het uitvoeren van een van bovenstaande handelingen zie je dat er keuzepijlen zijn toegevoegd aan de kolomkoppen van de geselecteerde cellen.

2. Hoe pas je een filter toe in Excel (één of meerdere criteria)

Nadat je het filter hebt toegevoegd, moet je het handmatig toepassen. In dit onderdeel laten we zien hoe je een filter toepast op één of meerdere kolommen in Excel.

2.1 Filter toepassen op één kolom (één criterium)

Als je het filter alleen op één kolom wilt toepassen, bijvoorbeeld om gegevens in kolom C te filteren zoals in de onderstaande schermafbeelding, ga dan naar die kolom en volg deze stappen.

1. 1) Klik op de keuzepijl in de kolomkop.
2. 2) Geef een filtervoorwaarde op naar wens.
3. 3) Klik op de knop "OK" om het filteren te starten. Zie schermafbeelding:
   

Nu is het filter toegepast op kolom C. Alle gegevens die aan de filtercriteria voldoen worden weergegeven, de rest wordt verborgen.

Na het toepassen van het filter zie je dat de keuzepijl verandert in een filterpictogram .

Handig is dat wanneer je de cursor boven het filterpictogram houdt, de door jou opgegeven filtercriteria als schermtip worden weergegeven, zoals in de onderstaande schermafbeelding. Dus als je de criteria bent vergeten, houd dan gewoon de cursor boven het filterpictogram.

2.2 Filter toepassen met meerdere criteria op meerdere kolommen (meerdere criteria)

2.2.1 Filter toepassen met meerdere criteria op meerdere kolommen één voor één

Wil je filteren op meerdere kolommen met meerdere criteria, herhaal dan bovenstaande methode voor elke kolom afzonderlijk.

Na het toepassen van het filter op meerdere kolommen zie je dat de keuzepijlen in de gefilterde kolommen zijn veranderd in filterpictogrammen.

2.2.2 Gelijktijdig filter toepassen met meerdere criteria op meerdere kolommen

Met bovenstaande methode moet je het filter per kolom toepassen, en bovendien ondersteunt deze methode alleen "EN"-criteria. Hier laten we methoden zien waarmee je niet alleen gelijktijdig op meerdere kolommen kunt filteren, maar ook zowel "EN"- als "OF"-criteria kunt toepassen.

Stel, je hebt een gegevenslijst zoals in de onderstaande schermafbeelding, en je wilt gegevens filteren uit meerdere kolommen op basis van meerdere criteria: "Product = AAA-1" en "Order >80", of "Totale prijs >10000". Probeer een van de volgende methoden om dit te bereiken.

2.2.2.1 Filter toepassen op meerdere kolommen met de functie Geavanceerd filter

Met de functie Geavanceerd filter kun je dit probleem oplossen. Volg hiervoor stap voor stap de onderstaande instructies.

1. Maak eerst de criteria aan in het werkblad zoals in de onderstaande schermafbeelding.

Opmerking: Voor EN-criteria plaats je de criteriumwaarden in verschillende cellen op dezelfde rij. Plaats de OF-criteriumwaarde op een andere rij.

2. Klik op "Gegevens" > "Geavanceerd" om de functie "Geavanceerd filter" te openen.

3. Stel in het dialoogvenster "Geavanceerd filter" het volgende in.

Nu worden de kolommen gelijktijdig gefilterd op basis van de opgegeven criteria, zoals in de onderstaande schermafbeelding.

2.2.2.2 Eenvoudig filter toepassen op meerdere kolommen met een krachtige tool

Omdat het beheren van "EN"- en "OF"-criteria in bovenstaande methode niet eenvoudig is, raden we de functie "Superfilter" van Kutools voor Excel aan. Met deze functie kun je eenvoudig filteren op meerdere kolommen met zowel EN- als OF-criteria in Excel.

1. Na installatie van Kutools voor Excel, klik op "Kutools Plus" > "Superfilter".

Het "Superfilter"-paneel verschijnt standaard aan de rechterkant van het werkblad.

Standaard worden er twee lege criteriagroepen toegevoegd met een OF-relatie ertussen in het "Superfilter"-paneel. De relatie tussen de criteria binnen dezelfde groep is EN. Je kunt de relatie tussen verschillende groepen aanpassen naar wens.

2. Stel in het "Superfilter"-paneel de filtercriteria als volgt in.

2.1) Vink het vakje "Gespecificeerd" aan, klik op de knop om het oorspronkelijke bereik of de tabel te selecteren die je wilt filteren;

2.2) Kies in de keuzelijst "Relatie" de optie "Of";

3.3) Klik op de eerste lege regel in de eerste groep en geef vervolgens de criteria op naar wens;

Tips: De eerste keuzelijst is voor kolomkoppen, de tweede voor filtertypen (je kunt kiezen uit "Tekst", "Getal", "Datum", "Jaar", "Tekstformaat" enz.), de derde voor criteriatypen en het laatste tekstvak voor de criteriumwaarde.

Zoals in het eerder genoemde voorbeeld kies ik hier "Product" > "Tekst" > "Is gelijk aan" uit de drie keuzelijsten en typ vervolgens "AAA-1" in het tekstvak. Zie schermafbeelding:

2.4) Maak de overige criteria aan en plaats de "Of"-criteria in een nieuwe groep. Zoals in de onderstaande schermafbeelding zijn alle criteria aangemaakt. Je kunt het lege criterium uit de groepen verwijderen.

2.5) Klik op de knop "Filteren" om het filteren te starten.

Nu worden alleen de overeenkomende gegevens weergegeven in het oorspronkelijke gegevensbereik en de rest wordt verborgen. Zie schermafbeelding:

Tips: Met deze handige functie kun je meer criteria aan een groep toevoegen, extra groepen aanmaken, het huidige filter opslaan als scenario voor toekomstig gebruik, enzovoorts. Het is een onmisbare tool die veel werktijd bespaart en de efficiëntie verhoogt.

Klik voor meer informatie over deze functie.

3. Hoe gebruik je het filter in Excel

In dit onderdeel leer je hoe je de filteropdracht gebruikt om verschillende soorten gegevens te filteren, zoals tekst, getallen, datums en opmaak.

3.1 Tekstwaarden filteren

3.1.1Tekstcellen filteren met specifieke criteria (begint met, eindigt met, bevat, enzovoorts)

De ingebouwde filteroperator – "Tekstfilters" biedt veel handige criteria om eenvoudig tekst te filteren. Stel dat je cellen wilt filteren die beginnen met een specifiek teken, zoals J, volg dan deze stappen.

1. Voeg een filter toe aan de kolomkop van het oorspronkelijke gegevensbereik. Klik voor instructies over het toevoegen van een filter aan de kolomkop.

2. Klik op de keuzepijl in de kopcel om het filtermenu uit te vouwen.

3. Klik op "Tekstfilters" > "Begint met".

4. Voer in het dialoogvenster "Aangepast automatisch filter" het specifieke teken in (hier typ ik een J) in het tekstvak en klik vervolgens op "OK".

Tips: Je kunt indien nodig nog een "En"- of "Of"-criterium toevoegen.

Nu worden alle cellen die beginnen met de letter J weergegeven in kolom D, zoals in de onderstaande schermafbeelding.

3.1.2 Filteren met hoofdlettergevoeligheid

Het lijkt eenvoudig om tekstcellen te filteren op basis van specifieke criteria met de ingebouwde filteroperator. Maar omdat de Filter-functie geen hoofdlettergevoeligheid ondersteunt, hoe kun je dan toch hoofdlettergevoelig filteren in Excel? In dit onderdeel laten we methoden zien om dit te bereiken.

3.1.2.1 Bepaalde tekst hoofdlettergevoelig filteren met formule en de filteropdracht

Stel dat je alle hoofdletters van een bepaalde tekst, zoals “TEXT TOOLS”, in kolom B wilt filteren, volg dan deze stappen.

1. Maak een hulpkolom naast het oorspronkelijke gegevensbereik (hier kies ik kolom D als hulpkolom). Voer de onderstaande formule in de tweede cel in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om de overige resultaten te krijgen.

=EXACT(B2,UPPER(B2))

Opmerking: Deze formule helpt om hoofdletters en kleine letters te onderscheiden. Als een cel alleen hoofdletters bevat, is het resultaat "TRUE"; anders krijg je "FALSE".

2. Selecteer kolom B en D (kolom C wordt ook geselecteerd, dat is niet erg), klik op "Gegevens" > "Filter" om filters toe te voegen.

3. Ga naar kolom B (de kolom met de teksten die je wilt filteren) en stel het volgende in.

Nu worden alleen hoofdletters en kleine letters van “text tools” weergegeven in kolom B.

4. Klik op de keuzepijl in kolom D, vink het vakje "Alles selecteren" uit, vink het vakje naast het item "TRUE" aan en klik vervolgens op de knop "OK".

Nu worden alle hoofdletters van de tekst “text tools” in kolom B gefilterd, zoals in de onderstaande schermafbeelding.

3.1.2.2 Eenvoudig cellen hoofdlettergevoelig filteren met een krachtige tool

Wil je alleen alle hoofdletters of kleine letters in een kolom filteren, dan raden we de functie "Speciaal filter" van Kutools voor Excel aan. Met deze functie kun je alle hoofdletters of kleine letters eenvoudig filteren met slechts enkele klikken.

1. Selecteer het kolombereik waarin je teksten wilt filteren en klik op "Kutools Plus" > "Speciaal filter" > "Speciaal filter".

2. Stel in het dialoogvenster "Speciaal filter" het volgende in.

3. Vervolgens verschijnt er een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel cellen zijn gevonden en gefilterd zullen worden. Klik op de knop "OK".

Nu zijn alle cellen met hoofdletters of kleine letters direct gefilterd, zoals in de onderstaande schermafbeelding.

Klik voor meer informatie over deze functie.

3.1.3 Filteren op tekstlengte

Wil je cellen filteren op tekstlengte, bijvoorbeeld cellen met een tekstlengte van precies10 tekens, hoe doe je dat? De onderstaande drie methoden kunnen je hierbij helpen.

3.1.3.1 Cellen filteren op tekstlengte met de filteropdracht

De filteropdracht heeft een ingebouwde operator om dit probleem op te lossen. Volg hiervoor deze stappen.

1. Selecteer het bereik van cellen dat je wilt filteren (hier selecteer ik B1:B27) en voeg een filter toe aan dit kolombereik door te klikken op "Gegevens" > "Filter".

2. Klik op de keuzepijl in de kolomkop en klik vervolgens op "Tekstfilters" > "Aangepast filter". Zie schermafbeelding:

3. Selecteer in het dialoogvenster "Aangepast automatisch filter" als criterium "is gelijk aan", typ10 vraagtekens (?) als patroon in het tekstvak en klik vervolgens op de knop "OK".

Tips: Deze10 vraagtekens geven aan dat het filter zoekt naar tekstreeksen met een lengte van10.

Nu worden alle cellen waarvan de tekstlengte10 is (inclusief spaties) direct gefilterd.

3.1.3.2 Cellen filteren op tekstlengte met formule en de filteropdracht

Daarnaast kun je de functie LENGTE gebruiken om de tekstlengte van elke cel te berekenen en vervolgens de filteropdracht toepassen om de gewenste cellen op basis van de berekende waarde te filteren.

1. Maak een hulpkolom naast het oorspronkelijke gegevensbereik. Voer de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om de overige resultaten te krijgen.

=LEN(B2)

Nu zie je de tekstlengte van elke cel in de opgegeven kolom.

2. Selecteer de hulpkolom (inclusief koptekst), klik op "Gegevens" > "Filter" om een filter toe te voegen.

3. Klik op de keuzepijl, vink het vakje "Alles selecteren" uit om alles te deselecteren, vink alleen het vakje naast het getal10 aan en klik tot slot op de knop "OK".

Nu worden alle cellen waarvan de tekstlengte10 is (inclusief spaties) direct gefilterd.

3.1.3.3 Eenvoudig cellen filteren op tekstlengte met een krachtige tool

We raden de functie "Speciaal filter" van Kutools voor Excel aan om eenvoudig cellen op tekstlengte te filteren in Excel.

1. Selecteer het kolombereik waarin je wilt filteren op tekstlengte, klik op "Kutools Plus" > "Speciaal filter" > "Speciaal filter". Zie schermafbeelding:

2. Stel in het dialoogvenster "Speciaal filter" het volgende in.

3. Er verschijnt een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel cellen zijn gevonden en gefilterd zullen worden. Klik op "OK" om door te gaan.

Nu zijn alle cellen met een tekstlengte van precies10 gefilterd, zoals in de onderstaande schermafbeelding.

3.2 Numerieke waarden filteren

In Excel is het ook heel eenvoudig om getallen te filteren met de opdracht Nummerfilters.

Stel dat je cellen wilt filteren met getallen tussen15000 en20000 in een kolom (zoals kolom C in de onderstaande schermafbeelding), volg dan deze stappen.

1. Selecteer het kolombereik met de getallen die je wilt filteren, klik op "Gegevens" > "Filter" om een filter toe te voegen.

2. Stel na het toevoegen van het filter het volgende in.

Tips: Omdat ik cellen wil filteren met getallen tussen15000 en20000, voer ik hier15000 en20000 apart in de tekstvakken in.

Nu zijn de cellen met getallen tussen15000 en20000 gefilterd, zoals in de onderstaande schermafbeelding.

3.3 Datums filteren

Standaard biedt de ingebouwde functie Datumfilters veel gangbare criteria voor het filteren van datums. Zoals je ziet, is er geen ingebouwde optie om te filteren op dag van de week, weekenden of werkdagen. In dit onderdeel leer je hoe je deze bewerkingen uitvoert.

3.3.1 Datums filteren op dag van de week of weekenden

Stel dat je een gegevenslijst hebt zoals in de onderstaande schermafbeelding en je wilt datums filteren op dag van de week of weekenden, pas dan een van de onderstaande methoden toe.

3.3.1.1 Datums filteren op dag van de week of weekenden met formule en de filteropdracht

In dit onderdeel gebruik je de functie WEEKDAG om de dag van de week van elke datum te berekenen en vervolgens het filter toe te passen om de gewenste dag van de week of het weekend te filteren.

1. Voer in een lege cel (D2 in dit geval) de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om deze formule toe te passen.

=WEEKDAY(A2)

Tips:

Opmerking: Zoals je ziet, geeft de formule getallen van "1" tot "7" terug, wat de dag van de week aangeeft van "Zondag" tot "Zaterdag" (1 is voor Zondag,7 is voor Zaterdag).

2. Selecteer alle formule-uitkomsten (inclusief de kopcel), klik op "Gegevens" > "Filter".

3. Klik op de keuzepijl en vink het vakje "Alles selecteren" uit.

Nu zijn alle cellen van het weekend of een bepaalde dag van de week gefilterd. Zie schermafbeelding:

3.3.1.2 Eenvoudig datums filteren op dag van de week of weekenden met een krachtige tool

Als bovenstaande methode niet handig is, raden we de functie "Speciaal filter" van Kutools voor Excel aan. Hiermee kun je eenvoudig cellen filteren op elke dag van de week met slechts enkele klikken.

1. Selecteer de cellen met de datums die je wilt filteren op een bepaalde dag van de week.

2. Klik op "Kutools Plus" > "Speciaal filter" > "Speciaal filter".

3. Stel in het dialoogvenster "Speciaal filter" het volgende in.

4. Vervolgens verschijnt er een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel cellen zijn gevonden en gefilterd zullen worden. Klik op "OK" om door te gaan.

Nu zijn alle cellen van het weekend of een bepaalde dag van de week gefilterd.

3.3.2 Cellen filteren op werkdagen met een krachtige tool

Naast het filteren op dag van de week of weekenden kan de functie "Speciaal filter" van Kutools voor Excel ook helpen om cellen op werkdagen te filteren.

1. Voer dezelfde stappen als hierboven uit om de functie "Speciaal filter" te activeren.

2. Stel in het dialoogvenster "Speciaal filter" het volgende in.

3. Vervolgens verschijnt er een dialoogvenster van Kutools voor Excel. Klik op "OK" om door te gaan.

Nu zijn alle cellen van werkdagen gefilterd.

3.4 Opmaak filteren

Normaal gesproken ondersteunt Excel het filteren van gegevens op basis van visuele criteria zoals letterkleur, celkleur of pictogramsets, zoals in de onderstaande schermafbeelding.

Wil je echter filteren op andere visuele criteria zoals lettertype (vet, cursief), lettereffecten (doorhalen) of speciale cellen (met formules), dan biedt Excel daar geen standaardoplossing voor. In dit onderdeel vind je methoden om deze problemen op te lossen.

3.4.1 Filteren op vet/cursief opgemaakte tekst

Stel dat je gegevens wilt filteren op vet of cursief opgemaakte tekst, zoals in de onderstaande schermafbeelding, dan kun je de volgende methoden gebruiken. Volg deze stappen.

3.4.1.1 Vet/cursief opgemaakte tekst filteren met formule en de filteropdracht

De combinatie van de Get.Cell-formule en de filteropdracht kan helpen om vet opgemaakte tekst in een kolombereik te filteren.

1. Klik op "Formules" > "Naam definiëren".

2. In het dialoogvenster "Nieuwe naam" moet je het volgende doen:

=GET.CELL(20,$B2)

=GET.CELL(21,$B2)

Formulesyntaxis:

=GET.CELL(type_num, reference)

Formuleargumenten

3. Selecteer een lege cel op dezelfde rij als B2, voer de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om deze formule toe te passen.

=Filter_Bold_Cells

4. Selecteer alle resultaatcellen (inclusief koptekst), klik op "Gegevens" > "Filter".

5. Klik op de keuzepijl, vink alleen het vakje naast de optie "TRUE" aan en klik vervolgens op "OK".

Nu zijn alle vet of cursief opgemaakte tekstcellen gefilterd. Zie schermafbeelding:

3.4.1.2 Vet of cursief opgemaakte tekst filteren met de opdrachten Zoeken en Vervangen en Filter

Je kunt ook de combinatie van Zoeken en Vervangen en de filteropdracht gebruiken om dit te bereiken.

1. Selecteer het kolombereik met de vet of cursief opgemaakte tekstcellen die je wilt filteren en druk op "Ctrl" + "F".

2. Stel in het dialoogvenster "Zoeken en Vervangen" het volgende in.

3. Nu zijn alle vet of cursief opgemaakte tekstcellen geselecteerd in het oorspronkelijke bereik. Klik op "Start" > "Opvulkleur" en kies een vulkleur voor de geselecteerde cellen.

4. Selecteer opnieuw het hele kolombereik, klik op "Gegevens" > "Filter" om een filter toe te voegen.

5. Klik op de keuzepijl, selecteer "Filteren op kleur" en klik vervolgens op de zojuist opgegeven vulkleur onder "Filteren op celkleur". Zie schermafbeelding:

Nu zijn alle vet of cursief opgemaakte tekstcellen gefilterd.

3.4.1.3 Eenvoudig vet of cursief opgemaakte tekst filteren met een krachtige tool

Zoals eerder genoemd kan de functie "Speciaal filter" van Kutools voor Excel eenvoudig helpen bij hoofdlettergevoelig filteren, filteren op tekstlengte en filteren van datums. Hier laten we zien hoe je deze functie gebruikt om vet of cursief opgemaakte tekstcellen te filteren in Excel.

1. Selecteer het kolombereik (inclusief koptekst) met de vet of cursief opgemaakte cellen die je wilt filteren.

2. Klik op "Kutools Plus" > "Speciaal filter" > "Filter vet / Filter cursief". Zie schermafbeelding:

3. Klik op "OK" in het pop-upvenster van Kutools voor Excel om door te gaan (dit venster geeft aan hoeveel cellen aan de criteria voldoen).

Nu zijn alle vet of cursief opgemaakte tekstcellen gefilterd.

3.4.2 Filteren op doorgehaalde tekst

Stel dat je een lijst hebt ontvangen waarin personen zijn doorgestreept, en je wilt alle cellen met doorhaling filteren. De onderstaande methoden kunnen je hierbij helpen.

3.4.2.1 Doorgehaalde tekst filteren met gebruikersgedefinieerde functie en de filteropdracht

Je kunt een gebruikersgedefinieerde functie gebruiken om cellen met doorgehaalde tekst te identificeren en vervolgens de filteropdracht toepassen om alle doorgehaalde cellen te filteren op basis van de resultaten.

1. Druk op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module". Kopieer vervolgens de onderstaande VBA-code in het codevenster.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Druk op "Alt" + "Q" om het venster "Microsoft Visual Basic for Applications" te sluiten.

4. Selecteer een lege cel (deze cel moet op dezelfde rij staan als de cel die je wilt berekenen), voer de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om deze formule toe te passen.

=HasStrike(B2)

Opmerking: Als de corresponderende cel een doorgehaald lettertype heeft, geeft het "TRUE" terug, anders "FALSE".

5. Selecteer alle resultaatcellen (inclusief de kopcel), klik op "Gegevens" > "Filter".

6. Klik vervolgens op de keuzepijl > vink alleen het vakje naast de optie "TRUE" aan > klik op "OK". Zie schermafbeelding:

Nu zie je dat alle cellen met doorgehaalde opmaak zijn gefilterd.

3.4.2.2 Eenvoudig doorgehaalde tekst filteren met een krachtige tool

Met de functie "Speciaal filter" van Kutools voor Excel kun je alle cellen met doorgehaalde opmaak direct filteren met slechts enkele klikken.

1. Selecteer het kolombereik waarin je alle cellen met doorgehaalde opmaak wilt filteren, klik op "Kutools Plus" > "Speciaal filter" > "Filter doorhalen".

2. Vervolgens verschijnt er een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel cellen voldoen, klik op "OK" om door te gaan.

Nu zijn alle cellen met doorgehaalde opmaak gefilterd, zoals in de onderstaande schermafbeelding.

3.4.3 Filteren op lettertype- of achtergrondkleur

Zoals aan het begin van dit onderdeel over opmaakfilteren genoemd, ondersteunt Excel het filteren van gegevens op basis van visuele criteria zoals letterkleur, celkleur of pictogramsets met de ingebouwde functie. In dit onderdeel laten we zien hoe je de functie "Filteren op kleur" gebruikt om cellen te filteren op lettertype- of achtergrondkleur. Daarnaast raden we een handige externe functie aan om dit probleem op te lossen.

3.4.3.1 Filteren op één lettertype- of achtergrondkleur met de filteropdracht

Je kunt direct de functie Filteren op kleur van de filteropdracht gebruiken om cellen te filteren op een specifieke lettertype- of achtergrondkleur in Excel.

1. Selecteer het kolombereik waarin je cellen wilt filteren op lettertype- of achtergrondkleur en klik op "Gegevens" > "Filter".

2. Klik op de keuzepijl > "Filteren op kleur". Nu zie je alle celkleuren en letterkleuren van het huidige kolombereik. Door op een celkleur of letterkleur te klikken, worden alle cellen op basis van de geselecteerde kleur gefilterd.

3.4.3.2 Filteren op meerdere achtergrondkleuren met gebruikersgedefinieerde functie en de filteropdracht

Wil je cellen filteren op meerdere achtergrondkleuren, gebruik dan de onderstaande methode.

Stel dat je alle cellen met oranje en blauwe achtergrondkleuren in kolom B wilt filteren, zoals in de onderstaande schermafbeelding. Je moet eerst de kleurcode van deze cellen berekenen.

1. Druk gelijktijdig op "Alt" + "F11".

2. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module". Kopieer vervolgens de onderstaande VBA-code in het codevenster.

VBA-code: Haal de kleurcode van de celachtergrond op

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Druk op "Alt" + "Q" om het venster "Microsoft Visual Basic for Applications" te sluiten.

4. Typ in een nieuwe kolom een koptekst in de eerste cel (deze cel moet op dezelfde rij staan als de koptekst van het oorspronkelijke bereik).

5. Selecteer een lege cel naast de kopcel (hier kies ik E2), voer de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om deze formule toe te passen.

=GetColor(B2)

Opmerking: Als de cel geen opvulkleur heeft, geeft het -4142 terug.

6. Selecteer de cellen van de hulpkolom (inclusief koptekst), klik op "Gegevens" > "Filter" om een filter toe te voegen aan de kolom.

7. Klik op de keuzepijl om de keuzelijst te openen en stel het volgende in.

Nu zijn de cellen gefilterd op de opgegeven achtergrondkleuren, zoals in de onderstaande schermafbeelding.

3.4.3.3 Eenvoudig filteren op lettertype- of achtergrondkleur met een krachtige tool

Het is zonder twijfel eenvoudig om de ingebouwde functie Filteren op kleur te gebruiken om cellen te filteren op lettertype- of achtergrondkleur. Het nadeel is echter dat de keuzelijst het oorspronkelijke gegevensbereik bedekt, waardoor je de gegevens niet altijd kunt bekijken om de juiste kleur te kiezen. Om dit te voorkomen, raden we de functie "Speciaal filter" van Kutools voor Excel aan.

1. Selecteer het kolombereik waarin je cellen wilt filteren op lettertype- of achtergrondkleur en klik op "Kutools Plus" > "Speciaal filter" > "Speciaal filter".

2. Stel in het dialoogvenster "Speciaal filter" het volgende in.

2.1) Het celadres van het geselecteerde kolombereik wordt weergegeven in het vak "Bereik";

2.2) Kies in het gedeelte "Filterregel" de optie "Opmaak";

2.3) Kies "Achtergrondkleur" of "Letterkleur" uit de keuzelijst;

2.4) Klik op het pipet-icoon ;

2.5) Selecteer in het geopende dialoogvenster "Filter speciale cellen" een cel met de letterkleur of achtergrondkleur waarop je wilt filteren en klik op "OK".

2.6) Wanneer je terugkeert naar het dialoogvenster "Speciaal filter", wordt de letterkleur of achtergrondkleur van de geselecteerde cel weergegeven in het tekstvak (je kunt de kleur aanpassen indien nodig). Klik op de knop "OK" om het filteren te starten.

Nu zijn alle cellen met de opgegeven letterkleur of achtergrondkleur in het geselecteerde bereik gefilterd.

3.4.4 Formulecellen filteren

Als je een lange lijst met gegevens hebt die zowel werkelijke waarden als formules bevat en je wilt alleen de formulecellen filteren, wat kun je dan doen? In dit onderdeel vind je twee methoden om dit te bereiken.

3.4.4.1 Formulecellen filteren met gebruikersgedefinieerde functie en de filteropdracht

Je moet eerst alle formulecellen in de lijst vinden met een gebruikersgedefinieerde functie en vervolgens de filteropdracht toepassen om de formulecellen te filteren op basis van de resultaten.

1. Druk op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code in het codevenster.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Druk op "Alt" + "Q" om het venster "Microsoft Visual Basic for Applications" te sluiten.

4. Selecteer een lege cel, deze cel moet op dezelfde rij staan als de cel die je wilt controleren of het een formulecel is. Voer de onderstaande formule in en druk op "Enter". Selecteer de resultaatcel en sleep de "AutoFill-hendel" naar beneden om deze formule toe te passen.

=HasFormula(C2)

Zoals in de bovenstaande schermafbeelding zijn de resultaten "FALSE" en "TRUE"; dit geeft aan dat als de corresponderende cel een formulecel is, het TRUE teruggeeft, anders FALSE.

5. Selecteer de resultaatcellen (inclusief de kopcel), klik op "Gegevens" > "Filter" om een filter toe te voegen.

6. Klik op de keuzepijl, vink alleen het vakje naast de optie "TRUE" aan en klik vervolgens op "OK".

Nu zie je dat alle formulecellen zijn gefilterd.

3.4.4.2 Eenvoudig formulecellen filteren met een krachtige tool

Hier laten we zien hoe de functie "Speciaal filter" van Kutools voor Excel je eenvoudig helpt formulecellen in een lijst te filteren met slechts enkele klikken.

1. Selecteer de lijst waarin je alle formulecellen wilt filteren, klik op "Kutools Plus" > "Speciaal filter" > "Filter formule".

2. Er verschijnt een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel cellen voldoen, klik op "OK" om door te gaan.

Nu zijn alle formulecellen gefilterd, zoals in de onderstaande schermafbeelding.

Daarnaast kun je met de functie "Speciaal filter" van Kutools voor Excel eenvoudig cellen filteren met andere opmaak, zoals:

Alle cellen met opmerkingen filteren, klik voor meer informatie...

Alle samengevoegde cellen filteren op basis van een bepaalde waarde, klik voor meer informatie...

3.5 Filteren met jokerteken

Soms weet je het exacte zoekcriterium niet bij het filteren. In dat geval raden we aan om jokertekens te gebruiken.

Er zijn slechts3 jokertekens in Excel:

Laten we bekijken hoe je jokertekens gebruikt bij het filteren.

Stel dat je alle cellen wilt filteren die eindigen op Market in kolom B, zoals in de onderstaande schermafbeelding. Volg dan deze stappen.

1. Maak eerst een criteriabereik aan. Voer een koptekst in die overeenkomt met de oorspronkelijke kolomkop en typ het filtercriterium in de cel eronder. Zie schermafbeelding:

2. Klik op "Gegevens" > "Geavanceerd".

3. Stel in het geopende dialoogvenster "Geavanceerd filter" het volgende in.

Nu zijn alle cellen die eindigen op Market gefilterd. Zie schermafbeelding:

Het gebruik van * en ~ jokertekens bij het filteren is hetzelfde als hierboven beschreven.

3.6 Filteren met ingebouwd zoekvak

Als je Excel2010 of een latere versie gebruikt, zie je dat er een ingebouwd zoekvak is in het Excel-filter. In dit onderdeel laten we zien hoe je dit zoekvak gebruikt om gegevens te filteren in Excel.

Zoals in de onderstaande schermafbeelding wil je alle cellen filteren die “Market” bevatten. Het zoekvak helpt je dit eenvoudig te doen.

1. Selecteer het kolombereik waarin je gegevens wilt filteren, klik op "Gegevens" > "Filter" om een filter toe te voegen.

2. Klik op de keuzepijl, voer “Market” in het zoekvak in en klik op "OK".

Je ziet dat alle overeenkomende teksten in realtime worden weergegeven terwijl je in het zoekvak typt.

Nu zijn alle cellen die “Market” bevatten gefilterd, zoals in de onderstaande schermafbeelding.

4. Alleen zichtbare gegevens kopiëren (negeer verborgen of gefilterde data)

Alleen zichtbare gegevens kopiëren met sneltoetsen

Je kunt sneltoetsen gebruiken om alleen de zichtbare cellen te selecteren en deze vervolgens handmatig te kopiëren en te plakken op de gewenste locatie.

1. Selecteer het bereik waarvan je alleen de zichtbare cellen wilt kopiëren. Zowel zichtbare als verborgen cellen worden in deze stap geselecteerd.

2. Druk gelijktijdig op de toetsen "Alt" en ";". Nu zijn alleen de zichtbare cellen geselecteerd.

3. Druk op "Ctrl" + "C" om de geselecteerde cellen te kopiëren en vervolgens op "Ctrl" + "V" om ze te plakken.

Eenvoudig alleen zichtbare gegevens kopiëren met een krachtige tool

Hier stellen we de functie "Plakken naar zichtbaar" van Kutools voor Excel voor, waarmee je eenvoudig alleen zichtbare gegevens in Excel kunt kopiëren. Met deze functie kun je waarden kopiëren en plakken naar alleen zichtbare cellen in een gefilterd bereik.

1. Selecteer het gefilterde bereik waarvan je alleen de zichtbare cellen wilt kopiëren, klik op "Kutools" > "Bereik" > "Plakken naar zichtbaar" > "Alles / Alleen waarden plakken".

Voor formulecellen kies je "Alles" om zowel het resultaat als de formules te kopiëren, kies "Alleen waarden plakken" om alleen de werkelijke waarden te kopiëren.

2. Kies in het pop-upvenster "Plakken naar zichtbaar bereik" een lege cel om de gekopieerde cellen te plakken en klik op "OK".

Nu zijn alleen de zichtbare cellen in het geselecteerde gefilterde bereik gekopieerd en geplakt naar een nieuwe locatie.

Opmerking: Als het bestemmingsbereik is gefilterd, worden de geselecteerde waarden alleen naar de zichtbare cellen geplakt.

5. Verborgen of zichtbare rijen verwijderen na filteren

Bij een gefilterde lijst wil je mogelijk de verborgen rijen verwijderen om alleen de zichtbare gegevens te behouden. In dit onderdeel leer je drie methoden om verborgen of zichtbare rijen in een gefilterde lijst in Excel te verwijderen.

Alle verborgen rijen uit het huidige werkblad verwijderen met VBA-code

De onderstaande VBA-code helpt om alle verborgen rijen uit het huidige werkblad in Excel te verwijderen.

Opmerking: Deze VBA verwijdert niet alleen de verborgen rijen in de gefilterde lijst, maar ook de rijen die je handmatig hebt verborgen.

1. Ga in het werkblad met verborgen rijen die je wilt verwijderen, druk gelijktijdig op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code in het modulevenster.

VBA-code: Verwijder alle verborgen rijen uit het huidige werkblad

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Druk op de toets "F5" om de code uit te voeren.

4. Vervolgens verschijnt er een dialoogvenster van Kutools voor Excel waarin wordt aangegeven hoeveel verborgen rijen zijn verwijderd. Klik op "OK" om het te sluiten.

Nu zijn alle verborgen rijen (zowel automatisch als handmatig verborgen) verwijderd.

Zichtbare rijen verwijderen na filteren met Ga naar-functie

Wil je alleen zichtbare rijen in een bepaald bereik verwijderen, dan kan de functie "Ga naar" je helpen.

1. Selecteer het gefilterde bereik waarvan je de zichtbare rijen wilt verwijderen, druk op de toets "F5" om het dialoogvenster "Ga naar" te openen.

2. Klik in het dialoogvenster "Ga naar" op de knop "Speciaal".

3. Selecteer in het dialoogvenster "Ga naar speciaal" de optie "Alleen zichtbare cellen" en klik op de knop "OK".

4. Nu zijn alle zichtbare cellen geselecteerd. Klik met de rechtermuisknop op het geselecteerde bereik en klik op "Rij verwijderen" in het contextmenu.

Nu zijn alle zichtbare cellen verwijderd.

Eenvoudig verborgen of zichtbare rijen verwijderen na filteren met een krachtige tool

Bovenstaande methoden zijn omslachtig en tijdrovend. Daarom raden we de functie "Verborgen (zichtbare) rijen & kolommen verwijderen" van Kutools voor Excel aan. Hiermee kun je eenvoudig verborgen of zichtbare rijen verwijderen, niet alleen in een geselecteerd bereik, maar ook in het huidige werkblad, meerdere geselecteerde werkbladen of het hele werkboek. Volg deze stappen.

1. Selecteer een bereik waarvan je alle verborgen of zichtbare rijen wilt verwijderen.

Opmerkingen:

2. Klik op "Kutools" > "Verwijderen" > "Verborgen (zichtbare) rijen & kolommen verwijderen".

3. Stel in het dialoogvenster "Verborgen (zichtbare) rijen & kolommen verwijderen" het volgende in.

4. Nu zijn alle zichtbare of verborgen rijen in één keer verwijderd. Er verschijnt een dialoogvenster waarin het aantal verwijderde rijen wordt weergegeven. Klik op "OK" om de hele bewerking af te ronden.

6. Filteren over meerdere werkbladen

Normaal gesproken is het eenvoudig om gegevens in één werkblad te filteren. In dit onderdeel leer je hoe je gegevens filtert met dezelfde criteria over meerdere werkbladen met een vergelijkbare datastructuur.

Stel dat een werkmap drie werkbladen bevat, zoals in de onderstaande schermafbeelding. Nu wil je gegevens filteren over deze drie werkbladen tegelijk met hetzelfde criterium “Product = KTE”. De onderstaande VBA-code kan je hierbij helpen.

1. Druk op "Alt" + "F11" om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik in het venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code in het modulevenster.

VBA-code: Gegevens filteren over meerdere werkbladen tegelijk

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Opmerking: De regel “Xws.Range(“A1”.AutoFilter1, “=KTE”)” in de code geeft aan dat je gegevens in kolom A filtert met het criterium =KTE, en het getal1 is het kolomnummer van kolom A. Je kunt deze aanpassen naar wens. Wil je bijvoorbeeld alle getallen filteren die groter zijn dan500 in kolom B, wijzig deze regel dan in “Xws.Range(“B1”.AutoFilter2, “>500”)”.

3. Druk op de toets "F5" om de code uit te voeren.

De opgegeven kolommen zijn nu gelijktijdig gefilterd over alle werkbladen in het huidige werkboek. Zie de onderstaande resultaten.

7. Filter opnieuw toepassen na wijzigen van gegevens

Soms heb je wijzigingen aangebracht in een gefilterd bereik. Toch blijft het filterresultaat hetzelfde, ongeacht de wijzigingen (zie onderstaande schermafbeelding). In dit onderdeel vind je twee methoden om het filter op het huidige bereik handmatig of automatisch opnieuw toe te passen, zodat je wijzigingen worden meegenomen.

Filter handmatig opnieuw toepassen met de opdracht Opnieuw toepassen

Excel heeft een ingebouwde functie "Opnieuw toepassen" om het filter handmatig opnieuw toe te passen. Volg deze stappen.

Klik op "Gegevens" > "Opnieuw toepassen" om het filter in het huidige werkblad opnieuw toe te passen.

Nu zie je dat het gefilterde bereik opnieuw is toegepast en de aangebrachte wijzigingen zijn meegenomen.

Filter automatisch opnieuw toepassen met VBA-code

Als de gefilterde lijst vaak verandert, moet je telkens opnieuw op deze functie klikken. Hier vind je een VBA-code waarmee het filter automatisch opnieuw wordt toegepast zodra de gegevens wijzigen.

1. Klik met de rechtermuisknop op het tabblad van het werkblad met het filter dat je automatisch wilt vernieuwen en kies "Code weergeven".

2. Kopieer in het geopende venster "Microsoft Visual Basic for Applications" de onderstaande VBA-code in het codevenster.

VBA-code: Filter automatisch opnieuw toepassen bij wijzigen van gegevens

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Opmerking: In de code is “Sheet2” de naam van het huidige werkblad. Je kunt dit aanpassen naar wens.

3. Druk op "Alt" + "Q" om het venster "Microsoft Visual Basic for Applications" te sluiten.

Vanaf nu wordt het filterresultaat dynamisch aangepast zodra je gegevens wijzigt in de gefilterde lijst. Zie de onderstaande gif-afbeelding.

8. Filter wissen of verwijderen

We hebben hierboven geleerd hoe je een filter toevoegt, toepast en gebruikt. Hier leer je hoe je een filter wist of verwijdert in Excel.

8.1 Een filter uit een kolom wissen

Na het toepassen van een filter op een kolom, klik je op het filterpictogram en vervolgens op "Filter wissen uit 'Koptekstnaam'" in het keuzemenu om het filter te wissen.

8.2 Alle filters in een werkblad wissen

Heb je filters toegepast op meerdere kolommen en wil je ze allemaal tegelijk wissen, klik dan op "Gegevens" > "Wissen".

Nu zijn alle filters gewist, zoals in de onderstaande schermafbeelding.

8.3 Filters van alle werkbladen in het huidige werkboek wissen

Stel dat je filters hebt toegepast over meerdere werkbladen in een werkmap en deze allemaal tegelijk wilt wissen. De onderstaande VBA-code kan je hierbij helpen.

1. Open de werkmap waarvan je alle filters wilt wissen en druk gelijktijdig op "Alt" + "F11".

2. Klik in het geopende venster "Microsoft Visual Basic for Applications" op "Invoegen" > "Module" en kopieer vervolgens de onderstaande VBA-code in het modulevenster.

VBA-code: Filters wissen van alle werkbladen in het huidige werkboek

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Druk op de toets "F5" om de code uit te voeren. Nu zijn alle filters van alle werkbladen in het huidige werkboek gewist.

8.4 Alle filters in een werkblad verwijderen

Met bovenstaande methoden wis je alleen de filterstatus, maar blijft de filterfunctie actief in het werkblad. Wil je alle filters uit een werkblad verwijderen, probeer dan de onderstaande methoden.

Alle filters in een werkblad verwijderen door Filter uit te schakelen

Klik op "Gegevens" > "Filter" om de functie uit te schakelen (de Filter-knop is dan niet meer gemarkeerd).

Alle filters in een werkblad verwijderen met sneltoets

Daarnaast kun je een sneltoets gebruiken om alle filters in een werkblad te verwijderen.

Druk in het werkblad met de filters die je wilt verwijderen gelijktijdig op "Ctrl" + "Shift" + "L".

Alle filters in het huidige werkblad zijn dan direct verwijderd.