Gouden Excelregels

Houd de vormgeving consistent

Consistente vormgeving houdt in dat kleurgebruik, lettertypen, rij- en kolomopmaak op een voorspelbare manier zijn toegepast in alle werkbladen. Consistente opmaak van een spreadsheet maakt het model makkelijker te volgen. Hoe ingewikkelder een model, hoe belangrijker een consequente opmaak is. Bovendien zorgt het ervoor dat het model beter is te onderhouden, ook door andere gebruikers.

Werk als een architect

Spreadsheets gaan vaak jaren mee. Het is daarom belangrijk een architectuur te kiezen die werkt voor alle functionele eisen van het model. Architectuur gaat over de keuzes voor de opbouw van het model. Bijvoorbeeld: apart invoerbladen voor database-geïmporteerde gegevens, het gebruik van tabellen en/of Power Query en het consequent omgaan met ID-nummers in rijen van tabellen.

Wees niet bang voor een renovatie

Vorm volgt functie. Als de functie van een model verandert of uitgebreid wordt, dan moet ook de vorm van het model aangepast worden. Maak er geen ‘hack’ van; als nieuwe functionele eisen een nieuwe rekenmethode vragen, schroom dan niet het model om te gooien. Alleen zo voorkom je dat je spreadsheet een niet-te-volgen breiwerk wordt.

Let op de volgorde van werkbladen

Een flink model heeft al gauw veel werkbladen. Zorg daarom dat je nadenkt over de volgorde waarin je ze plaatst. Werk je van links naar rechts? Van resultaat naar bron of invoer naar samenvatting? Niet elke spreadsheet vraagt om dezelfde aanpak, maar een consistente, voorspelbare werkbladvolgorde maakt je model veel prettiger in gebruik.

Geef ieder tabel een eigen werkblad

Er is een veelvoorkomende misvatting dat meer werkbladen een model ingewikkelder maken. Doorgaans is het tegendeel waar. Werkbladen waarbinnen veel wordt gecombineerd, bijvoorbeeld meerdere tabellen, zijn onoverzichtelijk en foutgevoelig. Vroeger was Excel beperkt in het aantal werkbladen, maar dat is al lang niet meer zo. Schematisch lijkt een model wellicht complexer te worden met extra tabbladen, maar in de praktijk merk je dat het juist duidelijker wordt.

Stuur je model op tijd met pensioen

Het voelt misschien niet zo, maar ook een Excelbestand kan verouderen. Door de jaren heen verzamelt een document restjes uit voorgaande modellen en oude Excelversies. Op een gegeven moment kun je een model beter herbouwen.

Maak gezamenlijke regels

Als je samen met je collega’s afspreekt hoe je werkt, hoe je omgaat met formules, met vormgeving en met indeling, dan is elkaars werk veel makkelijker te begrijpen of over te nemen. In een wat groter bedrijf is het zeker de moeite waard de belangrijkste regels met elkaar op te schrijven. Maak bijvoorbeeld heldere afspraken over kleurgebruik en formatting.

Gebruik niet altijd Excel

Excel is fantastisch en, laten we eerlijk zijn, echte Excelgebruikers zijn er een beetje aan verslaafd. Maar let op: Excel is niet altijd de beste keuze. Boekhouden doe je niet in Excel. Het houden van een enquête ook niet (maar de verwerking van de resultaten wel). Denk altijd na welke software je gebruikt voor het doel dat je wilt bereiken.

Kies een consequente tijdslijn

In Excel mag je bij uitzondering appels met peren vergelijken, omdat beiden een fruitsoort zijn. Het gaat mis bij lijsten met afwijkende grootheden, waar plotseling een rij of kolom ‘Fruit’ tussen de appels en peren verschijnt, behalve als fruit de optelling is van appels en peren. Wat betreft de tijdsfactor: kies voor dagen, weken of maanden, maar combineer ze niet zomaar. Maak liever een aparte tabel per tijdseenheid of gebruik op zijn minst duidelijke vormgeving om het verschil te duiden.

Gebruik duidelijke, consistente labels

Grijp alle kansen om je model begrijpelijk te houden. Dat voorkomt fouten. Kies daarom ook voor een vast systeem van naamgevingen voor tabbladen, rij- en kolomkoppen en (voor gedefinieerde) variabelen.

Scheid input, output en berekeningen

Je ziet in Excel niet gemakkelijk wat een formule is en wat vaste waarden zijn. Daarom is het verstandig deze te scheiden. Bijvoorbeeld in aparte kolomgroepen of zelfs aparte werkbladen, in ieder geval door een afwijkende vormgeving. Zo voorkom je bijvoorbeeld dat formules worden overschreven met (verse) inputdata. Soms kun je niet voorkomen dat input en output dicht bij elkaar staan, om de gevolgen van eventuele inputverandering snel te kunnen beoordelen. Let dan extra goed op de vormgeving.

Vermijd spaghetti

Iedereen is het er mee eens, maar het gaat zo makkelijk mis. Voordat je het weet wordt een Excelmodel een grote spaghetti van verwijzingen. Om dit te voorkomen bouw je een wat groter model op als een drietrapsraket met input, berekeningen en output. Let erop dat berekeningen alleen informatie ophalen uit input. Let erop dat de output alleen informatie ophaalt uit berekeningen en input. Visualiseer het model regelmatig om jezelf of een ander te controleren.

Houd je formules leesbaar

Het lijkt soms wel een wedstrijd wie de langste formule kan schrijven. Heel knap, maar écht knap is het om een rekenmodel op te stellen dat makkelijk te begrijpen is door iemand anders. Het is een balanskwestie: door te veel op te breken raakt de lezer onderweg de tel kwijt, door te veel bij elkaar in één cel te plaatsen, gaat de formule dansen voor je ogen. Tip: gebruik voor tussenresultaten duidelijke (kolom)namen. En plaats terugkerende deelformules in aparte cellen en geef die cel een naam. Ben tenslotte niet bang veel rekenwerk te verplaatsen naar een ander tabblad, zodat de eindgebruiker er geen “last” van heeft.

Vermijd herhalingen

Gebruik geen vaste getallen in formules

Met vaste getallen in formules ga je makkelijk de mist in. De getallen worden haast oncontroleerbaar, want je mist een duidelijk overzicht van de formules en welke getallen daarin worden gebruikt. Zeker als een getal vaak terugkomt (BTW bijvoorbeeld) gaat het vaak mis. Beter is om apart (liefst samen op één plek) constanten te definiëren en vervolgens daar enkel naar te verwijzen. Vaak gebruikte constanten verdienen een defined name.

Voeg nooit cellen samen

Het is aanlokkelijk om omwille van de opmaak een paar cellen samen te voegen tot één grote cel, maar dat leidt tot meer problemen dan oplossingen. Een verwijzing naar een samengevoegde cel waarvan het adres niet de linkerbovenhoek is geeft onverwachte resultaten. Bij sorteren, filteren, plakken, knippen en invoegen leiden samengevoegde cellen weer tot andere problemen. Als je echt een label wilt uitlijnen over meerdere cellen, gebruik dan de optie “center along selection”. Dan worden de cellen alleen optisch samengevoegd. Die optie is gek genoeg een beetje verstopt in “format cells”.

Beschermen is beter dan verbergen

Geen zorgen, het is oké om tabbladen te verbergen waar doorgaans niet naar omgekeken hoeft te worden. Maar cellen, rijen of kolommen verbergen, ook stiekem (door bijvoorbeeld de tekstkleur wit te maken) is not done. Er worden te makkelijk fouten mee gemaakt, omdat informatie wordt vergeten, overgeslagen of overschreven. Beter is het, om formules en gegevens die niet veranderd mogen worden te beschermen.

Bewaar de opmaak voor het laatst

Zorg ervoor dat je opmaak niet twee keer (of vaker) hoeft te doen. Alsnog niet precies duidelijk is hoe groot een tabel is, waar een input veld staat enzovoort, begin dan nog niet aan de opmaak, hoe verleidelijk het ook is. Zeker de lijnen tussen cellen moeten altijd weer aangepast worden als je er te snel aan begint.

Gebruik eenvoudige voorwaardelijk opmaak

Voorwaardelijke-opmaakregels maken spreadsheets gebruiksvriendelijk, maar het wordt ook gauw een zootje. Er ontstaan conflicterende regels voor één en dezelfde cel, regels worden gedupliceerd bij het kopiëren en plakken van cellen, of de spreadsheet wordt erg traag omdat elke celwijziging een herberekening van de opmaakregels met zich meebrengt. Gebruik daarom voorwaardelijke opmaakregels met mate. (Maar gebruik ze wel!)

Denk ook aan de nieuwste mogelijkheden

Mensen zijn gewoontedieren. We zijn snel geneigd problemen op te lossen op een manier die we kennen. Helaas kennen veel Excellers enkel verouderde technieken die reeds vervangen zijn door nieuwere, snellere alternatieven. Excel zelf ontwikkelt zich razendsnel!

Excel is geen database

Excel is een geweldig middel voor rapportage, documentatie, gegevensverwerking, data-inzicht, en zelfs als opslagmedium. Maar het is geen database. Directe lees- en schrijfoperaties, door tientallen mensen tegelijkertijd en over misschien wel miljoenen records: dat zijn kenmerken waar databases in schitteren, maar waar Excel het zwaar mee heeft. Het is overigens niet het één of het ander: Excel heeft krachtige mogelijkheden om met databases te werken.

Gebruik Power Query

Als je model afhankelijk is van gegevens uit een externe bron, zoals een database of CSV-file, gebruik dan Power Query. Met Power Query kun je filters en opschoningsstappen instellen zodat je data goed geordend binnenkomt. Bovendien vervangt Power Query de inmiddels verouderde connectiemogelijkheden uit voorgaande Excel-versies.

Vergeet de eenheden niet

Je werd er op school al op afgestraft; het achterwege laten van eenheden. Toch gaat het nog vaak mis. Kg worden geïnterpreteerd als lbs, uren als minuten en aantallen worden meegenomen als sommaties. Gebruik number formats, zet het in de kolomnaam, of zet de eenheden er achter (maar niet in dezelfde cel!): zorg dat altijd je eenheden duidelijk zijn.

Documenteer je werk

Gemiddeld werken 13 mensen aan een spreadsheet. Wel eens een verhaaltje doorverteld in een kringetje? Mondelinge overdracht is belangrijk, maar niets gaat boven solide documentatie van je werk. Dat gaat niet alleen over de werking van een model en een eventuele inhoudsopgave, maar ook over bijvoorbeeld versiebeheer. Zit er een ‘logblad’ in je spreadsheet om bij te houden wie wat heeft aangepast? Bedenk dat het lang niet altijd gaat om het terug kunnen halen van eerdere versies (daarvoor is een back-up geschikt), maar vooral ook om het begrijpen wat er waarom gewijzigd is.

Vermijd VBA als het kan

VBA is de kern van veel problemen in Excel. Excel is toegankelijk, VBA is dat maar beperkt. Zelfs in de handen van een ervaren Exceller kan VBA funest zijn. Er worden gegevens overschreven of weggegooid bij het opslaan, functies stoppen plots met werken, of de benodigde referenties zijn plots niet aanwezig, met cryptische meldingen tot gevolg. Opgenomen macro’s zijn het ergst. Deze zijn hoogst gevoelig voor wijzingen en zeer contextafhankelijk. Veel nieuwe Excel- functies nemen taken uit handen die voorheen alleen met VBA konden, zoals de UNIEK()-functie.

Kies voor eenvoudige reeksen

Je staat soms versteld van de mogelijkheden in Excel. Zo bestaat er de ‘intersect’-celverwijzingen, twee celbereiken gescheiden door een spatie. De overlappende range wordt genomen. Of denk aan de ‘multi-sheet’- celverwijzingen: waarin met één verwijzing gegevens uit meerdere werkbladen opgehaald kunnen worden. Het klinkt allemaal hartstikke leuk, maar ze zijn hoogst obscuur, gevoelig voor structuurwijzigingen, en eigenlijk onnodig. Houd daarom je bereik simpel en overzichtelijk.

Durf weg te gooien

Als men een monster van een spreadsheet voor ogen krijgt, betreft dit lang niet altijd een model dat veel kan. Het komt al te vaak voor dat oude spullen blijven hangen omdat niemand precies weet wat nog nodig is. Hier kunnen handige tools bij helpen. Want wens je een model overzichtelijk en uitvoerbaar te houden, dan is het van belang dat ongebruikte delen verwijderd worden.

Vermijd kort-door-de-bocht oplossingen

Een bekend gezegde onder modelbouwers is: “morgen, na de bespreking, maak ik de spreadsheet nog wel even netjes”. Maar in de praktijk gebeurt er in zo’n geval helemaal niets. De volgende keer dat dezelfde spreadsheet nodig is, word je geconfronteerd met de tijdelijke noodoplossingen van de keer ervoor.

Garbage in, garbage out

Uiteindelijk, hoeveel Excel ook kan, geldt er nog steeds: stop je er rotzooi in, dan krijg je er rotzooi uit. Het advies is daarom: controleer of er goede brondata beschikbaar is, zorg ervoor dat je brondata opschoont en begin dan pas aan de bouw van je spreadsheet.

Verwijs niet naar draaitabellen

Draaitabellen zijn een krachtig middel als eindonderdeel, bijvoorbeeld als onderdeel van een rapportage. Aangezien ze dynamisch zijn, kun je ze echter beter niet gebruiken als tussentijdse rekensom. Veranderingen in de brongegevens kunnen verschuivingen teweegbrengen in de draaitabel, en celverwijzingen houden daar geen rekening mee.

Wees spaarzaam met externe bronnen

Het is een beetje een dilemma. Aan de ene kant wil je dat alle data in je model zo goed mogelijk actueel blijven en bij voorkeur geautomatiseerd. Aan de andere kant wil je niet dat je model onhanteerbaar gevoelig wordt voor de precieze aanwezigheid van allerlei externe bronnen op van tevoren aangewezen plaatsen. In ieder geval geldt: organiseer je externe bronnen goed. Laat ze binnenkomen op een duidelijk aangewezen plaats in je spreadsheet. En wees er zuinig op. Meer dan 2 bronnen is al gauw te kwetsbaar. Tenzij je verstand hebt van Power Query. Dat kan veel meer aan en is minder kwetsbaar.

Maak je model niet onnodig afhankelijk van plug-ins

Add-ins kunnen handig zijn voor het binnenhalen van bepaalde informatiestromen of voor het uitvoeren van complexe taken. Maar toch, probeer je modellen zo te bouwen dat ze ook zonder de add-ins kunnen werken. Voor als een (externe) collega ermee moet werken, voor het geval dat een add-in niet aansluit bij de nieuwste versie van Excel of voor als je online wilt werken (in het geval van een zogenaamde COM Add-in).

Eén kolom, één functie

Gebruik nooit meer dan één (doorgetrokken) formule per kolom (of per rij) in een tabel. Anders wordt je tabel moeilijk te onderhouden en is een fout zo gemaakt. Bouw al je tabellen consistent op, zodat je gebruik krijgt wat hij of zij verwacht.

Bouw de nodige checks in

Let op je versiemanagement

Een collega heeft een nieuwe versie gestuurd, in de Cloud zitten op verschillende plekken twee verschillende bestanden met dezelfde naam, en je had zelf ook nog wat gesleuteld een model. Welke is de laatste versie? Welke moeten worden samengevoegd? Versiebeheer is goed te doen als je duidelijke naamgeving afspreekt. Een volgnummer, een datum, en een vaste opslag zijn voorbeelden. Mocht je onverhoopt twee versies van een bestand moeten vergelijken, zul je daar tooling voor moeten gebruiken.

Wees duidelijk bij negatieve getallen

Neem de omzet en trek daar de gemaakte kosten vanaf. Wat kan er misgaan? Als de kosten negatief staan vermeld, dan moet je ze optellen om ze af te trekken. Het gaat maar al te makkelijk fout met getallen onder nul. Het is daarom aan te raden negatieve getallen kenbaar te maken, bijvoorbeeld middels een rode markering.

Ontwerp voor de eindgebruiker

Het is een open deur, maar zorg dat je model aansluit bij de beoogde eindgebruikers. Is het belangrijk dat de brondata makkelijk in te zien zijn of moet het zo simpel mogelijk ogen met een enkel dashboard? Moet de output zo eenvoudig of juist zo volledig mogelijk? Maak je het model idiot-proof of benadruk je transparante berekeningen? Juist wanneer een spreadsheet nèt niet voldoet gaat het vaak mis, omdat gebruikers (niet-experts) zelf aanpassingen gaan maken.

Laat je model testen

Een Excelmodel bouwen in je eentje gaat vaak nog wel, al moet je niet onderschatten hoeveel tijd het kan kosten. Maar, laat op zijn minst iemand anders je werk controleren. Iemand anders let altijd weer op andere dingen dan jij. Als je lang aan iets bouwt word je ook wel eens blind voor sommige blunders. Kies bij voorkeur voor verschillende testers (mede-bouwers, eindgebruikers, auditors).

Vergeet niet: ook jij maakt fouten

Je zou denken dat mensen met meer ervaring minder fouten maken. Was het maar zo! Onderzoek heeft laten zien dat het maken van fouten bijna een constante is. De regel is: zodra we meer kunnen, gaan we ingewikkeldere dingen maken en dus weer evenveel fouten maken. Vertrouw nooit alleen op je eigen kunde. Bouw controles in, gebruik controletools of laat je werk controleren door een ander!