Tag Archief van: excel

Pas op met kopiëren van tabelformules

Als lijsten, filteren, sorteren een onderwerp is bij een Excel-training, vertel ik altijd dat het verstandig is om van die gegevens een Excel-tabel te maken. Dat heeft heel veel voordelen. Een hele tijd geleden heb ik hierover al eens een videotip gemaakt “20 voordelen van Excel-tabellen en 1 nadeel”. Ondanks dat die al 6 jaar oud is, is die nog steeds actueel! Dit is de link: https://youtu.be/YOGNJyaDIKU

Maar naast dat ene nadeel dat genoemd is in die videotip, is ook een waarschuwing op zijn plaats. Namelijk over het kopiëren van formules die in zo’n tabel voorkomen. Daarover gaat dit blog.
Bekijk eventueel ook deze snelle korte tip over tabelformules: https://youtu.be/JWj_x4alq_k

Als voorbeeld gebruik ik deze data.

Schermafbeelding van gebruikte Excel-data: 1e kolom datums en in de kolommen erachter de verkopen van de kwartelen KW!, KW2 en KW3 in aparte kolommen

Even: hoe maak je een Excel-tabel?

Je maakt hiervan een tabel door het bereik te selecteren:

  • Invoegen > Tabel ①.
  • Pas indien nodig het voorgestelde bereik aan en controleer of de eerste rij inderdaad kopteksten (=kolomomschrijvingen) zijn en kies OK ②.
  • Je gegevens zien er dan anders uit: het is duidelijk dat ze bij elkaar horen ③!
    Er is ook een extra tabblad Tabelontwerp bijgekomen als een cel in die tabel is geselecteerd ④.
    Een tabel krijgt altijd een naam (Tabel1, Tabel2, etc) die je via dat speciale tabblad kunt aanpassen.

Schermafbeelding hoe deze data een tabel worden. Uitleg van de stappen

Tabelformules

Wanneer je berekeningen maakt in de tabel of daarbuiten met verwijzingen naar cellen in de tabel, worden standaard geen ‘normale’ celverwijzingen gebruikt (zoals =A2 of A1:A10).
In plaats daarvan zie je tabelformules. Die verwijzen met speciale teksten naar de elementen in zo’n tabel.

Overzicht van gebruikte elementen in tabelformules. Tabel1[#Kopteksten]=Kolomomschrijvingen Tabel1[#Totalen]=Totaalrij Tabel1[#Alles]-De hele tabel Tabel1[#Gegevens]=Alle rijen van de tabel Tabel1[Kolomnaam]=Kolom in tabel Tabel1[@]=Huidige rij in tabel Tabel1[@Kolomnaam]=Cel in huidige rij

Kopiëren van cellen (normale situatie)

Wanneer je een cel kopieert heb je hiervoor vele mogelijkheden. Natuurlijk “kopiëren & plakken” of de sneltoetsen hiervoor (Ctrl+c & Ctrl+v). Maar heel vaak wordt ‘doorvoeren’ gebruikt met de ‘vulgreep’. De vulgreep is dat blokje dat rechtsonder een cel staat en waar je muisaanwijzer een zwarte plus wordt. Door te slepen kun je kopiëren naar de aansluitende cellen (naar rechts, omlaag, links en omhoog).

Schermafdruk die toont wat de vulgreep is

Het maakt geen enkel verschil welke manier van kopiëren je gebruikt in Excel: het eindresultaat is altijd hetzelfde.

Kopiëren van tabelformules

Er is echter wel een verschil tussen kopiëren & plakken en doorvoeren met de vulgreep als het gaat om tabelformules!

Bij ① zie je de tabelformule =Tabel1[@KW1]+Tabel1[@KW2]: tel op de inhoud van de huidige rij van kolom KW1 (B2) en KW2 (C2).

  • Resultaat na “kopiëren & plakken”: =Tabel1[@KW1]+Tabel1[@KW2]
    De formule blijft hetzelfde: de verwijzingen blijven naar dezelfde cellen verwijzen ②.
  • Resultaat na doorvoeren naar rechts: =Tabel1[@KW2]+Tabel1[@KW3]
    De formule is aangepast: de verwijzingen zijn opgeschoven naar rechts en KW2 (C2)en KW3 (D2) worden opgeteld! ③ Schermafbeelding van de gewijzigde tabelformules

Je ziet het bijvoorbeeld ook bij deze formule: =SOM(Tabel1[KW1])
Hier worden de verkopen van kolom KW1 van de tabel allemaal opgeteld.

Schermafbeelding van de situatie die erboven is beschreven

Kopieer & Plak je dit naar de cel ernaast, dan blijft het de optelling van KW1.
Maar gebruik je doorvoeren naar rechts dan krijg je de optelling van KW2!
Zou je hem nog verder doorvoeren, dan gebeurt er nog wat anders, zoals je in onderstaande afbeelding ziet. Drie cellen naar rechts is het de optelling geworden van de 1e kolom van de tabel (de Datum-kolom).

Schermafbeelding van de situatie die erboven is beschreven.

Kun je ook ‘normale’ celverwijzingen gebruiken in tabellen?

Ja dat kan ook, maar dan moet je ze zelf typen en kun je niet op de cellen klikken (dan gebruikt Excel standaard de tabelformule).
Maar je moet je dus wel realiseren wat er gebeurt als je tabelformules kopieert!

Eventueel kun je bij de instellingen van Excel aangeven dat je die tabelformules niet wilt gebruiken.
Pas op: dit is een instelling voor jouw Excel-programma en is dus niet gekoppeld aan een bestand! Met andere woorden: collega’s die in hetzelfde bestand werken kunnen hiervoor een andere instelling hebben!

  • Bestand > Opties.
  • Kies de rubriek Formules >Schakel het selectievakje bij Tabelnamen gebruiken in formules uit.
    Schermafbeelding van het dialoogvenster waarin je het wel/niet gebruik van tabelformules regelt.

Moet je dan geen tabel meer gebruiken?

Nee, dat zou mijn advies absoluut niet zijn. De voordelen blijven wat mij betreft opwegen tegen dit nadeel! Realiseer je dus het volgende als je berekeningen maakt met gegevens uit een Excel-tabel.

  • Kopiëren & Plakken
    De tabelformule blijft hetzelfde.
  • Doorvoeren met de vulgreep
    De tabelformule wordt aangepast.
    In feite ‘gedraagt’ de tabelformule zich nu als normale celverwijzingen. Die zouden ook bij het kopiëren naar rechts aangepast worden van =B2+C2 naar =C2+D2 (op welke manier je die dan ook zou kopiëren).

En zorg dat als je Excel-bestanden gebruikt waar anderen ook in werken, dat zij dit ook weten!

 

Bespaar tijd met deze 10 Excel-tips

Poppetje met Excel-hoofd en klok in de hand

Een van de doelstellingen die mensen vaak aangeven bij een cursus, is dat ze tips willen hebben om sneller te kunnen werken. Ik heb daar al eerder een blog over gemaakt (https://toels-pc.nl/blog-sneller-werken). In dit blog 10 tips om tijd te besparen met Excel.
Tip 8 is een tip over een vrije nieuwe functionaliteit.

  1. Kopieer de cel erboven
  2. Kopieer de cel links van huidige cel
  3. Snel naar de onderkant gaan
  4. Snel tot de onderkant selecteren
  5. Kopieer tot de onderkant
  6. Combineer tips
  7. Snel navigeren naar een ander werkblad
  8. Gebruik de (nieuwe) navigatie
  9. Draai de instelling om met Ctrl
  10. Doorvoeren met de rechtermuisknop

Er zijn ook twee Snelle Korte Tips waarin deze tip worden gedemonstreerd. De eerste is te vinden via https://youtu.be/AHqQa7ZHMWE en de tweede volgt op 15 juli.

Tip 1 Kopieer de cel erboven

Met de sneltoets Ctrl+d kun je altijd de inhoud en opmaak van de cel erboven kopiëren.

Schermafbeelding: Als A7 is geselecteerd en je gebruikt Ctrl+d dan kopieer je de cel A6

Tip 2 Kopieer de cel links van huidige cel

Vrijwel vergelijkbaar: dat kan ook met de cel links van de actieve cel met de sneltoets Ctrl+r.

Schermafbeelding: Als B2 is geselecteerd en je gebruikt Ctrl+2 wordt cel A2 gekopieerd.

Tip 3 Snel naar de onderkant gaan

Je hebt een (lange) lijst en wilt snel naar de onderkant. Hiervoor is een sneltoets Ctrl+↓.
Voorwaarde is dat de kolom geen lege cellen bevat, want anders ga je met deze sneltoets naar de cel bóven de eerste lege cel. Je moet het dan enkele keren herhalen.

Schermafbeelding: Als de A-kolom is gevuld tot en met A20 en A2 is geselecteerd, dan zal Ctrl+omlaag cel A20 selecteren

Tip 4: Snel tot de onderkant selecteren

Als je Shift ingedrukt houdt en dan de pijltoetsen gebruikt, selecteer je de cellen vanaf het startpunt.
Combineer je deze tip met de vorige: met Ctrl+Shift+↓ selecteer je vanaf de startcel tot de onderkant. Dit werkt ook als je bijvoorbeeld met =SOM( zo’n bereik wilt selecteren.

Schermafbeelding: Als de A-kolom is gevuld tot en met A20 en A2 is geselecteerd, dan zal Ctrl+Shift+ omlaag cel A2: A20 selecteren

Tip 5: Kopieer tot de onderkant

In een lange lijst kun je een berekening heel snel naar de andere cellen van de lijst kopiëren als de kolom ervoor ook is gevuld: met een dubbelklik op de vulgreep!

Schermafbeelding: Als er een berekening staat in D2 en de C-kolom ervoor is gevuld tot C20, dan zal dubbelklikken op de vuilgreep de berekening kopiëren tot en met D20

Tip 6: Combineertip

De vorige tip is handig, maar soms moet je het naar beneden kopiëren en is de kolom ervoor niet (helemaal) gevuld. Dan werkt dat niet!

Schermafbeelding waarbij in de kolom ervoor lege cellen staan: dan werkt dubbelklikken op de vulgreep niet

Daarom een workaround.

  • ① Ga naar de onderkant van de lijst in een andere wél helemaal gevulde kolom (sneltoets: Ctrl+↓).
  • ② Ga vervolgens in dezelfde rij naar de kolom waarvan je de inhoud wilt kopiëren.
  • ③ Gebruik de sneltoets Ctrl+Shift+↑ om te selecteren tot en met de cel die je wilt kopiëren.
  • ④ Gebruik de sneltoets Ctrl+d om de inhoud van de bovenste cel te kopiëren naar de andere geselecteerde cellen.

Schermafbeelding van de beschreven stappen

Tip 7: Snel navigeren naar een ander werkblad

Zeker als er veel werkbladen in een bestand zijn is dit een handige tip.

  • Klik met de rechtermuisknop op de navigatieknoppen bij de werkbladen.
  • Kies het werkblad waar je naar toe wilt gaan en OK (of dubbelklik erop).

Schermafbeelding waarbij met de rknop geklikt wordt op de navegiatieknoppen rechts van de werkbladen. je krijgt dan een venster met alle werkbladen.

Tip 8: Gebruik de (nieuwe) navigatie

Sinds een tijdje heeft Excel ook een handige navigatiemogelijkheid in het menu Beeld [View].
Schermafbeelding met de beschreven stappen

  • Kies Beeld > Navigatie ①.
  • Je hebt nu een overzicht van alle werkbladen ② en klikt op het blad waar je heen wilt.
  • Via de rechtermuisknop kun je een blad een andere naam geven, verbergen en verwijderen ③.
  • Via de driehoek kun je elementen zien op een blad, zoals bereiken, tabellen en namen en grafieken ④.

Tip 9: Draai de instelling om met Ctrl

Slepen met de linkermuisknop kan ook met Ctrl ingedrukt. Vaak wordt er dan net een andere actie uitgevoerd.
In tip 5 heb je gezien hoe je met de vulgreep gegevens kunt doorvoeren door te slepen. Wat er dan gebeurt is afhankelijk van de celinhoud. Tekst, getallen en berekeningen worden gekopieerd, van een datum wordt een reeks gemaakt en van speciale teksten ook.
Met Ctrl draai je dit om!

Schermafbeelding wat er gebeurt bij slepen met de linkermuisknop en wat er gebeurt als dan ook de Ctrl-toets wordt ingedrukt.

  • Zet de muis op het blokje: wacht tot je een zwarte plus ziet! ①
  • Dit is de standaardmanier zonder ctrl ②.
  • Sleep met Ctrl ingedrukt met de linkermuisknop ③.
    De situatie wordt omgedraaid bij getallen, datums en ‘bekende’ teksten!

Tip 10: Doorvoeren met de rechtermuisknop

Aanvullend op de tip hierboven: je kunt ook slepen met de rechtermuisknop. Excel maakt dan geen keuze voor wat je gaat doen, maar je kiest zelf uit een menu.

Schermafbeelding van verschillende menu's als er gesleept wordt met de rechtermuisknop in plaats van met links.

 

Het nieuwe lettertype in Office-programma’s

Misschien dat je het al hebt gemerkt: Microsoft heeft in de Office-apps (Word, Excel, PowerPoint en Outlook) sinds een tijdje een ander lettertype als je begint met een nieuw leeg document. Tot een tijdje geleden was het standaardlettertype Calibri en nu is dat Aptos.
Los van het feit of je het een ‘mooi’ lettertype vindt: velen vinden het hinderlijk dat er weinig onderscheid is tussen vette tekst in Aptos en normale tekst.

Tijdens mij trainingen krijg ik daarom vaak de vraag of dit aangepast kan worden. En het antwoord is: ja, dat kan. Maar het gaat niet in alle Office-apps op dezelfde manier.
In deze blog laat ik je zien hoe je terug kunt keren naar Calibri of naar een lettertype dat jouw voorkeur heeft voor nieuwe bestanden. Ik heb bijvoorbeeld voorkeur voor Verdana.

LET OP: als je een sjabloon gebruikt, bijvoorbeeld voor document in de huisstijl van je bedrijf, zal het deze lettertypewijziging niet van belang zijn. En onderstaande stappen gelden voor nieuwe lege documenten die je gaat maken in de programma’s.

Wat is een nieuw leeg bestand eigenlijk?

Het is in ieder geval niet een bestand waarin niets is geregeld. Sterker nog: er is al heel veel geregeld. Denk bijvoorbeeld aan het papierformaat, de marges, het lettertype, de uitlijning, is het staand of liggend.
Wat ik bedoel is: het bestand dat je maakt als je kiest voor Bestand > Nieuw > Leeg … [File > New > Blanc ... Of met de sneltoets Ctrl+n.

Standaardlettertype wijzigen in Word

In Word is het centrale woord hierbij de sjabloon NORMAL.DOTM. Dat is de basis voor een nieuw leeg Word-document. Je kunt zonder standaardlettertype wijzigen, zonder dat je deze speciale sjabloon opent.

  • Maak in een document via Start > Lettertype [Home > Font] de gewenste instelling via de knop bij ①.
  • Je krijgt dan het dialoogvenster Lettertype waar je de gewenste instelling maakt ②.
  • Daarna kies je voor Als standaard instellen [Set as Default] ③.
  • In het dialoogvenster dat dan verschijnt kies je de onderste optie ④.
  • Misschien dat je bij het afsluiten van Word nog een vraag krijgt of je de wijzigingen in NORMAL.DOTM wilt opslaan. Kies dan Ja.

Schermafbeelding met de verschillende stappen voor het instellen van een nieuw lettertype in Word.

Standaardlettertype wijzigen in Excel

Hier moet je naar de standaardinstelling van Excel zelf om het lettertype voor nieuwe lege werkmappen te vervangen.

  • Kies Bestand > Opties [File > Options].
  • Kies links de rubriek Algemeen [General] ①.
  • Blader naar het onderdeel Wanneer nieuwe werkmappen worden gemaakt [When creating new workbooks].
  • Kies daar het gewenste lettertype en lettergrootte ②.
    Schermafbeelding met de verschillende stappen voor het instellen van een nieuw lettertype in Excel.

Je krijgt een melding dat het pas ingaat als je Excel afsluit en daarna weer start.

Dialoogvenster met de extra waarschuwing.

Standaardlettertype wijzigen in PowerPoint

In dit programma is het iets ingewikkelder.

  • Maak een nieuwe lege presentatie.
  • Kies Ontwerpen ① > menu bij Varianten > Lettertypen ② [Design > Variants > Fonts] >kies Office 2013-2022 ③ (of maak een andere keuze).
    Schermafbeelding met de verschillende stappen voor het instellen van een nieuw lettertypethema in PowerPoint
  • Je hebt nu het lettertype gewijzigd voor de huidige presentatie.
    Nu moet dit nog doorgevoerd worden als je nieuwe standaard.
  • Houd de presentatie verder leeg en kies Bestand > Opslaan als [File > Save as].
    • Kies Bladeren [Browse].
    • Geef het bestand de naam Blank ①.
    • Geef als type op PowerPoint-sjabloon [PowerPoint Template] ②.
      WAARSCHUWING: Omdat je kiest voor het bestandstype “sjabloon” zal het bestand automatisch op de juiste plaats worden opgeslagen. Kies dus niet een andere locatie!
      Schermafbeelding hoe je het bestand voor de nieuwe lege presentatie moet opslaan in PowerPoint.
  • Nu moet je opletten. Je begint een nieuwe lege presentatie vanaf nu met Bestand > Nieuw > Default Theme [File > New > Default Theme] en niet meer met Lege presentatie [Blank Presentation]!
    Schermafbeelding welke nieuwe presentatie je moet kiezen voor het juiste lettertype in PowerPoint.

Alternatief: je kunt in de laatste stap het bestand ook opslaan als een sjabloon met een andere naam, bijvoorbeeld Mijn Lege Presentatie. Die gebruik je dan steeds als basis voor nieuwe

Standaardlettertype wijzigen in Outlook

In Outlook wijzig je het weer op een andere manier.

  • Kies Bestand > Opties [File > Options].
  • Ga naar de rubriek E-mail [Mail] ①.
  • Kies Briefpapier en Lettertype [Stationary and Fonts] ②.
  • Kies in het vervolgscherm voor het tabblad Persoonlijk e-mailpapier [Personal Stationary] ③.
  • Kies Lettertype onder Nieuwe e-mailberichten [Font New mail messages] ④ en maak kies daarna het lettertype en evt. grootte ⑤.
    Schermafbeelding met de verschillende stappen voor het instellen van een nieuw lettertype in Outlook.

Werken met vensters/schermdelen

Decoratief: verschillende citaten als "dit had ik eerder moeten weten".

Vaak heb je meer bestanden tegelijk geopend, soms zelfs meer per programma. Je hebt bijvoorbeeld 2 Word-documenten of Excel-bestanden tegelijk geopend. Hoe kun je daar nu handig mee werken? Of je nu één scherm hebt of met meer schermen werkt: deze tips kunnen altijd handig zijn. Vaak krijg ik een “had ik dit maar geweten” als ik het tijdens een training gebruik.

Windows-manier: twee venster onder/naast elkaar

Een standaardmanier van Windows om dit snel voor elkaar te krijgen is met een sneltoets:

  • Windows+→ (of met ←↓↑)
    zet het huidige venster rechts op het scherm zetten en toont de andere links. Klik op het venster dat je links wilt zien.
    Schermafbeelding van gebruik Windows-toets en pijl rechts: je kunt daarna een venster kiezen dat links moet komen.
  • In Windows 11 kun je bij een venster ook op het knopje wijzen en dan kiezen op welke manier je het scherm ingedeeld wilt hebben. Het huidige venster komt dan op die plaats en daarna kun je voor de overige vensters ook op een schermdeel klikken.
    Schermafbeelding van de knop Vorig formaat/Maximaliseren van een Windows-venster en de schermindeling die je dan ziet.

Binnen een programma overschakelen naar ander bestandsvenster

Wanneer je met Word, Excel of PowerPoint twee (of meer) bestanden geopend hebt, kun je in deze programma’s naar die andere geopende bestanden met het lint of met een sneltoets.

  • Ctrl+F6 schakelt over naar het volgende geopende bestand en dat kun je herhalen: je komt vanzelf weer terug bij het bestand waarmee je begon.
    Shift is ook hierbij de ‘omdraaitoets’: je gaat met Ctrl+Shift+F6 in dezelfde volgorde weer terug.
  • Via het lint doe je dit met Beeld > Ander venster. Je ziet dan alle geopende bestanden en kiest welke je wilt zien.
    Schermafbeelding van de knop Ander venster op de tab Beeld in het lint.

Twee delen van een bestand gelijktijdig zien

Soms is het handig om 2 delen van een programma tegelijk te kunnen zien. Het meest wordt dit gebruikt in Excel: je wilt bijvoorbeeld de gegevens op rij 100 zien, maar ook van de bovenste rijen. In PowerPoint kan het soms handig zijn bij het maken van een dia als je een andere dia ook kunt zien. In Excel is het handig als je verschillende werkbladen tegelijkertijd wilt zien.

Hierover heb ik ook een videotip gemaakt. Klik hier om die te bekijken.

  • Met Beeld > Nieuw venster maak je voor het document waar je mee bezig bent een tweede venster. Je zit dat in de titelbalk: er komt een nummer bij. Dit kun je herhalen voor een 3e, 4e enz aantal venster.
    Schermafbeelding van de titelbalk met de naam van een document en daarachter een nummer (1/2/etc).
  • Met Beeld > Alle vensters kun je deze vensters dan samen rangschikken op het scherm.

Waarschuwing met effen opvulling Waarschuwing met effen opvulling Waarschuwing met effen opvulling

Met Word en PowerPoint worden niet alleen de verschillende vensters van hetzelfde bestand hiermee geranhschikt op het scherm, maar óók andere geopende bestanden.
Excel heeft het wat dat betreft beter geregeld: die vraagt bij Alle vensters of het gaat om alle venster die er in het programma geopend zijn ①, of alleen de vensters van de actieve werkmap ② (=huidige bestand).
Schermafbeelding van het dialoogvenster in Excel Alle vensters.

Scherm splitsen

Word en Excel hebben de mogelijkheid het scherm te splitsen.

  • Beeld > Splitsen.

Je scherm wordt in tweeën gedeeld, maar blijft in hetzelfde venster. Elk deel heeft een schuifbalk ①, zodat je door te scrollen een ander deel van het bestand op het scherm kunt weergeven.
Door je muis op een splitsing te plaatsen kun je die verplaatsen ②.

De splitsing ongedaan maken doe je weer via het lint met Beeld > Splitsing verwijderen.

Schermafbeelding van Word net eeb in twee delen gesplitst venster.

Ook hier is het in Excel weer net even anders. Die splitst het scherm in 4 delen. Waar exact, hangt af van de actieve cel. Alle rijen erboven worden de horizontale splitsing en alle kolommen links ervan de verticale. Heb je zoals in de afbeelding I28 geselecteerd ①, dan zullen de eerste 27 rijen het bovenste schermdeel vormen en de kolommen A:H het linkerschermdeel. Scrollen doe je met de schuifbalken ②.

Schermafbeelding van een splitsing in Excel met 4 schermdelen.

Titels blokkeren op het scherm en op de afdruk

Excel heeft daarnaast de mogelijkheid om titels te blokkeren. Dat betekent dat de eerste rijen/kolommen op het scherm altijd blijven staan. Ook kun je titels blokkeren voor het afdrukken (of het maken van een PDF). Dat geef je apart aan.

Titels blokkeren op je scherm

  • Beeld > Blokkeren.
    Schermafbeelding van de knop Blokkeren in Excel.
  • Maak een keuze.
    • Titels blokkeren werkt op basis van de actieve cel (net als splitsen). Alle rijen erboven en alle kolommen links van de actieve cel worden vastgezet.
    • Bovenste rij blokkeren zet alleen rij 1 vast.
    • Eerste kolom blokkeren zet alleen kolom A vast.

Titels blokkeren bij het afdrukken of maken van een PDF

  • Kies Pagina-indeling > Afdruktitels.
  • Geef aan welke rij(en) en welke kolom(men) je op elke pagina herhaald wilt hebben.
    Schermafbeelding van het dialoogvenster Pagina-instelling in Excel , tab Blad waar je de afdruktitels in kunt stellen.

Waarschuwing met effen opvulling Waarschuwing met effen opvulling Waarschuwing met effen opvulling

LET OP:

  1. Titels blokkeren en Afdruktitels zijn instellingen die opgeslagen worden. Dat geldt niet voor de andere schermindelingsopties die hier besproken zijn. Ook hier weer een uitzondering voor Excel: splitsen wordt ook in Excel opgeslagen!
  2. Wil je een andere titelblokkering dan de huidige, dan moet je eerst kiezen voor Blokkeren > Titelblokkering opheffen.

Extra tips voor Excel-gebruikers

Soms wil je titels blokkeren gebruiken, maar gaat het eigenlijk alleen maar om één belangrijke rij. De rest staat er ‘toevallig’ boven. Bekijk onderstaande voorbeeld maar eens De eerste rijen bevatten informatie die niet per se op het scherm hoeft te blijven. Dat is pas van belang vanaf rij 10 (als je de datum ook wilt zien) of rij 11.

Schermafbeelding met 10 rijen met info, maar vanaf rij 10 moet het in beeld blijven. De andere zijn niet belangrijk.

Hoe regel je dat dan?

  • Je zorgt dat de eerst getoonde regel rij 10 is ①.
  • Je selecteer daarna rij A12 en kies Beeld > Blokkeren > Titels blokkeren.
    Je krijgt dan de situatie van ②.
    Schermafbeelding hoe je toch titels kunt blokkeren en die overbodige gegevens er niet blijven staan.

Heb je een voorbeeld als dit, dan vormen de cellen van rij 11 in feite een tabel. Maak er dan een Excel-tabel van, want dan hoef je de eerste rij met de kolomomschrijvingen niet te blokkeren. Dat gebeurt dan automatisch!

Is het een getal of niet?

Afbeelding met een vraagteken: twee lijsten met getallen. Zoek de verschillen (die zijn er niet)

Is het nu een getal of niet?

Een geregeld terugkerend probleem in Excel is de vraag waarom een berekening niet werkt. Dan kan de formule die is gemaakt wel goed zijn, maar de cellen waarmee gerekend worden bevatten dan vaak niet de juiste ‘soort’ gegevens.
Kijk maar eens naar dit voorbeeld.

Zoek de verschillen!

Zie jij een verschil tussen de gegevens in kolom A en D

Schermafbeelding: 2 kolommen met getallen die hetzelfde lijken. Beide kolommen zijn getallen met een euroteken ervoor.

Toch is er een belangrijk verschil, kijk maar als je ermee wilt rekenen. In zowel kolom B als in E staat de berekening dat de cel ervoor vermenigvuldigd moet worden met 10.

Schermafbeelding hier zie je met een berekening naast beide kolommen dat de ene kolom een foutmelding geeft (WAARDE) en de ander de juiste uitkomt.

Hetzelfde probleem zie je in onderstaand voorbeeld. Ook hier lijken er geen verschillen tussen de kolommen A en D, maar als je die cellen vermenigvuldigt met 10 zie je het verschil.

Schermafbeelding waarbij de getallen in de kolommen A en D hetzelfde lijken. Maar door de berekeningen in de kolommen B en E zie je hetzelfde als in de vorige afbeelding. Een kolom met een foutmelding en een met de uitkomst van de berekening

Een ander voorbeeld met een datum. Zoals je wellicht weet is een datum in Excel een getal. Daarom kun je ermee rekenen, zoals je ziet in kolom E: de functie MAAND berekent het maandnummer uit een datum. Maar in kolom B, waar dezelfde maandfunctie is gebruikt werkt dit niet.

Schermafbeelding Vergelijkbare afbeelding, maar nu met datums die hetzelfde lijken. Ook hier weer een kolom met een foutmelding bij een berekening en de echte uitkomst

Wat betekenen de foutmeldingen?

De foutmelding #WAARDE! betekent vaak dat er gerekend wordt met een tekst.

Kijk maar.

Schermafbeelding: je ziet via het waarschuwingspicotgram waarom de berekening een foutmelding oplevert

De foutmelding #GETAL! krijg je als een formule of functie een ongeldige numerieke waarde heeft.
Wanneer je het waarschuwingspictogram gebruikt bij de voorbeelden van eerder, dan is de melding veel cryptischer!

Schermafbeelding: hier is de melding via het waarschuwingspictogram minder duidelijk.

Wat is een gegevenstype?

Vooral bij berekeningen is het gegevenstype van een cel belangrijk. Het gegevenstype geeft aan wat voor een soort gegeven het is. Excel kent als gegevenstypes vooral getal en tekst. En zoals aangegeven kun je met een tekst niet rekenen!

Hoe herken je een getal als tekst?

Natuurlijk zie je het direct als je een berekening wilt uitvoeren, omdat je dan een foutmelding krijg. Een andere manier om het te ontdekken: wijzig de opmaak van de cel.
Kies je bijvoorbeeld voor minder decimalen bij de getallen in de A-kolom, dan zal er niets gebeuren. Terwijl dat wel effect heeft op ‘echte’ getallen zoals in kolom D.

Schermafbeelding hoe je door het aantal decimalen te wijzigen (knop in het lint) kunt zien of iets een getal is of niet

Bij een datum is het eenvoudiger om het te ontdekken. Selecteer de cel en kijk in de formulebalk. Als het een echte datum is zie je daar altijd d-m-jjjj.

Schermafbeelding hoe je het verschil kunt zien tussen de 2 datums (in de fbalk)

Tips voor het invoeren van getallen

Als je een getal of een datum wilt typen, typ dan altijd de simpelste manier. Maak dan het getal op zoals je het graag wilt zien.

Enkele voorbeelden:

Invoer: 1000
Gewenste opmaak: € 1.000,00
Kies dan uit menu:
Schermafbeelding over het kiezen Valuta-notatie

Invoer: 5,5
Gewenste opmaak: 5,50
Kies dan uit menu:
Schermafbeelding over het kiezen Getal-notatie

Invoer: 1234,5
Gewenste opmaak: 1.234,50
Kies dan uit menu Meer getalnotaties en dan:
Schermafbeelding over het instellen van een getalopmaak via het dialoogvenster Celeigenschappen

Hoe los je het probleem met ‘verkeerde getallen’ op?

Meestal komen deze “verkeerde” gegevens komen uit een ander systeem, bijvoorbeeld een boekhoudprogramma, een CRM-systeem of een download van een bank.
Hoe zet je dat dan om naar een echt getal waar je ook mee kunt rekenen?

Hiervoor zijn verschillende manieren.

Groene driehoekje

Als je een groen driehoekje bij een cel ziet staan, is dat een waarschuwing. Dat kan een waarschuwing zijn voor een mogelijk verkeerd gegevenstype, maar ook dat er een formule een foutmelding als resultaat heeft (zie afbeeldingen hierboven).
Plaats je de muisaanwijzer op het pictogram, dan kun je de waarschuwing lezen. Onderstaande melding gaat over een verkeerd gegevenstype in die cel. Dat is dan eenvoudig op te lossen.
Schermafbeelding met de melding als je wijst op het waarschuwingspictogram

  • Selecteer alle cellen met deze melding.
  • Klik op het waarschuwingspictogram (dat staat als je bijvoorbeeld een kolom hebt geselecteerd boven- of onderaan).
  • Kies Converteren naar getal.

Schermafbeelding met melding dat getal als tekst omgezet kan worden naar echt getal

Kopiëren en plakken speciaal

Een beetje een vreemde oplossing is dit, maar het werkt vaak wel.

  • Zet ergens in een lege cel een 1.
  • Kopieer die cel.
  • Selecteer de cellen met het verkeerde gegevenstype
  • Kies Plakken speciaal
  • Vul het dialoogvenster zo in en kies OK.
    Schermafbeelding van het dialoogvenster Plakken speciaal waar als bewerking Vermenigvuldigen is gekozen

Verkeerde datum

Hierboven heb ik al aangegeven hoe je een echte datum kunt herkennen: selecteer de cel en kijk in de formulebalk. Staat daar inderdaad gewoon een groot getal, dan kun je daar niet zomaar een datum van maken door er een datumopmaak voor te kiezen. Je moet er echt een datum van maken. Dat gaat het handigste op de volgende manier.

  • Selecteer de cellen met de datums
  • Kies Gegevens > Tekst naar kolommen.
  • Kies in de eerste stap Gescheiden > Volgende
  • Zorg in de 2e stap dat er geen scheidingsteken is geselecteerd (het is namelijk helemaal niet de bedoeling om de gegevens te scheiden!) > Volgende.
  • Kies in de derde stap Datum en selecteer de manier waarop in de selectie de datumnotatie staat (hier dus JMD: jaar, maand, dag) > Voltooien.

Schermafbeelding met de 3 stappen van de wizard Tekst naar kolommen

En nu zijn het allemaal echte datums geworden (ja ik gebruik expres ‘datums’ om het onderscheid te maken met data=gegevens).

 

Filter-functie

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.

Maak altijd een tabel!

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!

Gegevensvalidatie

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

Filteren met slicers

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.

Afbeeldingen in Excel

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!