Dollarteken in Excel: zo vergrendel je celverwijzingen

Dollarteken in Excel-formules (celverwijzingen)

In deze sectie leg ik uit hoe het dollarteken celverwijzingen in formules vergrendelt en waarom dat belangrijk is voor je werk. Excel gebruikt drie typen verwijzingen: relatieve, absolute en gemengde verwijzingen, en ik ga ze alle drie langs.

Relatieve verwijzingen (geen $)

Alle verwijzingen in Excel zijn standaard relatief. Dat betekent: als je in cel C1 een formule schrijft als = A1 + B1 en je kopieert die naar C2, past Excel de verwijzingen automatisch aan en wordt de formule = A2 + B2. Elke celverwijzing verschuift relatief ten opzichte van de nieuwe positie.

Ze werken omdat de meeste formules een patroon volgen langs rijen of kolommen. Je wilt dezelfde formule niet telkens opnieuw typen als je die ook eenmaal kunt kopiëren en Excel elke verwijzing slim aanpast.

Absolute verwijzingen ($A$1)

Met dit type vergrendel je zowel de kolom als de rij. Anders dan bij de relatieve verwijzing. Wanneer je =$A$1 schrijft, houdt Excel die celverwijzing vast, en als je deze formule ergens op dat werkblad (C10, B20) of naar een ander blad kopieert, zal hij altijd naar A1 verwijzen. 

Een absolute verwijzing lost een specifiek probleem op: Stel dat je 40% korting op elke artikelprijs in een verkoopdataset moet berekenen, en dat percentage staat in cel D2, en je doet dit voor zo'n 80 records. Je wilt de formule niet 80 keer typen, maar één formule naar beneden kopiëren.

Het probleem daarbij is dat, als je = E3*D2 in de eerste rij schrijft en omlaag kopieert, dit gebeurt:

• Rij 1: =E3*D2 (correct—E3 keer D2)

• Rij 2: =E4*D3 (fout—D3 is leeg!)

• Rij 3: =E5*D4 (fout—D4 is leeg!)

• Rij 4: =E6*D5 (fout—D5 is leeg!)

Excel verschuift beide verwijzingen omlaag, terwijl je alleen de verwijzing naar kolom E wilt laten verschuiven en D gelijk wilt houden.

De oplossing is: =E3*$D$2.

Als je nu naar beneden kopieert, gebeurt dit:

• Rij 1: =E3*$D$2 (E3 keer het kortingspercentage)

• Rij 2: =E4*$D$2 (E4 keer hetzelfde kortingspercentage)

• Rij 3: =E5*$D$2 (E5 keer hetzelfde kortingspercentage)

• Rij 4: =E6*$D$2 (E6 keer hetzelfde kortingspercentage)

Zie je het verschil? E verschuift (E3 wordt E4 wordt E5) terwijl $D$2 vast blijft. Het wordt nooit $D$3 of $D$4. Deze techniek is erg belangrijk in financiële modellen; wil je daar meer over leren, volg dan onze cursus Financial Modeling in Excel. 

Gemengde verwijzingen ($A1, A$1)

Gemengde verwijzingen gebruik je om óf de kolom óf de rij te vergrendelen, maar niet beide. Dit is krachtig zodra je weet hoe je het toepast. Er zijn twee manieren om de gemengde verwijzing te gebruiken; $A1 en A$1.

• =$A1 vergrendelt de kolom (A) maar laat de rij veranderen. Wanneer je deze formule naar rechts naar kolom B kopieert, blijft het A; kopieer je omlaag naar rij 2, dan wordt het $A2.

• =A$1 vergrendelt de rij (1) maar laat de kolom veranderen. Wanneer je deze formule naar rechts naar kolom B kopieert, wordt het B$1; kopieer je omlaag, dan blijft het rij 1.

Ik heb besproken wat elke verwijzing doet. Laat me nu een overzichtstabel toevoegen die handig kan zijn:

De regels in een notendop:

Hoe voeg je een dollarteken toe in Excel met F4

Om productiever te werken kun je met de F4-toets door de verschillende verwijzingstypen bladeren, zodat je het dollarteken niet handmatig hoeft te typen.

Gebruik deze techniek door in een cel te klikken en je formule te beginnen, bijvoorbeeld =A1+B1

Stop met typen en klik vlak voor of in het A1-gedeelte (het deel dat je wilt wijzigen).

Druk op F4

Excel bladert door de verschillende verwijzingstypen:

• Eerste druk: A1 wordt $A$1 (beide vergrendeld)

• Tweede druk: $A$1 wordt A$1 (rij vergrendeld, kolom vrij)

• Derde druk: A$1 wordt $A1 (kolom vergrendeld, rij vrij)

• Vierde druk: $A1 wordt A1 (terug naar start)

Stop met drukken zodra je de juiste verwijzingsnotatie voor jouw geval hebt.

Let op: deze sneltoets werkt op zowel Windows als Mac; werkt F4 niet op je Mac, controleer dan of Function Lock is ingeschakeld of probeer Fn+F4. Als F4 helemaal niet werkt, typ dan gewoon zelf het dollarteken.

Wil je meer begrijpen over sneltoetsen in Excel, dan is de Excel Shortcuts Cheat Sheet een goed startpunt.

Veelgemaakte fouten met dollartekens in Excel

Er zijn een paar fouten die je gemakkelijk kunt maken wanneer je het dollarteken in Excel voor het eerst gebruikt, namelijk:

• Te veel vergrendelen: Je vergrendelt vaak alles. Je schrijft iets als =$A$1+$B$1+$C$1 terwijl =A1+B1+C1 voldoende is. Dat breekt niets, maar maakt het onnodig complex. Vergrendel alleen wat nodig is. Ik raad aan te beginnen met relatieve verwijzingen en pas vergrendelingen toe wanneer een formule verkeerde resultaten oplevert.

• Vergeten te vergrendelen wanneer nodig: De tegenovergestelde fout is een formule kopiëren die naar een constante verwijst zonder de verwijzing te vergrendelen. Je kortingsberekening verwijst naar het kortingspercentage in D2. Je kopieert de formule omlaag, met de bedoeling dat D2 vast blijft, maar je gebruikte =amount*D2 in plaats van =amount*$D$2. Rij 2 schuift door naar rij 3, rij 4, enzovoort. Je berekeningen gaan mis.

• Formules kopiëren zonder de verwijzingen te controleren: Controleer altijd of je formuleverwijzingen correct aanpassen na het kopiëren. Neem dertig seconden om in de doelcel te klikken en de formulebalk te bekijken. Zijn de verwijzingen veranderd zoals verwacht? Zo niet, pas ze aan met F4 of handmatig.

Dollarteken in Excel voor valuta-opmaak

Ben je hier beland omdat je dollartekens in je cellen wilt weergeven in plaats van ze in formules te gebruiken? Dit moet je weten.

Wanneer je valuta-opmaak op een cel toepast, voegt Excel het $-symbool toe aan de weergave. Dat kan op 3 manieren:

• Klik met de rechtermuisknop op de cel → Cellen opmaken → kies Valuta.
• Gebruik de knop $ op de werkbalk (tabblad Start, bovenaan het scherm).
• Druk op Ctrl+1 op Windows of Cmd+1 op Mac en kies vervolgens Valuta.

Excel biedt ook twee stijlen voor valuta-opmaak:

• Valutanotatie: Hierbij staat het $-symbool direct naast het eerste cijfer in een opgemaakte cel.
• Boekhoudnotatie: Het $-symbool staat uiterst links in de cel en het getal uiterst rechts. Dit maakt kolommen makkelijker leesbaar omdat de decimalen perfect uitlijnen.

Belangrijk om te weten: welke opmaak je ook toepast, het beïnvloedt de berekeningen niet. Bijvoorbeeld, als je in cel A1 $1,500.00 hebt en in cel A2 2, dan wordt bij een bewerking A1 herkend als “1500”, ongeacht de toegepaste opmaak. Lokalisatie is van belang voor symbolen, maar de onderliggende logica blijft hetzelfde.

Conclusie

Ik hoop dat dit artikel je heeft geholpen te begrijpen hoe het wordt gebruikt om celverwijzingen in formules te vergrendelen, zodat je bepaalt hoe verwijzingen aanpassen wanneer formules naar andere cells worden gekopieerd.

Een andere belangrijke les uit dit artikel is te begrijpen welk verwijzingstype je gebruikt van de drie die genoemd zijn (Relatieve (A1), Absolute ($A$1) en Gemengde ($A1 of A$1) verwijzingen).

Dat gezegd hebbende: de beste manier om dit soort concepten te begrijpen is door te oefenen. Bouw een klein werkblad met een dataset, kopieer een formule omlaag terwijl je kijkt hoe de verwijzingen verschuiven, druk op F4 en blader door de verwijzingstypen. Zo veranker je het begrip beter dan met alleen een theoretische uitleg.