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
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
Und schon werden alle Zellen farblich hervorgehoben, die eine Formel enthalten:
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
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:
- das neue xlsm-Format, welches auch Makro-Code enthalten darf
- 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 hier noch ein paar Tipps sowie eine Übersicht aller Eigenschafts-Typen, die mit der Funktion verwendet werden können (leider ist der Original-Link von Frank Arendt-Theilen nicht mehr verfügbar, mein Leser Jörg Böhmichen hat aber diese Alternative gefunden, danke dafür!)
Zum Schluss noch ein kleiner Hinweis in eigener Sache:
Soeben ist mein neues E-Book „SVERWEIS & Co. Verweisfunktionen in Excel“ erschienen.
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…
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.
Einfach nur genial, genau das habe ich gesucht. Danke Martin
Liebe Grüße
Gern geschehen, Roswitha.
Schöne Grüße,
Martin
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
Hallo Michael,
danke für diesen guten Hinweis. Die Ersparnis bei der Dateigröße kann ich nur bestätigen!
Schöne Grüße,
Martin
Hallo, lieber Martin Weiß,
auf Deine Webseite http://www.tabellenexperte.de bin ich schon öfter gestoßen. Ich finde Deinen Schreibstil sehr freundlich und unaufgeregt – bestens geeignet, um anderen Lesern „die Angst vor Excel zu nehmen“ – sehr gut!
In Deinem Artikel „zellen-auf-formeln-ueberpruefen“ verweist Du auf eine Seite von Frank Arendt-Theilen, wo er sich mit der Funktion „Zelle.Zuordnen“ beschäftigt und die verschiedenen Typen (Funktionen) erklärt, die mit dieser Funktion möglich sind. Leider ist diese Seite gesperrt.
Ich habe aber eine andere Seite, eine Microsoft-Seite gefunden, die diesen Artikel enthält:
https://answers.microsoft.com/de-de/msoffice/forum/msoffice_excel-mso_other-mso_2013_release/die-excel4-makrofunktion-zellezuordnen/6ee8af02-b52c-45b7-94ef-7f7bb7e45d88
Vielleicht ändern Sie den Link in Ihrer Webseite auf diese Adresse?
Freundliche Grüße aus Dresden
Jörg Böhmichen
Hallo Jörg,
vielen Dank für das Lob und natürlich für den Hinweis mit dem defekten Link. Ich habe ihn jetzt oben im Artikel geändert.
Schöne Grüße,
Martin
Vielen Dank für den hilfreichen Artikel! Mit diesem Trick sollte es dann ja sicherlich auch möglich sein – anstelle einer bedingten Formatierung – mit der ZÄHLENWENN-Funktion alle Zellen mit Verweisen zusammenzuzählen.
Viele Grüße aus Dresden!
P.S.: Kleiner Tipp: die Original-Quelle ist nach wie vor noch über die Wayback-Machine des Internet Archive verfügbar: https://web.archive.org/web/20180428110838/http://www.at-exceltraining.de/index.php/artikel/48-formeln-und-funktionen/203-die-funktion-zellezuordnen.html
Hallo Leser,
die gute alte Wayback-Machine, die habe ich schon ganz vergessen 🙂
Danke für den Tipp.
Schöne Grüße,
Martin
Danke für diesen Super-Tipp!!! Diese bedingte Formatierung hat mich gerettet!!
Herzliche Grüße
Nicole
Hi Nicole,
das freut mich zu hören!
Schöne Grüße,
Martin
Hallo,
eine Frage: das geht aber auch mit der Formel „ISTFORMEL()“ oder liege ich da falsch?
Also sowohl bei der bedingten Formatierung und anderen Anwendungen.
Vielen Dank für die Website, sie hat mir an anderen Stellen schon mal weiter geholfen.
Best Grüße
Moritz
Hallo Moritz,
ja, das geht natürlich auch mit ISTFOMREL. Der Artikel sollte lediglich eine Lösung für Anwender älterer Excel-Versionen (Excel 2010, Excel 2007) aufzeigen, da die Funktion erst mit Excel 2013 eingeführt wurde.
Schöne Grüße,
Martin
nach einem Neustart funktioniert das formatieren mit „Formel“ nicht mehr 🙁
Hallo Nils,
was genau geht denn nicht und welche konkrete Formel nutzt du in der bedingten Formatierung, die nicht funktioniert?
Schöne Grüße,
Martin
Moin,
ich hatte gestern mit den Namensmanager und =ZELLE.ZUORDNEN(48; INDIREKT(„ZS“;FALSCH)) sowie anschliessendem bedingten Formatieren von Feldern eine Formelpruefung mit Formel experimentiert und dann als xlsm gespeichert.
Heute morgen sind alle Felder so, wie forher formatiert sind und nicht so, wie es bei „formel=ja“ sein sollten.
gruß KC
koennten Sie sich bitte noch einmal melden, ich war so froh, dass es alles funktioniert und am naechsten Tage nicht mehr, das muss doch irgendeinen kompensierbaren Grund haben ? 🙂