Zellen auf Formeln überprüfen 4

Ein verborgenes Juwel ermöglicht die ISTFORMEL-Funktion auch in Excel-Versionen vor 2013.
 

In den Excel-Quickies im Dezember hatte ich unter anderem gezeigt, wie man mit Hilfe der bedingten Formatierung und der ISTFORMEL-Funktion Zellen hervorheben kann, die eine Formel enthalten.

Leider hatte diese Lösung einen Haken: Die ISTFORMEL-Funktion gibt es erst seit Excel 2013. Alle Anwender vorheriger Versionen mussten leider mit dem Ofenrohr ins Gebirge schauen.

Solltest auch du eine Excel-Version 2010 oder älter einsetzen, dann zeige ich dir im heutigen Tipp, wie man das auch ohne ISTFORMEL-Funktion elegant hinbekommt.


Dieser sehr clevere Trick stammt jedoch nicht von mir. Ich bin auf der Website von Frank Arendt-Theilen darüber gestolpert, Ruhm und Ehre gebührt also zu 100 % ihm!

Dabei wird auf eine Makro-Funktion aus längst vergangenen Excel-Tagen zurückgegriffen. Aber keine Angst, hier muss niemand Makros oder VBA-Code programmieren, um diesen Trick einzusetzen.

Die Funktion ZELLE.ZUORDNEN

Mit dieser Funktion konnte (und kann) man eine Unmenge an Eigenschaften einer Zelle abfragen.

=ZELLE.ZUORDNEN(Typ; Bezug)

Dabei legt “Typ” fest, welche Eigenschaft gefragt ist und “Bezug”, welche Zelle einen dabei interessiert. Der Typ “48” prüft z.B., ob in der angegebenen Zelle eine Formel steht.

Diese mächtige Funktion kann jedoch nicht direkt in eine Zelle eingegeben werden. Stattdessen definiert man über den Namensmanager einen neuen Namen und gibt statt eines Zellbezugs eben die Formel ein. Da wir im zweiten Schritt über die bedingte Formatierung jedoch keine feste Zelle, sondern die jeweils gerade aktive Zelle ansprechen wollen, bedienen wir uns eines kleinen Tricks:

=ZELLE.ZUORDNEN(48; INDIREKT("ZS";FALSCH))

Neuen Namen definieren

Neuen Namen definieren

Zur Erklärung:
In der INDIREKT-Funktion geben wir die Zelladresse in der Z1S1-Schreibweise an. In dieser Schreibweise werden nicht Koordinaten verwendet, sondern Zeilen- und Spaltennummern. So wird aus der Zelle A1 eben Z1S1 und aus Zelle C15 wird Z15S3. Und die oben verwendete Angabe “ZS” verweist damit auf sich selbst.

Die bedingte Formatierung

Mit dem so erstellten Namen können wir nun eine neue Formatierungsregel anlegen und auf alle Zellen anwenden, die wir auf eine Formel hin untersuchen wollen:

Formatierungsregel anlegen

Formatierungsregel anlegen

Und schon werden alle Zellen farblich hervorgehoben, die eine Formel enthalten:

Die hervorgehobenen Zellen

Die hervorgehobenen Zellen

Einen kleinen Haken…

…gibt es jedoch. Wenn du deine Arbeitsmappe nämlich im aktuellen xlsx-Dateiformat speichern willst, wird dich Excel mit folgender Meldung konfrontieren:

Warnung vor dem falschen Dateiformat

Warnung vor dem falschen Dateiformat

Auch wenn wir keine richtigen Makros geschrieben haben, verwenden wir mit ZELLE.ZUORDNEN eben doch eine Makro-Funktion. Daher bleiben dir beim Speichern nur zwei Möglichkeiten:

  1. das neue xlsm-Format, welches auch Makro-Code enthalten darf
  2. das alte xls-Format mit den entsprechenden Einschränkungen

Eine Kröte musst du also schlucken aber ich hoffe, sie rutscht nicht allzu schwer hinunter.

Wer unabhängig von dem oben gezeigten Tipp mit der ZELLE.ZUORDNEN-Funktion experimentieren möchte, findet auf der Seite von Frank Arendt-Theilen noch ein paar Tipps sowie eine Übersicht aller Eigenschafts-Typen, die mit der Funktion verwendet werden können.
 
Zum Schluss noch ein kleiner Hinweis in eigener Sache:
Soeben ist mein neues E-Book “SVERWEIS & Co. Verweisfunktionen in Excel” erschienen.

E-Book SVERWEIS & Co.

Solltest du dich also für die Excel-Funktionen SVERWEIS, WVERWEIS, INDEX und dergleichen interessieren, möchte ich dir mein neues Werk wärmstens ans Herz legen. Hier erfährst du mehr dazu…

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

4 Gedanken zu “Zellen auf Formeln überprüfen

  • Michael

    Die Kombination mit INDIREKT ist wirklich eine feine Sache!
    Beim Speichern gibt es noch eine dritte Möglichkeit:
    3) das Binärformat .xlsb mit der Einschränkung, dass die Kompatibilät zu alternativen Office-Produkten verloren geht.
    Das Binär-Format reduziert außerdem die Dateigröße, bei großen Mengen von Daten um bis zu 50%.
    Viele Grüße aus Berlin
    Michael

    • Martin Weiß Beitragsautor

      Hallo Michael,

      danke für diesen guten Hinweis. Die Ersparnis bei der Dateigröße kann ich nur bestätigen!

      Schöne Grüße,
      Martin