Wie findet man nicht-gesperrte Zellen in Excel? 13

Artikelbild 135
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.

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

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

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      Hallo Harald,

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

      Schöne Grüße,
      Martin

      • Avatar-Foto
        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

      • Avatar-Foto
        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

        • Avatar-Foto
          Martin Weiß

          Hallo Thomas,

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

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Markus

    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!

    • Avatar-Foto
      Martin Weiß

      Hallo Markus,

      danke für das Feedback und freut mich sehr, wenn der Artikel weiterhelfen konnte.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    BilboBaggins

    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    helip

    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ß

    • Avatar-Foto
      Martin Weiß

      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