1. Home
  2. Artikelen
  3. 10 Excel wetten: het verschil of Excel voor jou of jij voor Excel werkt

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


Die managers werken voor Excel, terwijl Excel bedoeld is om voor hen te werken. De 10 onderstaande wetten zijn een aanzet om verandering aan te brengen in jouw denkpatroon omtrent modelleren in Excel.

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.


Gerelateerde artikelen

5 stappen in Power BI (2) Modelleren en berekenen

29-05-2020 11:23

Modelleren en berekenen, Power BI en DAX: webinar van Excel MVP Tony de Jonker.

De echte waarde van Power BI

29-05-2020 11:19

Je wilt bij BI-projecten zo snel mogelijk naar de niveaus delen en visualiseren, waar de waardecreat...

Visualiseren, de kunst van het verleiden (1)

29-05-2020 11:16

Visualiseren in Excel en Power BI, deel 1: de rol van hiërarchieën.

Visualiseren, de kunst van het verleiden (2)

29-05-2020 07:42

Visualiseren in Excel en Power BI, deel 2: schakelen tussen hiërarchieën.

5 stappen in Power BI

15-05-2020 13:00

Webinar van Excel MVP Tony de Jonker ontsluit de geheimen van Power BI (1)

De 8 do's en 8 don'ts bij een doorstart

15-05-2020 12:09

Wordt er in uw bedrijf een doorstart overwogen of voorbereid? Dan is het verstandig onderstaande vui...

Betere rapportages met Excel en Power BI

08-05-2020 10:56

Excel MVP Tony de Jonker's tips om met Excel en Power BI uw financiële rapportages te versnellen en...

Supermarkten in Control

15-04-2020 11:54

Supermarkten zijn het nieuwe rolmodel van de anderhalvemeter economie. Wanneer voor duurzaamheid?

Vergroot uw kennis, online en ook weer klassikaal

15-04-2020 11:54

Uw veiligheid staat voorop. Alle cursussen zijn 100% Coronaproof. Indien een leslocatie gesloten is,...

Veel horecabedrijven in de problemen bij terugbeta...

15-04-2020 11:52

Veel bedrijven komen ondanks overheidsmaatregelen in zwaar weer, mede door de terugvallende omzet va...

Coronacrisis mag geen schuldencrisis worden

15-04-2020 11:33

"Alleen samen kunnen we de financiële schade beperken." Michel van Leeuwen, directeur en gerechtsdeu...

'Organisaties niet goed voorbereid op ransomware'

15-04-2020 09:30

Organisaties lopen flink risico om geraakt te worden door ransomware én de impact kan dan groot zijn...