Excel OFFSET-Funktion — Ein Bezug, der sich verschiebt (und wann INDEX besser ist)

Kurz gefasst — OFFSET liefert einen Bezug, der an einer Ankerzelle beginnt, eine gegebene Anzahl von Zeilen nach unten und Spalten nach rechts wandert und optional einen Block von gegebener Höhe und Breite greifen kann. Syntax: =OFFSET(reference, rows, cols, [height], [width]). =OFFSET(A1, 2, 1) ist die Zelle zwei Zeilen unter und eine Spalte rechts von A1 (also B3). Weil sie einen Bezug liefert, keinen Wert, können Sie sie in SUM, AVERAGE oder COUNT einwickeln, und sie passt ihre Größe bei Bedarf an. Zwei Warnungen: sie ist flüchtig (volatil) (berechnet sich bei jeder Bearbeitung neu), und um schlicht eine Zelle nach Position auszuwählen, erledigt das nicht-flüchtige INDEX dieselbe Aufgabe ohne die Performance-Kosten. Behalten Sie OFFSET für Fenster, die sich wirklich bewegen.

=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))   ' Spalte A summieren, egal wie hoch sie wächst
=SUM(OFFSET(B2, COUNT(B:B)-3, 0, 3, 1))  ' die letzten 3 Zahlen in Spalte B summieren

Hinweis für deutsches Excel: Im deutschen Excel heißen diese Funktionen BEREICH.VERSCHIEBEN (OFFSET), INDEX (INDEX, gleich), SUMME (SUM), MITTELWERT (AVERAGE), ANZAHL (COUNT), ANZAHL2 (COUNTA), SUMMEWENNS (SUMIFS), VERGLEICH (MATCH) und FILTER. Der Fehlerwert #REF! erscheint in der deutschen Oberfläche als #BEZUG!. Die Formeln unten verwenden bewusst die englischen Funktionsnamen (sie funktionieren versionsübergreifend und sind die Suchbegriffe).

Die meisten begegnen OFFSET als der Maschine hinter einem "dynamischen Bereich" — einem benannten Bereich oder einer Diagrammquelle, die sich automatisch erweitert, wenn Zeilen hinzukommen. Das kann sie gut, ebenso wie eine Handvoll Bewegungsfenster-Probleme, die nichts anderes so sauber ausdrückt. Aber sie ist auch flüchtig und leicht zu überstrapazieren, und modernes Excel hat die Hälfte ihrer alten Einsatzfälle leise überflüssig gemacht. Diese Anleitung gibt Ihnen das mentale Modell, die Fallen und eine klare Linie, wann Sie stattdessen zu INDEX wechseln sollten.

Was Sie lernen werden

Das mentale Modell: an einem Anker beginnen, sich bewegen, dann optional einen Block greifen
Warum OFFSET einen Bezug liefert — und warum das erlaubt, dass SUM ihn umschließt
Die Kosten der Flüchtigkeit und die #REF!-über-den-Rand-Falle
Der klassische dynamische benannte Bereich — und warum eine Tabelle oder ein Überlaufbereich ihn heute schlägt
Die wirklich schwer ersetzbare Nische: bewegliche Fenster und gleitende Summen
Die Ermessensfrage: OFFSET vs. das nicht-flüchtige INDEX

Das mentale Modell: hier beginnen, so weit gehen, so viel greifen

OFFSET hat fünf Argumente, aber sie erzählen eine einfache Geschichte. Beginnen Sie am Anker, gehen Sie rows Schritte nach unten und cols Schritte nach rechts, um auf einer neuen Zelle zu landen, und — wenn Sie height und width angeben — dehnen Sie sich von dieser Landezelle aus zu einem Block dieser Größe aus:

=OFFSET(A1, 2, 1)         ' auf B3 landen (2 runter, 1 rechts), eine Zelle
=OFFSET(A1, 2, 1, 3, 2)   ' ein Block von 3 Zeilen × 2 Spalten ab B3  ->  B3:C5
=OFFSET(A1, -1, 0)        ' negativ läuft NACH OBEN -> Zeile über A1 (hier #REF!)

Positive Zahlen bewegen nach unten und rechts; negative Zahlen nach oben und links. Lassen Sie height und width weg, hat das Ergebnis dieselbe Größe wie der Anker (üblicherweise eine einzelne Zelle). Das ist die ganze Funktion — ein beweglicher Zeiger, der sich auch zu einem Rechteck strecken kann.

Warum sie einen Bezug liefert, keinen Wert

Das ist die Eigenschaft, die OFFSET nützlich macht und Neulinge verwirrt. OFFSET gibt nicht den Inhalt des Blocks zurück — es gibt den Bezug auf den Block zurück. Allein in einer Zelle wird =OFFSET(A1,2,1,3,2) überlaufen oder den obersten Wert zeigen, aber der eigentliche Zweck ist, an eine andere Funktion gespeist zu werden:

=SUM(OFFSET(A1, 0, 0, 12, 1))     ' einen 12-Zeilen-Block ab A1 summieren
=AVERAGE(OFFSET(A1, 0, 0, 12, 1)) ' den Durchschnitt desselben Blocks bilden

Weil die Höhe des Blocks ein Argument ist, können Sie sie zu einer Formel machen — COUNTA(A:A), MATCH(...), eine Zelle, in die der Nutzer tippt — und der Bereich passt seine Größe selbst an. Das ist die gesamte Grundlage jedes auf OFFSET gebauten "dynamischen Bereichs".

Der klassische dynamische Bereich — und sein moderner Ersatz

Jahrelang war die Standardmethode, ein Diagramm oder eine Dropdown-Liste mit Ihren Daten mitwachsen zu lassen, ein benannter OFFSET-Bereich:

' Name "SalesData" =  OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)
' erweitert sich automatisch auf so viele Zeilen, wie Spalte A gerade hat

Es funktioniert. Aber auf jeder aktuellen Excel-Version ist es die falsche Voreinstellung, aus zwei Gründen. Erstens ist OFFSET flüchtig — eine Arbeitsmappe voll davon berechnet bei jedem Tastendruck neu und beginnt sich langsam anzufühlen. Zweitens hat Excel inzwischen nicht-flüchtige Werkzeuge, die dasselbe lesbarer erledigen:

Eine Excel-Tabelle (Strg+T) wächst automatisch; referenzieren Sie sie als Table1[Sales], und jedes Diagramm, jedes SUMIFS und jede Dropdown-Liste, die darauf zeigt, erweitert sich von selbst — keine Formel, keine Flüchtigkeit.
Ein Überlaufbereich aus einer dynamischen Array-Formel nutzt den Operator #: wenn =FILTER(...) ab E2 überläuft, dann ist E2# der lebende Bereich, immer in der richtigen Größe.

Das Urteil: wenn Sie 2026 nach einem benannten dynamischen OFFSET-Bereich greifen, greifen Sie stattdessen zu einer Tabelle oder einem Überlauf-Bezug. Behalten Sie OFFSET-Bereiche nur für Altmappen, die Sie pflegen, nicht für neue.

Die Falle: über den Rand laufen liefert #REF!

OFFSET scheitert lautstark, wenn Sie es an einen Ort schicken, den es nicht gibt. Bewegen Sie sich über Zeile 1 hinaus oder links von Spalte A — oder dimensionieren Sie einen Block, der über die letzte Zeile des Blatts hinausläuft — und Sie erhalten #REF!:

=OFFSET(A1, -1, 0)          ' #REF!  — es gibt keine Zeile über A1
=OFFSET(A1, 0, 0, 0, 1)     ' #REF!  — ein Block kann keine Höhe von null haben

Die subtile Variante beißt in dynamischen Bereichen: wenn COUNTA($A:$A) einen verirrten Wert weit unten in der Spalte mitzählt oder auf einer leeren Spalte null zählt, läuft das Höhenargument schief, und der ganze Bereich bricht zusammen oder liefert einen Fehler. Verankern Sie am wahren oberen Ende Ihrer Daten und stellen Sie sicher, dass die Größenformel keine 0 zurückgeben kann.

Die Nische, die OFFSET behält: bewegliche Fenster

Hier verdient sich OFFSET noch seinen Platz — ein Fenster, das gleitet, bei dem sich Start und Größe zusammen bewegen. Gleitende Summen und "die letzten N Zeilen" sind die typischen Fälle:

' Gleitende Summe der jüngsten 3 Einträge in Spalte B
=SUM(OFFSET(B2, COUNT(B2:B1000)-3, 0, 3, 1))

' Wert N Zeilen unter einer gefundenen Überschrift (ein beweglicher Lesepunkt)
=OFFSET($A$1, MATCH("Total", $A:$A, 0)-1, 1)

Diese Bewegung — "verankern, dann zu einer von einer Formel berechneten Position springen, dann ein Fenster darum herum lesen" — lässt sich wirklich umständlich anders ausdrücken. Selbst hier ist eine datumsbasierte gleitende Summe oft sauberer als SUMIFS mit einer Bedingung ">="&cutoff — aber für ein positionelles Fenster nach Zeilenanzahl ist OFFSET immer noch das knappste Werkzeug.

Die Ermessensfrage: OFFSET vs. INDEX

Die eine Regel, die die meiste OFFSET-Überstrapazierung behebt: wenn Sie OFFSET verwenden, um in einen Bereich zu indizieren — "gib mir die Zelle an dieser Position" — wechseln Sie zu INDEX. INDEX liefert ebenfalls einen Bezug, macht dieselbe positionelle Auswahl und ist nicht flüchtig:

=OFFSET($A$1, n, 0)     ' flüchtiger Weg zur (n+1)-ten Zelle in Spalte A
=INDEX($A:$A, n+1)      ' dieselbe Zelle, nicht-flüchtig, Excel kann sie verfolgen

Eine Zelle/Zeile/Spalte nach Position auswählen → INDEX. Nicht-flüchtig, verfolgbar, liest sich klarer. Das deckt die große Mehrheit dessen ab, wofür Leute OFFSET verwenden.
Einen Bereich mitwachsen lassen, wenn Daten hinzukommen → eine Tabelle oder ein Überlauf-#-Bezug.
Ein wirklich bewegliches Fenster (gleitende Summe, letzte N Zeilen, Lesen-um-einen-Treffer-herum) → OFFSET ist noch die richtige Wahl, bewusst und in kleiner Zahl eingesetzt.

Die klare Meinung: OFFSET ist ein Spezialist, kein Grundnahrungsmittel. Jedes flüchtige OFFSET, das Sie durch INDEX, eine Tabelle oder einen Überlaufbereich ersetzen, macht die Arbeitsmappe schneller und für die nächste Person leichter zu verfolgen — und Sie verlieren nichts außer der Neuberechnungsverzögerung.

Wie ExcelMaster hilft

OFFSET-Formeln sind die Stelle, an der Tabellen langsam und unlesbar werden — verschachteltes COUNTA, um-eins-daneben-liegende Höhen und eine Flüchtigkeit, die niemand bemerkt, bis die Datei kriecht. ExcelMaster liest das Ziel — ein Diagramm, das mit den Daten wächst, eine gleitende 3-Monats-Summe, der Wert neben der Zeile, die "Total" sagt — und schreibt es auf die moderne Art: eine Tabelle oder einen Überlaufbereich, wenn Sie nur Wachstum brauchen, INDEX, wenn Sie nach Position indizieren, und ein knappes OFFSET nur dann, wenn ein bewegliches Fenster es wirklich verlangt. Sie erhalten das dynamische Verhalten, ohne eine flüchtige Formel zu erben, die Sie hüten müssen.

Häufig gestellte Fragen

Was macht die OFFSET-Funktion in Excel?

Sie liefert einen Bezug, der eine gegebene Anzahl von Zeilen und Spalten von einem Startanker entfernt liegt, optional auf einen Block vergrößert: =OFFSET(A1, 2, 1) ist die Zelle zwei Zeilen unter und eine Spalte rechts von A1. Weil sie einen Bezug liefert, wickeln Sie sie normalerweise in SUM, AVERAGE oder COUNT ein.

Warum gilt OFFSET als flüchtig?

Excel kann nicht vorhersagen, auf welche Zellen OFFSET zeigen wird, bis es rechnet, also wertet es bei jeder Neuberechnung neu aus, nicht nur wenn sich die Eingaben ändern. Ein paar sind harmlos; Tausende verlangsamen die Arbeitsmappe merklich. Das ist der Hauptgrund, wo möglich INDEX oder eine Tabelle zu bevorzugen.

OFFSET vs. INDEX — welche sollte ich verwenden?

Zum Auswählen einer Zelle nach Position verwenden Sie INDEX — es liefert genau wie OFFSET einen Bezug, ist aber nicht-flüchtig und verfolgbar. Behalten Sie OFFSET für wirklich bewegliche Fenster (gleitende Summen, "letzte N Zeilen"), bei denen sich Start und Größe zusammen verschieben.

Wie erstelle ich mit OFFSET einen dynamischen Bereich?

Das klassische Muster ist =OFFSET($A$1, 0, 0, COUNTA($A:$A), 1) in einem benannten Bereich, der wächst, während sich Spalte A füllt. In modernem Excel bevorzugen Sie stattdessen eine Excel-Tabelle oder einen Überlauf-Bezug (A2#) — beide erweitern sich automatisch ohne Flüchtigkeit.

Warum liefert mein OFFSET #REF!?

Sie sind über den Rand des Blatts hinausgelaufen (negative Zeilen über Zeile 1, über die letzte Spalte hinaus) oder haben einem Block eine Höhe oder Breite von 0 gegeben. In dynamischen Bereichen bedeutet das meist, dass die Höhenformel (wie COUNTA) 0 oder eine unerwartete Anzahl geliefert hat. Prüfen Sie den Anker und die Größenargumente.

Getestet in

Getestet in: Excel 365 (Windows 11) — zuletzt geprüft am 2026-07-01.

Verwandte Anleitungen: Excel INDIRECT · Excel ADDRESS · Excel SUMIFS · Excel FILTER · Excel XLOOKUP