So findest du zirkuläre Verweise in Excel

Es passiert meistens im ungünstigsten Moment: Eine Tabelle, die gestern noch super funktioniert hat, zeigt plötzlich kurz vor Ablauf der Frist eine Warnung wegen einer zirkulären Referenz an.

Die gute Nachricht? Diese Zirkelverweise sind einfacher zu beheben, als du denkst.

In diesem Leitfaden erkläre ich dir:

• Wie man in Excel Zirkelverweise findet
• Verstehe die verschiedenen Arten, wie sie auftauchen
• Repariere sie mit bewährten Methoden.

Was sind zirkuläre Verweise in Excel?

Ein Zirkelbezug in Excel passiert, wenn eine Formel (direkt oder indirekt) auf ihre eigene Zelle zurückgreift und so eine Berechnungsschleife erzeugt, die Excel nicht auflösen kann. Wenn die Zelle A1 zum Beispiel den Wert =A1+10 hat, versucht Excel immer weiter, das Ergebnis zu berechnen.

Selbst indirekte Schleifen machen Probleme. Wenn A1 von B1 abhängt, B1 von C1 abhängt und C1 von A1 abhängt, hängt Excel. Die Berechnung der Datei kann echt lange dauern, falsche Werte anzeigen oder gar nicht mehr aktualisiert werden.

Manche zirkulären Referenzen sind einfach nur Formelfehler, während andere absichtlich in fortgeschrittenen Modellen so gemacht werden. Ich komm später auf diesen Unterschied zurück, aber lass uns erst mal die verschiedenen Arten anschauen.

Direkte Zirkelbezüge

Ein direkter Zirkelbezug passiert, wenn eine Formel ihre eigene Zellenadresse enthält. Angenommen, du hast zwei Werte in den Zellen A2 und B2. Wenn wir in Zelle C2 „ A2 + B2 + C2 “ eingeben, verweist „ C2 “ direkt auf sich selbst. 

Wenn du die Formel eingibst und die Eingabetaste drückst, erscheint ein Popup-Fenster mit einer zirkulären Referenz. Und wenn du auf „OK“ klickst, gibt die Formel ein unerwartetes Ergebnis zurück.

Direkter Zirkelbezug. Bild vom Autor.

Das gibt „ 0 “ zurück, weil wir „ A2 “ und „ B2 “ zusammenzählen und eigentlich 6 rauskommen sollte, aber „C2“ sich immer wieder selbst hinzufügt. Excel kann „ C2 “ nicht berechnen, wenn es den Wert von „ C2 “ nicht kennt. Das führt zu einer Endlosschleife, die nie aufgelöst wird.

Anmerkung: Diese zirkulären Referenzen sind am einfachsten zu erkennen, weil die Selbstreferenz direkt in der Formelsyntax zu sehen ist. Tatsächlich erkennt Excel sie und markiert sie, sobald die Formel eingegeben wird.

Indirekte Zirkelverweise

Eine indirekte zirkuläre Referenz passiert, wenn mehrere Zellen eine Abhängigkeitskette bilden, die wieder zum Ausgangspunkt zurückführt.

Zum Beispiel:

• F2 Referenzen H4

• H4 Referenzen E4

• E4 Referenzen F2

Indirekter Zirkelbezug. Bild vom Autor.

Keine einzelne Formel sieht für sich genommen falsch aus. Das Problem tritt nur auf, wenn Excel die ganze Kette nachverfolgt und merkt, dass die Berechnung wieder da anfängt, wo sie aufgehört hat.

Die sind schwieriger zu erkennen, weil die Schleife viele Zellen und in komplizierten Arbeitsmappen sogar mehrere Arbeitsblätter umfassen kann, bevor Excel die zirkuläre Abhängigkeit erkennt.

Versteckte zirkuläre Referenzen

Ein versteckter Zirkelbezug ist einer, bei dem die Abhängigkeitsschleife aus der Formelsyntax nicht klar ersichtlich ist. Die kommen oft von:

• Benannte Bereiche

• Funktionen INDIRECT() oder OFFSET()

• Komplexe bedingte Logik

Zum Beispiel umfasst der benannte Bereich „ QuarterlyTotal “ die Zellen „ C3 “ bis „ C5 “, die die vierteljährlichen Verkaufszahlen enthalten.

In Zelle C5 zeigt die Formelleiste Folgendes an:

=QuarterlyTotal*0.1

Auf den ersten Blick sieht das gut aus. Die Formel verweist nicht direkt auf „ C5 “. Da aber QuarterlyTotal C5 enthält, muss Excel C5 als Teil der Summe berechnen, die für die Berechnung von C5 verwendet wird.

Das macht eine versteckte Schleife:

• Excel versucht zu berechnen QuarterlyTotal

• QuarterlyTotal beinhaltet C5

• C5 hängt ab von QuarterlyTotal

Der Verweis ist zirkulär, auch wenn das in der Formelsyntax nicht direkt zu sehen ist.

Das ist der Grund, warum versteckte zirkuläre Referenzen so schwer zu beheben sind. Die Formeln sehen richtig aus, die Arbeitsmappe läuft vielleicht lange normal, und der Zirkelbezug taucht erst auf, wenn Excel den benannten Bereich auswertet.

Versteckte zirkuläre Referenz. Bild vom Autor.

Bedingte Logik kann das Erkennen noch schwieriger machen. Warum? Weil verschachtelte IF()-Anweisungen nur unter bestimmten Bedingungen eine zirkuläre Referenz erzeugen können. Deshalb funktioniert die Datei, bis sich bestimmte Werte ändern.

Versteckte zirkuläre Referenzen sind am schwierigsten zu beheben, weil die Abhängigkeitsschleife durch Abstraktion und nicht durch sichtbare Zellreferenzen verdeckt wird.

Absichtliche vs. zufällige Zirkelverweise

Einige zirkuläre Referenzen sind absichtlich so gemacht. Andere sind Fehler, die du beseitigen musst.

Absichtliche (bewusste) Zirkelverweise

Ein absichtlicher Zirkelbezug wird gemacht, um bestimmte Modellierungsanforderungen zu unterstützen, bei denen Werte voneinander abhängen.

Nimm mal die Finanzmodellierung als Beispiel: Die Zinskosten hängen vom ausstehenden Schuldenstand ab, während sich der Schuldenstand je nach Cashflow ändert, der auch die Zinskosten mit einbezieht. Das macht eine Rückkopplungsschleife, die die echte Geschäftslogik widerspiegelt.

Das Modell fängt mit einer einfachen Struktur an:

• Der Schuldenstand ist der Anfangsbetrag.
• Die Zinskosten werden als Prozentsatz der Schulden berechnet.
• Die aktualisierte Schuld umfasst sowohl den ursprünglichen Saldo als auch die Zinsen.

Absichtliche Zirkelverweise. Bild vom Autor.

Wenn die Zinsen auf der Grundlage der Schulden berechnet werden und die aktualisierten Schulden diese Zinsen enthalten, kommt es natürlich zu einer Schleife in der Berechnung. Excel sieht das als Zirkelbezug und gibt standardmäßig Null zurück, weil es die Abhängigkeit nicht in einem einzigen Durchgang lösen kann.

Um das zu vermeiden, schalt die iterative Berechnung so ein:

1. Geh zu Dateien > Optionen > Formeln
2. In der Berechnungsoption die Option „Iterative Berechnung aktivieren“ .

Aktiviere die Option für iterative Berechnungen. Bild vom Autor.

Sobald die iterative Berechnung aktiviert ist, berechnet Excel die Zinsen und die aktualisierten Schuldenwerte immer wieder neu. Mit jedem Durchlauf ändern sich die Zahlen ein bisschen, bis sie sich einpendeln und nicht mehr weiter verändern. 

Das Endergebnis ist eher eine konvergierte Lösung als eine Berechnung in einem einzigen Schritt. Das klappt, weil der Zirkelbezug absichtlich gemacht ist. Zinsen beeinflussen Schulden, und Schulden beeinflussen Zinsen. Jeder Wert beeinflusst den anderen, daher ist eine wiederholte Neuberechnung zu erwarten und notwendig.

Iterative Berechnung. Bild vom Autor.

Anmerkung: Iterative Berechnungen allein garantieren noch kein aussagekräftiges Ergebnis. Es wiederholt die Logik, die du in das Modell eingebaut hast, weshalb absichtliche Zirkelverweise sorgfältig überwacht und überprüft werden müssen.

Zufällige zirkuläre Referenzen

Ein versehentlicher Zirkelbezug kann durch Formelfehler, Kopierfehler oder unbeabsichtigte Änderungen entstehen. Angenommen, du gibst die Werte 2, 4 und 6 in die Zellen A1:A3 ein.

In „ A4 “ willst du eine Gesamtsumme, also gibst du Folgendes ein:

=SUM(A1:A4)

Die Zelle „ A4 “ wird jetzt in die Berechnung mit einbezogen.

Wenn du die Eingabetaste drückst, zeigt Excel eine Warnung wegen einer Zirkelschleife an. Wenn du auf „OK“ klickst, zeigt Excel „ 0 “ an.

Versehentliche zirkuläre Referenz. Bild vom Autor.

Dieser Zirkelschluss hat nichts mit echter Logik zu tun. Es ist da, weil der Formelbereich nicht stimmt.

So behebst du zirkuläre Verweise in Excel

Sobald Excel eine Schleife erkennt, musst du jetzt genau herausfinden, wo sie anfängt, und entscheiden, wie du sie beheben kannst. 

Schauen wir uns ein paar Methoden an, um zirkuläre Referenzen zu beheben, je nachdem, ob sie zufällig oder absichtlich entstanden sind: 

Schnelle Erkennung, um das Problem zu finden

Excel hat ein paar eingebaute Tools, um zirkuläre Verweise schnell zu finden, und jedes hat seine eigenen Vorteile und Einschränkungen. Schauen wir uns diese Optionen mal an. 

Statusleistenbenachrichtigungen

Der schnellste Weg, um eine zirkuläre Referenz zu erkennen, ist über die Statusleiste unten links im Excel-Fenster.

Wenn es einen Zirkelbezug gibt, zeigt Excel einen Text wie „Zirkelbezüge: G4.” Hier erfährst du, welche Zelle zuletzt in einer Schleife erkannt wurde.

Zeig die zirkuläre Referenz in der Statusleiste an. Bild vom Autor.

Die Statusleiste zeigt aber immer nur eine zirkuläre Referenz auf einmal an. Wenn es mehrere Schleifen gibt, zeigt Excel sie nacheinander an.

So kannst du das Problem beheben: 

1. Die in der Statusleiste angezeigte Zelle reparieren
2. Schau nochmal in die Statusleiste rein.
3. Mach das so oft, bis die Meldung weg ist.

Tipp: Schreib dir jede Zellenadresse auf, denn wenn man einen Fehler behebt, findet man oft einen neuen.

Erste Warnmeldungen

Excel zeigt auch eine Warnmeldung an, wenn:

• Du erstellst eine zirkuläre Referenz oder
• Du öffnest eine Arbeitsmappe, die schon eine enthält.

Die Warnung sagt meistens, dass eine oder mehrere Formeln sich selbst beziehen und deshalb vielleicht falsch berechnet werden.

Diese Warnungen werden möglicherweise nicht mehr angezeigt, wenn:

• Du hast die Nachricht vorhin mit „OK“ weggeklickt.
• Iterative Berechnung ist aktiviert
• Der Berechnungsmodus ist auf „Manuell“ eingestellt (Formeln > Berechnungsoptionen > Manuell).

Wenn die Ergebnisse falsch aussehen und keine Warnung angezeigt wird, mach eine Neuberechnung so:

• Drück F9, um das aktive Arbeitsblatt neu zu berechnen.
• Drück Strg + Alt + F9, um die Neuberechnung für alle Arbeitsblätter zu starten.

Tipp: Wenn du die Arbeitsmappe schließt und wieder öffnest, kann Excel auch wieder die Warnung anzeigen.

Fehlerprüfungswerkzeug

Um mehrere zirkuläre Verweise gleichzeitig anzuzeigen, kannst du das Fehlerprüfungs-Tool von Excel nutzen: Geh zu „Formeln“ > „Fehlerprüfung“ > „Zirkuläre Verweise“

Dieses Menü zeigt alle gefundenen zirkulären Verweise an und lässt dich direkt zu jeder Zelle springen, was die Diagnose komplexer Arbeitsmappen vereinfacht.

Wenn das Untermenü „Zirkuläre Verweise“ leer ist, Excel aber trotzdem nicht richtig läuft, drück einfach F9, um die Neuberechnung zu starten und die Liste zu aktualisieren.

Ein Tool, das Fehler sucht, um zirkuläre Referenzen zu finden. Bild vom Autor.

Anmerkung: In Excel 2007 und neueren Versionen findest du diese Funktion in der Multifunktionsleiste. Ältere Versionen zeigen ähnliche Optionen über die Menüleiste an.

Visuelle Nachverfolgungswerkzeuge

Sobald du eine problematische Zelle gefunden hast, kannst du mit visuellen Verfolgungswerkzeugen sehen, wie die zirkuläre Referenz entsteht.

Diese Tools zeichnen Pfeile zwischen Zellen und machen Abhängigkeiten sichtbar, statt sie in Formeln zu verstecken. Beide sind in der Gruppe „Formelprüfung“ auf der Registerkarte „Formeln“ verfügbar:

• „Vorgänger verfolgen“ zeigt dir, welche Zellen in die ausgewählte Formel reinkommen. Excel zeichnet Pfeile von den Eingabezellen zur Formelzelle. Die Tastenkombination dafür ist Alt + M, P.
• „Abhängigkeiten verfolgen“ zeigt dir, welche Zellen den Wert der ausgewählten Zelle benutzen. Excel zeichnet Pfeile von der ausgewählten Zelle zu allen Formeln, die darauf verweisen. Die Tastenkombination dafür ist Alt + M, D.

Wenn man Vorläufer und Nachfolger verfolgt und wieder bei der ersten Zelle landet, bilden die Pfeile eine Schleife und zeigen, dass es eine zirkuläre Referenz gibt.

Vorgänger und Nachfolger verfolgen. Bild vom Autor.

Nachdem du die Formel korrigiert hast, gehen die Pfeile nicht mehr zurück und der Zirkelbezug ist weg. Du kannst die Pfeile über „Pfeile entfernen“ im selben Menü löschen.

Formelumbau und alternative Berechnungen

Die meisten Zirkelverweise passieren, weil die Berechnungen falsch angeordnet sind, nicht weil Excel nicht richtig funktioniert. Für solche versehentlichen Zirkelverweise ist die Umstrukturierung der Formel die beste Lösung. 

Hier sind ein paar Methoden, um das Problem zu beheben:  

Formeln umstrukturieren, um Selbstabhängigkeiten zu entfernen

Zirkelbezüge tauchen oft auf, wenn eine Formel versucht, einen Wert mit ihrem eigenen Ergebnis zu berechnen.

Zum Beispiel führt eine Formel wie „ =A2+1 “ in der Zelle „ A2 “ zu einer sofortigen Schleife, weil die Zelle von sich selbst abhängt. Um das zu beheben, verschiebe die benötigte Eingabe in eine separate Zelle und verweise stattdessen auf diesen Wert.

Sobald die Abhängigkeit weg ist, kann Excel die Formel ganz normal berechnen.

Benutze Hilfsspalten für Zwischenberechnungen

Zirkuläre Referenzen kommen auch vor, wenn eine einzelne Formel versucht, mehrere Schritte gleichzeitig zu erledigen.

Wenn zum Beispiel zwei Zellen voneinander abhängen, um einen gemeinsamen Wert zu berechnen, hat Excel keinen klaren Ausgangspunkt. Um das zu beheben, füge eine Hilfszelle ein, die zuerst die Zwischenberechnung macht.

Durch die Berechnung gemeinsamer Werte in einer separaten Hilfszelle kann Excel eine klare Reihenfolge einhalten:

1. Helferzelle rechnet zuerst
2. Abhängige Formeln berechnen weiter

Dieser Ansatz macht Formeln einfacher und vermeidet zirkuläre Abhängigkeiten.

Zusammenfassungen von Berechnungen trennen

Summen und Summenformeln sollten nicht in die Zellen gemischt werden, auf die sie sich beziehen. Wenn eine Zusammenfassung Zahlen aus einem Bereich nimmt, der sich selbst enthält, macht Excel eine zirkuläre Referenz. 

Wenn zum Beispiel A2 einen Basisbetrag von 1000 und B2 einen Satz von 10 % hat, zeigt C2 den Gesamtbetrag an und D2 berechnet eine Gebühr.

Wenn C2 =A2+D2 nutzt und D2 =C2*$B$2 nutzt, gibt's einen Zirkelschluss, weil die Gesamtsumme von der Gebühr abhängt und die Gebühr von der Gesamtsumme abhängt. 

Excel kann keines von beiden zuerst berechnen.

Um das zu ändern, sollte die Gebühr vom Basiswert aus berechnet werden, nicht vom Gesamtbetrag. Also, ändere D2 zu =A2*$B$2 und lass C2 als =A2+D2.

Benutze separate Zellen, um Zirkelbezüge zu vermeiden. Bild vom Autor.

Jetzt kann Excel erst die Gebühr berechnen und sie dann zum Basiswert draufschlagen. Der Zirkelbezug verschwindet, weil jede Formel einen klaren Ausgangspunkt hat.

Iterative Berechnung für absichtliche zirkuläre Logik aktivieren

Iterative Berechnungen solltest du nur machen, wenn zirkuläre Referenzen absichtlich so gemacht wurden und keine Fehler sind. Diese Methode passt gut für Finanzmodelle und andere Szenarien, wo es legitime Rückkopplungsschleifen gibt und die zirkuläre Logik das echte Verhalten widerspiegelt.

So schaltest du die iterative Berechnung in Excel für Windows ein:

1. Geh zu „Datei“ > „Optionen“ > „Formeln“
2. Unter „Berechnungsoptionen“ klickst du „Iterative Berechnung aktivieren“ an.
3. Klick auf OK

Sobald diese Option aktiviert ist, berechnet Excel Formeln, die in zirkulären Verweisen vorkommen, immer wieder neu, bis sie stabil sind oder eine Stoppbedingung erreichen.

Wichtige Iterationseinstellungen

Zwei Einstellungen bestimmen, wie sich die iterative Berechnung verhält:

• „Maximale Iterationen“ legt fest, wie oft Excel die Formeln neu berechnet, bevor es aufhört. Ein Wert von 100 Iterationen passt für die meisten Geschäfts- und Finanzmodelle gut.
• Die maximale Änderung legt fest, wie klein der Unterschied zwischen den Berechnungsergebnissen sein muss, bevor Excel die Neuberechnung stoppt. Ein Wert von 0,001 ist ein gängiger Ausgangspunkt, der Genauigkeit und Leistung gut ausbalanciert.

Wenn du eine der beiden Einstellungen erhöhst, kann das die Genauigkeit verbessern, aber bei großen Arbeitsmappen die Neuberechnung verlangsamen.

Wann sollte man diese Methode anwenden?

Aktiviere die iterative Berechnung nur, wenn:

• Der zirkuläre Verweis zeigt eine funktionierende Geschäftslogik.
• Das Modell hat legitime Rückkopplungsschleifen (zum Beispiel hängt der Zins von der Verschuldung ab, während sich die Verschuldung je nach Zins ändert).
• Du denkst, dass Werte eher zusammenlaufen als ewig hin und her schwanken.

Diese Methode ist keine Lösung für versehentliche Zirkelbezüge, die durch Formelfehler entstehen.

Wichtige Hinweise, die du beachten solltest

Bevor du die iterative Berechnung aktivierst, denk an diese Risiken:

• Zufällige zirkuläre Referenzen lösen vielleicht keine Warnungen mehr aus, was es schwieriger macht, Fehler zu erkennen.
• Große oder komplizierte Modelle können langsamer neu berechnet werden.
• Wenn die Werte schwanken statt sich zu stabilisieren, können die Ergebnisse unzuverlässig sein.

Wegen dieser Risiken sollte man iterative Berechnungen immer genau dokumentieren.

Implementierung von Leistungsschaltern für komplexe Modelle

Ein Leistungsschalter ist eine coole Technik, mit der du bestimmen kannst, wann die zirkuläre Logik aktiv ist. Anstatt eine zirkuläre Referenz zu entfernen, hältst du sie absichtlich an oder lässt sie mithilfe eines Steuerungswerts innerhalb der Formel zu.

Dieser Ansatz ist super für Finanzmodellierer und Power-User, die in manchen Fällen, aber nicht immer, zirkuläre Logik brauchen.

Ein Leistungsschalter braucht normalerweise:

• Eine Kontrollzelle (oft auf „ 0 “ oder „ 1 “ eingestellt)

• Eine IF-Anweisung, die zwischen zirkulärer und nicht-zirkulärer Logik hin- und herwechselt

Das allgemeine Muster sieht so aus:

=IF($A$1=1, circular_logic, non_circular_alternative)

• Wenn die Kontrollzelle gleich „ 0 “ ist, vermeidet die Formel die zirkuläre Referenz.
• Wenn die Steuerzelle gleich „ 1 “ ist, darf die zirkuläre Logik laufen.

Angenommen, du gibst die folgenden Werte ein: 

• A1: Leistungsschalter (0 oder 1)

• A2: Startguthaben = 1000

• B2: Interesse

• C2: Aktualisierter Saldo

In „ B2 “ (Eingabeaufforderung) gib „ =IF($A$1=1, C2*10%, A2*10%) “ ein und in „ C2 “ (Eingabeaufforderung) gib =A2+B2

Jetzt:

• Wenn A1 = 0, wird der Zins vom Anfangssaldo berechnet. Die zirkuläre Referenz ist deaktiviert, und Excel rechnet ganz normal.

• Wenn A1 = 1, wird der Zins vom aktualisierten Saldo berechnet. Die zirkuläre Logik wird aktiv und erzeugt eine absichtliche Rückkopplungsschleife.

Einen Leistungsschalter benutzen. Bild vom Autor.

Wann man einen Leistungsschalter benutzt

Leistungsschalter sind nützlich für:

• Halte die zirkuläre Logik beim Erstellen und Überprüfen von Formeln deaktiviert.
• Zirkuläre Logik ein- und ausschalten, um Probleme zu isolieren und Ergebnisse zu überprüfen
• Zeigt das Modell mit deaktivierter zirkulärer Logik, um Annahmen und Mechanismen einfacher zu erklären.

Problematische Formeln löschen und neu anfangen

Das ist die letzte Option: Man setzt sie ein, wenn alles andere nicht klappt. Das ist super für Arbeitsmappen mit total verworrenen Abhängigkeiten, geerbten Dateien mit unklarer Logik oder Situationen, in denen man schnell und zuverlässig was machen muss.

Anstatt zu versuchen, kaputte Formeln zu entwirren, setzt du das Modell zurück und baust es methodisch neu auf. 

Schauen wir mal, wie das geht.

Schritt 1: Mach 'ne Sicherungskopie

Mach erst mal ein komplettes Backup der Arbeitsmappe, bevor du irgendwas änderst. So geht's:

• Öffne die Arbeitsmappe in Excel.

• Geh auf „Datei“ > „Speichern unter“ > „Durchsuchen“ und such dir einen anderen Speicherort aus oder behalt den gleichen Ordner, um deine Datei zu speichern.

• Benenne die Datei um, zum Beispiel Workbook_Backup.xlsx

• Klick Speichern

Mach eine Sicherungskopie von einer Arbeitsmappe. Bild vom Autor.

Dadurch bleiben die ursprünglichen Formeln, die Logik und die Ergebnisse erhalten, sodass du später darauf zurückgreifen oder bei Bedarf zurücksetzen kannst.

Probier diese Methode niemals mit der einzigen Kopie einer Datei aus.

Schritt 2: Dokumentiere die aktuelle Struktur

Als Nächstes solltest du festhalten, wie die Arbeitsmappe gerade funktioniert:

• Mach Screenshots von den wichtigsten Stellen
• Schau mal, was jedes Arbeitsblatt berechnet.
• Identifiziere Inputs, Outputs und Zusammenfassungen.

Diese Dokumentation hilft dir zu verstehen, was neu aufgebaut werden muss, und verhindert, dass wichtige Logik während der Bereinigung verloren geht.

Schritt 3: Formeln durch Werte ersetzen

Um alle zirkulären Abhängigkeiten auf einmal zu beenden, kopier einfach die betroffenen Bereiche und wähl „ “ (Einfügen > Speziell) aus. Rechtsklick > Einfügen > Werte , um Formeln durch ihre berechneten Ergebnisse zu ersetzen.

Benutz die Option „Inhalte einfügen“. Bild vom Autor.

Das löscht sofort alle zirkulären Verweise, während die sichtbaren Zahlen als vorübergehende Basis beibehalten werden.

Schritt 4: Formeln schrittweise neu erstellen

Mach die Arbeitsmappe Stück für Stück neu, statt alles auf einmal:

1. Formeln für einen logischen Block hinzufügen
2. Presse F9 , um die Neuberechnung zu starten.
3. Stell sicher, dass keine zirkulären Referenzen auftauchen.
4. Zum nächsten Abschnitt gehen

Schritt 5: Überprüfe die Ergebnisse anhand der Sicherung

Vergleich die Ergebnisse nach dem Wiederaufbau jedes Abschnitts mit der Sicherungsdatei. Du wirst vielleicht Unterschiede bemerken, die darauf hindeuten, dass:

• Fehlende Eingaben
• Falsche Formellogik
• Früher versteckte Rechenfehler

Fortschrittliche Erkennung schwer auffindbarer Referenzen

Manche zirkulären Verweise werden mit den Standardtools von Excel nicht angezeigt. Probier in solchen Fällen mal fortgeschrittene Techniken aus: 

VBA-Makros für umfassendes Scannen

Wenn das manuelle Nachverfolgen zu langsam ist, kann VBA in Sekundenschnelle Tausende von Zellen scannen und zirkuläre Verweise in der ganzen Arbeitsmappe finden.

Der grundlegende Ansatz sieht so aus:

• Drück Alt + F11 , um den Visual Basic-Editor zu starten.
• Geh zu Einfügen > Modul.
• Füge ein Makro zum Erkennen von Zirkelschlüssen ein.
• Presse F5 , um es zu starten.

VBA-Schnittstelle. Bild vom Autor.

Tipp: Du musst VBA nicht von Grund auf neu schreiben. Sag einfach, was du willst, und lass chatGPT ein individuelles Erkennungsmakro erstellen.

Hier ist ein Beispiel für ein VBA-Skript: 

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 Function

Benutze VBA-Makros, um die Zellen mit zirkulären Verweisen zu finden. Bild vom Autor.

Dieses Makro checkt das aktive Blatt auf Zirkelbezüge in Formeln. Wenn eine Formel direkt oder über andere Zellen auf sich selbst verweist, markiert das Makro diese Zellen, damit du das Problem leicht erkennen kannst. Das klappt auch, wenn die iterative Berechnung aktiviert ist.

VBA hat die Zellen mit zirkulären Verweisen markiert. Bild vom Autor.

Untersuchung über mehrere Arbeitsblätter hinweg

Zirkuläre Verweise, die sich über mehrere Arbeitsblätter erstrecken, sind schwieriger zu erkennen, weil die Verfolgungspfeile keine Registerkarten überqueren.

Es könnte so aussehen:

• Blatt A verweist auf Blatt B.
• Blatt B verweist auf Blatt C.
• Blatt C verweist auf Blatt A.

Eine Möglichkeit, das zu erkennen, ist, die Abhängigkeiten der Blätter zu kartieren. Schreib dazu auf, welche Blätter auf andere Blätter verweisen, und such in dieser Liste nach Schleifen.

Du kannst auch Suchen & Ersetzen verwenden, um blattübergreifende Verweise zu finden:

• Drück Strg + H

• Gib einen Blattnamen ein, gefolgt von „ ! “ (zum Beispiel „ Sheet1! “).

• Klick Alle suchen

Excel listet alle Formeln auf, die auf dieses Blatt verweisen, sodass du Abhängigkeiten zwischen Tabellen verfolgen kannst.

Such die verbundenen Blätter in einer Arbeitsmappe. Bild vom Autor.

Hey, schau mal: Die Funktion „Suchen und Ersetzen“ erkennt zirkuläre Referenzen nicht automatisch. Es findet nur blattübergreifende Abhängigkeiten, die du dann auf Schleifen checken kannst.

Fehlerbehebung bei benannten Bereichen

Benannte Bereiche können zirkuläre Verweise verstecken, weil Formeln keine direkten Zelladressen mehr anzeigen. Um sie zu überprüfen:

• Öffne den „Namensmanager“ mit Strg + F3
• Überprüfe jede benannte Bereichsdefinition
• Such nach Bereichen, die Zellen haben, die in Formeln auf denselben Bereichsnamen verweisen.

Schau dir die benannten Bereiche an. Bild vom Autor.

Probleme mit der bedingten Logik

Manche Zirkelverweise tauchen nur unter bestimmten Bedingungen auf.

Zum Beispiel:

=IF(A1>100, A2+10, 100)

Wenn „ A1 “ unter 100 liegt, nimmt Excel die zweite Bedingung und der Zirkelbezug ist nicht da. Wenn „ A1 “ größer als 100 ist, schaut Excel sich die erste Bedingung an und versucht, „ A2 “ mit „ A2 “ selbst zu berechnen, was zu einer zirkulären Referenz führt.

Um diese Probleme zu erkennen:

• Probier verschiedene Eingabewerte aus, um alle bedingten Pfade zu testen.
• Pass auf, dass unter bestimmten Bedingungen Warnungen wegen zirkulärer Referenzen auftauchen.

Um das zu beheben, verschiebt die zirkuläre Logik in separate Hilfszellen und macht die verschachtelten IF-Anweisungen zu klareren, schrittweisen Berechnungen.

Wie man zirkuläre Verweise in Excel vermeidet

Ein paar Gewohnheiten am Anfang können später viel Zeit beim Debuggen sparen, wenn Arbeitsmappen größer werden oder mit anderen geteilt werden. 

Hier sind ein paar Tipps, die du beachten solltest: 

Planung von Arbeitsmappen und Formelentwurf

Eine gute Struktur verhindert die meisten zirkulären Referenzen, bevor sie überhaupt entstehen. Mach bitte Folgendes:

• Trenne Eingaben, Berechnungen und Ausgaben, damit die Formeln klar verständlich sind.

• Benutze Hilfszellen oder Hilfsspalten, anstatt alles in einer Formel zu machen.

• Berechnungen sollten auf früheren Werten basieren, nicht auf ihren eigenen Ergebnissen oder nachgelagerten Summen.

• Halte Eingabezellen von Summen und Zusammenfassungen fern, um selbstreferenzierende Bereiche zu vermeiden.

Kopieren und Einfügen sowie Referenzverwaltung

Beim Kopieren und Einfügen entstehen oft Zirkelverweise. Also, hier ist, was du bedenken solltest: 

• Kopier die Formel erst mal in eine Zelle und check die Formelleiste, bevor du sie in einen Bereich kopierst.

• Benutze absolute Referenzen (z. B. $A$1) für Zinssätze und Annahmen, die sich nicht ändern sollten.

• Pack keine Summenformeln in die Bereiche, die sie berechnen.

• Klare Namen machen deutlich, worauf sich eine Formel bezieht, und verringern Fehler, wenn Bereiche erweitert werden.

Dokumentation und regelmäßige Prüfungen

Eine kurze Dokumentation reicht schon aus. Deshalb solltest du bei der Arbeit Folgendes beachten:

• Füge kurze Zellkommentare hinzu oder führe ein einfaches Dokumentationsarbeitsblatt.

• Überprüfe Formeln mit den Tools zur Formelüberprüfung, bevor du wichtige Präsentationen machst oder Aufgaben übergibst.

• Speicher Versionen wichtiger Dateien, damit du nachverfolgen kannst, wann Probleme aufgetreten sind.

• Schau dir in gemeinsamen Dateien regelmäßig die Änderungen an, damit sich kleine Fehler nicht ausbreiten.

Abschließende Gedanken

Wenn du gerade mit einer zirkulären Referenz zu tun hast, überprüfe, ob sie zufällig oder absichtlich entstanden ist. Diese Entscheidung bestimmt, ob du Formeln umstrukturieren, Iterationen aktivieren oder Teile des Modells neu gestalten solltest.

Mach dich nicht gleich an Einstellungen oder Workarounds ran, bevor du nicht sicher bist, dass die Logik dahinter Sinn ergibt.

Sobald das Problem behoben ist, nimm dir kurz Zeit, um die Datei für die weitere Arbeit zu vereinfachen. Mach dir kurze Notizen zu wichtigen Formeln, speicher eine saubere Kopie der Arbeitsmappe und stell sicher, dass alle anderen, die sie benutzen, verstehen, wie die Berechnungen funktionieren sollen.