Cirkelverwijzingen in Excel vinden

Het gebeurt meestal op het slechtst denkbare moment: een spreadsheet die gisteren nog prima werkte, geeft vlak voor een deadline ineens een waarschuwing voor een cirkelverwijzing.

Het goede nieuws? Deze cirkelverwijzingen zijn makkelijker te verhelpen dan je denkt.

In deze gids leg ik uit:

• Hoe je cirkelverwijzingen in Excel vindt
• De verschillende manieren waarop ze ontstaan
• Hoe je ze oplost met beproefde methoden

Wat zijn cirkelverwijzingen in Excel?

Een cirkelverwijzing in Excel ontstaat wanneer een formule naar zijn eigen cel verwijst (direct of indirect) en zo een berekeningslus creëert die Excel niet kan oplossen. Als in cel A1 bijvoorbeeld =A1+10 staat, blijft Excel eindeloos proberen het resultaat te berekenen.

Ook indirecte lussen zorgen voor problemen. Als A1 afhangt van B1, B1 afhangt van C1 en C1 weer afhangt van A1, zit Excel vast. Het bestand kan lang doen over berekenen, onjuiste waarden tonen of helemaal stoppen met updaten.

Sommige cirkelverwijzingen zijn simpele formuleslippertjes, andere zijn juist bewuste ontwerpkeuzes in geavanceerde modellen. Daar kom ik later op terug, maar eerst de verschillende typen.

Directe cirkelverwijzingen

Een directe cirkelverwijzing treedt op wanneer een formule zijn eigen celadres bevat. Stel dat je twee waarden hebt in de cellen A2 en B2. Als we in cel C2 A2 + B2 + C2 typen, verwijst C2 direct naar zichzelf. 

Wanneer je de formule invoert en op Enter drukt, verschijnt er een pop-up met een waarschuwing voor een cirkelverwijzing. En als je op OK klikt, geeft de formule een onverwacht resultaat terug.

Directe cirkelverwijzing. Afbeelding door de auteur.

Dit levert 0 op, want als we A2 en B2 optellen, zou dat 6 moeten zijn, maar 'C2' blijft zichzelf toevoegen. Excel kan C2 niet berekenen zonder de waarde van C2 te kennen. Dat creëert een directe lus die nooit oplost.

Let op: Dit zijn de makkelijkst te herkennen cirkelverwijzingen, omdat de zelfverwijzing rechtstreeks in de formulesyntaxis te zien is. Excel detecteert en markeert ze zelfs meteen zodra je de formule invoert.

Indirecte cirkelverwijzingen

Een indirecte cirkelverwijzing ontstaat wanneer meerdere cellen samen een afhankelijkheidsketen vormen die terugloopt naar het beginpunt.

Bijvoorbeeld:

• F2 verwijst naar H4

• H4 verwijst naar E4

• E4 verwijst naar F2

Indirecte cirkelverwijzing. Afbeelding door de auteur.

Geen enkele formule lijkt op zichzelf fout. Het probleem verschijnt pas wanneer Excel de hele keten naloopt en ziet dat de berekening terugkeert naar het startpunt.

Deze zijn lastiger op te sporen, omdat de lus over veel cellen kan lopen en, in complexe werkmappen, zelfs over meerdere werkbladen, voordat Excel de cirkelafhankelijkheid herkent.

Verborgen cirkelverwijzingen

Een verborgen cirkelverwijzing is er een waarbij de afhankelijkheidslus niet duidelijk is aan de hand van de formulesyntaxis. Deze komen vaak voort uit:

• Genoemde bereiken

• INDIRECT()- of OFFSET()-functies

• Complexe voorwaardelijke logica

Bijvoorbeeld: een genoemd bereik QuarterlyTotal omvat de cellen C3 tot en met C5, die kwartaalomzet bevatten.

In cel C5 staat in de formulebalk:

=QuarterlyTotal*0.1Mogelijk gemaakt door

Op het eerste gezicht lijkt dit prima. De formule verwijst niet rechtstreeks naar C5. Maar omdat QuarterlyTotal C5 omvat, moet Excel C5 berekenen als onderdeel van het totaal dat wordt gebruikt om C5 te berekenen.

Dat creëert een verborgen lus:

• Excel probeert QuarterlyTotal te berekenen

• QuarterlyTotal omvat C5

• C5 hangt af van QuarterlyTotal

De verwijzing is circulair, ook al laat de formulesyntaxis dat niet expliciet zien.

Dat maakt verborgen cirkelverwijzingen zo lastig te troubleshooten. De formules lijken correct, de werkmap kan lange tijd normaal rekenen en de cirkelverwijzing komt pas aan het licht wanneer Excel het genoemde bereik evalueert.

Verborgen cirkelverwijzing. Afbeelding door de auteur.

Voorwaardelijke logica kan dit nog lastiger maken. Waarom? Omdat geneste IF()-formules soms alleen onder bepaalde voorwaarden een cirkelverwijzing creëren. Daardoor werkt het bestand totdat bepaalde waarden wijzigen.

Verborgen cirkelverwijzingen zijn het moeilijkst te verhelpen, omdat de afhankelijkheidslus wordt verhuld door abstractie in plaats van zichtbare celverwijzingen.

Opzettelijke vs. per ongeluk gemaakte cirkelverwijzingen

Sommige cirkelverwijzingen bestaan bewust. Andere zijn fouten die je moet verwijderen.

Opzettelijke (bewuste) cirkelverwijzingen

Een opzettelijke cirkelverwijzing wordt bewust gemaakt om specifieke modelleerbehoeften te ondersteunen waarbij waarden van elkaar afhankelijk zijn.

Neem financiële modellering als voorbeeld: rentekosten hangen af van het uitstaande schuldsaldo, terwijl het schuldsaldo wijzigt op basis van de kasstroom, die de rentekosten bevat. Dit creëert een feedbacklus die echte bedrijfslogica weerspiegelt.

Het model begint met een eenvoudige structuur:

• Schuldsaldo stelt het openingsbedrag voor
• Rentekosten worden berekend als een percentage van de schuld
• Bijgewerkte schuld omvat zowel het oorspronkelijke saldo als de rente

Opzettelijke cirkelverwijzingen. Afbeelding door de auteur.

Wanneer rente wordt berekend op basis van schuld en de bijgewerkte schuld die rente bevat, loopt de berekening vanzelf in een lus. Excel markeert dit als een cirkelverwijzing en geeft standaard nul terug, omdat het de afhankelijkheid niet in één stap kan oplossen.

Om dit te voorkomen, schakel je iteratieve berekening in:

1. Ga naar Bestand > Opties > Formules
2. Vink in de sectie Berekeningsopties de optie Iteratieve berekening inschakelen aan

Schakel de optie voor iteratieve berekening in. Afbeelding door de auteur.

Zodra iteratieve berekening is ingeschakeld, herberekent Excel herhaaldelijk de rente en de bijgewerkte schuld. Bij elke ronde passen de getallen zich iets aan totdat ze stabiliseren en niet meer veranderen. 

Het eindresultaat is een geconvergeerde uitkomst in plaats van een berekening in één stap. Dit werkt omdat de cirkelverwijzing opzettelijk is. Rente beïnvloedt schuld en schuld beïnvloedt rente. Elke waarde beïnvloedt de andere, dus herhaalde herberekening is verwacht en nodig.

Iteratieve berekening. Afbeelding door de auteur.

Let op: iteratieve berekening garandeert op zichzelf geen betekenisvol resultaat. Het herhaalt de logica die je in het model hebt ingebouwd; daarom vereisen opzettelijke cirkelverwijzingen zorgvuldige monitoring en validatie.

Per ongeluk gemaakte cirkelverwijzingen

Een per ongeluk gemaakte cirkelverwijzing ontstaat door formulefouten, copy-pastefouten of onbedoelde wijzigingen. Stel, je voert de waarden 2, 4 en 6 in in cellen A1:A3.

In A4 wil je een totaal, dus voer je in:

=SUM(A1:A4)Mogelijk gemaakt door

Cel A4 neemt nu zichzelf mee in de berekening.

Wanneer je op Enter drukt, toont Excel een waarschuwing voor een cirkelverwijzing. Na op OK te klikken, geeft Excel 0 terug.

Per ongeluk gemaakte cirkelverwijzing. Afbeelding door de auteur.

Deze cirkelverwijzing weerspiegelt geen echte logica. Hij bestaat omdat het formuledomein verkeerd is.

Hoe los je cirkelverwijzingen in Excel op

Zodra Excel een lus markeert, moet je precies lokaliseren waar die begint en beslissen hoe je hem oplost. 

Laten we een paar methoden bekijken om cirkelverwijzingen te verhelpen, afhankelijk van of ze per ongeluk of opzettelijk zijn: 

Snel detecteren om het probleem te lokaliseren

Excel heeft verschillende ingebouwde detectietools om cirkelverwijzingen snel te vinden, elk met eigen sterke punten en beperkingen. Laten we deze opties doornemen. 

Meldingen in de statusbalk

De snelste manier om een cirkelverwijzing te detecteren is via de statusbalk linksonder in het Excel-venster.

Wanneer er een cirkelverwijzing bestaat, toont Excel tekst zoals “Cirkelverwijzingen: G4.” Dit vertelt je de meest recent gedetecteerde cel die bij een lus betrokken is.

Cirkelverwijzing weergeven in de statusbalk. Afbeelding door de auteur.

De statusbalk toont echter slechts één cirkelverwijzing tegelijk. Als er meerdere lussen zijn, laat Excel ze één voor één zien.

Zo pak je dit aan: 

1. Herstel de cel die in de statusbalk wordt getoond
2. Controleer de statusbalk opnieuw
3. Herhaal totdat het bericht verdwijnt

Tip: Noteer elk celadres, want het oplossen van de ene lus onthult vaak een andere.

Eerste waarschuwingsmeldingen

Excel toont ook een waarschuwing wanneer:

• Je een cirkelverwijzing maakt, of
• Je een werkmap opent die er al een bevat

De waarschuwing vermeldt meestal dat een of meer formules naar zichzelf verwijzen en mogelijk onjuist rekenen.

Deze waarschuwingen kunnen stoppen met verschijnen als:

• Je het bericht eerder hebt gesloten door op OK te klikken
• Iteratieve berekening is ingeschakeld
• De berekeningsmodus is ingesteld op Handmatig (Formules > Berekeningsopties > Handmatig)

Als de resultaten onjuist lijken en er geen waarschuwing verschijnt, forceer dan een herberekening:

• Druk op F9 om het actieve werkblad te herberekenen
• Druk op Ctrl + Alt + F9 om alle werkbladen te forceren tot herberekening

Tip: Het sluiten en opnieuw openen van de werkmap kan er ook voor zorgen dat Excel de waarschuwing opnieuw toont.

Foutcontroles

Om meerdere cirkelverwijzingen tegelijk te bekijken, gebruik je de tool Fouten controleren van Excel: Ga naar Formules > Fouten controleren > Cirkelverwijzingen

Dit menu somt alle gedetecteerde cirkelverwijzingen op en laat je direct naar elke cel springen, wat het diagnosticeren van complexe werkmappen makkelijker maakt.

Als het submenu Cirkelverwijzingen leeg lijkt maar Excel nog steeds vreemd gedrag vertoont, druk dan op F9 om te herberekenen en de lijst te verversen.

Een tool voor foutencontrole om cirkelverwijzingen te vinden. Afbeelding door de auteur.

Let op: In Excel 2007 en later is deze functie beschikbaar op het Lint. Oudere versies bieden vergelijkbare opties via de menubalk.

Visuele traceringstools

Zodra je een probleemcel hebt geïdentificeerd, gebruik je visuele traceringstools om te zien hoe de cirkelverwijzing ontstaat.

Deze tools tekenen pijlen tussen cellen en maken afhankelijkheden zichtbaar in plaats van verborgen in formules. Beide vind je in de groep Formulecontrole op het tabblad Formules:

• Voorgangers traceren toont welke cellen invoer zijn voor de geselecteerde formule. Excel tekent pijlen van invoercellen naar de formulecel. Sneltoets: Alt + M, P
• Afhankelijken traceren toont welke cellen de waarde van de geselecteerde cel gebruiken. Excel tekent pijlen van de geselecteerde cel naar formules die ernaar verwijzen. Sneltoets: Alt + M, D

Als het traceren van voorgangers en afhankelijken uiteindelijk terugleidt naar de startcel, vormen de pijlen een lus en bevestigen ze de cirkelverwijzing.

Voorgangers traceren en Afhankelijken traceren. Afbeelding door de auteur.

Na het corrigeren van de formule lopen de pijlen niet meer terug en verdwijnt de cirkelverwijzing. Je kunt de pijlen verwijderen met Pijlen verwijderen in hetzelfde menu.

Formules herstructureren en alternatieve berekeningen

De meeste cirkelverwijzingen ontstaan omdat berekeningen in de verkeerde volgorde staan, niet omdat Excel hapert. Voor zulke per ongeluk gemaakte cirkelverwijzingen is herstructureren van formules de effectiefste oplossing. 

Hier zijn een paar manieren om dit op te lossen:  

Formules herstructureren om zelfafhankelijkheden te verwijderen

Cirkelverwijzingen verschijnen vaak wanneer een formule een waarde probeert te berekenen met behulp van zijn eigen resultaat.

Een formule als =A2+1 in cel A2 creëert bijvoorbeeld een directe lus omdat de cel van zichzelf afhangt. Verplaats om dit te verhelpen de benodigde invoer naar een aparte cel en verwijs in plaats daarvan naar die waarde.

Zodra de afhankelijkheid is verwijderd, kan Excel de formule normaal berekenen.

Hulpkolommen gebruiken voor tussenberekeningen

Cirkelverwijzingen ontstaan ook wanneer één formule te veel stappen tegelijk probeert uit te voeren.

Als twee cellen bijvoorbeeld van elkaar afhankelijk zijn om een gedeelde waarde te berekenen, heeft Excel geen duidelijk startpunt. Introduceer een hulpcel die eerst de tussenberekening uitvoert.

Door gedeelde waarden in een aparte hulpcel te berekenen, kan Excel een duidelijke volgorde volgen:

1. Hulpcel berekent eerst
2. Afhankelijke formules berekenen daarna

Deze aanpak vereenvoudigt formules en elimineert cirkelafhankelijkheden.

Samenvattingen scheiden van berekeningen

Totalen en samenvattingsformules moeten niet worden vermengd met de cellen waarvan ze afhangen. Wanneer een samenvatting getallen ophaalt uit een bereik waar het zelf in zit, creëert Excel een cirkelverwijzing. 

Als A2 bijvoorbeeld een basisbedrag van 1000 bevat en B2 een percentage van 10%, C2 het totaal toont en D2 een vergoeding berekent.

Als C2 =A2+D2 gebruikt en D2 =C2*$B$2, ontstaat een cirkelverwijzing omdat het totaal afhangt van de vergoeding en de vergoeding afhangt van het totaal. 

Excel kan geen van beide eerst berekenen.

Om dit te verhelpen, moet de vergoeding vanaf de basis worden berekend, niet vanaf het totaal. Verander D2 dus in =A2*$B$2 en laat C2 =A2+D2.

Gebruik aparte cellen om cirkelverwijzingen te voorkomen. Afbeelding door de auteur.

Nu kan Excel eerst de vergoeding berekenen en die daarna optellen bij het basisbedrag. De cirkelverwijzing verdwijnt omdat elke formule een duidelijk startpunt heeft.

Iteratieve berekening inschakelen voor opzettelijke circulaire logica

Iteratieve berekening moet je alleen gebruiken wanneer cirkelverwijzingen bewuste ontwerpelementen zijn, niet wanneer ze per ongeluk ontstaan. Deze methode is geschikt voor financiële modellen en andere scenario’s met legitieme feedbacklussen, waarin de circulaire logica echt gedrag weerspiegelt.

Iteratieve berekening inschakelen in Excel voor Windows:

1. Ga naar Bestand > Opties > Formules
2. Vink onder Berekeningsopties Iteratieve berekening inschakelen aan
3. Klik op OK

Eenmaal ingeschakeld herberekent Excel formules met cirkelverwijzingen herhaaldelijk totdat ze stabiliseren of een stopconditie bereiken.

Belangrijke iteratie-instellingen

Twee instellingen bepalen hoe iteratieve berekening zich gedraagt:

• Maximumaantal iteraties bepaalt hoe vaak Excel de formules herberekent voordat het stopt. Een waarde van 100 werkt goed voor de meeste business- en financiële modellen.
• Maximale wijziging definieert hoe klein het verschil tussen berekeningsresultaten moet zijn voordat Excel stopt met herberekenen. Een waarde van 0,001 is een veelgebruikte startwaarde die nauwkeurigheid en performance in balans houdt.

Het verhogen van een van beide kan de precisie verbeteren, maar kan herberekenen in grote werkmappen vertragen.

Wanneer je deze methode gebruikt

Schakel iteratieve berekening alleen in wanneer:

• De cirkelverwijzing geldige bedrijfslogica vertegenwoordigt
• Het model legitieme feedbacklussen bevat (bijvoorbeeld rente die afhangt van schuld, terwijl schuld verandert op basis van rente)
• Je verwacht dat waarden convergeren in plaats van eindeloos te oscilleren

Deze methode is geen oplossing voor per ongeluk gemaakte cirkelverwijzingen door formulefouten.

Belangrijke waarschuwingen

Houd vóór het inschakelen van iteratieve berekening rekening met deze risico’s:

• Per ongeluk gemaakte cirkelverwijzingen geven mogelijk geen waarschuwingen meer, waardoor fouten lastiger te detecteren zijn.
• Grote of complexe modellen kunnen trager herberekenen.
• Als waarden oscilleren in plaats van stabiliseren, kunnen resultaten onbetrouwbaar zijn.

Vanwege deze risico’s moet iteratieve berekening altijd expliciet worden gedocumenteerd.

Een “stroomonderbreker” implementeren voor complexe modellen

Een stroomonderbreker is een geavanceerde techniek waarmee je bepaalt wanneer circulaire logica actief is. In plaats van een cirkelverwijzing te verwijderen, pauzeer of sta je die bewust toe met een stuurwaarde in de formule.

Deze aanpak past het best bij financiële modelleurs en power users die circulaire logica soms nodig hebben, maar niet continu.

Een stroomonderbreker gebruikt doorgaans:

• Een stuurcel (vaak ingesteld op 0 of 1)

• Een IF-verklaring die schakelt tussen circulaire en niet-circulaire logica

Het algemene patroon ziet er zo uit:

=IF($A$1=1, circular_logic, non_circular_alternative)Mogelijk gemaakt door

• Als de stuurcel 0 is, vermijdt de formule de cirkelverwijzing
• Als de stuurcel 1 is, wordt de circulaire logica toegestaan

Stel dat je de volgende waarden invoert: 

• A1: Stroomonderbreker (0 of 1)

• A2: Beginsaldo = 1000

• B2: Rente

• C2: Bijgewerkt saldo

Gebruik in B2 =IF($A$1=1, C2*10%, A2*10%) en voer in C2 =A2+B2 in

Nu:

• Als A1 = 0, wordt de rente berekend vanaf het beginsaldo. De cirkelverwijzing is uitgeschakeld en Excel rekent normaal.

• Als A1 = 1, wordt de rente berekend vanaf het bijgewerkte saldo. De circulaire logica wordt actief en creëert een bewuste feedbacklus.

Een stroomonderbreker gebruiken. Afbeelding door de auteur.

Wanneer gebruik je een stroomonderbreker

Stroomonderbrekers zijn nuttig voor:

• Circulaire logica uit laten staan tijdens het opbouwen en valideren van formules
• Circulaire logica aan- en uitzetten om issues te isoleren en resultaten te verifiëren
• Het model tonen met circulaire logica uitgeschakeld om aannames en mechaniek eenvoudiger uit te leggen

Problematische formules verwijderen en opnieuw beginnen

Dit is de nucleaire optie: gebruik deze wanneer andere methoden falen. Het is geschikt voor werkmappen met hopeloos verwarde afhankelijkheden, geërfde bestanden met onduidelijke logica of situaties waarin tijdsdruk om een snelle maar betrouwbare oplossing vraagt.

In plaats van kapotte formules te ontwarren, reset je het model en bouw je het methodisch opnieuw op. 

Zo pak je dat aan.

Stap 1: Maak een back-upkopie

Maak vóór je wijzigingen doorvoert een volledige back-up van de werkmap. Zo doe je dat:

• Open de werkmap in Excel

• Ga naar Bestand > Opslaan als > Bladeren en kies een andere locatie of behoud dezelfde map om je bestand op te slaan

• Hernoem het bestand, bijvoorbeeld Workbook_Backup.xlsx

• Klik op Opslaan

Maak een back-upkopie van een werkmap. Afbeelding door de auteur.

Dit bewaart de oorspronkelijke formules, logica en resultaten zodat je er later naar kunt verwijzen of kunt terugdraaien indien nodig.

Probeer deze methode nooit op de enige kopie van een bestand.

Stap 2: Documenteer de huidige structuur

Leg vervolgens vast hoe de werkmap nu werkt:

• Neem schermafbeeldingen van sleutelgedeelten
• Noteer wat elk werkblad berekent
• Identificeer input, output en samenvattingen

Deze documentatie helpt te begrijpen wat herbouwd moet worden en voorkomt dat belangrijke logica verloren gaat tijdens het opschonen.

Stap 3: Vervang formules door waarden

Om alle cirkelafhankelijkheden in één keer te doorbreken, kopieer je de betrokken bereiken en gebruik je Plakken speciaal. Rechtermuisknop > Plakken speciaal > Waarden om formules te vervangen door hun berekende resultaten.

Gebruik de optie Plakken speciaal. Afbeelding door de auteur.

Dit verwijdert direct alle cirkelverwijzingen terwijl de zichtbare cijfers als tijdelijk ijkpunt behouden blijven.

Stap 4: Formules stapsgewijs herbouwen

Bouw de werkmap sectie voor sectie opnieuw op in plaats van alles in één keer:

1. Voeg formules toe voor één logisch blok
2. Druk op F9 om herberekenen te forceren
3. Controleer dat er geen cirkelverwijzing verschijnt
4. Ga door naar de volgende sectie

Stap 5: Resultaten verifiëren met de back-up

Vergelijk, naarmate elke sectie is herbouwd, de resultaten met het back-upbestand. Je kunt verschillen zien die wijzen op:

• Ontbrekende input
• Onjuiste formulelogica
• Eerder verborgen rekenfouten

Geavanceerde detectie voor hardnekkige verwijzingen

Sommige cirkelverwijzingen verschijnen niet met de standaardtools van Excel. Probeer in zulke gevallen geavanceerde technieken: 

VBA-macro’s voor volledige scanning

Als handmatig traceren te traag is, kan VBA duizenden cellen in seconden scannen en cirkelverwijzingen in de hele werkmap identificeren.

De basisaanpak is als volgt:

• Druk op Alt + F11 om de Visual Basic Editor te openen.
• Ga naar Invoegen > Module.
• Plak een macro voor het detecteren van cirkelverwijzingen.
• Druk op F5 om hem uit te voeren.

VBA-interface. Afbeelding door de auteur.

Tip: Je hoeft geen VBA from scratch te schrijven. Beschrijf in gewone taal wat je wilt en laat ChatGPT een aangepaste detectiemacro genereren.

Hier is een voorbeeld van een VBA-script: 

Option Explicit
Public Sub HighlightCircularReferences_ActiveSheet()
    Dim ws As Worksheet, c As Range
    Dim hasAny As Boolean

    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    ' Optional: clear previous highlighting (only on used range)
    On Error Resume Next
    ws.UsedRange.Interior.Pattern = xlNone
    On Error GoTo 0

    ' Scan formula cells only
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If IsCircularFormula(c) Then
            c.Interior.Color = RGB(255, 230, 153) ' light yellow
            hasAny = True
        End If
    Next c

    Application.ScreenUpdating = True

    If hasAny Then
        MsgBox "Circular reference cells highlighted on: " & ws.Name, vbInformation
    Else
        MsgBox "No circular references found on: " & ws.Name, vbInformation
    End If
End Sub

Private Function IsCircularFormula(ByVal startCell As Range) As Boolean
    Dim inPath As Object
    Set inPath = CreateObject("Scripting.Dictionary")

    IsCircularFormula = TraceForCycle(startCell, startCell, inPath, 0)
End Function

Private Function TraceForCycle(ByVal startCell As Range, ByVal curCell As Range, _
                               ByVal inPath As Object, ByVal depth As Long) As Boolean
    Dim prec As Range, a As Range, x As Range
    Dim key As String

    ' Safety limit to avoid deep/huge graphs freezing Excel
    If depth > 200 Then Exit Function

    key = curCell.Parent.Name & "!" & curCell.Address(False, False)

    If inPath.Exists(key) Then
        ' We hit a loop in the current path
        TraceForCycle = True
        Exit Function
    End If

    inPath.Add key, True

    ' Direct precedents (cells referenced by curCell)
    On Error Resume Next
    Set prec = curCell.DirectPrecedents
    On Error GoTo 0

    If Not prec Is Nothing Then
        For Each a In prec.Areas
            For Each x In a.Cells
                ' If any precedent is the start cell -> circular
                If x.Address(External:=True) = startCell.Address(External:=True) Then
                    TraceForCycle = True
                    Exit Function
                End If

                ' Recurse only within worksheets (avoid names/constants)
                If x.Parent Is curCell.Parent Or True Then
                    If TraceForCycle(startCell, x, inPath, depth + 1) Then
                        TraceForCycle = True
                        Exit Function
                    End If
                End If
            Next x
        Next a
    End If

    inPath.Remove key
End FunctionMogelijk gemaakt door

Gebruik VBA-macro’s om de cellen met cirkelverwijzingen te identificeren. Afbeelding door de auteur.

Deze macro controleert het actieve blad op cirkelverwijzingen in formules. Als een formule, direct of via andere cellen, naar zichzelf terugverwijst, markeert de macro die cellen zodat je eenvoudig ziet waar het probleem zit. Dit werkt zelfs als de iteratieve berekening AAN staat.

VBA heeft de cirkelverwijzingscellen gemarkeerd. Afbeelding door de auteur.

Onderzoek over meerdere werkbladen

Cirkelverwijzingen die over meerdere werkbladen lopen zijn lastiger te detecteren, omdat traceerpijlen niet over tabbladen heen gaan.

Het kan er zo uitzien:

• Blad A verwijst naar Blad B
• Blad B verwijst naar Blad C
• Blad C verwijst naar Blad A

Een manier om dit te detecteren is het in kaart brengen van bladafhankelijkheden. Noteer welke bladen naar andere bladen verwijzen en zoek lussen in die lijst.

Je kunt ook Zoeken en vervangen gebruiken om verwijzingen tussen bladen te vinden:

• Druk op Ctrl + H

• Voer een bladnaam in gevolgd door ! (bijvoorbeeld Sheet1!)

• Klik op Alles zoeken

Excel toont elke formule die naar dat blad verwijst, zodat je afhankelijkheden tussen tabbladen kunt traceren.

Zoek de gekoppelde bladen in een werkmap. Afbeelding door de auteur.

Let op: Zoeken en vervangen detecteert niet automatisch cirkelverwijzingen. Het lokaliseert alleen afhankelijkheden tussen bladen, die je vervolgens analyseert op lussen.

Probleemoplossing met genoemde bereiken

Genoemde bereiken kunnen cirkelverwijzingen verbergen, omdat formules geen directe celadressen meer tonen. Controleer ze zo:

• Open de “Naambeheer” met Ctrl + F3
• Controleer elke definitie van een genoemd bereik
• Zoek naar bereiken die cellen omvatten die in formules naar hetzelfde bereik verwijzen

Controleer de genoemde bereiken. Afbeelding door de auteur.

Problemen met voorwaardelijke logica

Sommige cirkelverwijzingen verschijnen alleen onder specifieke voorwaarden.

Bijvoorbeeld:

=IF(A1>100, A2+10, 100)Mogelijk gemaakt door

Wanneer A1 onder 100 is, gebruikt Excel de tweede voorwaarde en bestaat de cirkelverwijzing niet. Als A1 boven 100 komt, evalueert Excel de eerste voorwaarde en probeert A2 te berekenen met A2 zelf, wat een cirkelverwijzing veroorzaakt.

Zo spoor je deze problemen op:

• Test verschillende invoerwaarden om alle conditionele paden te forceren
• Let op waarschuwingen voor cirkelverwijzingen die alleen onder bepaalde voorwaarden verschijnen

Los ze op door circulaire logica te verplaatsen naar aparte hulpcellen en geneste IF’s te vereenvoudigen tot duidelijke, stapsgewijze berekeningen.

Hoe voorkom je cirkelverwijzingen in Excel

Een paar gewoontes vooraf kunnen later uren debuggen schelen naarmate werkmappen groeien of worden gedeeld. 

Hier zijn dus enkele best practices die je moet volgen: 

Werkmapplanning en formuleontwerp

Goede structuur voorkomt de meeste cirkelverwijzingen al vóór ze ontstaan. Zorg in elk geval voor het volgende:

• Scheid input, berekeningen en output, zodat formules in één duidelijke richting lopen.

• Gebruik hulpcellen of hulpkolommen in plaats van alles in één formule te proppen.

• Berekeningen moeten afhangen van eerdere waarden, niet van hun eigen resultaten of downstream-totalen.

• Houd invoercellen weg van totalen en samenvattingen om zelfverwijzende bereiken te voorkomen.

Kopieer-/plakacties en verwijzingsbeheer

Veel cirkelverwijzingen worden geïntroduceerd tijdens kopiëren en plakken. Houd daarom rekening met het volgende: 

• Plak een formule eerst in één cel en controleer de Formulebalk voordat je hem over een bereik kopieert.

• Gebruik absolute verwijzingen (bijvoorbeeld $A$1) voor tarieven en aannames die niet mogen verschuiven.

• Plaats geen samenvattingsformules binnen de bereiken die ze berekenen.

• Heldere namen maken duidelijk waar een formule naar verwijst en verminderen fouten wanneer bereiken worden uitgebreid.

Documentatie en regelmatige controle

Een beetje documentatie helpt enorm. Houd daarom hier rekening mee tijdens het werk:

• Voeg korte celopmerkingen toe of houd een simpel documentatieblad bij.

• Controleer formules met Formulecontrole-tools vóór belangrijke presentaties of overdrachten.

• Bewaar versies van kritieke bestanden zodat je kunt nagaan wanneer issues zijn geïntroduceerd.

• Controleer in samenwerkingsbestanden regelmatig wijzigingen, zodat kleine fouten zich niet verspreiden.

Tot slot

Als je nu met een cirkelverwijzing zit, bepaal dan eerst of die per ongeluk of opzettelijk is. Die keuze bepaalt of je formules moet herstructureren, iteratie moet inschakelen of een deel van het model moet herontwerpen.

Duik niet meteen in instellingen of workarounds voordat je zeker weet dat de logica zelf klopt.

Neem, zodra het probleem is opgelost, even de tijd om het bestand toekomstbestendiger te maken. Voeg korte notities toe aan belangrijke formules, sla een schone kopie van de werkmap op en zorg dat iedereen die ermee werkt begrijpt hoe de berekeningen horen te werken.