Formeln ohne Blattschutz vor Überschreiben schützen

Der Blattschutz ist manchmal unpraktisch: Sortieren, Filtern oder Formatieren funktionieren dann nur noch eingeschränkt. Trotzdem sollen Formeln nicht aus Versehen überschrieben werden. Mit einer Datenüberprüfung lehnt Excel Eingaben in bestimmte Zellen ab, ohne dass das Blatt geschützt ist.

Die Kurzlösung

  1. Markieren Sie die Zellen, in die etwas eingegeben werden darf, und entfernen Sie über Strg+1, Registerkarte Schutz, das Häkchen bei „Gesperrt“. Alle anderen Zellen bleiben gesperrt, das ist die Voreinstellung.
  2. Klicken Sie in die Zelle A1 und markieren Sie dann das ganze Blatt mit Strg+A (in einer Tabelle zweimal drücken) oder über das Dreieck links oberhalb von Zeile 1. A1 bleibt dabei die aktive Zelle, ihr Bezug steht im Namensfeld.
  3. Daten, Datenüberprüfung, Zulassen: „Benutzerdefiniert“, Formel:
=NICHT(ZELLE("Schutz";A1))
  1. Unter Fehlermeldung den Typ „Stopp“ beibehalten (nur er lehnt die Eingabe ab, „Warnung“ und „Information“ lassen sie zu) und einen verständlichen Text eintragen, zum Beispiel „Diese Zelle enthält eine Formel und kann nicht überschrieben werden.“

Wer jetzt etwas in eine gesperrte Zelle tippt, erhält die Meldung, die Eingabe wird nicht übernommen. In die freigegebenen Zellen lässt sich normal schreiben.

So arbeitet die Lösung

ZELLE("Schutz";A1) liefert 1, wenn die Zelle die Eigenschaft „Gesperrt“ hat, und 0, wenn nicht. NICHT kehrt das um: Für gesperrte Zellen ist die Bedingung der Datenüberprüfung nicht erfüllt, Eingaben werden abgelehnt.

Die Eigenschaft „Gesperrt“ wirkt normalerweise erst zusammen mit dem Blattschutz. Diese Lösung nutzt sie ohne Blattschutz als Markierung.

Wichtig ist der relative Bezug A1 bei aktiver Zelle A1. Excel überträgt ihn auf jede markierte Zelle, B5 prüft also sich selbst. In B2 lautet die gespeicherte Regel deshalb =NICHT(ZELLE("Schutz";B2)).

Die Grenzen

Die Datenüberprüfung prüft nur, was von Hand eingetippt wird. Sie ist ein Schutz vor Versehen, nicht vor Absicht:

  • Entf löscht den Inhalt einer gesperrten Zelle ohne Rückfrage.
  • Einfügen überschreibt die Zelle samt Datenüberprüfung, wenn etwas aus einer anderen Zelle kopiert wird.
  • Makros schreiben ohne Prüfung in jede Zelle.

Für Listen, die mehrere Personen bearbeiten, ist diese Lösung deshalb nur eine Ergänzung.

Die bessere Alternative: Blattschutz mit Optionen

Oft lässt sich der Blattschutz so einstellen, dass er nicht stört. Unter Überprüfen, Blatt schützen können Sie gezielt erlauben, was weiter möglich sein soll:

Option im Dialogerlaubt
Nicht gesperrte Zellen auswählenEingaben in die freigegebenen Zellen
Zellen formatieren, Spalten formatierenFarben, Schrift, Spaltenbreite
SortierenSortieren, aber nur in Bereichen ohne gesperrte Zellen
AutoFilter verwendenFiltern in einem vorher eingerichteten Filter

Damit sind Formeln wirksam geschützt, auch gegen Entf und Einfügen. Ein Kennwort ist dabei optional.

Typische Fehler

  • Falsche aktive Zelle: Ist beim Anlegen der Regel nicht A1 die aktive Zelle, prüft jede Zelle eine verschobene Nachbarzelle. Die Freigaben stimmen dann scheinbar zufällig nicht.
  • Alles gesperrt: Wer vergisst, die Eingabezellen zu entsperren, kann nirgends mehr etwas eingeben. Prüfen Sie mit =ZELLE("Schutz";B5), ob eine Zelle gesperrt ist (1) oder nicht (0).
  • Neue Zeilen ohne Regel: Die Datenüberprüfung gilt nur für die Zellen, die beim Anlegen markiert waren. Markieren Sie deshalb das ganze Blatt.
  • Vorhandene Datenüberprüfungen: Eine Zelle kann nur eine Datenüberprüfung haben. Für Zellen mit Auswahllisten ersetzt die neue Regel die alte. Nehmen Sie diese Zellen beim Markieren aus oder entsperren Sie sie.

Ein Wert in einer gesperrten Zelle gilt damit als ungültig, in einer entsperrten Zelle als gültig.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.