Eén cel vergrendelen met absolute verwijzingen

Stel je voor dat het belastingtarief 0.08 in cel E1 staat. De besteltotalen staan in kolom B. Om belasting aan elke bestelling toe te voegen, voer je in C2 het volgende in:

=B2*$E$1

B2 is relatief en volgt dus elke rij, terwijl $E$1 absoluut is en altijd het belastingtarief leest.

Voer de formule vanaf C2 door naar C2, C3, C4 en kijk wat er met elk deel gebeurt:

• B2 wordt B3 en vervolgens B4 (relatief, verschuift)
• $E$1 blijft elke keer $E$1 (absoluut, vast)

Rij 3 bevat dus =B3*$E$1 en rij 4 bevat =B4*$E$1. Het belastingtarief gaat nooit verloren.

Vergeet de dollartekens en schrijf =B2*E1. Wanneer je de formule naar beneden kopieert, verschuift E1 naar E2 en E3, die leeg zijn.

Je belastingkolom wordt gevuld met nullen. De getallen lijken verkeerd, maar de oorzaak is eenvoudig: de verwijzing naar het tarief was relatief terwijl die absoluut had moeten zijn.

Als je E1 snel wilt omzetten in $E$1, klik je op E1 in de formulebalk en druk je één keer op F4. Excel voegt beide dollartekens voor je toe.

Dit is veel sneller en minder foutgevoelig dan de symbolen handmatig typen, vooral in lange formules.

Stel dat $B$1 een USD-naar-EUR-koers van 0.92 bevat en dat de dollarbedragen onder elkaar in kolom A staan. Zet elk bedrag om met:

=A2*$B$1

Kopieer de formule over honderden rijen en elke omzetting gebruikt dezelfde koers. Werk de koers in B1 één keer bij en de hele kolom wordt direct opnieuw berekend.

Omdat beide onderdelen zijn vergrendeld, blijft een absolute verwijzing ongewijzigd wanneer je die naar beneden, naar rechts of in beide richtingen kopieert.

Kopieer =B2*$E$1 naar rechts en de formule wordt =C2*$E$1: B verschuift naar C, maar $E$1 blijft precies staan. De vergrendeling werkt in elke richting.

Absolute verwijzingen komen ook goed van pas in functies. Bereken het aandeel van elke waarde in een vast totaal in B1:

=A2/$B$1

Of vergelijk elk puntentotaal met een doelwaarde in D1:

=IF(A2>=$D$1,"Pass","Fail")

De vergrendelde cel blijft in elke rij het referentiepunt.

Het echte voordeel van een absolute verwijzing is één centraal regelpunt. Omdat elke formule dezelfde vergrendelde cel leest, werkt het wijzigen van die ene cel de hele kolom tegelijk bij.

Verhoog het belastingtarief in E1 van 0.08 naar 0.09 en elke formule met =B2*$E$1 wordt direct opnieuw berekend. Je hoeft geen tientallen formules te bewerken: één invoer stuurt ze allemaal aan.

Ervaren gebruikers zetten constanten, tarieven en doelwaarden vaak in een klein gelabeld gebied boven aan het werkblad en verwijzen er vervolgens absoluut naar.

Zo blijven formules leesbaar en kun je met één invoer het hele rapport bijwerken. Het dollarteken zorgt ervoor dat elke formule naar die invoer blijft verwijzen.

Een absolute verwijzing zoals $A$1 vergrendelt zowel de kolom als de rij, zodat de verwijzing altijd naar dezelfde cel wijst wanneer je de formule kopieert.

• Gebruik dit voor gedeelde waarden zoals tarieven, kosten en doelwaarden
• Combineer het met relatieve verwijzingen, zodat alleen de juiste onderdelen verschuiven
• Druk op F4 om beide dollartekens direct toe te voegen

Vervolgens vergrendel je steeds maar één onderdeel met gemengde verwijzingen.