Excel: Ganze Spalte ohne oberste Zellen referenzieren

Angenommen, Sie haben eine Datenliste, von der Sie jetzt noch nicht wissen, wie viele Einträge sie haben wird. Direkt oberhalb der Liste wollen Sie eine Berechnung mit den Daten in der Liste durchführen. Sie würden hierfür gerne die Daten der ganzen Spalte berücksichtigen, unabhängig davon, wie viele Einträge es geben wird. 

Dies aus zwei Gründen: Falls die Berechnung nur z.B. Zeilen 3 bis 50 berücksichtigt, dann würden die Daten ab Zeile 51 nicht mehr in die Formel miteinbezogen. Damit hätten Sie falsche Resultate. Zweitens: Wenn Sie in der Formel mit etwas wie «=SUMME(J:J)» die ganze Spalte adressieren, darf die Formel nicht in derselben Spalte stehen. Excel würde damit zu Recht einen Zirkelbezug monieren:

So geht es also nicht. Wie denn sonst? Sie haben grundsätzlich «zweieinhalb» Möglichkeiten. Eine davon ist sogar sehr elegant.

Maximale Zellenzahl: Eine Excel-Tabelle kann derzeit maximal 1'048'576 Zeilen haben. Soll also die ganze Spalte berücksichtigt werden, setzen Sie beim «Von»-Wert die Zelle ein, ab der berechnet werden soll, und beim «Bis»-Wert die unterste überhaupt mögliche Zelle, also etwa so: =SUMME(J4:J1048576). Nur die wenigsten wollen sich diese Zahl merken. Es ist auch gar nicht nötig. Sie können auch eine kleinere Zahl verwenden, die aber auf jeden Fall höher sein muss als die grösste zu erwartende Anzahl Zeilen.

Wenn Sie sich die Zahl nicht merken wollen, lässt sich die Spalte auch so markieren: Fangen Sie an, die Formel einzutippen, z.B. =SUMME(. An der Stelle, an der Sie die Zellen referenzieren wollen, markieren Sie die bereits ausgefüllten Zellen per Maus, z.B. J4 bis J43. Drücken und halten Sie jetzt mit der linken Hand die Ctrl+Shift-Tasten (Strg+Umschalt) und drücken Sie mit der rechten noch zusätzlich die Abwärtspfeil-Taste. Flugs markiert Excel in dieser Spalte auch alle Zellen unterhalb der bereits ausgefüllten Tabelle – bis hin zur untersten möglichen Zelle in dieser Spalte. Schliessen Sie die Formel noch ab, z.B. indem Sie die schliessende Klammer eintippen und Enter drücken.

Viel eleganter: Definieren Sie einen Namen!

Excel kann für frei wählbare Bereiche in Ihren Arbeitsblättern Namen verwalten. Der lässt sich danach in Formeln wieder ansprechen. Das ist viel eleganter. Markieren Sie die ganze Spalte, indem Sie auf den Spaltenkopf klicken. Jetzt sind die obersten Zellen wie z.B. der Spaltentitel und das Feld für die Formel noch mitmarkiert. Drücken und halten Sie deshalb die Ctrl-Taste (Strg), während Sie per Maus der Reihe nach die paar obersten Zellen «entmarkieren», die nicht in den zu addierenden Bereich gehören. Wenn Sie in der Tabelle herunterscrollen, sehen Sie, dass die ganze Spalte markiert ist, nicht nur der Teil, der bereits Daten enthält – und ohne die «entmarkierten» Zellen.

Im Reiter Formeln geht es im Bereich Definierte Namen zu Namen definieren. Excel schlägt schon den Spaltentitel als Namen vor, hier zum Beispiel «Spenden». Übernehmen Sie den Namen oder ändern Sie ihn. Wie Sie sehen, ist als ausgewählter Bereich tatsächlich die ganze Spalte (hier: ab J4) angegeben. Klicken Sie auf OK.

Für die Formel geht es nun kinderleicht: Bleiben Sie gleich im Reiter Formeln. Klicken Sie in die Zelle, in der die Formel hinsoll. Ist es z.B. eine Summe, beginnen Sie die Formel einzutippen: =SUMME(. Öffnen Sie im Reiter Formel unter Definierte Namen den Punkt In Formel verwenden. Hier entdecken Sie den Namen, den Sie vorhin für den Bereich festgelegt haben, hier zum Beispiel Spenden. Klicken Sie ihn an und schliessen Sie die Formel ab.

Das wars schon! Excel berechnet nun die komplette Spalte, ohne die obersten Zeilen.

Und was ist mit LibreOffice? Wenn Sie in diesem eine ganze Spalte durch Klick auf den Spaltenkopf markieren, erscheint nicht «J:J», sondern der tatsächliche Zellbereich – bis hinunter zur unterstmöglichen Zeile. Das macht die Sache einfacher, weil Sie in der Formel bequem die «Von»-Angabe von «J1» zu «J4» ändern können, wenn die zu berechnenden Zahlen bei J4 anfangen. Das mit dem händischen markieren mit Ctrl+Shift+Abwärtspfeil klappt aber auch hier. Und selbstverständlich geht auch das Definieren eines Namens. Sie finden das unter Data/Bereich festlegen. Die Nutzung ist identisch wie bei den definierten Namen in Excel.