Gebruik nooit samenvoegen in Excel

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

 

Weg met vert.zoeken!

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

 

Sneller werken

Poppetje met duim omhoog en GOED-vinkje. Symbool voor Sneller Slimmer Beter

Vrijwel altijd komt in de introductiedeel van een training op mijn vraag “wat wil je vandaag leren” als antwoord “… en ook tips om sneller te werken”.
Maar hoe kun je nu “sneller werken”? In de eerste plaats is dat “weten wat er kan in een programma”, zodat je niet iets met een vreselijke omweg gaat uitvoeren.
Verder is werken met toetsencombinaties een manier om sneller te werken. Hiervoor kun je vaak terecht in de Help met als zoekterm “sneltoets” of “shortcut”. Diezelfde termen kun je ook gebruiken om te googlen. Zet er dan vooral de programmanaam bij als je zoekt!
Sneltoetsen werken sneller omdat de muis gebruiken extra handelingen geeft. Je moet de muis oppakken, dan de juiste plaats aanwijzen op het scherm en dan klik je ergens. Ik noem dat altijd “pakken-richten-schieten”.

Over die sneltoetsen gaat deze blog niet. Wel over dubbelklikken met diezelfde muis.
Hieronder heb ik, met Excel als voorbeeld, enkele handige dubbelkliks beschreven.
Veel van die dubbelkliks werken niet alleen in Excel, maar ook in andere programma’s!

1.    Dubbelklik in dialoogvensters (werkt in vrijwel alle programma’s)

Vaak hoef je niet te klikken op OK in een dialoogvenster, omdat dubbelklikken ook werkt.
Doe dit niet bij selectievakjes ☒, want dan is dubbelklikken Aan en weer UIT.

2 voorbeelden van dialoogvensters waarin dubbelklikken sneller is

2.    Formule bewerken en tegelijk controleren (specifiek Excel)

Als je op een cel met een formule dubbelklikt, kun je de formule direct bewerken. Je hoeft dus niet in de formulebalk bovenaan te klikken.
Het is ook een manier om te controleren of je de juiste cellen gebruikt in de berekening, want dat geeft Excel aan met kleuren (maar dat werkt ook in de formulebalk).

Schermafbeelding waarin je met kleuren ziet van de cellen waarmee gerekend worden

Pictogram van een wekker waar alarm afgaat, ten teken van LET OP!  LET OP!

Zorg wel dat je dubbelklikt met de juiste muisvorm!
Een grote witte plus: je bewerkt de celinhoud.
Klik je op een van de randen (muisvorm: soort windroos met pijl), dan ga je een andere cel selecteren: rechterrand naar meest rechtse nog gevulde cel, onderrand naar onderste gevulde cel.

Schermafbeelding wat er gebeurt als je dubbeklilkt op de onderrand of rechterrand

3.    Kopiëren naar beneden (specifiek Excel)

Wil je de celinhoud van bijvoorbeeld een formule net zover naar beneden kopiëren als in de cel ervoor of erachter? Dubbelklik dan op het blokje rechtsonder bij de cel (let op de muisvorm: een zwarte plus!).

Schermafbeelding wat er gebeurt bij dubbeklikken op de vulgreep

4.    Meer ruimte voor je werk (werkt in alle MS Office-programma’s)

Ik heb mijn lint altijd helemaal geopend, zodat ik de knoppen altijd zie. Maar soms wil je zo veel mogelijk van het werkgebied in een programma op het scherm zien.
Door te dubbelklikken op een van de tabs in het lint kun je die snel open en dichtvouwen.

Schermafbeelding van het lint opengevouwen en dichtgevouwen

5.    Schermvullend maken en terug (werkt in programma’s met Windows als besturingssysteem)

Meestal werk ik met het programma helemaal schermvullend (maximaal) om zoveel mogelijk te zien.
Als je dubbelklikt in de titelbalk van een programma, maak je het venster maximaal. Vaak werkt dit ook op een lege plek in het lint.
Doe je hetzelfde nog een keer dan krijgt het venster weer de vorige venstergrootte.

Hetzelfde dus als de knoppen rechtsboven bij een venster.

Schermafbeelding van locatie om te dubbelklikken voor schermvullend of niet

6.    Dubbel voordeel: Speciale tabbladen sneller zichtbaar en opmaakvenster (werkt in veel MS Office-programma’s)

Je kent de context-tabbladen wel. Als je een afbeelding invoegt of een grafiek maakt dan verschijnt in het lint direct het tabblad voor dat speciale onderdeel (die context), vandaar de naam ①.
Als je verder werkt en op de grafiek of afbeelding klikt, wordt de afbeelding of grafiek alleen geselecteerd: de speciale tab wordt niet geactiveerd ②.
Dubbelklik je op de grafiek/afbeelding, dan gebeurt dat wel.
Bij een grafiek heeft dat nog een ander voordeel: rechts verschijnt ook het deelvenster om het onderdeel waarop je dubbelklikte verder op te maken ③.

Schermafbeelding van gafiek met contextgevoelig tabblad en speciaal deelvenster

7.    Kolombreedte (werkt in veel programma’s bij kolombreedtes)

Door te dubbelklikken op de scheiding tussen twee kolommen, kun je een kolom best passend maken ①. Dat weten veel mensen wel.
Maar dat kan ook voor meer kolommen tegelijk: selecteer meer kolommen en dubbelklik dan op de scheiding bij één van de geselecteerde kolommen ②.

Schermafbeelding hoe je verschillende kolommen tegelijk best passend kunt maken

 

Digitoegankelijk

3 niet bestaande toetsen die symbool staan voor toegankelijk maken

Digitoegankelijke Word-documenten

logo Word, oog met streep en duimpje mhoog

Nog niet zo lang geleden werd ik benaderd door een bedrijf met de vraag of ik een Word-sjabloon ‘digitoegankelijk’ wilde maken. Voor overheidsorganisaties is dit intussen al verplicht, maar het is natuurlijk altijd goed om te zorgen dat documenten ook ‘leesbaar’ zijn voor mensen met een handicap. Er zijn in Nederland een half miljoen mensen die hulpmiddelen gebruiken om digitale documenten te kunnen lezen. Zo’n document wordt dan voorgelezen.

Maar het moet hier dan wel geschikt voor zijn. In deze blog laat ik je enkele eenvoudige tips zien om te zorgen dat jij ook voor digitoegankelijke Word-documenten kunt zorgen.

Wist je trouwens dat Word ook een controle-hulpmiddel voor toegankelijkheid heeft? En dat ik een YouTube-video heb gemaakt over digitoegankelijk maken van afbeeldingen? Dit is de link naar die video https://youtu.be/aaCdRiopdc0.

Maar eerst die eenvoudige tips.

Gebruik de stijlen voor koppen

Ook voor jezelf is het werken met stijlen voor koppen (Kop1, Kop2, enzovoort of in de Engelse versie Heading 1,2,3) handig, want het brengt direct overzicht en structuur aan in je document. Je kunt bovendien via Beeld > Navigatiedeelvenster een handig overzicht op je scherm houden tijdens het bewerken en snel naar een onderdeel toe gaan.
Zet alle hoofdstukken in Kop1 (1e niveau), paragrafen in Kop2 (2e niveau) enzovoort. Je kunt tot 9 niveaus gaan!

Knop voor stijl Kop1 in het lint

  • Selecteer de alinea van het hoofdstuk/paragraaf/subparagraaf
  • Klik op Kop1/Kop2/Kop3

De ‘platte tekst’ zet je in Standaard (Engels: Normal)

Afbeeldingen

Zorg dat je afbeelding in de tekstregel staan (Engels: in line). Als dat niet het geval is staan ze (technisch gezien) in een andere documentlaag en lukt het ‘voorlezen’ niet.

Knop naaast afbeelding om "In tekstregel' te controleren en in te stellen (1) en menu van rechtermuisknop bij afbeelding om vervangende tekst te typen (met deelvenster erbij)

  • Klik op de afbeelding om die te selecteren en klik op de knop (1) die je dan ziet > In tekstregel (Engels: In Line With Text)

Dat brengt meteen het volgende: een afbeelding moet een beschrijving hebben in de vorm van een ‘alternatieve tekst’. Daarin vertel je wat er in de afbeelding staat en dat al worden voorgelezen.

  • Klik met de rechtermuisknop op de afbeelding (2) > Vervangende tekst weergeven (of Alternatieve tekst).
  • Accepteer de tekst die automatisch is gemaakt of vervang die met een eigen beschrijving.

 

Het nadeel van afbeeldingen in de tekstregel is dat de tekst er niet naast kan doorlopen zoals bij 1. Hoe los je dat dan op?

Heel simpel: door het in een tabel te zetten zoals bij 2‚.

Voorbeeld van afbeelding met tekst ernaast en in een tabel

  • Maak een tabel van 2 kolommen. Zet in de ene kolom de afbeelding en in de andere de tekst. Haal bij de tabel de lijnen weg (bij ‚ zijn de lijnen verwijderd, maar de rasterlijnen niet, zodat je ziet dat het in een tabel staat: maar ook die kun je weghalen als je wilt).

Titel

Elk opgeslagen document heeft metagegevens. De bekendste zijn bijvoorbeeld de datum waarop het is aangemaakt en voor het laatste is bewerkt. Maar ook een ‘titel’ is een van de metagegevens. Geef elk document een titel.

  • Kies Bestand > Info.
  • Klik bij Titel en typ hier een goede omschrijving.

Bestand Info waar je titel kunt opgeven

Hyperlinks

Vaak zie je dat er hyperlinks achter een tekst of een afbeelding schuilgaan. Voor de digitale toegankelijkheid is het verstandiger om die hyperlink zelf weer te geven.
Dus https://digitaaltoegankelijk.nl/kennisbank/hoe-maak-ik-een-pdf-en-word-document-digitaal-toegankelijk/ in plaats van lees hier verder.

Enter-gebruik

Maak geen extra witruimte door enkele keren achter elkaar Enter te gebruiken. Die lege ruimtes worden namelijk ook voorgelezen.
Vaak zie je dat enkele keren Enter wordt gebruikt om extra ruimte na een hoofdstuktitel of over vóór een paragraaftitel te krijgen. Vervang dat door alinea-afstand.

  • Klik met de rechtermuisknop in een alinea > kies Alinea.
  • Vul bij Voor en/of Na de extra ruimte in die je wilt hebben.
    Standaard wordt dit aangegeven met ‘pt’, maar je kunt ook zelf # mm of # cm typen.

deel van Alinea-venster voor opgeven alinea-afstand

Contrast

Ik erger met al vaak aan witte letters op een gele achtergrond als ik een etiket moet lezen op een artikel: te weinig contrast!
Voorleeshulpmiddelen hebben ook problemen met weinig contrast. Zorg dus voor voldoende contrast tussen de achtergrond- en de tekstkleur.

Ik heb ook problemen met  een 5 punts letter die ik alleen met een leesbril of vergrootglas kan lezen. Daar hebben de controlehulpmiddelen dan weer geen last van .

Controleer je document

Op elk moment kun je het document controleren op toegankelijkheid.

  • Kies (1) Controleren > Toegankelijkheid controleren (Engels: Review > Check Accessibility)

Rechts verschijnt een rapport over je document. In de afbeelding hieronder zie je een voorbeeld (2).

Knop in lint voor controleren Teogankjelijkheid en deelvenster et resultaat

Klik je op een van de gevonden problemen dan kun je de fout vaak ook oplossen (3).

Als je het selectievakje inschakelt (4), krijg je al tijdens het werken met Word tips voor de toegankelijkheidscontrole. Wil je er meer over weten, klik dan op de link eronder.

Daarnaast heb je natuurlijk de mogelijkheid om te controleren op spelling- en grammaticafouten (tab Controleren > Editor (Engels: Review > Editor). Daar zit tegenwoordig overigens ook een leesbaarheidstatistiek bij. Dat geeft aan of je document lastig is om te lezen of niet. Hieronder zie je de afbeelding van het editor-rapport van dit document.

Eindrapportvoorbeeld van Editor

 

Wat-als-analyse

Schermafbeelding Excel emt vraagteken: symbool voor Wat_Als

In mei is er een boek verschenen van Vincent Kouters “Over geld praat je nietwel“ met als subtitel “Hoe word je tonnair en al het andere wat je altijd al had willen weten over geldzaken”.
(tussendoor: “tonnair” wordt natuurlijk door Word onderstreept, want het is een (nog)-niet-bestaand woord, variant op miljonair.)

Een van de belangrijkste dingen die hij zich realiseerde, is dat je inzicht moet krijgen in je financiën. Dan is Excel natuurlijk een handig hulpmiddel. Ik wil er voor dit blog één aspect uitlichten: hoeveel moet je sparen per maand om tonnair te zijn (dus om €100.000 te hebben) over 5 jaar? Eerst laat ik zien hoe je hiervoor een model opzet en dan laat ik zien hoe je Excel het maandbedrag kunt laten uitrekenen.

Ik heb hierover ook een videofilm gemaakt, die je op YouTube kunt vinden als een ‘snelle korte tip’. Dit is de link: https://youtu.be/2mgIYyq7gY4

Je kunt bijvoorbeeld het volgende gebruiken.

Zet alvast een bedrag in B1, bijvoorbeeld 100: dat wil je per maand gaan inleggen op de spaarrekening.

Als je googelt vind je dat je hoogste spaarrente die je op dit moment kunt vinden bij een Nederlandse bank 2,5% is (op jaarbasis).

Je wilt 5 jaar lang sparen: dat zet je in B3.

De berekening komt in cel B6: selecteer die cel.
De functie die je daarvoor nodig hebt is TW, dat staat voor “toekomstige waarde” (in de Engelse versie is dit FV).
Je typt: =tw(
Je ziet dan een tooltip waarin staat wat je allemaal moet opgeven.

  1. De rente. Je ziet dat dit vet gedrukt is in de tooltip, dus dat moet je hier opgeven.
    De rente staat in B2, maar omdat dit een jaarrente is en je de inleg per maand wilt weten, moet je hier maandrente van maken. Dus moet hier komen: B2/12
  2. Dan typ je een ; voor het volgende deel van de berekening: het aantal termijnen: je ziet dat nu aantal-termijnen vet is.
    Hoe lang je wilt sparen staat in B3, maar ook dit zijn jaren en moeten maanden worden.
    Dus moet hier komen: B3*12
  3. Dan typ je weer een ; voor het volgende deel: bet wordt vet gedrukt.
    Dat staat voor het bedrag dat je elke maand wil inleggen. Dat is dus B1.
    Je ziet nu dit:
  4. De andere delen staan tussen [ ]. Dat betekent dat je dit niet hoeft op te geven (optioneel).
    Dat doen we inderdaad niet, dus druk op Enter. Het haakje sluiten vult Excel dan automatisch aan, maar je mag het natuurlijk ook zelf typen en dan op Enter drukken.

Je krijgt het eindbedrag te zien in cel B6: -6.384,05

Waarom is dat een negatief bedrag? De inleg die je per maand doet, is natuurlijk een uitgave voor jou. Dat zou je dus moeten opgeven als -100. Meestal doen mensen dat niet, vandaar dat het eindbedrag nu negatief is. Hoe los je dat op?
Twee opties: je typt -100 in cel B1 of je zet in de berekening een minteken voor de functie (dus direct achter het isgelijk-teken):
= – TW(B2/12;B3*12;B1)
Ik heb de formule in B6 aangepast en gekozen voor dat laatste.

Dat is nog lang geen ton: dus met € 100 per maand red je het niet.
Je kunt natuurlijk iets anders proberen in B1, bijvoorbeeld 500. Dan ben je er nog niet. Zo kun je blijven proberen,… maar dat kan ook handiger.

Nu komt een van de Wat-Als-Analyse-hulpmiddelen van pas: de doelzoeker (Engels Goal Seek).
Hoe gebruik je die?

  1. Selecteer de cel met de formule, dus B6.
  2. Kies in het lint/menu voor de tab Gegevens > Wat-als-analyse > Doelzoeken (Engels: Data > What‑If‑Analysis > Goal Seek)
    De tooltip zegt al waar je dit voor kunt gebruiken: “Zoeken naar de juiste invoer voor de gewenste waarde.”
  3. Je krijgt dan een venster waarin je 3 dingen moet opgeven.
    • De cel die je wilt instellen op een gewenste waarde is B6, de cel met de berekening.
    • Op welke waarde wil je die hebben: 100.000 (je mag dit ook typen zonder een punt erin)
    • Welke cel moet hiervoor gewijzigd worden? Dat is de cel met de inleg voor elke maand, dus B1.
  4. Na OK zie je dat Excel een oplossing gaat zoeken.

Na enkele tellen zie je de oplossing: je moet 1566,40 elke maand gaan sparen om tonnair te zijn na 5 jaar!

Nou, ik kan je verzekeren: dat gaat mij niet lukken! Jou wel?