Wer die Formel aus einer Zelle sichtbar machen möchte, kann dafür die sehr praktische FORMELTEXT-Funktion nutzen. Die macht genau, was ihr Name andeutet: Die Formel in der angegebenen Zelle wird einfach als Text angezeigt. Leider klappt das immer nur mit einer einzelnen Formelzelle. Was aber, wenn ich eine Übersicht sämtlicher Formeln in einem Arbeitsblatt möchte?
Hierfür gibt es keine Standard-Lösung. Heute zeige ich dir, wie man mit Microsoft 365 (oder Excel 2024) eine Lösung für dieses Problem basteln kann. Und am Ende des Artikels gibt es sogar noch eine Variante für Anwender von Excel 2021, 2019 und 2016!
Und so geht’s:
Beispieldatei herunterladen
Die Ausgangslage
Meine kleine Beispieldatei enthält 2 Listen und 3 Formeln, alles im Bereich von A1:E22. Die Formeln habe ich hellblau markiert, so dass man das Beispiel leichter nachvollziehen kann:
Einfaches Beispiel mit 3 Formelzellen
Die folgende Lösung für das Problem habe ich vor längerer Zeit bei Mike Girvin gesehen, einem amerikanischen Excel-MVP. Ich fand diesen Trick so beeindruckend, dass ich ihn dir nicht vorenthalten möchte. Ruhm und Ehre gebühren aber Mike Girvin!
Ich habe mir lediglich eine etwas erweiterte Variante überlegt, die ich weiter unten nach der ursprünglichen Variante von Mike noch vorstellen werde.
Schritt 1: Die FORMELTEXT-Funktion
Die FORMELTEXT-Funktion zeigt also im Klartext die Formel an, die sich in der angegebenen Zelle befindet. Wer beispielsweise Schulungsunterlagen erstellt, kann damit ganz leicht einzelne Formeln für die Schulungsteilnehmer sichtbar machen.
=FORMELTEXT(Zellbezug)
In Excel aus Microsoft 365 kann man sogar einen Zellenbereich angeben (in älteren Excel-Versionen funktioniert das leider nicht). Als Ergebnis erhält man eine Matrix, die genauso groß ist, wie der in der Funktion angegebene Bereich:
=FORMELTEXT(A1:E22)
Hilfstabelle 1 mit der FORMELTEXT-Funktion
Das ist zumindest schon mal ein Anfang. Dort, wo in der Datenquelle Formeln enthalten sind, wird die Formel angezeigt, alle anderen Zellen enthalten einen #NV-Fehler Aber das ist natürlich sehr unübersichtlich. Du kannst dir sicherlich vorstellen, je größer der ausgewählte Zellenbereich ist, desto größer (und unübersichtlicher) wird natürlich auch diese Ausgabetabelle. Außerdem man kann nicht erkennen, in welcher Zelle sich die Formel angezeigte Formel befindet.
Schritt 2: Die Zelladressen bestimmen
Rechts neben meine erste Ausgabematrix erstelle ich jetzt mit Hilfe der ADRESSE-Funktion eine Tabelle mit allen Zelladressen des Quellbereichs. Die Funktion erwartet folgende Argumente:
=ADRESSE(Zeilennummer; Spaltennummer; [Absolut/Relativ]; [Adressenformat]; [Tabellenblatt])
Da ich keine einzelne Zelle verarbeiten möchte, sondern einen größeren Bereich, muss ich für die ersten beiden Argumente auf zwei weitere Funktionen zurückgreifen: ZEILE und SPALTE (nochmal der Hinweis: das funktioniert in dieser Form nur in Microsoft 365!)
=ADRESSE(ZEILE(A1:E22);SPALTE(A1:E22);4)
Hilfstabelle 2 zum Erzeugen aller Zelladressen
Das dritte Argument legt die Art der Adressierung fest: Relativ, absolut oder gemischt. Der Wert 4 steht für relative Bezüge.
Schritt 3: Adressen und Formeln kombinieren
Als nächstes kombiniere ich die beiden erzeugten Ausgabetabellen, damit vor den Formeln die jeweilige Zelladresse steht. Zur besseren Lesbarkeit wird die Adresse durch einen Doppelpunkt von der Formel getrennt:
=M1#&": "&G1#
Hilfstabelle 4 kombiniert die Adressen mit den Formeln
Da es sich dabei um dynamische Bereiche handelt, spreche ich nur die jeweils erste Zelle an, gefolgt von dem #-Zeichen. Dadurch werden die kompletten Tabellenbereiche dynamisch erfasst.
Die Raute: Ein kleines Zeichen mit großer Wirkung!
Schritt 4: Die Matrix in eine einzelne Spalte umwandeln
Kommen wir zum Finale, der Umwandlung der Matrix in eine einspaltige Liste. Dafür brauchen wir die Funktion ZUSPALTE:
=ZUSPALTE(S1#;2)
Die Matrix wird in eine einspaltige Liste umgewandelt
Der Wert 2 in der Funktion besagt, dass Fehler ignoriert werden sollen. Damit sind wir ganz elegant alle #NV-Fehler losgeworden!
Sieht doch gar nicht schlecht aus, oder?
Selbstverständlich können wir auf die ganzen Hilfstabellen verzichten und alles in eine einzige Formel packen:
=ZUSPALTE(ADRESSE(ZEILE(A1:E22);SPALTE(A1:E22);4)&": "&FORMELTEXT(A1:E22);2)
Es geht auch ohne Hilfstabellen
Erweiterte Variante: Aufteilen in 2 Spalten
Auf Anregung meines Excel-Kollegen Dieter Schiecke habe ich mir überlegt, wie man die schöne Lösung von Mike auf 2 Spalten aufteilen könnte. In der ersten Spalte soll dabei die Zelladresse stehen und in der zweiten die dazugehörige Formel.
Der erste, mir naheliegend erscheinende Versuch scheiterte kläglich. In der Ausgangsformel hänge ich ja an die Zelladresse einen Doppelpunkt an. Und ich hatte mir gedacht, dass man diesen Doppelpunkt wunderbar als Trennzeichen in der TEXTTEILEN-Funktion nutzen könnte:
=TEXTTEILEN(ZUSPALTE(ADRESSE(ZEILE(A1:E24);SPALTE(A1:E24);4)&": "&FORMELTEXT(A1:E24);2);":")
Einfach gedacht, funktioniert aber nicht: TEXTTEILEN
Leider falsch gedacht. Es bleibt dann nur noch die erste Spalte mit den Zelladressen übrig, die Formeln werden dummerweise nicht mehr angezeigt.
Versuch 2 brachte mich etwas näher an das gewünschte Ergebnis heran. Die Überlegung war, die beiden Spalten zunächst getrennt zu berechnen und dann über HSTAPELN zusammenzusetzen:
=HSTAPELN(ZUSPALTE(ADRESSE(ZEILE(A1:E24);SPALTE(A1:E24);4))&":";ZUSPALTE(FORMELTEXT(A1:E24)))
Variante 2 liefert auch Zeilen ohne Formeln
Wie man im Bild oben sieht, klappt das grundsätzliche schon, es werden zwei separate Spalten erstellt. Allerdings gibt es jetzt für jede einzelne Zeile im ausgewählten Bereich eine eigene Zeile. Und überall, wo keine Formel steht, wird ein hässliches #NV ausgegeben.
Dieses #NV ließe sich zwar über die WENNFEHLER-Funktion unterdrücken, ändert aber nichts daran, dass trotzdem alle Zeilen im ausgewählten Bereich aufgelistet werden.
Das führte zu Versuch 3. Denn mit Hilfe der FILTER-Funktion lassen sich die unerwünschten Zeilen ausfiltern. Als Filterkriterium suche ich nach allen Zeilen, die mit einem =-Zeichen beginnen. Was ja für alle Formelzeilen der Fall ist. Das führt dann zu folgender Lösungsformel:
=FILTER(HSTAPELN(ZUSPALTE(ADRESSE(ZEILE(A1:E24);SPALTE(A1:E24);4))&":";ZUSPALTE(FORMELTEXT(A1:E24)));ISTZAHL(SUCHEN("=";ZUSPALTE(FORMELTEXT(A1:E24)))))
Die FILTER-Funktion bringt die Lösung
Zugegeben:
Eine etwas komplexere Variante. Aber sie erfüllt ihren Zweck.
Nachtrag: Lösung für Excel 2021 und älter
Mein Leser Ernst hat mir jetzt sogar noch eine Lösung für Nicht-Microsoft-365-Anwender geschickt. Diese Variante funktioniert mit Excel 2016, 2019 und 2021.
An dieser Stelle nochmal vielen Dank, Ernst!
=XMLFILTERN("<M>"&TEXTKETTE(WENNFEHLER("<R>"&ADRESSE(ZEILE(A1:E22);SPALTE(A1:E22);4)&":"&FORMELTEXT(A1:E22)&"</R>";""))&"</M>";"//R")
Lösung für ältere Excel-Versionen
Ganz wichtig:
Die geschweiften Klammern oben im Bild dürfen nicht von Hand eingetippt werden. Stattdessen muss die Formel zwingend mit Strg+Umschalt+Eingabe abgeschlossen werden, damit sie in diesen älteren Excel-Versionen funktioniert.
Fazit
Mit ein paar clever kombinierten Funktionen lässt sich in Excel aus Microsoft 365 eine übersichtliche Liste aller im jeweiligen Arbeitsblatt verwendeten Formeln erzeugen. In älteren Excel-Versionen ist etwas mehr Aufwand nötig, aber Dank der Lösung von Ernst ist es trotzdem möglich.
Zwei weitere Einschränkungen sollen aber auch nicht unerwähnt bleiben:
Hat man mehrere Arbeitsblätter im Einsatz, muss diese Formel auch für jedes Blatt erstellt werden. Außerdem sollte man sich bei der Auswahl des Datenbereichs auch auf den tatsächlich verwendeten Zellbereich beschränken, und nicht einfach das komplette Arbeitsblatt auswählen. Denn das ist zu viel des Guten und wird mit einer Fehlermeldung abgestraft.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
das Beispiel für ein strukturiertes Herangehen an eine komplexe Formel ist dir wieder sehr schön gelungen.
In der Praxis benutze ich dafür aber gerne das Add-In ‚Inquire‘, das man sich in den Excel-Optionen als COM-Addin einbinden kann.
Das bietet einem außer der Formelübersicht noch sehr viele weitere Daten zu der Arbeitsmappe.
Hallo Jochen,
danke für das schöne Feedback. Wer das Inquire-Add-In nutzen kann, ist natürlich im Vorteil. Leider ist das nur in bestimmten Lizenzen enthalten (Office Professional Plus bzw. Microsoft 365 Enterprise), ich muss daher auch darauf verzichten. Was mich schön öfter geärgert hat…
Schöne Grüße,
Martin
Hallo,
der Vollständigkeit halber sei auf die Funktion: „Formeln > Formeln anzeigen“ hingewiesen.
Oft ausreichend und nur Klicks, nur die Spaltenbreiten muss man leider anpassen.
Hallo Clemens,
sehr guter Hinweis. Für einen schnellen Überblick ist das auch eine gute Option.
Schöne Grüße,
Martin
Hallo Martin
Leider steht mir das Add-In „Inquire“ nicht zur Verfügung. So kann ich nicht ausprobieren, was damit möglich ist.
Was mir an Deiner komplexen Formel erst nicht so gefallen hat, war die Anordnung der Ergebnisse. Es werden alle Formeln ab einer Zeile (zum Beispiel ab Zeile „7“) bis zur letzten Zeilen (z.B. bis Zeile „20“) angezeigt.
Durch eine simple Ergänzung der Formel konnte ich es aber erreichen, das zuerst alle Formeln ab einer Spalte (zum Beispiel ab Spalte „D“) bis zur letzten Spalte (z.B. Spalte „H“) angezeigt werden.
Die Lösung sieht so aus:
=ZUSPALTE(ADRESSE(ZEILE(D4:E25);SPALTE(D4:E25);4)&": "&FORMELTEXT(D4:E25);2;WAHR)
Die Formel muss am Ende nur durch einen weiteren optionalen Wahrheitswert ergänzt werden. Fehlt der Wert, beziehungsweise es wurde „FALSCH“ eingegeben, ist die Sortierung Zeilen —> Spalten. Bei „WAHR“ ist die Sortierung Spalten —> Zeilen.
mit freundlichen Grüßen und ein großes Dankeschön für diesen tollen Beitrag
Heinz-Jürgen
Damit die Formel aus meinen Kommentar nach Copy und Paste auch korrekt funktioniert, müssen die kryprischen Anführungszeichen vor &FORMELTEXT durch die korrekten Anführungszeichen mit [SHIFT-2] ersetzt werden. Und der Bereich (D4:E25) muss überall an die eigenen Erfordernisse angepasst werden.
Schade, das beim Posten die Anführungszeichen so komisch umkodiert werden.
Hallo Heinz-Jürgen,
vielen Dank für dein Feedback und deine Ergänzungen. Ja, es ist leider etwas ärgerlich, dass manche Zeichen in den Kommentaren automatisch umgewandelt werden. Ich habe das jetzt direkt in deinem Kommentar geändert, die Formel sollte also jetzt zum Kopieren passen.
Schöne Grüße,
Martin