Wie findet man nicht-gesperrte Zellen in Excel? 7

Zwei Möglichkeiten, um in Excel Zellen zu erkennen, die keinen Zellschutz haben.
 

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

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

Zellschutz entfernen

Blattschutz aktivieren

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

Suchfenster aufrufen

Die gewünschte Funktion verbirgt sich dann hinter der Schaltfläche „Format…“:

Suchen nach Formaten

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

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

Suche starten

Suchergebnisse

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"

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

Bedingte Formatierung aufrufen

Nun legen wir eine neue Formatierungsregel an, die folgende Formel enthält:
=NICHT(ZELLE("Schutz";A1))

Formatierungsregel anlegen

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

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.

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.



Schreibe einen Kommentar

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

7 Gedanken zu “Wie findet man nicht-gesperrte Zellen in Excel?

  • Uwe Käpernick

    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.

    • Martin Weiß Autor des Beitrags

      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

  • Harald

    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

    • Martin Weiß Autor des Beitrags

      Hallo Harald,

      da muss ich Dich enttäuschen, eine solche Statusanzeige gibt es in Excel leider nicht.

      Schöne Grüße,
      Martin

      • Harald

        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

      • Thomas Föller

        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

        • Martin Weiß Autor des Beitrags

          Hallo Thomas,

          sehr cooler Trick, da wäre ich auch nicht drauf gekommen. Vielen Dank dafür!

          Schöne Grüße,
          Martin