So zeigen Sie Formeln in Excel an (einblenden, finden und extrahieren)

Excel zeigt normalerweise Formelergebnisse statt der Formeln selbst an. Das macht Arbeitsblätter übersichtlich und leicht lesbar, kann jedoch unpraktisch sein, wenn Sie Berechnungen überprüfen, Fehler beheben oder verstehen müssen, wie eine unbekannte Arbeitsmappe funktioniert.

Es gibt mehrere Möglichkeiten, Formeln in Excel anzuzeigen, egal ob Sie eine einzelne Zelle prüfen, ein ganzes Arbeitsblatt überprüfen, Formeln mit ihren Ergebnissen vergleichen oder Formeln aus mehreren Arbeitsblättern oder Arbeitsmappen in großen Mengen extrahieren möchten. Die folgende Tabelle hilft Ihnen, die richtige Methode auszuwählen.

Wann sollten Sie Formeln in Excel anzeigen?

Das Anzeigen von Formeln ist besonders nützlich, wenn:

• Finanztabellen geprüft werden
• Berechnungsfehler behoben werden
• Von anderen Benutzern erstellte Formeln überprüft werden
• Geprüft wird, ob kopierte Formeln auf die richtigen Zellen verweisen
• Tabellen für Compliance-Prüfungen vorbereitet werden

Formel in einer einzelnen Zelle anzeigen

Wenn Sie prüfen möchten, wie ein Wert in einer bestimmten Zelle berechnet wurde, können Sie seine Formel direkt in der Bearbeitungsleiste oder im Bearbeitungsmodus anzeigen, ohne das Arbeitsblatt zu ändern.

Option 1: Verwenden der Bearbeitungsleiste

1. Wählen Sie die Zelle aus, die die Formel enthält.
2. Sehen Sie in der Bearbeitungsleiste über dem Arbeitsblatt nach, um die Formel anzuzeigen.

Angenommen, B2 und C2 enthalten 100 bzw. 150. Zelle D2 verwendet die Formel =SUM(B2:C2), um das Ergebnis 250 zu berechnen. Wenn Sie D2 auswählen, wird die Formel in der Bearbeitungsleiste angezeigt.

Option 2: Verwenden des Bearbeitungsmodus

Um die Formel und ihre referenzierten Zellen direkt im Arbeitsblatt zu prüfen:

1. Doppelklicken Sie auf die Formelzelle oder drücken Sie F2 unter Windows.

2. Excel zeigt die Formel in der Zelle an und hebt ihre Bezüge hervor.

   

3. Drücken Sie Esc, um das Bearbeiten ohne Änderungen zu beenden.

Tipp: Wenn die Bearbeitungsleiste ausgeblendet ist, aktivieren Sie sie unter:

Ansicht > Anzeigen > Bearbeitungsleiste.

Alle Formeln in einem Excel-Arbeitsblatt anzeigen

Beim Überprüfen großer Finanzmodelle oder Berichtsvorlagen mit vielen Berechnungen kann das Prüfen von Zellen einzeln zeitaufwendig sein. Excel bietet integrierte Optionen, um alle Formeln gleichzeitig anzuzeigen.

Option 1: Verwenden der Schaltfläche „Formeln anzeigen“

1. Öffnen Sie das Arbeitsblatt, das Sie überprüfen möchten.

2. Wechseln Sie zu Formeln > Formeln anzeigen in der Gruppe Formelüberwachung.

3. Excel zeigt Formeln anstelle ihrer berechneten Ergebnisse an.

   

Tipp: Sie können erneut auf Formeln anzeigen klicken, um zur normalen Ansicht zurückzukehren.

Option 2: Verwenden einer Tastenkombination

Drücken Sie unter Windows Strg + ` (die Gravis-Taste, normalerweise unter Esc), um zwischen Formeln und berechneten Werten zu wechseln.

Hinweis: Diese Funktion gilt für das aktive Arbeitsblatt und ändert nur die Anzeige der Formeln. Die zugrunde liegenden Berechnungen bleiben unverändert.

Wenn Sie stattdessen eine statische Kopie der Ergebnisse benötigen, können Sie Formeln in Excel entfernen und dabei die berechneten Werte beibehalten.

Zellen finden, die Formeln in Excel enthalten

Wenn Sie nicht jede Formel anzeigen möchten, aber Formelzellen schnell finden müssen, verwenden Sie Excels Gehe zu Speziell-Funktion. Diese Methode ist nützlich, wenn Sie Formeln vor dem Bearbeiten, Kopieren oder Konvertieren von Tabellendaten überprüfen möchten.

1. Wählen Sie das Arbeitsblatt oder den Bereich aus, den Sie durchsuchen möchten.

2. Wechseln Sie zu Start > Suchen und Auswählen > Gehe zu Speziell.

   

3. Wählen Sie Formeln aus.

4. Klicken Sie auf OK.

Excel hebt Zellen hervor, die Formeln enthalten. Sie können im Dialogfeld Gehe zu Speziell auch die Optionen unter Formeln verwenden, um Formeln anhand ihrer Ergebnistypen auszuwählen. Weitere Informationen finden Sie im Leitfaden von Microsoft zum Finden von Zellen, die Formeln enthalten.

Formeln und Ergebnisse nebeneinander anzeigen

Wenn Sie Formeln mit ihren berechneten Ergebnissen vergleichen müssen, verwenden Sie die FORMULATEXT-Funktion. Sie zeigt die Formel aus einer Zelle als Text in einer anderen an, ohne die ursprüngliche Berechnung zu beeinflussen.

Angenommen, Zelle D2 zeigt 250 an, berechnet mit der Formel =SUM(B2:C2).

So zeigen Sie die Formel neben ihrem Ergebnis an:

1. Wählen Sie eine leere Zelle daneben aus, z. B. E2.

2. Geben Sie die folgende Formel ein:

   =FORMULATEXT(D2)
   

3. Drücken Sie die Eingabetaste. Zelle E2 zeigt =SUM(B2:C2) an, während D2 weiterhin 250 anzeigt.

4. Ziehen Sie das Ausfüllkästchen nach unten, um Formeln für weitere Zeilen anzuzeigen.

Das Ergebnis sieht so aus:

Dies ist besonders nützlich, um Finanzberechnungen zu prüfen, Berichte zu überprüfen oder Tabellenkalkulationsformeln zu dokumentieren.

Hinweis: Laut Microsofts FORMULATEXT-Dokumentation gibt die Funktion #N/A zurück, wenn die referenzierte Zelle keine Formel enthält, die Formel 8.192 Zeichen überschreitet, die Formel aufgrund des Arbeitsblattschutzes nicht angezeigt werden kann oder die referenzierte externe Arbeitsmappe geschlossen ist.

Um Zellen leer zu lassen, wenn FORMULATEXT einen Fehler zurückgibt, verwenden Sie:

=IFERROR(FORMULATEXT(D2),"")

Da IFERROR alle Fehler abfängt, nicht nur #N/A, verwenden Sie diese Option nur, wenn ein leeres Ergebnis einem angezeigten Fehler vorzuziehen ist.

Warum kann ich Formeln in Excel nicht sehen?

Wenn Formeln nicht wie erwartet angezeigt werden, prüfen Sie diese häufigen Probleme:

• Bearbeitungsleiste fehlt: Wechseln Sie zu Ansicht > Anzeigen > Bearbeitungsleiste, um sie zu aktivieren.
• Formeln sind ausgeblendet: Das Arbeitsblatt ist möglicherweise geschützt, wodurch ausgeblendete Formeln nicht in der Bearbeitungsleiste angezeigt werden. Wenn Sie die Berechtigung haben, wechseln Sie zu Überprüfen > Blattschutz aufheben. Damit Formeln sichtbar bleiben, wenn das Blatt erneut geschützt wird, wählen Sie die betroffenen Zellen aus, öffnen Sie Zellen formatieren > Schutz und deaktivieren Sie das Kontrollkästchen Ausgeblendet.
• Formel erscheint als Text statt als Ergebnis: Prüfen Sie zunächst, ob Formeln anzeigen auf der Registerkarte Formeln aktiviert ist. Wenn nur bestimmte Zellen Formeln als Text anzeigen, prüfen Sie, ob sie als Text formatiert sind oder ein führendes Apostroph enthalten. Ändern Sie das Zellenformat bei Bedarf in Allgemein, entfernen Sie unbeabsichtigte führende Apostrophe und geben Sie die Formel erneut ein, indem Sie F2 und dann die Eingabetaste drücken.
• FORMULATEXT gibt #N/A zurück: Prüfen Sie, ob die referenzierte Zelle eine Formel enthält. Verweise auf Zellen in geschlossenen externen Arbeitsmappen können diesen Fehler ebenfalls verursachen.

Erweitert: Excel-Formeln programmgesteuert mit Python extrahieren

Für Entwickler oder Benutzer, die mit mehreren Excel-Dateien arbeiten, kann das manuelle Prüfen von Formeln über Arbeitsblätter hinweg zeitaufwendig sein. Python kann diesen Prozess automatisieren, indem es Formelausdrücke und Zelladressen aus mehreren Arbeitsmappen extrahiert, ohne jede Datei manuell öffnen zu müssen.

Das folgende Beispiel verwendet Free Spire.XLS for Python, um Formeln in mehreren Excel-Arbeitsmappen zu extrahieren und sie in einer Textdatei zu speichern. Microsoft Excel muss nicht installiert sein.

Schritt 1: Free Spire.XLS installieren

Führen Sie den folgenden pip-Befehl in Ihrem Terminal aus:

pip install spire.xls.free

Schritt 2: Formeln aus mehreren Excel-Arbeitsmappen extrahieren

Erstellen Sie einen Ordner namens Excel Files und legen Sie die .xlsx-Arbeitsmappen, die Sie verarbeiten möchten, darin ab.

Das folgende Skript durchsucht jede Arbeitsmappe, identifiziert Zellen mit Formeln und exportiert ihre Positionen und Ausdrücke in eine Textdatei.

from pathlib import Path
from spire.xls import Workbook

# Input folder and output file
input_dir = Path("Excel Files")
output_file = Path("Extracted Formulas.txt")

# Check the input folder
if not input_dir.is_dir():
    raise FileNotFoundError(f"Input folder not found: {input_dir}")

# Get all Excel files in the folder
excel_files = sorted(
    file for file in input_dir.glob("*.xlsx")
    if file.is_file() and not file.name.startswith("~{BODY_CONTENT}quot;)
)

if not excel_files:
    raise FileNotFoundError(f"No XLSX files found in: {input_dir}")

formula_count = 0

# Extract formulas from all workbooks
with output_file.open("w", encoding="utf-8") as output:
    for input_file in excel_files:

        # Load the Excel workbook
        workbook = Workbook()
        try:
            workbook.LoadFromFile(str(input_file))

            # Loop through all worksheets
            for i in range(workbook.Worksheets.Count):
                sheet = workbook.Worksheets[i]

                # Get the used range
                used_range = sheet.AllocatedRange

                # Find and extract formulas
                for cell in used_range:
                    if cell.HasFormula:
                        cell_address = cell.RangeAddressLocal.replace("{BODY_CONTENT}quot;, "")
                        formula = cell.Formula

                        # Write formula information to the text file
                        output.write(f"{input_file.name} | {sheet.Name}!{cell_address}: {formula}\n")
                        formula_count += 1
        finally:
            workbook.Dispose()

Schritt 3: Extrahierte Formeln prüfen

Öffnen Sie nach dem Ausführen des Skripts Extracted Formulas.txt. Die Ausgabe sieht in etwa so aus:

Budget.xlsx | Forecast!E2: =IF(D2>1000,D2*0.1,0)
Sales Report.xlsx | Summary!D2: =SUM(B2:C2)
Sales Report.xlsx | Summary!D3: =SUM(B3:C3)

Jede Zeile identifiziert die Quellarbeitsmappe, das Arbeitsblatt, die Zellposition und die Formel, was das Suchen, Überprüfen oder Dokumentieren von Berechnungen über mehrere Dateien hinweg erleichtert.

Hinweis: Das Skript liest Formelausdrücke, ohne die ursprünglichen Arbeitsmappen zu ändern. Es berechnet Formeln nicht explizit neu. Free Spire.XLS hat Arbeitsblatt- und Zeilenbeschränkungen für ältere .xls-Dateien, aber diese Beschränkungen gelten nicht für .xlsx-Dateien.

Zusammenfassung

Für eine einzelne Formel verwenden Sie die Bearbeitungsleiste oder den Bearbeitungsmodus. Um Formeln im gesamten Arbeitsblatt anzuzeigen, verwenden Sie Formeln anzeigen. Um Formelzellen zu finden, wählen Sie Gehe zu Speziell; um Ausdrücke und Ergebnisse zu vergleichen, verwenden Sie FORMULATEXT. Wenn Sie Formelausdrücke aus vielen .xlsx-Arbeitsmappen benötigen, kann die Python-Methode die Extraktion automatisieren.

Häufig gestellte Fragen

Kann ich in Excel Formeln und Werte gleichzeitig anzeigen?

Ja. Sie können Formeln und Werte zusammen anzeigen, indem Sie die FORMULATEXT-Funktion verwenden. Wenn D2 beispielsweise eine Formel enthält, geben Sie =FORMULATEXT(D2) in einer anderen Zelle ein, um die Formel anzuzeigen und gleichzeitig den berechneten Wert sichtbar zu lassen.

Wie sehe ich in Excel Formeln statt Werte?

Wählen Sie eine Formelzelle aus und prüfen Sie die Bearbeitungsleiste, oder aktivieren Sie Formeln anzeigen auf der Registerkarte Formeln, um alle Formeln im Arbeitsblatt anzuzeigen.

Warum zeigt Excel Formeln statt Ergebnisse an?

Wenn Formeln im gesamten Arbeitsblatt angezeigt werden, ist möglicherweise Formeln anzeigen aktiviert. Drücken Sie Strg + ` oder deaktivieren Sie Formeln > Formeln anzeigen. Wenn nur bestimmte Zellen Formeln als Text anzeigen, prüfen Sie, ob sie als Text formatiert sind oder ein führendes Apostroph enthalten.

Kann ich Excel-Formeln extrahieren, ohne Microsoft Excel zu öffnen?

Ja. Python-Bibliotheken wie Free Spire.XLS können Excel-Arbeitsmappen laden und Formeln extrahieren, ohne Microsoft Excel zu starten.

Verwandte Excel-Tutorials

• So gruppieren Sie Zeilen in Excel
• So löschen oder entfernen Sie Filter in Excel
• So löschen Sie Zellen in Excel
• So markieren Sie leere Zellen in Excel