Excel #BEZUG!-Fehler beheben: Ursache und Lösung

Der #BEZUG!-Fehler ist eine der ärgerlichsten Fehlermeldungen in Excel, da er oft erst auftaucht, nachdem Sie etwas gelöscht oder verschoben haben. Anders als ein Tippfehler weist er auf einen Bezug hin, der buchstäblich nicht mehr existiert. Im Folgenden erfahren Sie, woher das kommt, wie Sie ihn beheben und wie Sie verhindern, dass er erneut auftritt.

Der #BEZUG!-Fehler bedeutet, dass eine Formel auf eine Zelle verweist, die nicht mehr existiert

Der #BEZUG!-Fehler (im Englischen #REF!, eine Abkürzung von reference) erscheint, sobald eine Formel auf eine Zelle, Zeile, Spalte oder ein Blatt verweist, die bzw. das nicht mehr gültig ist. Excel kann den Bezug nicht durch eine andere Zelle ersetzen und setzt daher den Text #BEZUG! an die Stelle, an der zuvor eine Zelladresse stand.

Das sehen Sie in der Bearbeitungsleiste. Wo zuvor etwa =B2*C2 stand, lesen Sie nach dem Löschen von Spalte C plötzlich =B2*#BEZUG!. Die Formel besteht noch, aber sie vermisst eine gültige Adresse zum Rechnen.

Der Fehler pflanzt sich fort. Verweist eine zweite Formel auf die Zelle mit dem #BEZUG!-Fehler, übernimmt sie den Fehler. In einem umfangreichen Modell kann eine einzige gelöschte Spalte so Dutzende Zellen rot einfärben. Daher lohnt es sich, die Fehlerquelle aufzuspüren, statt jede Zelle einzeln zu reparieren.

Verwechseln Sie #BEZUG! nicht mit anderen Meldungen. #NAME? bedeutet, dass Excel einen Funktionsnamen oder einen benannten Bereich nicht erkennt, #WERT! weist auf einen falschen Datentyp hin, und #NV zeigt an, dass eine Suchfunktion nichts gefunden hat. #BEZUG! steht speziell für einen Bezug, der sein Ziel verloren hat. Diese Unterscheidung hilft Ihnen, die richtige Lösung zu wählen: Bei #BEZUG! reparieren Sie den Bezug, nicht den Funktionsnamen oder den Datentyp.

Vier Situationen verursachen fast jeden #BEZUG!-Fehler

Der Fehler entsteht immer, weil das Ziel eines Bezugs verschwindet oder außerhalb des Bereichs liegt. Die folgende Tabelle fasst die vier häufigsten Ursachen zusammen.

Besonders das Ausschneiden (Strg+X) und anschließende Einfügen ist tückisch. Schneiden Sie eine Zelle aus und fügen Sie sie über einer Zelle ein, auf die eine Formel verweist, verschwindet das ursprüngliche Ziel und es bleibt ein #BEZUG!-Fehler. Kopieren (Strg+C) hat diesen Effekt nicht, da die Quelle bestehen bleibt.

In Such- und Verweisfunktionen taucht #BEZUG! am schnellsten auf

Bestimmte Funktionen sind besonders anfällig für diesen Fehler, da sie mit festen Positionen arbeiten.

• SVERWEIS. Der Fehler tritt auf, wenn die Spaltenindexnummer größer als die Anzahl der Spalten in der Matrix ist. Wenn Sie eine Spalte innerhalb dieser Matrix löschen, verschiebt sich die Zählung und der Index fällt aus der Tabelle. Wenn Sie eine Methode bevorzugen, die auf die Spalte selbst verweist, lesen Sie den Unterschied in SVERWEIS vs. XVERWEIS.
• INDEX. Fragen Sie mit INDEX nach Zeile 12 in einem Bereich von 10 Zeilen, gibt die Funktion #BEZUG! zurück, weil diese Zeile nicht existiert.
• INDIREKT. Diese Funktion baut einen Bezug aus Text auf. Stimmt der Text nicht, etwa durch ein gelöschtes Blatt, folgt sofort ein #BEZUG!.
• 3D-Bezüge. Verweist eine Formel auf eine Blattgruppe (=SUMME(Jan:Dez!B2)) und Sie löschen eines dieser Blätter, kann die Gruppe brechen.

Das Muster ist jedes Mal dasselbe: die Funktion rechnet mit einer Position, die durch eine Änderung weggefallen ist.

Direkt nach einer Löschung beheben Sie den Fehler mit Strg+Z

1. Drücken Sie sofort Strg+Z oder klicken Sie auf Rückgängig in der Schnellzugriffsleiste. Die gelöschte Zeile oder Spalte kehrt zurück und der Bezug stellt sich wieder her.
2. Wenn Rückgängig nicht mehr möglich ist, klicken Sie auf die Fehlerzelle und sehen Sie sich die Formel in der Bearbeitungsleiste an.
3. Markieren Sie den Teil #BEZUG! in der Formel und überschreiben Sie ihn mit der richtigen Zelladresse oder dem richtigen Bereich.
4. Bestätigen Sie mit Enter und prüfen Sie, ob das Ergebnis stimmt.

Reparieren Sie eine Suchfunktion, passen Sie die Spaltenindexnummer an die neue Position der gewünschten Spalte an oder ersetzen Sie den festen Index durch einen, der die Spalte selbst sucht. Steckt der Fehler in einer INDEX-Formel, prüfen Sie, ob die angegebene Zeilen- oder Spaltennummer noch innerhalb der Grenzen des Bereichs liegt.

In einer großen Arbeitsmappe spüren Sie jeden #BEZUG! mit Suchen und Ersetzen auf

1. Drücken Sie Strg+H, um Suchen und Ersetzen zu öffnen.
2. Geben Sie unter Suchen nach den Text #BEZUG! ein.
3. Klicken Sie auf Optionen und setzen Sie Innerhalb auf Arbeitsmappe, sodass alle Blätter durchsucht werden.
4. Wählen Sie Alle suchen. Excel zeigt eine Liste aller Zellen, die den Fehler enthalten; klicken Sie einen Eintrag an, um direkt dorthin zu springen.

Wenn Sie verstehen möchten, wie eine Formel zu dem Fehler kommt, verwenden Sie Formeln > Formelauswertung. Excel geht die Berechnung Schritt für Schritt durch, sodass Sie genau sehen, wann der Bezug wegfällt. Ein zweites nützliches Hilfsmittel ist Formeln > Vorgängerpfeile anzeigen, das mit Pfeilen zeigt, welche Zellen eine Formel speisen.

Eine gebrochene SVERWEIS-Formel reparieren zeigt den Ansatz

Ein häufiger Fall: Sie hatten =SVERWEIS(A2; Preise!A:E; 5; FALSCH), um den Preis aus der fünften Spalte zu holen. Jemand löscht Spalte C im Blatt Preise, sodass die Tabelle nur noch vier Spalten hat. Die Formel zählt weiter 5, diese Spalte existiert nicht mehr und das Ergebnis wird #BEZUG!.

1. Klicken Sie auf die Fehlerzelle und prüfen Sie in der Bearbeitungsleiste, ob die Indexzahl (5) noch zur neuen Tabellenbreite passt.
2. Zählen Sie die Spalten im Blatt Preise erneut. Der Preis befindet sich jetzt in der vierten Spalte.
3. Passen Sie die Indexzahl auf 4 an: =SVERWEIS(A2; Preise!A:D; 4; FALSCH).
4. Kopieren Sie die korrigierte Formel in die übrigen Zeilen.

Möchten Sie solche Korrekturen in Zukunft vermeiden, ersetzen Sie den festen Index durch XVERWEIS oder INDEX mit VERGLEICH, die auf die Spalte selbst verweisen. Der vollständige Unterschied steht in SVERWEIS vs. XVERWEIS.

Gelöschte Namen und Gültigkeitslisten geben ebenfalls einen #BEZUG!

Nicht nur Zellbezüge brechen. Wenn Sie einen benannten Bereich in einer Formel verwenden und diesen Namen über Formeln > Namens-Manager löschen, verwandelt sich jede Formel, die den Namen verwendete, in einen Fehler. Excel zeigt dann meist #NAME? an, bei einem Bereichsbezug innerhalb dieses Namens kann es aber auch #BEZUG! werden.

Auch die Datenüberprüfung ist empfindlich. Verweist eine Auswahlliste (Daten > Datenüberprüfung) auf einen Bereich, den Sie später entfernen, funktioniert die Liste nicht mehr. Prüfen Sie bei solchen Fehlern über Formeln > Namens-Manager, ob die Quelle noch existiert, und reparieren Sie den Namen oder weisen Sie einen neuen Bereich zu. So halten Sie nicht nur Ihre Formeln, sondern auch Ihre Eingabelisten und Pivot-Tabellen heil.

Tabellen und Namen verhindern, dass Bezüge brechen

Sie verhindern den #BEZUG!-Fehler, indem Sie Bezüge verwenden, die sich mitbewegen, wenn sich die Struktur ändert.

• Arbeiten Sie mit Excel-Tabellen. Machen Sie aus Ihrem Bereich eine Tabelle mit Strg+T. Strukturierte Verweise wie Verkauf[Umsatz] passen sich automatisch an, wenn Sie Zeilen oder Spalten hinzufügen oder löschen.
• Verwenden Sie Namen für Bereiche. Geben Sie einem Bereich über Formeln > Namen definieren einen Namen. Wenn Sie mit diesem Namen verweisen, bleibt die Formel lesbar und verschiebt sich mit.
• Wählen Sie INDEX und VERGLEICH oder XVERWEIS anstelle einer festen Spaltenindexnummer. Solche Suchmethoden verweisen auf die Spalte selbst und brechen nicht, wenn Sie eine dazwischen einfügen.
• Löschen Sie Spalten bewusst. Möchten Sie eine Spalte entfernen, prüfen Sie zuerst mit Vorgängerpfeilen, ob Formeln davon abhängen.
• Ausblenden statt Löschen. Wenn eine Hilfsspalte nur optisch im Weg ist, blenden Sie sie aus (Rechtsklick, Ausblenden), anstatt sie zu löschen; ausgeblendete Spalten brechen keine Bezüge.

Pivot-Tabellen sind ebenfalls empfindlich gegenüber gelöschten Quellspalten; wie Sie diese aufbauen und aktualisieren, steht in So erstellen Sie eine Pivot-Tabelle in Excel.

Mit WENNFEHLER verbergen Sie einen verbleibenden Fehler sauber

Manchmal ist ein Bezug vorübergehend leer oder der Fehler gehört einfach nicht in Ihren Bericht. Fangen Sie ihn dann mit WENNFEHLER ab, sodass ein leerer Wert oder eigener Text anstelle von #BEZUG! erscheint:

=WENNFEHLER(B2*C2; "")

Verwenden Sie WENNFEHLER bewusst. Es verbirgt nicht nur #BEZUG!, sondern auch andere Fehler wie #DIV/0! und #NV. Lösen Sie also zuerst den zugrunde liegenden Bezug und setzen Sie WENNFEHLER erst darum, wenn Sie sicher sind, dass der Fehler unschädlich ist. Andernfalls maskieren Sie ein echtes Problem in Ihrer Berechnung und merken erst spät, dass Zahlen fehlen.

Wenn Sie nur bestimmte Fehler abfangen möchten, verwenden Sie WENNNV ausschließlich für #NV oder prüfen Sie vorab mit ISTFEHLER. So behalten Sie die Fehler im Blick, die Sie sehen wollen. Eine gute Vorgehensweise ist, die Fehler während des Aufbaus sichtbar zu lassen und WENNFEHLER erst hinzuzufügen, wenn das Modell fertig und bereit zum Teilen ist. So maskieren Sie keine Probleme, die Sie noch lösen müssen. Mehr zu häufigen Meldungen und deren Vermeidung finden Sie in der Erklärung von Microsoft zum #BEZUG!-Fehler.