Decoratieve afbeelding met een filter

Filteren, maar dan anders …

In mijn trainingen en tijdens één-op-één-spreekuurgesprekken zie ik dat een van de meest gebruikte onderdelen van Excel het werken met lijsten/tabellen is. Het is een hele mooie manier om gegevens weer te geven, je kunt goed sorteren en je kunt selecteren (wat Excel dan ‘filteren’ noemt). De meeste mensen schakelen hiervoor de filterknoppen in (tab Gegevens > Filter).

Maar bij het filteren werk je altijd IN de lijst zelf. Filter je bijvoorbeeld op de kolom waar de provincienaam staat op Gelderland, dan worden de rijen waarin dat woord niet voorkomt verborgen.
Hieronder zie je een voorbeeld van zo’n lijst ①. Je ziet dat de filterknop is ingeschakeld ②.
Aan de trechter bij de provinciekolom ③, zie je dat er in die kolom is gefilterd. Als je de muis erboven houdt, zie je ook waarop is gefilterd ④. De rijen waar Gelderland niet staat, zijn verborgen ⑤ (je mist rijnummers!).

Schermafdrukken van de knop Filter op de tab Gegevens. Eronder een voorbeeldlijst waar je de filterknoppen ziet en waar op de provincie Gelderland is gefilterd.

Er zijn 2 problemen met deze manier van filteren.
Als naast je tabel/lijst ook gegevens staan en die staan toevallig in verborgen rijen, dan zie je die ook niet meer!
En bij gebruik van de filterknoppen is het niet mogelijk tegelijk een lijst op je scherm te krijgen van de gegevens van Gelderland en een andere lijst met Friesland-gegevens.
Met de functie Filter kun je dat wel. Je laat de originele lijst intact en zet het resultaat met het filter ergens anders neer. En er is een connectie met de basislijst, dus als daar iets wijzigt, zie je dat automatisch ook in de gefilterde lijst.

Je bent nog beter af als je voor het filteren van je cellenbereik een tabel maakt. Daarover heb ik eerder een blog geschreven (Maak altijd een tabel).
In de blog Filteren met slicers heb je gezien dat er nog een manier is om te filteren, maar die werkt ook met verborgen rijen!

De lijst uit de afbeelding bij ① gebruik ik nu om de functie Filter te beschrijven.

De functie Filter() in een cellenbereik

Het gebruik van deze functie is vrij eenvoudig. Je moet opgeven welke cellen de lijst vormen (A2:C19) en je moet opgeven op welke kolom je wilt filteren (B2:B19) en wat hiervoor het gewenste selectiecriterium is (=”Gelderland”).
Het resultaat is dat je alle gegevens uit die tabel van de provincie Gelderland ziet. Je moet er nog wel zelf de kolomomschrijvingen boven zetten.

Afbeelding met tekst, schermopname, Lettertype, nummer Automatisch gegenereerde beschrijving

Er is wel iets bijzonders: je ziet dat er een blauwe rand om de cellen met het resultaat. Die rand geeft het ‘overloopgebied’ aan.
Technisch gezien staat de berekening alleen in de linkerbovenhoek van het blauw omrande gebied. De uitkomst loopt over in de andere cellen die nodig zijn.
Dat overloopgebied is flexibel: als in de lijst iets wijzigt (naam 13 wordt verwijderd of Naam 5 wordt toch Gelderland), dan zal het overloopgebied automatisch wijzigen.

2 schermafdrukken: een kleiner overloopgebied en een groter overloopgebied.

Foutmelding #OVERLOPEN!

De cellen in het overloopgebied moeten echt leeg zijn, anders krijg je een foutmelding #OVERLOPEN! (Engels: #SPILL!).
Je ziet met een gestreepte rand hoe groot het overloopgebied is. In dit voorbeeld zie je direct wat de niet lege cel is. Maar als het overloopgebied groot is, zie je het misschien niet zo snel. Door te klikken op het getoonde pictogram kun je de cel(len) met de problemen laten selecteren. Haal je die leeg, dan werkt het weer!
Excel noemt het overloopgebied een ‘dynamisch gebied’.

Schermafbeelding met een overloopgebied waarin een cel niet leeg is: je ziet de foutmelding #OVERLOOP! Via het pictogram dat erbij staat kun je de probleemcel opzoeken.

Meer flexibiliteit

Je maakt het geheel natuurlijk nog flexibeler door in de filterberekening “Gelderland” niet zelf te typen, maar door te verwijzen naar een cel waar je de provincienaam kunt invullen. Als je die provincienaam dan ook nog met gegevensvalidatie laat kiezen uit een lijst wordt het natuurlijk helemaal fraai (zie de blog over Gegevensvalidatie).

Schermafbeelding waarin niet "Gelderland" is getypt in de filterfunctie, maar verwezen wordt naar een cel waar "Gelderland" staat.

LET OP

Er zijn met deze functie wel enkele dingen waar je rekening mee moet houden.

  • Zorg dat je bij de verwijzingen naar de lijst/tabel de kolomomschrijving niet meeneemt (dus niet A1:C19): je geeft dus alleen de rijen met de gegevens op (A2:C19). Dat doe je natuurlijk ook bij de kolomgegevens waarop je wilt filteren.
  • Dat betekent dus ook dat je de kolomomschrijving zelf boven de kolommen moet zetten boven de filterresultaten.
  • De opmaak van de cellen van de basislijst wordt niet overgenomen in de gefilterde lijst.
    De cellen in het overloopgebied hebben een eigen opmaak. Hieronder zie je daarvan een duidelijk voorbeeld. Je moet dus de opmaak van de cellen in het overloopgebied wellicht aanpassen.
    Nieuwe lijt met een datum erin. Die datum is in het

De functie Filter() met een tabel

Wanneer je van je cellenbereik een tabel hebt gemaakt, werkt de filterfunctie op dezelfde manier. Je verwijst dan echter niet naar de cellen met de gegevens, maar je gebruikt de tabelverwijzingen. Die krijg je automatisch als je bij het maken over de cellen sleept met je muis.

Hieronder zie je een afbeelding hiervan. Van de celen A1:C19 is met Invoegen > Tabel een tabel gemaakt ①. Die heeft de naam Tabel1 gekregen. Die naam zie je terug als je de cellen selecteert in de functie Filter bij de lijst . Selecteer je de cellen met de provincienamen, dan wordt dit automatisch Tabel1[Provincie] (=de kolom Provincie van Tabel 1) ②.

Schermafbeelding van dezelfde lijst maar dan is er eerst een tabel van gemaakt. In de filterfunctie zijn dan de celverwijzingen vervangen door tabelverwijzingen.

Voordeel bij een tabel

Stel dat er een nieuwe naam bij komt in de basislijst ①. Als het een cellenbereik moet je in de filterfunctie het cellenbereik B2:B19 aanpassen naar B2:B20 om die nieuwe gegevens ook mee te nemen ②!
Bij een tabel is dat niet nodig ③! Dit is weer een voorbeeld van waarom je met een tabel beter af bent!

2 afbeeldingen met een nieuwe naam erbij. In het filter met het cellenbereik is die naam niet automatisch opgenomen, in de tabel wel.

Klik hier om het xlsx-bestand te downloaden.
Het bestand wordt dan geopend in de online-versie van Excel.
Kies Bestand > Opslaan als en download een kopie op je computer.
Je moet het bestand daarna openen in de Excel-versie op je computer om de eieren te zoeken.

Als je het YouTube-filmpje nog eens wilt bekijken, klik dan hier.

Succes!

Afbeelding met een cellenbereik en een tabel en de tekst 20 voordelen van een Excel-tabel

Maak altijd een tabel!

In mijn trainingen en tijdens één-op-één-spreekuurgesprekken zie ik dat in Excel heel vaak data staan in tabelvorm ①. Er staan kolomomschrijvingen boven elke kolom en daaronder staan de gegevens.
Filterknoppen erbij en je kunt dan heel simpel sorteren, selecties maken ② en als je dat wilt de data analyseren met een draaitabel (pivot table) ③.

Voorbeeld van een cellenbereik in tabelvorm zonder filterknoppen (1), met filterknoppen (2) en weergegeven in een draaitabel (3).

Op zich werkt dat prima, maar het is slimmer hier ook een Excel-tabel van te maken!
Dit heeft een groot aantal voordelen. Hierover heb ik jaren geleden al eens een videofilm over gemaakt die nog steeds actueel (https://www.youtube.com/watch?v=YOGNJyaDIKU).

Hoe maak je een tabel?

Er zijn 2 manieren. Het grote verschil is dat manier 1 Excel de tabelstijl bepaalt en bij manier 2 bepaal je die zelf. In beide situaties kun je altijd achteraf de tabelstijl nog wijzigen.

  1. Selecteer een cel in het cellenbereik en kies Invoegen > Tabel ①.
    Controleer het cellenbereik en kijk of er inderdaad kopteksten (=kolomomschrijvingen) boven elke kolom staan) en kies OK ③.
    Voor deze manier is ook een sneltoets: Ctrl+L (voor de mensen met de Engelstalige Excel is dat Ctrl+t).
  2. Selecteer een cel in het cellenbereik en kies Start > Opmaken als tabel > maak een keuze voor de tabelstijl ②.
    Controleer het cellenbereik en kijk of er inderdaad kopteksten (=kolomomschrijvingen) boven elke kolom staan) en kies OK ③.

Schermafbeeldingen van de knop Invoegen > Tabel (1), Start > Opmaken als tabel (2) en het dialoogvenster dat in beide situaties volgt (3).

Waarom is het maken van een tabel slimmer?

Hieronder heb ik de voordelen op een rij gezet. Hier zie je 20 voordelen op een rij!

  1. Je ziet direct welke cellen de tabel vormen door de opmaak. Die stijl kan worden aangepast of verwijderd.
    Schermafbeelding van de gegevens in een Excel-tabel. Je ziet direct de eerste 3 voordelen.
  2. De filterknoppen komen er automatisch bij.
  3. De om en om gekleurde rijen zorgen (zeker bij brede tabellen) dat het eenvoudiger is om de regels per rij te lezen. Die om-en-om-kleuren blijven ook als er gesorteerd wordt!
  4. De kolomomschrijvingen blijven altijd zichtbaar, ook als je naar onderen scrolt. Je hoeft dus geen titels te blokkeren.
    Schermafbeelding waarin je ziet dat de kolomomschrijvingen altijd zichtbaar blijven.
  5. Voeg je een kolom toe dan komt die automatisch bij die tabel gevoegd en krijgt die een filterknop.
  6. Als er extra gegevens worden toegevoegd onderaan, breidt de tabel vanzelf uit.
    Zou dit niet gebeuren, dan kun je dat altijd aanpassen door de tabelgrootte te wijzigen via het speciale tabblad Tabelontwerp in het lint.
    Schermafbeeldingen waarbij je ziet dat er een extra kolom bij komt en een extra rij. Zie je de knop in het lint om de tabelgrootte handmatig te wijzigen.
  7. Het tabelbereik is dynamisch, wat voordelen heeft op verschillende gebieden.
    Bijvoorbeeld als je er een grafiek van maakt: nieuwe toegevoegde gegevens (ook onderaan!) komen vanzelf in de grafiek erbij. Je hoeft het grafiekgebied niet aan te passen.
  8. Het dynamische tabelbereik is ook handig voor draaitabellen. Bij een gewoon cellenbereik moet je het gebied waarop de draaitabel is gebaseerd eerst aanpassen als je er rijen/kolommen aan toevoegt en daarna vernieuw je de draaitabel.
    Bij een Excel-tabel hoef je alleen maar te vernieuwen, want het tabelbereik is automatisch aangepast!.
  9. Het is mogelijk een totaalrij in te schakelen via de tab Tabelontwerp.
    Dat is een extra rij onder het laatste item van de tabel. Daarin zal in eerste instantie alleen bij de laatste kolom een berekening gemaakt worden (optelling van die laatste kolom bij numerieke gegevens of een telling bij niet-numerieke gegevens).
    Je kunt nu op die totaalrij bij elke tabelkolom kiezen voor een berekening.
    Wanneer je de filterknoppen gebruikt laat deze totaalrij de berekening zien voor de cellen die je hebt gefilterd!
    Schermafbeelding van een totaalrij.
  10. Maak je een berekening in de tabel, dan worden er speciale tabelformules gebruikt.Schermafbeelding met tabelformules en dat een formule in een kolom automatisch doorgevoerd wordt naar de andere cellen in de kolom.
    Dus niet de ‘normale’ celverwijzing =D4, maar een verwijzingen naar dezelfde rij, andere kolom: =[@Verkopen]
    Als je bijvoorbeeld buiten de tabel een optelling wilt hebben van een kolom, wordt de kolomnaam gebruikt: =SOM(Tabel2[Verkopen])
  11. De berekening wordt automatisch doorgevoerd naar de andere rijen in de tabel.
  12. Invoegen en verwijderen via de rechtermuisknop van tabelrijen en tabelkolommen gebeurt alleen in de tabel. Als er dus iets buiten de tabel staat, wordt dat niet beïnvloed.
    Schermafbeelding waarin je ziet hoe je alleen in de tabel een rij kunt invoegen
  13. Filteren met slicers is mogelijk.
    Dit is sneller dan met de filterknoppen en het is ook nog visueler. Hierover heb ik eerder een blog gemaakt: https://www.toels-pc.nl/2024/01/08/filteren_met_slicers
    Schermafbeelding waarin je ziet dat er met een slicer gefilterd is op regio Zuid
  14. Automatisch controle op dubbele kolomnamen. Komt een naam dubbel voor, dan wordt er een getal achter gezet. Uiteraard is het slim dit zelf te wijzigen!
    Dubbele kolomnamen geven bijvoorbeeld bij draaitabellen een probleem.
  15. Er zin speciale selecties mogelijk in de tabel: alle rijen van een kolom (a), of alle kolommen van een rij (b) en alle data van de tabel (c).
    Je moet dan letten op de zwarte pijlvorm van je muis en dan klikken.
    Schermafbeeldingen met muisaanwijzers op de plaats waar je een hele tabelkolom, tabelrij en de hele tabel kunt selecteren
  16. De tabel heeft altijd een naam Tabel#. Je kunt die naam aanpassen via Tabelontwerp > Tabelnaam.
    Via het naamvak kun je altijd snel naar de tabel gaan.
    Schermafbeelding van de tabelnaam en het naamvak
  17. Tijdens het typen van nieuwe data in de tabel kun je altijd met de Tab-toets naar de volgende rij. Een totaalrij zal automatisch opschuiven.
  18. De opmaak wordt automatisch doorgevoerd naar nieuwe cellen.
  19. Je kunt de lijst exporteren naar een SharePoint-lijst of Visio-diagram via Tabelontwerp > Exporteren.
    Schermafbeelding van de knop Exporteren
  20. Er komen steeds meer nieuwe functies, die met name flexibiliteit hebben als ze verwijzen naar een tabel. Voorbeelden zijn =SORTEREN, =UNIEK en =FILTER. Maar ook bestaande functies zijn flexibeler (zie de =SOM(Tabel2[Verkopen] van voordeel 10.
    In een volgend blog zal ik hier een voorbeeld van geven.

Overtuigd?

Mocht je toch achteraf nog spijt hebben, dan kun je een tabel altijd weer terugzetten naar een normaal cellenbereik via de tab Tabelontwerp. Gelukkig kun je daarna ook altijd weer terug!

2 voorbeelden van gegevensvalidatie met een invoerbericht

Om te zorgen dat er in een cel niet zomaar van alles ingevoerd kan worden, kun je validatie gebruiken. Daarmee geeft je aan wat is toegestaan als inhoud voor een cel. Bijvoorbeeld dat er alleen een datum ingevoerd mag worden vóór de datum van vandaag (of juist erná), of dat er alleen gekozen kan worden uit een lijst van mogelijkheden.
Dat kan handig zijn om te zorgen dat bijvoorbeeld verderop in een berekening niet een foutmelding komt te staan, omdat er iets verkeerds is ingevuld. Gegevensvalidatie is hiervoor een handig hulpmiddel!

Wil je meer leren over gegevensvalidatie? Volg dan een training. Mijn trainingen zijn altijd maatwerk: je leert dus wat je nodig hebt! Tijd stoppen in leren wat je toch niet gaat gebruiken is zonde van je tijd en van je geld!

Hoe instellen?

  • Selecteer de cel of de cellen waarvoor je de validatie wilt instellen.
  • Kies Gegevens > Gegevensvalidatie.
    Schermafbeelding met de knop Gegevensvalidatie
  • Stel het scherm naar jouw wensen.
    Het dialoogvenster waarin je de gegevensvalidatie opgeeft

Bij de cel(len zie je over het algemeen verder niets bijzonders: je ervaart vanzelf dat er een validatie is ingesteld als je iets verkeerds invoert. Verderop leg ik uit hoe je wel aanwijzingen kunt geven.

Wat gebeurt er als er toch iets verkeerd wordt ingevuld?

De standaardinstelling is dat een verkeerde invoer niet is toegestaan: je moet dus iets anders invoeren. De melding die je hierover krijg is soms wat cryptisch: het geeft in ieder geval geen aanwijzing wat het WEL zou moeten zijn. Verderop leg ik uit hoe je dat kunt aanpassen.
De standaardfoutmelding bij een foutieve invoer

Voorbeeld: getallen met evt. aanvullende beperkingen

  • Kies onder Toestaan voor Geheel getal of Decimaal getal ①.
  • Kies eronder bij Gegeven voor een van de opties ②
  • Specificeer dat vervolgens eronder ③.
    Hier kun je een vast getal typen of verwijzen naar een cel waar een getal staat ④.
    Afbeelding hoe je het scherm gegevensvalidatie moet invullen voor gehele of decimale getallen

Voorbeeld: datum of tijd met evt. aanvullende beperkingen

  • Kies onder Toestaan voor Datum ①.
    Bij een tijd kies je natuurlijk voor Tijd.
  • Kies eronder bij Gegeven voor een van de opties ②
  • Eronder kun je bijvoorbeeld een startdatum invullen in de vorm van een datum ③.
    Maar je kunt ook een datumfunctie gebruiken als vandaag ④ (die past zich steeds aan).
    Voor een tijd kun je de tijd invoeren als 5:25 en ook hier kun je een tijdfunctie gebruiken.
    Afbeelding hoe je het scherm gegevensvalidatie moet maken voor datums

LET OP: voor Excel is een datum een getal in de vorm van een datum. Als er dus bijvoorbeeld staat groter dan 1-1-2024 (wat het getal 45292) kan iemand ook bijvoorbeeld 46000 invoeren.
Zorg dus ook altijd dat de getalnotatie van een cel met een datumvalidatie in een datumnotatie staat!
Een tijd is een decimaal getal tussen 0 en 1. 12:00 is bijvoorbeeld het getal 0,5 (12/24e = 0,5) en 18:00 het getal 9,75 (18/24e = 0,75).

Kiezen uit een lijst

  • Kies onder Toestaan voor Lijst ①.
  • Vul eronder bij Bron in waaruit gekozen kan worden. Dit kun je doen n de vorm van teksten (van elkaar gescheiden met een ;) of door te verwijzen naar een cellenbereik waar je de opties per cel invult. ②
    Voorbeeld van een validatie waarbij uit een lijst gekozen moet worden

Aanwijzing voor het invoeren opgeven

Wil je een aanwijzing opgeven wat er ingevoerd moet worden, dan vul je het tweede tabblad Invoerbericht in.

  • Schakel het selectievakje in ①
  • Voer eventueel een titel in en het bericht eronder ②
  • Het ziet er dan zo uit als de cel is geselecteerd.

Voorbeeld van hoe je een invoerbericht maakt

Aangepaste foutmelding

De standaardinstelling is dat er geen foute invoer mag komen. Dat kun je wijzigen in een waarschuwing (waarbij je alsnog de invoer kunt aanpassen) of alleen informatie dat het niet is toegestaan.

Om een aangepaste foutmelding te maken, dan vul je het derde tabblad Foutmelding in.

  • Schakel het selectievakje in ①
  • Kies de soort waarschuwing bij Stijl ②
  • Voer eventueel een titel in en het bericht eronder ③
  • Er MOET een juiste invoer gedaan worden: Kies bij stijl Stop.
    Dit is een voorbeeld van een Stopwaarschuwing ④
  • Als je bij stijl kiest voor Waarschuwing, krijg je een melding als bij ⑤
  • Als je bij stijl kiest voor Info, krijg je een melding als bij ⑥

Voorbeeld van hoe je een foutbericht maakt

  • Stel het scherm naar jouw wensen.
    Het dialoogvenster waarin je de gegevensvalidatie opgeeft

Schermafdruk van een voorbeeld van een,lijst met een slicer.

Een van de handelingen die veel met Excel wordt uitgevoerd is filteren. Dat hoor ik vaak tijdens de Excel-trainingen. Het werkt simpel en snel. Maar nog sneller werkt het met een slicer (of meer slicers). In deze blog leg ik uit wat dit zijn en hoe het werkt. Je zult zien dat het nog sneller werkt dan de bekende filterknoppen!
Als voorbeeld gebruik ik dit bestand: een lijst met gegevens over medewerkers in een bedrijf.

Gebruikt voorbeeldbestand. Cellenbereik vanaf A1 met 5 kolommen: 1. Mw-ID 2. Naam 3. Regio 4. Vestiging 5. Afdeling

Er is ook een Snelle Korte Tip over dit onderwerp in de vorm van een video-instructie

Filterknoppen

Wanneer je in deze lijst één cel selecteert en kiest voor Gegevens > Filter, dan verschijnen de filterknoppen.

Schermafbeelding van de filterknop in het lint en de filterknoppen bij de kolomomschrijvingen in de lisjt.

Die kun je gebruiken om selecties te maken (filteren dus). Bijvoorbeeld alleen de mensen van de vestiging Arnhem. De rijen met gegevens die niet voldoen worden dan verborgen.
Dat er een filter is zie je aan de gewijzigd filterknop. En met de muisaanwijzer erbij zie je ook welk filter is ingesteld.

Schermafbeelding van een ingesteld filter (knop is trechter geworden en bij aanwijzen zie je welk filter is ingesteld.

Slicer om te filteren

Hier zie je dezelfde filterinstelling, maar dan met een slicer. Wil je filteren op een andere vestiging, dan klik je gewoon op een andere naam in de slicer!

Schermafbeelding met dezelfde filterinstelling maar dan met een slicer.

Je ziet direct de voordelen van een slicer:

  • Je hoeft niet op de filterknop te wijzen om te zien welk filter is ingesteld: dat zie je direct in de slicer.
  • Je ziet ook welke vestigingen er nog meer zijn (die dus NIET zijn geselecteerd).
  • Je hoeft bij het selecteren van andere optie minder te klikken.
    Bij een filterknop moet je die eerst openen, dan een keuze maken en dan OK kiezen.
  • Het filter verwijderen gaat met één klik op het knopje rechtsboven in het filter.

Hoe maak je zo’n slicer?

Verplicht voor een slicer is dat je van het cellenbereik met de lijst eerst een tabel maakt.

  • Selecteer één cel in de lijst (je mag ook alle cellen selecteren van de lijst)
  • Kies Invoegen > Tabel.
  • Controleer of het juiste gebied is geselecteerd en of er inderdaad boven elke kolom een omschrijving staat (dat noemt Excel hier ‘kopteksten”)

Schermafdruk van het maken van een tabel van de lijst.

Hierna is de lijst een tabel. Dat zie je duidelijk aan de kleur en aan het feit dat er een extra tabblad Tabelontwerp in het lint staat (als er een cel in de tabel is geselecteerd). In dat extra tabblad kun je met Tabelstijlen ook andere kleuren kiezen voor de tabel. Ook de kleur ‘geen’ is hier aanwezig.

Schermafdruk van de tab Tabelontwerp. Hierop zijn de knoppen Tabelstijlen en Slicer invoegen omkaderd.

Een van de knoppen die daar ook op staat, is Slicer invoegen. Daarmee maak je een slicer.

  • Klik op Tabelontwerp > Slicer invoegen > kies de kolomnaam waar je een slicer voor wilt maken. Je kunt ook meer slicers tegelijk maken.

Schermafdruk van het dialoogvenster Slicer invoegen.

Filter instellen en opmaken

Daarna wordt de slicer op het scherm geplaatst en is die geselecteerd (herkenbaar aan de bolletjes rondom). Je kunt daarna de slicer verplaatsen door te slepen met de muis in de titel ① en de afmetingen aanpassen met de bolletjes ②.
Er is ook een speciaal tabblad Slicer als er een slicer is geselecteerd. Daarin kun je onder andere bij Kolommen ③instellen hoeveel knoppen er op één rij moeten komen. In de afbeelding zie je de slicer Vestiging ook in 2 kolommen ④.

Schermafbeelding met een geselecteerde slicer en het speciale tabblad Slicer. Met cijfers is aangegeven hoe je de slicer kunt verplaatsen (=via titelbalk), afmetingen kunt wijzigen ((=via bolletjes), aantal knoppen op een rij kunt wijzigen (=Kolommen) en hoe de slicer eruitziet met 2 kolommen.

Door in een cel te klikken zorg je ervoor dat de slicer niet meer is geselecteerd (bolletjes verdwijnen). Als je de slicer weer wilt selecteren, klik je er weer op (liefst in de titelbalk).

Hoe werk je met de slicer?

  • Klik op een item om er één te selecteren (bijvoorbeeld Arnhem).
    Wil je een ander item selecteren, dan hoef je niet eerst het huidige filter uit te schakelen.
  • Wil je een filter verwijderen, klik dan rechtsboven op Filter wissen ①.
  • Wil je meer items selecteren dan heb je 2 mogelijkheden.
    • Schakel het knopje Meervoudige selectie ② in: nu kun je op meer items klikken. De items werken nu als aan/uit-schakelaar.
      Zolang dit vakje is ingeschakeld is het gekleurd.
    • Werk je niet met dat knopje, dan kun je altijd een meervoudige selectie maken door na het klikken op het eerste item op elk volgende item te klikken met Ctrl ingedrukt.

Schermafbeelding van een slicer met een pijl naar de knop rechtsboven (filter wissen) en de knop links ervan (meervoudige selectie).

Handige tip

Meestal wil je de slicer(s) bovenaan hebben staan. Scrol je echter naar de onderkant van de lijst, dan verdwijnt die uit beeld. Je kunt ervoor kiezen om de slicer(s) boven de tabel te zetten, de eerste rij te verhogen en daarna de eerste regel te blokkeren op het scherm (Beeld > Blokkeren > Bovenste rij blokkeren).
Zo blijft de slicer(s) altijd in beeld.

Schermafdruk met 2 slicers op de eerste rij die is verhoogd, zodat de slicers helemaal zichtbaar zin.

Filteren met slicers

Een van de handelingen die veel met Excel wordt uitgevoerd is filteren. Dat hoor ik vaak tijdens de Excel-trainingen. Het werkt simpel en snel. Maar nog sneller werkt het met een slicer (of meer slicers). In deze blog leg ik uit wat dit zijn en hoe het werkt. Je zult zien dat het nog sneller werkt dan de bekende filterknoppen!
Als voorbeeld gebruik ik dit bestand: een lijst met gegevens over medewerkers in een bedrijf.

Gebruikt voorbeeldbestand. Cellenbereik vanaf A1 met 5 kolommen: 1. Mw-ID 2. Naam 3. Regio 4. Vestiging 5. Afdeling

Filterknoppen

Wanneer je in deze lijst één cel selecteert en kiest voor Gegevens > Filter, dan verschijnen de filterknoppen.

Schermafbeelding van de filterknop in het lint en de filterknoppen bij de kolomomschrijvingen in de lisjt.

Die kun je gebruiken om selecties te maken (filteren dus). Bijvoorbeeld alleen de mensen van de vestiging Arnhem. De rijen met gegevens die niet voldoen worden dan verborgen.
Dat er een filter is zie je aan de gewijzigd filterknop. En met de muisaanwijzer erbij zie je ook welk filter is ingesteld.

Schermafbeelding van een ingesteld filter (knop is trechter geworden en bij aanwijzen zie je welk filter is ingesteld.

Slicer om te filteren

Hier zie je dezelfde filterinstelling, maar dan met een slicer. Wil je filteren op een andere vestiging, dan klik je gewoon op een andere naam in de slicer!

Schermafbeelding met dezelfde filterinstelling maar dan met een slicer.

Je ziet direct de voordelen van een slicer:

  • Je hoeft niet op de filterknop te wijzen om te zien welk filter is ingesteld: dat zie je direct in de slicer.
  • Je ziet ook welke vestigingen er nog meer zijn (die dus NIET zijn geselecteerd).
  • Je hoeft bij het selecteren van andere optie minder te klikken.
    Bij een filterknop moet je die eerst openen, dan een keuze maken en dan OK kiezen.
  • Het filter verwijderen gaat met één klik op het knopje rechtsboven in het filter.

Hoe maak je zo’n slicer?

Verplicht voor een slicer is dat je van het cellenbereik met de lijst eerst een tabel maakt.

  • Selecteer één cel in de lijst (je mag ook alle cellen selecteren van de lijst)
  • Kies Invoegen > Tabel.
  • Controleer of het juiste gebied is geselecteerd en of er inderdaad boven elke kolom een omschrijving staat (dat noemt Excel hier ‘kopteksten”)

Schermafdruk van het maken van een tabel van de lijst.

Hierna is de lijst een tabel. Dat zie je duidelijk aan de kleur en aan het feit dat er een extra tabblad Tabelontwerp in het lint staat (als er een cel in de tabel is geselecteerd). In dat extra tabblad kun je met Tabelstijlen ook andere kleuren kiezen voor de tabel. Ook de kleur ‘geen’ is hier aanwezig.

Schermafdruk van de tab Tabelontwerp. Hierop zijn de knoppen Tabelstijlen en Slicer invoegen omkaderd.

Een van de knoppen die daar ook op staat, is Slicer invoegen. Daarmee maak je een slicer.

  • Klik op Tabelontwerp > Slicer invoegen > kies de kolomnaam waar je een slicer voor wilt maken. Je kunt ook meer slicers tegelijk maken.

Schermafdruk van het dialoogvenster Slicer invoegen.

Filter instellen en opmaken

Daarna wordt de slicer op het scherm geplaatst en is die geselecteerd (herkenbaar aan de bolletjes rondom). Je kunt daarna de slicer verplaatsen door te slepen met de muis in de titel ① en de afmetingen aanpassen met de bolletjes ②.
Er is ook een speciaal tabblad Slicer als er een slicer is geselecteerd. Daarin kun je onder andere bij Kolommen ③instellen hoeveel knoppen er op één rij moeten komen. In de afbeelding zie je de slicer Vestiging ook in 2 kolommen ④.

Schermafbeelding met een geselecteerde slicer en het speciale tabblad Slicer. Met cijfers is aangegeven hoe je de slicer kunt verplaatsen (=via titelbalk), afmetingen kunt wijzigen ((=via bolletjes), aantal knoppen op een rij kunt wijzigen (=Kolommen) en hoe de slicer eruitziet met 2 kolommen.

Door in een cel te klikken zorg je ervoor dat de slicer niet meer is geselecteerd (bolletjes verdwijnen). Als je de slicer weer wilt selecteren, klik je er weer op (liefst in de titelbalk).

Hoe werk je met de slicer?

  • Klik op een item om er één te selecteren (bijvoorbeeld Arnhem).
    Wil je een ander item selecteren, dan hoef je niet eerst het huidige filter uit te schakelen.
  • Wil je een filter verwijderen, klik dan rechtsboven op Filter wissen ①.
  • Wil je meer items selecteren dan heb je 2 mogelijkheden.
    • Schakel het knopje Meervoudige selectie ② in: nu kun je op meer items klikken. De items werken nu als aan/uit-schakelaar.
      Zolang dit vakje is ingeschakeld is het gekleurd.
    • Werk je niet met dat knopje, dan kun je altijd een meervoudige selectie maken door na het klikken op het eerste item op elk volgende item te klikken met Ctrl ingedrukt.

Schermafbeelding van een slicer met een pijl naar de knop rechtsboven (filter wissen) en de knop links ervan (meervoudige selectie).

Handige tip

Meestal wil je de slicer(s) bovenaan hebben staan. Scrol je echter naar de onderkant van de lijst, dan verdwijnt die uit beeld. Je kunt ervoor kiezen om de slicer(s) boven de tabel te zetten, de eerste rij te verhogen en daarna de eerste regel te blokkeren op het scherm (Beeld > Blokkeren > Bovenste rij blokkeren).
Zo blijft de slicer(s) altijd in beeld.

Schermafdruk met 2 slicers op de eerste rij die is verhoogd, zodat de slicers helemaal zichtbaar zin.

Schermafbeelding dat je nu ok 2 manieren afbeeldingen kunt opnemen: over cellen en in cellen!

Tot voor kort moest ik mensen in trainingen altijd teleurstellen als het om afbeeldingen ging in Excel. Natuurlijk kun je die toevoegen, maar ze lagen altijd bovenop de cellen met alle vervelende gevolgen die daarbij horen.
Sinds kort kun je een afbeelding ook IN een cel zetten. Vooral mensen die lijsten maken met bijvoorbeeld artikelentabellen hebben nu de optie om er eenvoudig foto’s bij zetten en zonder problemen bijvoorbeeld te sorteren en filteren. Het kan ook handig zijn voor personeelslijsten met foto’s van medewerkers.

Situatie vroeger

Of je nu een afbeelding invoegt of kopieert-plakt: die kwam altijd bovenop de cellen te liggen. Om het leesbaar te houden bijvoorbeeld in een artikelentabel, pas je de grootte aan en de rijhoogte/kolombreedte ①. Maar als je dan sorteert dan gaat het mis met de afbeeldingen ②!

Schermafbeelding van artikellijst met foto erbij in een Exceltabel en een tweede schermafbeelding nadat de lijst is gesorteerd.

Het enige wat je kon regelen was hoe de afbeelding zich moest ‘gedragen’ bij het verplaatsen (sorteren is verplaatsen) en als rijen/kolommen werden verborgen (wat bij filteren gebeurt).

Als je klikt met de rechtermuisknop op de afbeelding > Grootte en eigenschappen dan krijg je een taakvenster. Alleen verplaatsing gerelateerd aan cellen is de standaardinstelling.

Schermafbeelding van de Eigenschappen die ingesteld kunnen worden bij een affbeelding

Wat gebeurt er als afbeeldingen die andere opties hebben?

  • Alleen verplaatsing gerelateerd aan cellen betekent
    Als je de cel verplaatst, verplaatst de afbeelding ook. Er is dus een koppeling met de cel waar de afbeelding op staat. Handig voor zo’n tabel als deze.
    Maar er is geen koppeling met het formaat van de afbeelding: die blijft zijn oorspronkelijke afmetingen houden. Daarom zie je nog maar een deel van de boormachine (zie ②).
  • Verplaatsing en formaat gerelateerd aan cellen betekent
    Als je de cel verplaatst, verplaatst de afbeelding ook. Er is dus een koppeling met de cel waar de afbeelding op staat.
    Maar als die cel dan verplaatst wordt naar een rijhoogte die kleiner is, dan wordt die in elkaar gedrukt als de rijhoogte kleiner is. Er is namelijk ook koppeling van het formaat van de afbeelding met de cel (zie de boormachine bij ③).
  • Verplaatsing en formaat niet gerelateerd aan cellen betekent
    Als je de cel verplaatst, verplaatst de afbeelding niet naar die andere cel, maar die blijft in de cel staan. De afmetingen wijzigen ook niet: er is geen enkele relatie van de afbeelding met de cel waar die op staat. Dan klopt er van je tabel niet veel meer (zie ④).

Schermafbeelding 3 wat er is gebeurd na het sorteren als afbeeldingen qua fomraat eb verplaatsing zijn gerelateerd aan cellen en 4 als dat niet zo is.

Situatie nu

Nu is er een mogelijkheid om een afbeelding echt IN een cel te zetten: het wordt dus de inhoud van de cel. Net zoals er een tekst, getal of berekening in een cel kan staan, kan er ook een afbeelding in staan. En je kunt er ook nog berekeningen mee maken!

Bij Invoegen > Afbeelding staan nu twee opties.

Schermafbeelding van de nieuwe mogelijkheden bij Invoegen > Afbeeldingen

Plaatsen over cellen is wat er eerst alleen mogelijk was. In cel plaatsen kan nu ook. In beide gevallen kun je daarna kiezen voor Dit apparaat, Stockafbeeldingen of Onlineafbeeldingen.

Voeg ik de afbeeldingen van de boormachine, zaag en schuurmachine in, dan ziet dat er bij het plaatsen in de cel zo uit.

Schermafbeelding met de menu-optie voor het invoegen van afbeeldingen en de cel en hoe dat eruitziet in een tabel

De afbeeldingen passen zich nu aan de celgrootte aan. Hier zijn de rijen hoger gemaakt ①.
Ook kun je de uitlijning aanpassen, bijvoorbeeld centeren ②
En bij het sorteren gaat het automatisch mee ③: je hoeft er niets voor te regelen!

Drie genummerde schermafbeeldingen waarbij je ziet wat er gebeurt met afbeeldingen in een cel geplaatst bij wijzigen rijhoogte (1), bij centreren (2) en bij sorteren (3)

Als je op zo’n afbeelding klikt met de rechtermuisknop > Voorbeeld weergeven dan krijg je een grotere afbeelding te zien (sneltoets: Ctrl+Shift+F5).

Schermafbeelding van een vergrote afbeelding als "voorbeeld weergeven' is gebruikt

Ook kun je bijvoorbeeld een zoeken-functie gebruiken om een afbeelding te zoeken bij een artikelcode ①. Of andersom: bij een afbeelding de artikelcode zoeken ② (de gebruikte functie is hier x.zoeken)

Schermafbeelding van 2 voorbeelden waarbij de zoeken-functie een afbeelding kan retourneren of op een afbeelding een artikelcode kan zoeken

Kortom: je hebt nu veel meer mogelijkheden met afbeeldingen in Excel!

 

Olifant en slak als symbool voor grote en langzame Excelbestanden

Tijdens Excel-trainingen krijg ik regelmatig de vraag wat er gedaan kan worden aan langzame en enorm grote Excel-bestanden (grootte wat betreft de omvang in bytes). Nou hoeft groot niet altijd te betekenen dat het ook langzaam is en andersom ook niet: een langzaam bestand hoeft niet altijd groot te zijn.

Belangrijker is: kan er iets aan gedaan worden? Het antwoord is: ja, soms wel.

Wat kun je doen aan grote bestanden?

1. Opruiming houden

Het meest voor de hand liggend is om onderdelen die niet nodig zijn te verwijderen. Denk aan lege werkbladen, maar ook aan cellen die wel een opmaak hebben, maar die helemaal niet gebruikt worden. Die opmaak neemt wel ruimte in. Geef dus niet een hele rij of kolom een rand, opvulkleur, lettertype of getalopmaak, maar alleen de cellen waarvoor dit nodig is. Vergeet hierbij ook de voorwaardelijke opmaak niet (die heeft een nog grotere impact dan de gewone celeigenschappen!).

Er komt overigens een speciale opdracht in Excel die je hierbij een handje kan helpen. Die zit nu nog niet in de Microsoft365-versie, maar al wel in de browser-versie. Heb je een bestand opgeslagen op OneDrive of SharePoint, dan kun je dat bestand in een browser openen en die opdracht alvast bekijken en gebruiken. Hij staat op de tab Controleren en heet Prestatie controleren (Eng: Check Performance).
Rechts verschijnt dan een deelvenster dat je kunt gebruiken voor deze opruiming.

Schermafbeelding met de knop Prestatie controleren en het bijbehorende deelvenster Werkmapprestaties.

2. Vermijd het oude bestandformaat .xls

Vaak gaan bestanden jaren mee, waar op zich niets mis mee is. Maar ik zie nog regelmatig .xls-bestanden. Dat betekent dat ze nog in het oude bestandsformaat van versie 2003 staan. Dat bestand gaat dus al minstens 20 jaar mee! Maar een bestand in dat oude bestandsformaat is ook vele malen groter dan tegenwoordig noodzakelijk is. Bij een van mijn cursisten kon een bestand van 73 mB alleen al door het op te slaan in het huidige bestandsformaat .xlsx tot 21 mB worden teruggebracht!

Hoe herken je een bestand in het oude bestandsformaat? Omdat er bij de bestandsnaam (hier ‘voorbeeld’) bovenaan het scherm ”Compatibiliteitsmodus” staat ①. Als je het weergeven van de bestandsextensie hebt ingeschakeld zie je ook achter de naam .xls staan ②.

Schermafbeelding met 2 voorbeelden van bestandsnaam in de titelbalk (met en zonder extensie) en de tekst Compatibiliteitsmodus.

Hoe kun je zo’n bestand overzetten naar het huidige bestandsformaat? Open het bestand > kies Bestand > Opslaan als. Kies bij Opslaan als in de lijst met bestandsformaten Excel-werkmap (dat is de eerste in de lijst).
Omdat het bestand een andere extensie krijgt heb je hierna 2 bestanden: zorg nu dat je de oude niet meer gebruikt (verwijder die of geef die aan andere naam die duidelijk maakt dat het de oude versie is). Je zult niet de eerste zijn die per ongeluk soms in het ene en dan in het ander bestand werkt!

Schermafbeelding met deel van het dialoogvenster Opslaan als die laat zien hoe je opslaan als een Excel-werkmap in het nieuwe bestandsformaat.

Een ander voordeel van het nieuwe bestandsformaat is dat je nu ook in dit bestand gebruik kunt maken van alle nieuwe functionaliteit die Excel te bieden heeft.
Is er ook een nadeel? Ja, als het gaat om een bestand dat nog bewerkt moet kunnen worden in versie 2003 of nog eerder, dan moet je het niet opslaan in het nieuwe bestandsformaat. Maar wie gebruikt er nog versie 2003 of eerder …

Oh, en vergeet niet dat hetzelfde ook geldt voor bestanden in Word en PowerPoint!

3. Staan er afbeeldingen in je werkmap?

Die kunnen ook een enorme impact hebben op de bestandsgrootte. Zeker als ze ook nog eens zijn bijgesneden en/of de afmetingen zijn gewijzigd. Excel onthoudt van elke afbeelding de oorspronkelijke grootte en instelling, zodat je hiernaar altijd terug kunt keren (Afbeelding opnieuw instellen). Maar dat kost natuurlijk ruimte.
Beperk de grootte door afbeeldingen te comprimeren. Selecteer een afbeelding en kies Afbeeldingsindeling > Afbeelding comprimeren. Je kunt de instelling die je dan maakt toepassen op de geselecteerde afbeelding of op alle afbeeldingen.

Schermafbeelding met de knop Afbeelding comprimeren en het dialoogvenster dat dan verschijnt.

Wat kun je doen aan langzame bestanden?

Soms moet je erg lang wachten tot een bestand is geopend, bijvoorbeeld omdat het heel veel werkbladen heeft en/of heel veel berekeningen. Dat komt onder andere omdat bij het openen alle formules in alle werkbladen opnieuw worden doorgerekend.
Als het bestand eenmaal is geopend en je wijzigt wat, dan zal Excel bij elke wijziging alle geopende bestanden opnieuw doorrekenen. Bij heel veel en/of ingewikkelde berekeningen, kan het soms lang duren voordat dit klaar is en je de volgende wijziging kunt doen. De veel gebruikte VERT.ZOEKEN (Eng: VLOOKUP) is hierom berucht!

1. Handmatig berekenen

Je zou het doorrekenen tijdelijk kunnen uitschakelen. Je doet dit op het tabblad Formules > Berekeningsopties ① (Eng: Calculations Options) Je voert daarna de gewenste wijzigingen door en zet dan de automatische berekeningen weer aan. Tussendoor kun je eventueel laten doorrekenen met ② Nu berekenen (rekent alle geopende werkmappen door, Eng: Calculate Now) of Blad berekenen (rekent alleen het huidige werkblad door, Eng: Calculate Sheet).
Of je gebruikt de sneltoetsen hiervoor: F9 en Shift+F9.

Schermafbeelding met de knoppen Berekeningsopties (het menu van deze knop) en de knoppen Nu berekenen en Blad berekenen.

Als er niet doorgerekende formules in je werkblad staan, kun je dat zien op de statusbalk, omdat daar links Berekenen (Engels: Calculate)staat.

Schermafbeelding van de statusbalk waarop je kunt zien dat niet alles is doorgerekend: melding Berekenen.

Als je de berekeningsopties op handmatig hebt gezet, zal Excel vóór het opslaan toch het bestand nog doorrekenen (tenzij je dit specifiek uitschakelt bij Bestand > Opties in de rubriek Formules).

Schermafbeelding met het deel van de standaardinstellignen waarin je kunt aangeven dat je de werkmap wel/niet wilt laten doorrekenen bij het opslaan.

2. Optimaliseer je berekeningen

Vermijd berekeningen met hele kolommen als je niet de hele kolom nodig hebt, zoals =SOM(A:A). Maak er dan van =SOM(A1:A2000).
Soms zie ik hele lange complexe berekeningen. Die zijn niet alleen lastig om te lezen en aan te passen, maar zijn ook langzamer dan berekeningen in stapjes (met hulpkolommen die je eventueel verbergt).
Probeer zogenaamde volatiele functies niet te veel te gebruiken als dit niet nodig is. Dat zijn functies die elke keer opnieuw berekend worden. Dat kost natuurlijk tijd, zeker als je er veel gebruikt. Denk aan functies als NU, VANDAAG, ASELECT, ASELECT.TUSSEN en VERSCHUIVING (Eng: NOW, TODAY, RAND, RANDBETWEEN en OFFSET).

3. Koppelingen naar andere bestanden

Wanneer die aanwezig zijn in je bestand kan dat ook vertragend werken. Bij het openen krijg je meestal de vraag of je de gegevens wilt bijwerken of niet. Als je kiest voor niet bijwerken opent het bestand sneller, maar dan kunnen de gegevens verouderd zijn. Kies je voor bijwerken, dan duurt het openen langer, maar je weet wel dat je dan de bijgewerkte gegevens hebt!
Vaak worden tijdelijk koppelingen toegepast of zijn koppelingen per ongeluk in een bestand gekomen. Koppelingen kun je verbreken op de volgende manier.
Kies op de tab Gegevens > Koppelingen bewerken > selecteer de koppeling > Koppeling verbreken (Engels: Data > Edit Links > Break Link). Het resultaat van de koppeling staat dan als waarde in de cel (soort waarden plakken).

Schermafbeelding met de knop Koppelingen bewerken en eht dialvenster dat getoond wordt als je deze optie kiest.

 

Je kunt binnen de Office-programma’s op verschillende manieren een PDF-bestand maken. Met Word en PowerPoint is dat minder een probleem dan met Excel. Je werkt daar immers op een ‘vel’ zoals het ook uit de printer komt.
Bij Excel werk je niet op een standaardvel papier, maar gebruik je gewoon de rijen en kolommen die je nodig hebt. En dat levert vaak problemen op bij het afdrukken, want vaak past het niet, wordt er op onhandige plaatsen op ene andere pagina gestart en dat soort zaken.
Al deze ‘problemen’ gelden ook als je er een PDF van maakt.

3 manieren om PDF’s te maken met Excel

  1. Bestand > Afdrukken
    Vervolgens kies je als printer Microsoft Print to PDF.
    Schermafdruk van Bestand Afdrukken waarbij het menu van de gekozen printer is geopend. Daar zie je Microsoft Print to PDF.
  2. Bestand > Exporteren > PDF of XPS maken
    Kies de plaats en typ de naam waaronder je het wilt opslaan > Publiceren.
    Schermafbeelding Bestand Exporteren, een beschrijving wat deze optie doet en de knop PDF of XPS maken.
  3. Bestand > Opslaan als
    Sla op als bestandstype PDF > kies plaats en typ de naam > Opslaan
    Schermafbeelding van Bestand Opslaan als waarbij als bestandstype is gekozen voor PDF.

In al deze gevallen kijkt Excel kijken naar de Instellingen van de printer die je vindt bij Bestand > Afdrukken > Instellingen.

  • Actieve bladen afdrukken
    Meestal heb je één werkblad geselecteerd, maar je kunt er ook meer selecteren. De geselecteerde werkbladen worden afgedrukt.
  • Hele werkmap afdrukken
    Alle werkbladen in dit bestand waar iets op staat worden afgedrukt. Ieder werkblad begint op een nieuwe pagina.
  • Selectie afdrukken
    De cel(len) die nu geselecteerde zijn worden alleen afgedrukt.

Schermafbeelding van Bestand Afdrukken met het geopende menu van de Instellingen.

Afdrukvoorbeeld

Of je nu een PDF gaat maken of gewoon afdrukt in Excel: als je veel rijen of kolommen hebt, zal het meestal niet passen op een vel A4. Daarom is het altijd van belang om het afdrukvoorbeeld te bekijken met Bestand > Afdrukken. Je kunt dan bladeren door de pagina’s en zien hoe het wordt.

Met welke instellingen?

Welke afdrukinstellingen er gebruikt worden kun je wijzigen bij de paginalay-out op de tab Pagina-indeling. Ook bij het maken van de PDF worden deze instellingen aangehouden.

Schermafbeelding van de tab Pagina-indeling

  • Marges: de witruimte die langs de randen van het papier vrijgehouden wordt.
  • Afdrukstand: staand of liggen (Eng: portrait of landscape)
  • Formaat: het papierformaat (meestal A4 of A3)
  • Afdrukbereik: het gebied van het huidige werkblad dat altijd afgedrukt wordt (het wordt dus ook opgeslagen!). Je moet tevoren dit gebied selecteren en dan kiezen voor Afdrukbereik bepalen.
  • Eindemarkeringen: hiermee kun je pagina-einden maken en verwijderen
  • Achtergrond: kies een afbeelding als achtergrond (een soort watermerk)
  • Afdruktitels: kies welke rijen en/of kolommen op elke afgedrukte pagina herhaald worden. Dat is handig bij lange tabellen voor de kolomnamen of bij brede tabellen voor de eerste kolom(men).
  • Breedte en Hoogte: hiermee bepaald je hoeveel pagina;s het in de breedte en/of in de hoogte moeten worden. Dit wordt bereikt door de Schaal aan te passen.
  • Schaal: als de Breedte of Hoogte zijn ingesteld (dat wil zeggen “niet staan op Automatisch”) is dit grijs en kun je het niet instellen. Anders kun je dat hier wel instellen.
  • Rasterlijnen > Afdrukken ingeschakeld betekent dat je de dunnen lijntjes ziet die je meestal ook op het scherm ziet. Je hoeft dan geen randen in te stellen bij de celeigenschappen.
  • Koppen > Afdrukken ingeschakeld betekent dat de rijnummers en kolomletters ook afgedrukt worden.

Overige instellingen

Dan zijn er enkele instellingen die minder vaak gebruikt worden. Die vind je als je klikt op deze plaatsen op die tab Pagina-indeling.

Schermafbeelding van de tab Pagina-indeling met pijlen bij de knopjes rechtsonder in de groepen Pagina-instellingen, Aanpassen aan pagina en Werkbladopties.

Het enige verschil tussen deze knoppen is het tabblad waar je in eerste instantie op uitkomt in het dialoogvenster Pagina-instelling.

Schermafbeelding van het venster Pagina-indeling met de tabs Pagina en Marges.

  • Pagina-tab: hier kun je de afdrukkwaliteit nog instellen en met welk nummer de paginanummering moet beginnen ①
  • Marges-tab: hier kun je ook aangeven waar de koptekst en voettekst geplaatst moeten worden ②. Zorg dat de waarde bij Koptekst kleiner is dan de marge Boven en dat de Voettekst-waarde kleiner is dan de marge Onder!
    Hier kun je ook opgeven of het wel of niet gecentreerd op de pagina moet komen ③ (alleen van belang als er weinig wordt afgedrukt)

Schermafbeelding van het venster Pagina-indeling met de tabs Koptekst/voettekst en Blad.

  • Koptekst-voettekst-tab: hier kun je aangeven welke tekst op elke afgedrukte pagina bovenaan moet komen (koptekst) of onderaan( voettekst). Je kunt kiezen uit verschillende kop-voetteksten ①die je vervolgens ziet bij ②.
    Met de knoppen Aangepaste koptekst/voettekst kun je eigen teksten maken.
    Bij ③ geef je bijzonderheden op voor de nummering en de uitlijning.
  • Blad-tab: hier kun je nog iets opgeven over de afdrukkwaliteit, wat er moet gebeuren met notities en opmerkingen die in het werkblad staan en fouten ④.
    Bij ⑤ geef je de volgorde op: eerst alle rijen en daarna de kolommen of andersom.

Volgorde bij maken PDF

  1. Maak voor elk werkblad waar je een PDF van wilt maken de gewenste pagina-instellingen.
  2. Bekijk het afdrukvoorbeeld
  3. Maak de PDF.

Waarom je nooit “samenvoegen” moet gebruiken in Excel!

In mijn trainingen zie ik vaak dat mensen cellen samenvoegen “want dat ziet er zo netjes uit”. Mee eens, maar dat gaat wel vaak ten koste van andere functionaliteit. Het beperkt je bijvoorbeeld bij het sorteren, filteren, bij berekeningen en bij draaitabellen. Liever toch niet doen dus!

Daarover gaat dit blog … en natuurlijk heb ik ook een oplossing.
Oh en een extra tip aan het einde hoe je het nog beter kunt aanpassen met een echte tabel.

Wat zijn samengevoegde cellen?

Soms wil je midden boven een groepje cellen een tekst zetten en niet links ①. Bij ②zie je kwartaalnummer midden boven de maandnamen. In de groep Uitlijning kun je dan kiezen voor Samenvoegen en centreren ③. Het ziet er dan netjes uit. Je hebt van de cellen A2:D2 in feite één cel gemaakt (B2, C2 en D2 bestaan ook echt niet meer!).

Schermopname van Excel werkblad dat verschil laat zien tussen linksuitlijnend en samengevoegd en gecentreerd.

Problemen die je dan kunt tegenkomen

Maar dan komen de problemen. Je wilt ook een jaartotaal hebben!

Links gaat dat prima ①, maar rechts heb je een probleem! Je kunt niet selecteren over alleen de D-cellen! Zodra je bij het selecteren over een samengevoegde cel gaat, zie je dat D overgaat naar A. Daarom wordt bij ② het gebied A4:D21 geselecteerd bij de som-functie! En dan klopt het jaartotaal niet!

Schermafbeelding waarbij je ziet dat het optellen van een gebied waarin samengevoegde cellen staan problemen oplevert.

Ook bij het sorteren zie je dat samengevoegde cellen een probleem kunnen opleveren.

Schermafbeelding waarin je een melding krijgt dat sorteren niet lukt vanwege samengevoegde cellen

De oplossing

Hoe kun je dan toch het kwartaalnummer netjes boven de 3 kwartaalmaanden krijgen zonder deze problemen?
Met Centreren over selectie.

Uitgangspunt is situatie 1. In A2 staat de tekst “Kwartaal 1”. Je selecteert A2:D2.

Schermafbeelding waarin de cellen A2:D2 geselecteerd zijn. In A2 staat de tekst Kwartaal 1

Vervolgens kies je voor de knop Uitlijning ①. Bij Horizontaal kies je Centreren over selectie ②.

De knop om de uitlijning te kunnen kiezen en een deel van het dialoogvenster waarin je Centreren over selectie instelt.

Je probleem is opgelost. Het ziet er hetzelfde uit, maar je hebt niet dezelfde problemen!

Schermafbeelding met het eindresultaat van centreren over selectie

Of nog beter…

Stel dat er ook productcategorieën bij staan in de kolom ervoor, zoals hieronder.
Je hebt dan wel een mooi rapport, maar als je de data verder wilt analyseren wordt het toch lastiger. We noemen deze tabelvorm ook wel een kruistabel: je leest de verkopen van categorie 2 van de maand maart op het kruispunt van de rij Cat 2 en Maart.

Schermafbeelding van verkoopcijfers van verschillende categorieën in diverse maanden in kruistabelvorm

Beter is het dan om je data in tabelvorm te zetten, zoals hier. De data staan per rij.
Je hebt dan veel meer mogelijkheden voor analyses en rapportages. Daarover een volgende keer.

Schermafbeelding van verkoopcijfers van verschillende categorieën in diverse maanden in kruistabelvorm

 

Schermafbeelding van functiearguenten voor vert.zoeken en verbodsbord

Een van de meest gebruikte functies in Excel is VERT.ZOEKEN (Engels VLOOKUP). Het is ook een van de functies waar het vreselijk vaak fout gaat. En het heeft ook nog eens enkele vervelende beperkingen. Weg ermee dus, want er is al een tijdje een beter alternatief!

Opfrisser: wat doet verticaal zoeken?

Voor wie het niet precies weet: verticaal zoeken zoekt in een lijst van boven naar beneden (verticaal dus) naar iets. Als dit is gevonden stopt het zoeken en wordt uit een kolom rechts ervan op dezelfde rij de celinhoud gehaald. Dat is dan het eindresultaat.

In het voorbeeld hieronder wil je van factnr 4 (B2) weten wat het bedrag is (dat moet in cel B3 komen) en de betalingsstatus (in B4). Voor het bedrag moet uit de tabel eronder de 5e kolom gebruikt worden en voor de betalingsstatus de 6e.

Schermafbeelding in Excel waar je verticaal zoeken gebruikt

Hieronder zie je dan welke berekeningen er in die cellen komen.

Schermafbeelding in Excel: succesvol toegepaste vert.zoeken-berekeningen met smiley

Kies je voor Factnr 3, dan gaat het mis: die staat niet in de lijst.

Schermafbeelding in Excel: verkeerd resultaat met vert.zoeken. Met teleurgestelde 'smiley'

Dat komt omdat het 4e argument van de functie hier niet is ingevuld. En als dat niet is gebeurd, wordt WAAR ingevuld. Dat betekent dat de best passende uitkomst gezocht wordt. Er wordt van boven naar beneden gezocht, dan is 4 te hoog en dus worden de gegevens van 2 gebruikt.

Schermafbeelding van dialoogvenster waarin aangegeven waarom het fout gaat met vert.zoeken: 4e argument!

Dat ondervang je door bij het 4e argument ONWAAR in te vullen. Die zoekt namelijk naar exact factnr 3. Maar die is er niet, dus krijg je de foutmelding #N/B (niet beschikbaar).

Schermafbeedling Excel met #NB-foutmelding voor vert.zoeken omdat een waarde niet voorkomt. Met boze "smiley'.

In je rapportage is dat niet zo fraai, dus moet je weer iets verzinnen om dat op te lossen!

Verticaal zoeken werkt helemaal niet als de tabel iets anders is opgezet, zoals hieronder.
Het factuurnummer staat nu achteraan. Verticaal zoeken kan niet zoeken in de kolommen links van de zoekkolom.

Schermafbeelding Excel met voorbeeld dat je vert.zoeken niet kunt toepassen. Met huilende 'smiley'.

Een ander bezwaar is, dat je bij een brede tabel moet gaan tellen uit welke kolom de celinhoud opgehaald moet worden (bijv. de 12e kolom).

Kortom: LASTIG en vervelend.
Weg ermee, want er is een veel beter alternatief!

Schermafbeelding van functiearguenten voor vert.zoeken en verbodsbord

Het veel betere alternatief: X.ZOEKEN (XLOOKUP)

Excel-gebruikers hebben jarenlang met deze beperking moeten werken. Gelukkig is er nu een alternatief. Hetzelfde plaatje nogmaals maar dan met die nieuwe functie X.ZOEKEN.

① Je geeft de zoekwaarde op (B2, je zoekt factnr 2), je selecteert in de tabel in welke kolom dit gezocht moet worden ② en je geeft de kolom waaruit de celinhoud gehaald moet worden ③.

Schermafbeelding met uitleg van de functie X.ZOEKEN

En als de zoekwaarde niet voorkomt (zoals bij factnr 3) kun je door een extra uitbreiding aan de functie ook nog een alternatief resultaat opgeven.

Schermafbeelding met het dialoogvenster Functieargumenten van X.ZOEKEN

Samengevat

Hieronder lees je hoe ik denk over deze 2 functies. Mijn ervaring meestal exact zoeken gebruikt worden (ONWAAR dus als 4e argument bij VERT.ZOEKEN), dus daarop zijn mijn oordelen gebaseerd.
Hiervoor het ik nog niet alle mogelijkheden van x.zoeken vermeld: de laatste twee argumenten van de functie heb ik overgeslagen. Ze zijn wel opgenomen in het schema hieronder.

Tabel met de 'eindstand' van de vergelijking verticaal zoeken en x.zoeken Automatisch gegenereerde beschrijving

Afbeelding met trofee voor X.zoeken: de winnaar! Automatisch gegenereerde beschrijving