10 Excel wetten: het verschil of Excel voor jou of jij voor Excel werkt

Excel wetten? Maar Excel biedt je juist alle vrijheid? Anarchie, leve de vrijheid, dat heeft Excel zo populair gemaakt. Leuk natuurlijk, totdat je de prijs van die vrijheid (h)erkent. Inefficiënte en onbeheersbare Excel-modellen zijn uiteindelijk je deel. Kortom, herken je jouw Excel modellen in het bovenstaande? Profiteer dan van de onderstaande Excel wetten. 

Wet #1 – Excel is een rekenprogramma

Los je ALLE handelingen op met berekeningen, dan werkt Excel voor jou. Iedere handelingen, die je NIET berekent blijf je zelf doen en doe jij dus voor Excel. Deze basis wet is eigenlijk heel simpel. Blijf zoeken naar oplossingen in berekeningen. Sta jezelf geen -of zo weinig mogelijk- compromissen toe, die handmatig werk vereisen. De beste Excel-modellen zijn die modellen waarbij de gebruiker het model opent, waarna vervolgens alle data wordt opgehaald en doorberekend. De gebruiker gaat vervolgens direct aan de slag met de beoordeling van de nieuw berekende resultaten in het dashboard.

Wet #2 – Excel kent gebruikers en ontwerpers / bouwers

In de dagelijkse praktijk bezit je vaak, maar niet altijd, beide rollen. Als ontwerper bouw en onderhoud je het model en daarna beoordeel je, als gebruiker, de resultaten. Maak een duidelijk onderscheid tussen de rollen. Je herkent de rollen door hoe je kijkt naar een waarde in een cel. Stel je hebt een bedrag van € 10.000 in cel C5 staan. Als gebruiker beoordeel je of dat bedrag binnen jouw budget past of dat je ergens gaat bezuinigen. Als ontwerper / bouwer ben je niet geïnteresseerd in de hoogte van het bedrag, al was het 10 miljoen. De ontwerper / bouwer garandeert dat (de berekeningen van) het getal op de juiste wijze tot stand is gekomen. Anders gezegd: hij of zij garandeert dat het resultaat van het dashboard betrouwbaar is.

Wet #3 – Denk vanuit berekeningen, niet vanuit het dashboard

Wet #1 stelt dat Excel een rekenprogramma is. Modelleren in Excel is dus denken in berekeningen. Praktisch iedere Excel bouwer begint met het dashboard op te zetten en bouwt van daaruit zijn berekeningen op. Het gevolg is dat er steeds meer dashboards bijkomen. Vaak is dit een kopie van een ander dashboard is (bijvoorbeeld iedere maand in een nieuw tabblad). Gebruik je op die manier meerdere tabbladen, dan krijg je redundante (gelijkvormige, herhalende) berekeningen, wat een dramatisch gevolg heeft op zowel performance als beheersbaarheid. Denk je vanuit berekeningen, dan is een maandoverzicht meer een sjabloon. De gebruiker selecteert de gewenste maand en vervolgens ‘valt’ het resultaat van die maand in het sjabloon.

Wet #4 – Hou je aan de ontwerp volgorde

BEDENK eerst wat je wilt zien in het dashboard, verzamel dan de invoerbronnen (alles wat iemand anders al in werkbestanden, applicaties of het internet heeft gezet wil jij niet opnieuw typen), maak de noodzakelijke berekeningen en laat die vallen in het, dan pas te bouwen, dashboard. Let goed op externe bronnen. Die bezitten niet vaak het formaat dat jij wilt hebben. Gebruik het nieuwe Excel gereedschap Power Query om je importbestanden te transformeren naar het formaat dat jij nodig hebt.

Wet #5 – Excel werkt met bereiken. Denk je in bereiken, dan werk je het meest efficiënt

Een bereik wordt in Excel gedefinieerd door de eerste lege rij onder en boven en de eerste lege kolom rechts en links naast gevulde cellen. Staat de actieve cel in een bereik, dan gebruik de sneltoets combinatie CONTROL +* om het bereik te selecteren. Horen gegevens bij elkaar dan staan deze in een bereik. Horen ze niet bij elkaar, dan zit er minimaal een lege rij of kolom tussen.

Wet #6 – Gebruik namen om jouw bereiken te beheren

Namen vind je in het tabblad Formules, sectie gedefinieerde namen. Gebruik je bijvoorbeeld een naam in een aggregaat functie (bijvoorbeeld een SOM functie) en verandert de grootte van het bereik, dan pas je alleen het bereik in de naam aan, niet de formule.  Zonder namen bouw ik geen fatsoenlijk Excel model. Weet je niet hoe je een naam maakt? Selecteer meerdere cellen met getallen in een bereik, klik in het naamvak en typ een naam. Spaties en cel verwijzingen zijn niet toegestaan. Maak een andere cel actief, gebruik de AutoSom en druk op de F3 toets. In het venster selecteer je de naam, klik op OK en druk vervolgens op Enter en klaar ben je.

Wet #7 – Breng structuur aan door verschillende werkbladen voor verschillende functies te gebruiken

Zo kent een goed Excel model invoer-, berekenings-, variabelen- en uitvoerbladen. De grote Excel beheersbaarheidstruc is dat je plaatsen zoekt waar je diverse functies beheert. Zie je jouw berekeningen als het meest belangrijkste deel van het (automatisch werkend) model, dan wil je dat alle berekeningen bij elkaar staan. Zijn er aanpassingen nodig, dan weet je direct waar je moet zoeken. Daarnaast is beveiliging een stuk gemakkelijker als alle berekeningen in een blad staan. Overigens, per definitie is een berekeningsblad een blad, waar de gebruiker niets te zoeken heeft. Tijdens de productiefase verberg je dit blad.

Wet #8 – Gebruik als vuistregel de 40 – 60% controle wet

Werkt Excel voor jou, dan wil je ook zo min mogelijk handmatige controles uitvoeren. Professionele modellen bestaan voor ongeveer 40 procent uit berekeningen, die je nodig hebt voor de berekeningen van je dashboards. De overige 60 procent van de berekeningen gebruik je voor controles. Hoe meer controles je in jouw model inbouwt, hoe meer zekerheid je hebt over het resultaat van jouw dashboards. En ook hier geldt dat, als je de controles berekenend oplost, Excel voor jou de controles doet.

Wet #9 – Invoer in een berekening is verboden

Gebruik ALTIJD een cel voor de invoer in formules. Maak nooit deze berekeningen: =100*21% of =$G$16*21%. Wanneer het BTW-percentage wijzigt, moet je formules aanpassen. Dat wil je niet. Gebruik je voor de invoer cellen, dan ziet de formule er zo uit: =$G$17*$H$17. Gebruik je namen, dan ziet de formule er zo uit: =VarValue*VarVat.  

Wet #10 – Voorkom formule breuken

Formules in een rij-context bereik moeten gelijk zijn. Deze wet heeft veel te maken met de voorafgaande wet. Voor de integriteit van jouw model wil je dat rij-context formules gelijk zijn, zodat de formules automatisch door Excel worden gemaakt. Je gebruikt daarvoor de dubbelklik op de vulgreep – techniek. Omdat je niet ziet wat in de formules staat, is een formule breuk niet makkelijk zichtbaar te maken. Dit kan dodelijk zijn voor de resultaten van jouw model.

Bovenstaande wetten zijn handvatten om professionele Excel modellen te bouwen. Pas je deze wetten toe, dan leidt dit tot andere inzichten hoe je modelleert in Excel. Een van inzichten is dat modelleren in Excel feitelijk een logische exercitie omvat van invoer (van je brondata) naar uitvoer (in dashboards). Binnen die logische exercitie ben je steeds op zoek naar plaatsen waar je het meest efficiënt de verschillende onderdelen, waaruit jouw model bestaat, beheert.

Ik kan het niet nalaten om een laatste uitsmijter aan dit artikel toe te voegen. Hou je jezelf vast aan de volgende vuistregel: voor de gebruiker zijn de dashboards het belangrijkst; voor de ontwerper / bouwer zijn de berekeningen het belangrijkst.

#1 – Ik spreek hier van dashboards, maar dat kunnen natuurlijk ook rapportages, overzichten tabellen etc. zijn.

Henk Vlootman geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. In 2016 werd de MVP award voor de gebieden Excel en Data platform toegekend. Die laatste erkenning kreeg hij vanwege zijn werk met de Power BI gereedschappen.

Henk is de auteur van “Excelmodellen voor financiële economische informatie” en “ExcelLeren, de Vlootman methode in de praktijk” en medeauteur van het “Praktijkboek Powerpivot in Excel” en “Handboek Power pivot”. Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.