Principes voor goed Excel-gebruik
Excel is bijzonder flexibel en juist daarom moet Excel-modellering zeer serieus genomen worden. Excel modellen zijn ingewikkelde stukjes software en moeten daarom eenvoudig, correct en goed te onderhouden zijn. Onze principes voor goed Excelgebruik formuleren enkele belangrijke standaarden met betrekking tot architectuur, vormgeving en het gebruik van bepaalde formules.
Keep it simple
Eenvoud verbetert de leesbaarheid, vermindert fouten en verhoogt productiviteit. Het is essentieel modellen eenvoudig te houden!
Keep it safe
Bouw betrouwbare spreadsheets zonder fouten in formules, privacy kwesties of gebrek aan bescherming. Houd je modellen veilig!
Keep it going
Het is belangrijk om een spreadsheet te hebben die snel, gemakkelijk te onderhouden en aan te passen zijn. Bouw voor de toekomst!
1. Houd de vormgeving consistent
Consistente opmaak van een spreadsheet maakt het model makkelijker te volgen. Hoe ingewikkelder een model, hoe belangrijker een consequente opmaak. Bovendien zorgt het ervoor dat het model beter is te onderhouden, ook door andere gebruikers. Consistente vormgeving houdt in dat kleurgebruik, lettertypen en rij- en kolomopmaak op een voorspelbare manier zijn toegepast in alle werkbladen.
2. 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. De architectuur gaat over de keuzes voor de opbouw van het model. Bijvoorbeeld: altijd een apart invoerblad voor database-geïmporteerde gegevens. Of: consequent een ID-kolom aan het begin en berekeningen achteraan.
3. Wees niet bang voor een renovatie
Vormt 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.
4. 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.
5. Maak zoveel werkbladen als je wilt
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 aantalwerkbladen, 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 het juist eenvoudiger wordt.
6. Kies een consequente tijdlijn
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. Maak liever een aparte tabel per tijdseenheid.
7. Scheid input, output en berekeningen
Je ziet in Excel niet makkelijk 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 afwijkendevormgeving. 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.
8. Gebruik consistente labels
Of het nu is om de volgende gebruiker duidelijk te maken wat je bedoelt, voor je eigen geheugen, of voor het maken van grafieken of tabellen, (duidelijke) labels zijn altijd belangrijk. Dus daarom, gebruik altijd labels: koptitels, rij- en kolomnamen, named ranges en duidelijke werkbladnamen.
9. 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 raak 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.
10. Vermijd dubbele berekening
Tweemaal hetzelfde berekenen is onnodig. Eénmaal berekenen en daar in ‘t vervolg naar verwijzen is beter.Niet alleen scheelt dat rekentijd, je voorkomt ook dat je één berekening vergeet aan te passen mocht er in de toekomst iets wijzigen. Heb je de uitkomst vaak nodig? Maak dan van de cel met die berekening een defined name.
11. Gebruik geen vaste getallen in formules
Met vaste getallen in formules ga je makkelijk de mist in. De getallen worden haast oncontroleerbaar, want van de formules heb je geen overzicht. 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.
12. Voeg geen cellen samen
Het is aantrekkelijk om, omwille de opmaak ,een paar cellen samen te voegen tot één grote cel, maar ze leiden vaak tot meer problemen dan ze oplossen. 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.
13. Beveiligen 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 wordente 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.
14. Bouw geautomatiseerde controles in
Excel is de king of numbers. Maak er gebruik van. Tel bijvoorbeeld subtotalen los op en vergelijk ze met het totaal. Komen deze overeen? Maak bij grote spreadsheets een apart werkblad als ‘controleblad’. Zorg dat je daarin met slimme doch overzichtelijke formules in één blik kan verifiëren of de spreadsheet nog doet wat hij doen moet.
15. Bewaar de opmaak voor het laatst
Zorg ervoor dat je opmaak niet twee keer (of vaker) hoeft te doen. Als nog 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.
16. Werk volgens bedrijfsafspraken
Als je samen met je collega’s afspreekt hoe je werkt, hoe je omgaat met fomules, 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 metelkaar op te schrijven.
17. Houd voorwaardelijke opmaak eenvoudig
Voorwaardelijke-opmaakregels maken spreadsheets gebruiksvriendelijk, maar het wordt ook gauw een zootje. Er onstaan conflicterende regels voor één en dezelfde cel, regels worden gedupliceerd bij het kopiëren en plakken vancellen, of de spreadsheet wordt er traag omdat elke celwijziging een herberekening van de opmaakregels met zich meebrengt. Gebruik daarom voorwaardelijke opmaakregels met mate. (Maar gebruik ze wel!)
18. Maak gebruik van nieuwe 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!
19. Gebruik duidelijke opmaak voor negatieve getallen
Neem de omzet en trek daar de gemaakte kosten vanaf. Wat kan er mis gaan?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 duidelijk kenbaar te maken, bijvoorbeeld middels een rode markering.
20. 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.
21. Duidelijke labels voor eenheden
Je werd er op school al op afsgestraft; 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 dekolomnaam, of zet de eenheden er achter (maar niet in dezelfde cel!): zorg dat altijd je eenheden duidelijk zijn.
22. 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 bijvoorbeeldversiebeheer. 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.
23. Vermijd VBA
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 weggegooit bij het opslaan, functies stoppen plots met werken, of de benodigde referenties zijn plots niet aanwezig, met cryptischemeldingen 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.
24. Houd bereik simpel en overzichtelijk
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 meerderewerkbladen 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.
25. 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. Wantwens je een model overzichtelijk en uitvoerbaar te houden, dan is het van belang dat ongebruikte delen verwijderd worden.
26. Let op 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 een model. Welke is de laatste versie? Welke moeten worden samengevoegd? Versiebeheer is goed te doen als je duidelijke naamgevingafspreekt. 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.
27. Werk je model zorgvuldig af
De afwerking maakt het verschil. In Excel is dat niet alléén maar visueel. Goede bescherming van formules kan zorgen dat er niet per ongeluk dingen stuk gaan. Testdata moeten worden weggehaald, anders kunnen die tot in lengte van jaren blijven staan. Een duidelijke markering van werkbladen en invoercellen voorkomt incorrectgebruik van het model.
28. Vermijd shortcuts
Een bekend gezegde onder modelbouwers is: “morgen, na de bespreking, maar 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, wordt je geconfronteerd met de tijdelijke noodoplossingen van de keer ervoor.
29. 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 eerst of er goede brondata beschikbaar zijn, begin dan pas aan de bouw van je spreadsheet.
30. Vraag een second opinion
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 (med-bouwers, eindgebruikers, auditors).
31. Gebruik Excel niet voor alles
Excel is fantastisch en, laten we eerlijk zijn, echte Excelgebebruikers zijn er een beetje aan verslaafd. Maar let op: Excel is vaak, maar niet altijd de beste keuze. Boekhouden doe je niet in Excel. Het houden van een enquete ook niet (maar de verwerking van de resultaten wel). Denk altijd na welke software je gebruikt voor het doel dat jewilt bereiken.
32. Link 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 debrongegevens kunnen verschuivingen teweegbrengen in de draaitabel, en celverwijzingen houden daar geen rekening mee.
33. Stuur oude modellen 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.
34. Consistente naamgeving
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 (voorgedefineerde) variabelen.
35. 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 te voren 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.
36. 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 controle-tools of laat je werkcontroleren door een ander!