Mit manuellen Eingaben überschriebene Formeln sichtbar machen
Besonders wenn mehrere Personen mit einer Excel-Datei arbeiten, kann es schnell passieren, dass Zellen, in denen Berechnungen vorhanden waren aus Versehen mit festen Werten überschrieben werden. Das kann zu fehlerhaften Neuberechnungen führen und ist gerade bei großen Tabellen schwer wieder in den Griff zu bekommen. Davor schützt das Sperren von Zellen und das Setzen eines Blattschutzes. Aber auch wenn es bereits zu spät ist, gibt es noch Möglichkeiten, um schnell festzustellen, ob in den Zellen ein fester, eingegebener Wert oder eine Formel-Berechnung steht.
Was in einer einzelnen Zelle eingetragen ist, sieht man bekanntlich in der Bearbeitungsleiste. Hier in unserem einfachen Beispiel nehmen wir die Summenspalte in unserer Excel-Vorlage „Rechnungsformular“ mit dem Preis der Zeilenposition:
Bild 1: Summenformel in I24
Nehmen wir nun an, wir nutzen das Formular gemeinsam mit anderen und beim Erstellen der Rechnung kommt jemand auf die gut gemeinte Idee, da fehlt ja die MwSt und trägt somit händisch einen Wert inkl. MwSt. ein:
Bild 2: Ãœberschreiben der Summenformel in I24
Dies ist nicht nur für spätere Rechnungen schlecht, sondern auch noch falsch, da die Steuer ja erst später und dann auf sämtliche Positionen berechnet wird. Aber: Excel hat mehrere Funktionen parat, um Zellen dahingehend zu überprüfen, ob eine Formel in einer Zelle hinterlegt ist oder eben nicht (mehr):
Mit "Suchen und Auswählen" (Startmenü/Gruppe bearbeiten) können Sie z.B. nach Formeln in markierten Bereichen suchen und sich diese anzeigen lassen. Das Ergebnis ist eine Markierung (Auswahl) sämtlicher Zellen, welche Formeln beinhalten:
Bild 3: Excel markiert nur die Zellen mit Formeln; Zelle I24 nicht
Mit der Funktion FORMELTEXT können Formeln angezeigt werden, die sich in den angesprochenen Zellen befinden =FORMELTEXT(I24). Bei Zellen ohne Formel erscheint die Fehlermeldung #NV.
Mit der Funktion ISTFORMEL werden Wahrheitswerte angezeigt. Diese Funktion liefert als Ergebnis "WAHR", wenn die Zelle eine Berechnung enthält oder "FALSCH", wenn keine Formel enthalten ist bzw. ein fester Wert in der Zelle steht =ISTFORMEL(I24).
Bei beiden Varianten muss in einer separaten Zelle die entsprechende Funktion eingegeben werden. Um Zellen mit manuell überschriebenen Formeln jedoch sofort und direkt in der entsprechenden Zelle sichtbar zu machen, kann man sich der Bedingten Formatierung bedienen. Und zwar indem man die Funktion ISTFORMEL und deren Wahrheitswert in einer Regel kombiniert.
Hierzu gehen wir im Register Start auf Bedingte Formatierung und wählen Neue Regel ...
Bild 4: Neue Regel für eine bedingte Formatierung anlegen
Für die neue Formatierungsregel wählen wir den Regeltyp "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus und geben in das Feld für die Regelbeschreibung folgende Funktion ein:
=ISTFORMEL($I24)=FALSCH
Bild 5: Regelbeschreibung eingeben und Formatierung wählen
Die Regel überprüft alle Zellen in der Spalte I ($I) und formatiert für den Fall, dass das Ergebnis von ISTFORMEL "FALSCH" ist, sämtliche Zellen ohne Formel mit der über die Schaltfläche "Formatieren..." eingestellten Formatierung (hier gewählt: fette, rote Schrift mit gelbem Hintergrund).
Bild 6: Die Zelle I24 wird aufgrund der fehlenden Formel automatisch farbig gekennzeichnet
Es ist somit in den benachbarten Zellen keine Formel zur Berechnung notwendig und wenn in die Zelle I24 wieder eine (irgendeine!) Formel eingesetzt wird, erscheint die ursprüngliche Formatierung wieder.
Das hier angewandte Excelbeispiel Rechnungsformular finden Sie bei unseren Allg. Vorlagen.
In Kooperation mit ...
Seminartipp:
weitere Seminartipps ...
Startseite > Excel-Tipps und -Tricks > Exceltipp 168
