Wie hebt man in Excel alle Zellen hervor, auf die sich eine Formel bezieht?
Beim Arbeiten mit komplexen Arbeitsmappen in Excel verweisen Formeln oft auf zahlreiche Zellen über mehrere Arbeitsblätter hinweg. Die visuelle Identifizierung aller Zellbezüge, die eine bestimmte Formel nutzt, ist entscheidend – sei es zur Prüfung, Fehlersuche oder einfach, um den Datenfluss in Ihrer Arbeitsmappe besser zu verstehen. Dieser Artikel zeigt Ihnen praktische Methoden, wie Sie alle von einer Formel referenzierten Zellen in Excel effizient hervorheben können:
Alle von einer Formel referenzierten Zellen mithilfe einer Tastenkombination finden
Alle von einer Formel referenzierten Zellen mithilfe von VBA-Code hervorheben
Alle von einer Formel referenzierten Zellen mithilfe einer Tastenkombination finden
Möchten Sie schnell prüfen, welche Zellen in einer Formel referenziert werden, bietet Excel eine integrierte Tastenkombination, um diese Zellen sofort auszuwählen – ideal für spontane Prüfungen oder für Anwender, die auf codebasierte Methoden verzichten möchten.
Angenommen, Ihre Formel steht in Zelle E1, und Sie möchten alle von ihr referenzierten Zellen hervorheben. Klicken Sie zunächst auf die Formelzelle (E1), um sie auszuwählen. Halten Sie bei ausgewählter Zelle E1 gleichzeitig die Tasten Strg+[ (öffnende eckige Klammer) gedrückt. Mit diesem Tastaturbefehl werden sofort alle in der Formel von E1 verwendeten Zellbezüge markiert – besonders praktisch bei Formeln mit mehreren, verstreuten Eingaben!
Nachdem Sie die referenzierten Zellen ausgewählt haben, können Sie diese manuell mit einer Füllfarbe hervorheben: Wechseln Sie zur Registerkarte Start im Menüband, klicken Sie auf das Werkzeug Füllfarbe (Symbol des Farbeimers) und wählen Sie Ihre bevorzugte Farbe aus. So werden alle Quellzellen, die in Ihre Formel eingehen, visuell hervorgehoben.
Zusätzlich zu den Tastenkombinationen können Sie auch die Funktion „Vorgänger verfolgen“ unter der Registerkarte Formeln nutzen. Wählen Sie einfach eine Formelzelle aus und klicken Sie auf Vorgänger verfolgen – schon werden Pfeile eingeblendet, die die Beziehungen zwischen der Formel und ihren referenzierten Zellen visuell darstellen. So erhalten Sie im Handumdrehen eine klare Übersicht!
Dieses Verfahren eignet sich besonders gut für Situationen, in denen Sie eine schnelle visuelle Prüfung benötigen oder Formeln während gemeinsamer Arbeitsbesprechungen überprüfen möchten. Beachten Sie jedoch, dass diese Tastenkombination nur Bezüge innerhalb des aktuellen Arbeitsblatts markiert – falls Ihre Formel Zellen auf anderen Blättern oder benannte Bereiche referenziert, greift sie nicht. Zudem hebt diese Methode ausschließlich direkte Vorgänger hervor und berücksichtigt weder indirekte Bezüge noch Bereiche, die durch dynamische Formeln definiert sind.
Falls die Tastenkombination nicht zu funktionieren scheint, stellen Sie sicher, dass Ihre Excel-Arbeitsmappe nicht geschützt ist und dass die ausgewählte Zelle tatsächlich eine Formel enthält – erkennbar am vorangestellten Gleichheitszeichen. Da Tastaturlayouts die Funktionalität von Tastenkombinationen beeinflussen können, prüfen Sie bei unerwartetem Verhalten auch Ihre Systemeinstellungen.
Alle von einer Formel referenzierten Zellen mithilfe von VBA-Code hervorheben
Für mehr Flexibilität oder beim Umgang mit komplexeren Formeln – insbesondere solchen, die mehrere Blätter referenzieren oder indirekte Bezüge enthalten – bietet eine VBA-Lösung die Möglichkeit, alle referenzierten Zellen automatisch hervorzuheben.
Gehen Sie zur Anwendung dieser Methode wie folgt vor:
1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im Fenster Microsoft Visual Basic for Applications auf Einfügen > Modul. Dadurch wird ein neues Modul erstellt, in das Sie den Code eingeben können. Kopieren Sie anschließend den folgenden VBA-Code und fügen Sie ihn in das Modulfenster ein:
VBA-Code: Alle von einer Formel referenzierten Zellen in Excel hervorheben
Sub HighlightCellsReferenced()
Dim rowCnt As Integer
Dim i As Integer, j As Integer, strleng As Integer
Dim strTxt As String, strFml As String
Dim columnStr, cellsAddress As String
Dim xRg As Range, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox(Prompt:="Please select formula cell(s)...", _
Title:="Kutools For Excel", Type:=8)
strTxt = ""
Application.ScreenUpdating = False
For Each yRg In xRg
If yRg.Value <> "" Then
strFml = yRg.Formula + " "
strFml = Replace(strFml, "(", " ")
strFml = Replace(strFml, ")", " ")
strFml = Replace(strFml, "-", " ")
strFml = Replace(strFml, "+", " ")
strFml = Replace(strFml, "*", " ")
strFml = Replace(strFml, "/", " ")
strFml = Replace(strFml, "=", " ")
strFml = Replace(strFml, ",", " ")
strFml = Replace(strFml, ":", " ")
For j = 1 To Len(strFml)
If Mid(strFml, j, 1) <> " " Then
cellsAddress = cellsAddress + Mid(strFml, j, 1)
Else
On Error Resume Next
Range(cellsAddress).Interior.ColorIndex = 3
cellsAddress = ""
End If
Next
End If
Next yRg
Application.ScreenUpdating = True
End Sub3. Drücken Sie die Taste F5oder klicken Sie auf die Schaltfläche „Ausführen“ ()
), um den VBA-Code auszuführen. Wählen Sie im erscheinenden Dialogfenster mit dem Titel Kutools für Excel die Formelzelle oder -zellen aus, für die Sie alle referenzierten Zellen hervorheben möchten, und klicken Sie anschließend auf OK.
Die referenzierten Zellen werden nun rot hervorgehoben, sodass Sie auf einen Blick alle Eingabedaten erkennen, die in Ihre ausgewählten Formeln einfließen – wie unten dargestellt.
Dieser VBA-Ansatz eignet sich ideal, wenn Ihre Formeln mehrere Arbeitsblätter umfassen oder Sie den Hervorhebungsprozess automatisieren möchten. Gehen Sie jedoch mit Bedacht vor: Der Code analysiert Adressen direkt aus dem Formeltext und erkennt daher möglicherweise nicht alle komplexen Bezüge – beispielsweise strukturierte Tabellen, benannte Bereiche oder bestimmte Array-Funktionen.
Stellen Sie vor dem Ausführen des VBA-Codes sicher, dass Makros in Ihren Excel-Einstellungen aktiviert sind, und erstellen Sie stets eine Sicherungskopie Ihrer Arbeitsmappe, um unbeabsichtigte Formatierungsänderungen zu vermeiden. Sollten Sie die Hervorhebung später entfernen wollen, können Sie die Füllfarbe einfach manuell aus den betroffenen Zellen löschen.
Falls das Makro nicht wie erwartet hervorhebt, vergewissern Sie sich, dass sich die referenzierten Zellen auf dem aktiven Arbeitsblatt befinden, und prüfen Sie die Syntax Ihrer Formel. Die Leistung des VBA-Codes kann je nach Excel-Version leicht variieren. Sollten Laufzeitfehler auftreten, stellen Sie sicher, dass der ausgewählte Formelbereich keine zusammengeführten Zellen enthält, da diese die Hervorhebung beeinträchtigen können.
Verwandte Artikel:
- Wie behält man in Excel einen Zellbezug in einer Formel konstant bei?
- Wie verweist man in Excel auf einen Tab-Namen, der in einer Zelle steht?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins