Goede spreadsheets creëren en onderhouden

Er zijn een aantal best practices die je in gedachten kunt houden bij het creëren en onderhouden van goede spreadsheets. Hieronder vind je een lijst met tips die jouw Excel gebruik drastisch kunnen verbeteren.

Introductie

Excel wordt beschouwd als een eenvoudig hulpmiddel voor algemene doeleinden, omdat het zo’n flexibel programma is. Het gebruiksgemak heeft echter een nadeel, want de flexibiliteit zorgt ervoor dat je makkelijk fouten maakt. Veel voorkomende fouten zijn bijvoorbeeld onbegrijpelijke berekeningen, complexe relaties en verborgen informatie.

21 Excel tips & best practices

Wij willen je helpen bij de creatie en het onderhoud van goede spreadsheets. Daarom hebben we een lijst opgesteld met best practices voor Excel, die veelvoorkomende fouten bij het gebruik van Excel aanpakken. Gebruik deze tips als een hulpmiddel om duidelijke en beknopte spreadsheets ‘from scratch’ te maken.

1. Voorbereiding van een goede spreadsheet

Onze allereerste tip is om een duidelijke organisatiestandaard te kiezen voordat je een spreadsheet ontwikkelt. Blijf hier aan vasthouden zo lang als je de spreadsheet gebruikt. Een organisatiestandaard zet de toon voor alle toekomstige gebruikers, verbetert de communicatie en bespaart ontwikkeltijd. Gestandaardiseerde organisatie kan elementen als celopmaak, algemene lay-out, kleurenschema, volgorde en structuur omvatten.

2. Houd rekening met de toekomst als je werkbladen aanmaakt

Een goede voorbereiding is het halve werk. In de toekomst kun je situaties tegenkomen die niet aanwezig waren toen je begon met de bouw van je spreadsheet. Er kunnen later bijvoorbeeld toevoegingen in het spel komen. Of misschien heb je ruimte nodig voor waarden die afhankelijk zijn van gebeurtenissen die in de loop van de tijd zullen veranderen. Probeer zoveel mogelijk toekomstige factoren in overweging te nemen die je mogelijk zouden kunnen dwingen om een werkblad te wijzigen. Door je voor te bereiden op eventuele toekomstige ontwikkelingen, verleng je de levensduur van het werkblad. Een langere levensduur betekent dat je minder tijd verspilt aan het maken van een vervangend werkblad of zelfs een geheel nieuwe werkmap.

3. De volgorde van werkbladen

Zet verschillende soorten gegevens op verschillende werkbladen. Gebruik bijvoorbeeld de eerste paar werkbladen voor invoergegevens, de volgende werkbladen voor berekeningen en het laatste werkblad als presentatiewerkblad voor grafieken en resultaten. Beperk het aantal tabellen per werkblad tot één. Meerdere tabellen per werkblad veroorzaken problemen bij het sorteren, invoegen of opmaken van cellen.

Voeg bij het maken van een spreadsheet met een groot aantal werkbladen een werkblad met verklarende documentatie toe aan de inhoudsopgave. Dit maakt het voor een andere gebruiker makkelijker om te begrijpen hoe de spreadsheet precies werkt.

4. Helderheid boven aantrekkelijkheid

Gebruik alleen een aantrekkelijk ‘Resultatenwerkblad’ als je werkblad gericht is op de gebruiker. Als dat niet zo is, doe het dan niet! De meeste werkbladen werken het beste als ze zijn ontworpen met transparante actieve berekeningen. Je kunt proberen om lange formules op te splitsen, maar kies er niet voor om ze te verbergen. Op deze manier bereik je het meest heldere resultaat.

5. Consistente tijdlijn

Houd de tijdlijn consistent in alle werkbladen, zelfs als dit leidt tot lege rijen. Consistente tijdlijnen verbeteren de duidelijkheid van de spreadsheet enorm en verminderen het risico op onjuiste formules. Zo geeft het inkorten van vier maanden tot kwartaalcijfers misschien een duidelijkere vormgeving qua presentatie, maar verhoogt ook het risico op onjuiste totalen. Een eenvoudige manier om dit te voorkomen, is door één enkele tijdlijn per werkblad in te voegen, idealiter bovenaan, in een bevroren kopregel.

6. Organiseer de stroom van informatie

Probeer werkbladen zo in te delen dat informatie altijd van linksboven naar rechtsonder stroomt. Dit maakt het voor een andere gebruiker aanzienlijk makkelijker om te begrijpen hoe de spreadsheet werkt.

Indien je spreadsheet gebruikersgeoriënteerd is, dan kun je voor de helderheid een ‘invoerwerkblad‘ en/of een ‘resultatenwerkblad’ aan het begin plaatsen, maar structureer de tabbladen in principe in chronologische volgorde, van links naar rechts. Vermijd kruislingse afhankelijkheden bij het beheren van de informatiestroom, en vermijd te allen tijde kringverwijzingen als er een link naar eerdere gegevens nodig is.

7. Label kolommen en rijen

Kolommen die geen heldere en consistente namen hebben vormen onduidelijkheid voor de gebruiker. Misschien dat de maker begrijpt wat er in welke kolom staat, maar zodra de spreadsheet met andere gebruikers gedeeld wordt ontstaat er een probleem. Daarom adviseren wij om kolommen, rijen en tabellen altijd te labelen met simpele koppen die aansluiten bij de afgesproken regels rondom naamgeving in de organisatie.

8. Leesbare formules

Splits lange formules op in meerdere kleinere berekeningen. Hiermee vergroot je de leesbaarheid aanzienlijk. Teveel operatoren of teveel verschillende verwijzingen maken een formule onleesbaar, waardoor de spreadsheet ondoorgrondelijk wordt voor andere gebruikers dan de maker. De maker zal de spreadsheet altijd uitgebreid moeten toelichten als anderen ermee willen werken.

9. Vermijd de herhaling van formules

Bereken formules slechts 1 keer! Een exacte kopie van een formule verdubbelt het risico op errors, omdat aanpassingen in formules niet automatisch worden doorgevoerd in eventuele ko­pieën. Ko­pieën worden vaak over het hoofd gezien, waardoor er inconsistentie in de spreadsheet kan ontstaan. Als het nodig is om precies dezelfde formule te gebruiken, verwijs dan terug naar de locatie van de originele formule.

10. Vermijd vaste getallen in formules

Gebruik nooit vaste getallen in Excel formules, maar gebruik een aparte cel voor een vaste waarde en verwijs naar deze cel vanuit de berekening. Een formule die vaste waarden bevat vormt een groot risico, omdat deze waarde overal waar hij voorkomt handmatig moet worden aangepast wanneer hij verandert. In het minst erge geval veroorzaakt dit een hoop werk, maar in het ergste geval kan het fatale fouten veroorzaken (denk aan de btw wijziging van 19% naar 21%).

11. Voeg cellen niet samen

Een belangrijk punt in onze lijst met Excel tips is om cellen niet samen te voegen. Cellen worden doorgaans samengevoegd uit esthetische overwegingen, maar leiden uiteindelijk tot een vergrote kans op problemen met berekeningen en verwijzingen. Het grootste risico vormt de verwijzing naar een samengevoegde cel. Alle cellen in de samenvoeging worden namelijk onderdeel van de berekening, maar slechts één ervan bevat de juiste informatie. Naarmate een spreadsheet langer gebruikt wordt biedt deze ambiguïteit ruimte aan een toenemend aantal errors.

12. Verberg niets

Het verbergen van informatie, zoals kolommen, rijen, of hele worksheets is zelden een goed idee. Het vergroot dat kans dat een gebruiker is over het hoofd ziet wanneer hij met de spreadsheet werkt, waardoor het risico op fouten groeit. The enige uitzondering op deze regel is wanneer het absoluut nodig is om informatie te verbergen die om specifieke redenen niet in een extern document geplaatst kan worden. In de praktijk zijn dergelijke situaties zeer zeldzaam.

13. Bouw dataverificatie in

Het inbouwen van dataverificatie, zoals audit tests, notificaties en ingebouwde automatische controles, is een prima methode om schadelijke wijzigingen of toevoegingen in je werk te voorkomen. Het is cruciaal om deze checks direct vanaf het begin in te bouwen, omdat je het later vaak vergeet. Ingebouwde dataverificatie garandeert een sterke fundering voor het onderhoud van je spreadsheet.

14. Bewaar vormgeving voor het laatst

Excel biedt uitgebreide mogelijkheden voor vormgeving. Het is mogelijk om het voorkomen van cellen en waarden te finetunen, en er zijn volop opties voor kleuren, randen en andere kenmerken. Hoewel vormgeving kan helpen om een spreadsheet leesbaar te maken, kan het proces van vormgeving ook informatie onttrekken aan de aandacht van de bouwer. Wat goed is voor het eindresultaat maakt de ontwikkeling moeizaam en vergroot de kans op fouten. Bewaar de vormgeving daarom voor het laatst.

15. Houd vormgeving consistent

Jouw vormgevingskeuzes moeten in de gehele spreadsheet consistent zijn. Simpele en consistente styling is cruciaal voor de leesbaarheid. Neem ook altijd een legenda op. Afkortingen en celkleurtoelichting kunnen eventueel op een aparte worksheet worden opgenomen.

16. Houd voorwaardelijke opmaak eenvoudig

Voorwaardelijke opmaak helpt gebruikers om te begrijpen hoe de spreadsheet precies werkt. Ingewikkelde opmaakregels schieten dat doel voorbij en veroorzaken verwarring. Houd je regels voor voorwaardelijke opmaak eenvoudig (denk bijvoorbeeld aan de verandering van celkleur op basis van inhoud).

17. Gebruik positieve getallen

Heb je wel eens per ongeluk iets afgetrokken dat opgeteld had moeten worden? De kans bestaat dat dit een gevolg was van een negatief getal in een cel. Probeer je spreadsheet altijd zo te bouwen dat het gebruik van positieve getallen wordt gestimuleerd.

18. Duidelijke eenheden en getalnotatie

Zorg ervoor dat gebruikers altijd weten welke eenheden en getalnotatie er gebruikt wordt in de spreadsheet. Je kunt dit doen door de eenheden in te stellen in de celeigenschappen van Excel, of je kunt het benoemen in de labels van de kolommen. Maar zorg ervoor dat je nooit een valutasymbool direct naast de waarde typt! Dit veroorzaakt namelijk problemen wanneer je de waarde in een formule wilt gebruiken.

19. Verklaar je bronnen

Wanneer je ingewikkelde berekeningen maakt in Worksheet A met cijfers uit Worksheet B, zorg er dan voor dat deze cellen uit Worksheet B getoond worden in Worksheet A. Gebruikers die proberen te begrijpen hoe de berekening in elkaar zit kunnen dan op dezelfde worksheet blijven en hoeven niet steeds te schakelen tussen Worksheet A en B. Verhelder en verklaar je verwijzingen. Zonder heldere opmaak kunnen verwijzingen een worksheet heel rommelig doen lijken, vooral als je er veel gebruikt.

20. Vermijd het gebruik van macro’s

Probeer altijd gebruik te maken van de ingebouwde oplossingen van Excel voordat je toevlucht neemt tot VBA macros. Een spreadsheet wordt vele malen minder transparant door het gebruik van macros, omdat je de logica ermee verhult. In sommige gevallen presteren VBA macro’s zelfs slechter dan Excel’s eigen oplossingen.

21. Ruim formulebereik

Best practices laten zien dat het in veel gevallen slim is om extra lege rijen of kolommen op te nemen in het bereik van je berekeningen. Op deze manier voorkom je dat toekomstige aanpassingen van je spreadsheet formules in de war gooit. Hoe minder je kleine, losse ranges gebruikt, hoe kleiner de kans op onjuiste berekeningen in de toekomst.