Wer seine Excel-Tabellen Kollegen oder anderen Anwendern zur Bearbeitung zur Verfügung stellt, verwendet sicherlich gerne den Blattschutz in Kombination mit dem Zellschutz.
Damit wird verhindert, dass versehentlich Formeln überschrieben werden oder Eingaben an unerwünschten Stellen erfolgen.
Muss die Tabelle später jedoch angepasst werden, kommen hinterher Fragen auf: Wie erkennt man, welche Zellen geschützt sind und welche nicht? Habe ich den Schutz auch bei keiner wichtigen Zelle vergessen?
In diesem Artikel zeige ich dir, wie du auch in großen Tabellen Klarheit zu diesen Fragen erlangst.
Nehmen wir also an, wir haben uns eine schöne Zeiterfassungstabelle erstellt, in der die Mitarbeiter ihre Arbeitszeiten eintragen sollen:
Beispiel: Zeiterfassungstabelle
Um die Tabelle möglichst flexibel zu nutzen und die Eingaben zu erleichtern, sind einige Formeln hinterlegt, die z.B. Datum, Kalenderwoche und die jeweilige Arbeitszeit berechnen. Damit die Anwender diese Formeln nicht überschreiben, haben wir die frei zugänglichen Zellen entsperrt und dann den Blattschutz aktiviert:
Zellschutz entfernen
Blattschutz aktivieren
Doch wie es der Teufel will, hat ein besonders findiger Kollege doch eine Formel aus einer Zelle gelöscht, die wir beim Zellschutz offensichtlich übersehen haben. Daher wollen wir etwaige weitere Schlupflöcher identifizieren und stopfen.
Möglichkeit 1: Die Such-Funktion
Um sich einen schnellen Überblick zu verschaffen, welche Zellen keinen Schutz haben, reicht die normale Suchfunktion in Excel aus. Hier erweitern wir das Suchfenster zunächst über die Schaltfläche „Optionen“:
Suchfenster aufrufen
Die gewünschte Funktion verbirgt sich dann hinter der Schaltfläche „Format…“:
Suchen nach Formaten
Im Register „Schutz“ entfernt man nun das Attribut „Gesperrt“, um nach ungeschützten Zellen zu suchen (standardmäßig haben alle Zellen das Attribut „Gesperrt“):
Gesperrt-Haken entfernen
Dann schließen wir das Format-Fenster mit OK und starten die Suche über die Schaltfläche „Alle suchen“. Damit erhalte ich eine Liste aller Zellen, die nicht gesperrt sind:
Suche starten
Suchergebnisse
Wie man sieht, enthält diese Liste auch die Zelleninhalte, so dass man etwaige Formeln sofort erkennt. Durch Anklicken der Zeile wird die betroffene Zelle ausgewählt und ich kann bei weiterhin geöffnetem Suchfenster für diese Zelle per Rechtsklick das „Zelle formatieren“-Fenster aufrufen und dort den Zellschutz aktivieren:
Korrektur „on-the-fly“
Anschließend kehrt man in das Suchergebnisfenster zurück und kann sich so Zeile für Zeile durch die Liste arbeiten und bei Bedarf aktiv werden.
Möglichkeit 2: Bedingte Formatierung
Über eine bedingte Formatierung ist es ebenfalls ein Kinderspiel, sämtliche nicht gesperrte Zellen zu finden und z.B. farbig zu hinterlegen. Dazu markieren wir den betreffenden Bereich (oder notfalls auch die komplette Tabelle) und rufen die bedingte Formatierung auf:
Bedingte Formatierung aufrufen
Nun legen wir eine neue Formatierungsregel an, die folgende Formel enthält:
=NICHT(ZELLE("Schutz";A1))
Formatierungsregel anlegen
Wichtig: Sollte bei dir eine andere Zelle als A1 die gerade aktive Zelle sein, muss diese Zelladresse in der Formel verwendet werden!
Mit Hilfe der ZELLE-Funktion lassen sich alle möglichen Informationen über eine Zelle abfragen, eine davon ist eben der Zellschutz. Zurückgegeben wird der logische Wert WAHR, wenn die Zelle gesperrt ist oder FALSCH, wenn sie nicht gesperrt ist. Da wir nur die nicht gesperrten hervorheben wollen, kehren wir das Ergebnis mit der NICHT-Funktion um.
Und schon werden sämtliche ungeschützte Zellen eingefärbt:
Ungeschützte Zellen werden markiert
Zum Schluß noch ein Hinweis:
Vor allem, wenn man diese Formatierungsregel auf das gesamte Arbeitsblatt angewendet hat empfiehlt es sich sehr, die Regel hinterher wieder zu entfernen. Grund: Jede in einer bedingten Formatierung eingesetzte Funktion wird automatisch zu einer sogenannten volatilen Funktion. Insbesondere bei großen Tabellen verbraucht das unnötige Rechenleistung und kann Excel unter Umständen merklich ausbremsen.
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.
Hallo Martin,
Ich beistze eine Exceltabelle, an der mehrere arbeiten.
In ihr befinden sich Einträge (Formeln, bedingte Formatierungen, Gültigkeitsregeln etc. etc.)
Einige Spalten sind gänzlich ausgeblendet, einige sichtbare Zellen sind gesperrt.
Nun mein Problem: Ich möchte bestimmte Zelleinträge nachträglich (nach Ablauf einiger Wochen) automatisch „einfrieren“ – also für Mitarbneiter nicht manipulierbar machen. Gibt es für diesen Fall die Möglichkeit ein „Zeitschloß“ einzubauen?
(Zeitgrenze wird ermittelt zwischen datum heute() und dem eingetragenen Erstelldatum in der jeweiligen Zeile).
Danke im Voraus
Uwe K.
Hallo Uwe,
so eine Möglichkeit ist mit der Standardfunktionalität nicht umsetzbar. Hier musst Du auf VBA zurückgreifen.
Schöne Grüße,
Martin
Hallo Martin,
ich bin auf der Suche nach einer einfachen Anzeige, ob eine Zelle gesperrt ist oder nicht – wenn ich in der Zelle bin.
Wenn ich jedesmal über den Befehl „Zelle formatieren“ gehen muss, dann ist mir das zu anstrengend.
Gibt es eine einfache Möglichkeit, sich den „Status“ der Zelle anzeigen zu lassen?
So ähnlich wie den „Fett“ Button, der anders erscheint, wenn der Text in der Zelle fett formatiert ist. Ich würde mir das gern als Button in die Symbolleiste legen können (gilt auch für „Ausblenden“.
Danke
Harald
Hallo Harald,
da muss ich Dich enttäuschen, eine solche Statusanzeige gibt es in Excel leider nicht.
Schöne Grüße,
Martin
Hallo Martin,
danke Dir für die Antwort. Mal sehen, ob ich so einen Button selber erstellen kann. Aber das würde mir bei meiner Arbeit sehr helfen.
Harald
Hallo Martin, hallo Harald,
einen kleinen Trick gibt es schon. Im Menüband START gibt es im Menüpunkt ZELLEN den Unterpunkt FORMAT, und dort den Befehl ZELLE SPERREN. Diesen Befehl kann man sich in die Schnellzugriffsleiste integrieren (rechter Mausklick auf den Befehl ZELLE SPERREN, „Zu Symbolleiste für den Schnellzugriff hinzufügen“ auswählen). Anhand dieses Symbols kann man nun sofort erkennen, ob eine Zelle gesperrt ist oder nicht.
Thomas Föller
Hallo Thomas,
sehr cooler Trick, da wäre ich auch nicht drauf gekommen. Vielen Dank dafür!
Schöne Grüße,
Martin
Sehr guter Hinweis zur bedingten Formatierung: bevor man mit einem Klick alle Zellen markiert, ist es wichtig sich zu merken, welche Zelle als letztes den Fokus hatte. Diese Zelle dann in der Formel eintragen (in unserem Beispiel war also die Zelle A1 zuletzt markiert und dann wurde der gesamte Bereich ausgewählt, oder auch das gesamte Tabellenblatt, im Formeltext steht daher A1).
Habe diesen Hinweis nicht aufmerksam gelesen und bin deshalb prompt auf die Nase gefallen (insbesondere weil bei mir auch noch die Zelle A1 durch ein Firmenlogo verdeckt war, und mir das dann gar nicht aufgefallen ist, wo der Fokus stand).
Danke Martin für dieses hervorragende Tutorial!
Hallo Markus,
danke für das Feedback und freut mich sehr, wenn der Artikel weiterhelfen konnte.
Schöne Grüße,
Martin
Hallo!
Habe leider keinen Button zum „Upvoten“ gefunden, daher hier fix ein Feedback-Kommentar, auch wenn der Artikel schon ein bissch älter ist 😉
Vielen Dank für den Tipp mit der Formatierungsregel! Genau das, was ich gesucht habe.
Grüße!
Hallo Bilbo,
danke für dein Feedback, freut mich zu hören. Und viele Excel-Tipps sind ja auch zeitlos 🙂
Schöne Grüße,
Martin
Hallo Martin,
wenn ein Tabellenblatt geschützt ist und ich darin eine geschützte Zelle zu ändern versuche kommt von Excel ein entsprechenden Hinweis, dass das nicht möglich ist. Spätestens nach dem zweiten Aufploppen nervt das gewaltig. Wie kann ich diesen Hinweis ausschalten? In den Excel-Optionen habe ich dazu nichts gefunden, oder habe da was übersehen? Gibt es mit VBA eine Lösung?
Gruß
Hallo helip,
nein, dafür gibt es keine Option, die Meldung kommt immer. Ich wüsste auch nicht, wie sich das per VBA abstellen lässt.
Schöne Grüße,
Martin