Principes voor goed Excelgebruik

Keep it safe

Spreadsheets worden over het algemeen door 13 of meer mensen gebruikt en 88% van de spreadsheets bevat ernstige risico’s. Ervoor zorgen dat uw formules correct worden berekend, gevoelige informatie met respect behandeld wordt en er voldoende aandacht is voor de bescherming daarvan zijn slechts enkele manieren waarop je modellen veilig kunt houden.

Het klinkt logisch, maar de vraag is hoe je dit praktisch doet? Op deze pagina vindt u 14 principes die je zullen helpen spreadsheets veilig te houden óf maken.

Gebruik duidelijke labels

Use consistent names // PerfectXL

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.

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. Maak liever een aparte tabel per tijdseenheid.

Keep Timeline Consistent // PerfectXL

Houd je formules leesbaar

Keep formulas readable // PerfectXL

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.

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.

Avoid fixed numbers in formulas // PerfectXL

Voeg geen cellen samen

Het is aanlokkelijk 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 geeftonverwachte 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.

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 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.

Protect. don't hide // PerfectXL

Bouw de nodige checks in

Build in automated checks // PerfectXL

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.

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 datkleurgebruik, lettertypen, rij- en kolomopmaak op een voorspelbare manier zijn toegepast in alle werkbladen.

Keep your formatting consistent

Werk als een architect

Choose a uniform architecture // PerfectXL

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.

Gebruik een 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.

Clearly indicate negative numbers // PerfectXL

Vergeet de eenheden niet

clearly label units // PerfectXL

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.

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.

clearly document your work // PerfectXL

Vermijd VBA

avoid vba // PerfectXL

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.

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 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.

Use simple ranges // PerfectXL

Mind your version management

Mind your version management // PerfectXL

A colleague sends you a new version of a spreadsheet, in the cloud there are 2 different spreadsheets with the same name and you had also personally made some edits in one of the versions locally. What’s the newest version? How do your merge the spreadsheets? Version management isn’t difficult if you have clear agreements about naming and saving. Be consistent in this, because when it goes wrong it will be very difficult to find the best version without external software.

Don’t take shortcuts

We’ve all said it before, “after the meeting tomorrow, I’ll finish up the model, but it works for now.” In reality this never really happens. The next time you need that spreadsheet you’ll be confronted with a situational, temporary solution that hasn’t been properly finished as a model.

Don't take shortcuts // PerfectXL

Garbage in, garbage out

Garbage in, garbage out // PerfectXL

As great as Excel is, it’s still true that if you put garbage into your spreadsheet, you’re going to get garbage results. That’s we advise you to first properly control, clean, and format your data before working on the rest of the model.

Every model needs a second opinion

Building a model on your own is usually fine, although it can cost a lot of time, but you should, at the very least, have someone else look over the model when it’s done. Someone else will always see things just a bit differently. The longer you spend building a model, the harder it is to spot your own mistakes. In an ideal situation pick a few different testers for your model.

Every model needs a second opinion // PerfectXL

Don’t reference Pivot Tables

Don't reference pivot tables // PerfectXL

Pivot tables are a powerful reporting tool, and look great in a dashboard or report sheet. On the other hand, you shouldn’t use them as a calculation tool. Don’t reference a Pivot table because if data shifts the Pivot table won’t recognize the change in cell location, and will present incorrect or incomplete results.

Retire old models

It may not seem like it, but Excel models get old. Throughout the years Excel models begin to collect random little bits of unnecessary data and objects from earlier versions of Excel. Eventually you have to make the decision to rebuild.

Retire old models // PerfectXL

Even you make mistakes

You would assume people with more experience should make less mistakes in Excel. We wish! Research has shown that making mistakes in Excel is a constant. The rule is: The better we get, the more thing we try, and as complexity increases, we make more mistakes. So, do not only trust your own abilities. Build in automatic controls, control your work with external software, and have your colleagues double check your work!

Heb je vragen?

We beantwoorden graag al je vragen over de bovenstaande principes en onze software. Gebruik het onderstaande contactformulier en we streven ernaar om je zo spoedig mogelijk te beantwoorden!