Vielleicht möchtest Du auf Deinem Arbeitsblatt manchmal sehen, welche Zellen Formeln enthalten. Zwei Möglichkeiten dazu möchte ich Dir anbieten.
1. Über die Suchfunktion in Excel
Deine Liste könnte so aussehen:
In B13 steht die Formel
=B12*2
In B17 steht die Formel
=B13*3
Und in B19 steht die Formel
=B15*2
Eine Möglichkeit ist, über das Menü Start / Bearbeiten „Suchen und Auswählen“ aufzurufen und dann „Formeln“ auszuwählen.
Die Zellen mit einer Formel werden jetzt in Deiner Liste markiert.
Der Nachteil dieser Methode ist, dass die Markierung wieder verschwindet, sobald Du im Arbeitsblatt weiterarbeitest.
2. Über eine bedingte Formatierung
Eine weitere Möglichkeit, Formelzellen hervorzuheben bietet die bedingte Formatierung.
Definiere zuvor im Menü Formeln / Definierte Namen / Namen definieren den Namen „Formelzelle“.
Unter „Bezieht sich auf“ schreibst Du die Formel
=ZELLE.ZUORDNEN(48;INDIREKT(„ZS”;FALSCH))
ZELLE.ZUORDNEN ist eine sogenannte Excel4-Makrofunktion.
Das erste Argument (Typ) ist die Zahl 48. Die Zahl 48 gibt WAHR zurück, wenn die Zelle eine Formel enthält, ansonsten FALSCH.
Für das zweite Argument (Bezug) wird die Funktion INDIREKT() benutzt. Mit ihr wird ein Zellbezug auf die Zelle selbst erstellt.
ZS ist die absolute Adresse der Zelle auf sich selbst in der Z1S1-Schreibweise. Es ist die einzige Adressangabe bei der eine Zelle auf sich selbst adressiert werden kann. Das Argument FALSCH in der Funktion INDIREKT() gibt an, das es sich ausdrücklich um die Z1S1-Schreibweise handelt. [1]
Markiere den Bereich B12:B22. Rufe nun über das Menü Start / Formatvorlagen / Bedingte Formatierung „Neue Regel“ auf und wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
Gib in das Formelfeld die Formel
=Formelzelle
ein. Suche unter Formatieren z.B. Ausfüllen aus und wähle eine grüne Farbe.
Nachdem Du auf OK geklickt hast, werden die Formelzellen grün markiert sein. Die Farbe wird erst verschwinden, wenn die entsprechenden Zellen keine Formeln mehr enthalten.
Du kannst somit im Arbeitsblatt weiterarbeiten und behältst die Formelzellen im Auge.
Quellen:
Entdecke mehr von Clevercalcul
Subscribe to get the latest posts sent to your email.
Seit Excel2013 geht die Variante mit bedingter Formatierung dank neuer Funktion ISTFORMEL() komfortabler. Die Definition des Namens „Formelzelle“ kann entfallen und bei der bedingten Formatierung schreibt man einfach:
=ISTFORMEL(B12)
Das geht natürlich auch.
Ich wollte aber meine Variante zeigen.
Aber ich danke für den Tipp.
Ich denke, ich sollte meinen Beitrag entsprechend ergänzen.
Hallo,
das ist eine tolle Funktion, bei mir besteht leider auch das Problem, dass nach dem Schließen und erneutem Öffnen die Formelzellen nicht formatiert sind, obwohl die bedingte Formatierung und die Formel existieren. Mit welchem Dateiformat muss das Excel gepeichert werden – .xlsm?
LG
Margarita
Hallo Margarita,
danke für Dein Interesse am Beitrag. Ja, die Datei muss mit .xlsm gespeichert werden. Es handelt sich um ein Makro, wenn auch ein Excel4-Makro.
Ich hoffe, es funktioniert dann in Deiner Datei.
LG
Gerhard
Ist bei mir auch so. Geht solange, bis die Datei neu geöffnet wird. Dann ist die Formel für die Formatierung noch da und die Formatierung wird nicht mehr ausgeführt.
Hallo, ich habe meine Tabelle noch einmal geprüft. Es funktioniert alles wie beschrieben, auch nach erneutem Öffnen der Datei.
Ich hatte übrigens vor einiger Zeit aufgrund eines Hinweises einen Formelfehler bereinigt.
Bei der Definition des Namens “Formelzelle” muss es bei “Bezieht sich auf” richtig heißen:
=ZELLE.ZUORDNEN(48;INDIREKT(“ZS”;FALSCH))
Viele Grüße
Gerhard
Leider funktioniert das bei mir nur bis zum Schließen der Datei. Beim erneuten Öffnen sind zwar Name und die Regel zur bedingten Formatierung noch vorhanden, aber leider werden die Zellen mit Formeln nicht wie vorher gekennzeichnet. Woran kann das liegen?
Hallo Bernie, ich habe versucht, Deinen Fehler zu analysieren, finde aber keine Ursache. Bei mir funktioniert alles wie beschrieben.
Versuche bitte folgendes:
1. Falls sich eine Formel auf eine andere Datei bezieht, muss diese Datei auch geöffnet sein.
2. Prüfe, ob vielleicht noch eine andere bedingte Formatierung auf Deinen Daten liegt, die evtl. Vorrang hat.
3. Prüfe noch einmal, ob Du wirklich alles exakt nach meinem Beitrag eingerichtet hast, es schleichen sich schnell mal Fehler ein.
Falls alles nichts hilft, kannst Du mir Deine Datei gern zusenden, ich würde sie mir ansehen. Aber nur wenn Du willst. Ich behandle alles vertraulich und werde die Datei dann wieder bei mit löschen.
E-Mail:
gerhard-pundt@freenet.de
Viele Grüße
Gerhard
Schon wieder falsch… wie kann man hier was als code oder preformatted oder fixed width und ohne typografische Ersetzungen posten?
Die Anführungszeichen um das ZS herum müssen beide oben sein.
Hallo tmomas,
danke für Deinen Hinweis. Hier hat sich ein Tippfehler eingeschlichen. Wo gearbeitet wird, passieren auch Fehler. Kennst Du das nicht?
Richtig muss es heißen:
=ZELLE.ZUORDNEN(48;INDIREKT(„ZS”;FALSCH))
Damit funktioniert es.
VG
Gerhard
So wie’s da oben steht funktionierts nicht und Excel meckert wegen falscher Formel.
Flasch: =ZELLE.ZUORDNEN(48;INDIREKT(„ZS);FALSCH))
Richtig: =ZELLE.ZUORDNEN(48;INDIREKT(„ZS“;FALSCH))