Dropdowns und Datenüberprüfung: Eingabefehler verhindern

Eine Auswertung ist nur so gut wie die Daten, auf denen sie beruht. Wer eine Liste mit mehreren Personen pflegt, kennt die üblichen Abweichungen: „Büromaterial“, „Buromaterial“ und „Büromaterial “ mit Leerzeichen am Ende stehen nebeneinander, ein Betrag wurde als Text erfasst, ein Datum liegt im falschen Jahr. Jede dieser Kleinigkeiten fällt beim Eintippen nicht auf, verfälscht aber später Summen, Filter und Pivot-Tabellen.

Die Datenüberprüfung (Data Validation) setzt früher an. Sie legt fest, was in eine Zelle eingegeben werden darf, und weist alles andere ab oder warnt zumindest. Die bekannteste Form ist die Dropdownliste. Dieser Artikel zeigt an einem Beispiel, welche Regeln sich lohnen, wie Sie sie einrichten und wo die Datenüberprüfung Lücken hat. Die beschriebenen Schritte gelten laut Microsoft für Excel für Microsoft 365 sowie Excel 2016 bis 2024. Die Datenüberprüfung gibt es auch in Excel für Mac und Excel für das Web.

Das Beispiel: ein Vereinskassenbuch mit acht Belegen

Beispieldatei: Kassenbuch eines fiktiven Sportvereins. Drei Ehrenamtliche tragen Belege ein, ohne feste Vorgaben. Nach einem Quartal sieht ein Ausschnitt so aus:

Die tatsächlichen Ausgaben betragen 206,15 €. Die Formel =SUMME(C2:C9) liefert aber nur 193,65 €, denn SUMME (SUM) übergeht den als Text gespeicherten Betrag von 12,50 €. Noch deutlicher wird es bei der Auswertung nach Kategorie:

=SUMMEWENN(B2:B9;"Büromaterial";C2:C9)

SUMMEWENN (SUMIF) ergibt 23,80 €. Richtig wären 93,40 €, nämlich 23,80 + 41,20 + 15,90 + 12,50. Zwei Belege fallen wegen der abweichenden Schreibweise heraus, ein dritter, weil sein Betrag Text ist. Es fehlen 69,60 €, ohne dass Excel irgendeine Warnung zeigt. Genau diese drei Fehlerarten lassen sich mit Datenüberprüfung bei der Eingabe verhindern.

Schritt 1: Dropdownliste für die Kategorie

Die zulässigen Kategorien gehören an eine zentrale Stelle, nicht verstreut in die Regeln. Microsoft empfiehlt, die Einträge auf einem eigenen Blatt als Excel-Tabelle anzulegen.

1. Legen Sie ein Blatt Listen an und tragen Sie in A1 die Überschrift Kategorie ein, darunter Büromaterial, Porto, Reisekosten, Bewirtung.
2. Klicken Sie in die Liste und drücken Sie Strg+T, um sie in eine Tabelle umzuwandeln. Sortieren Sie die Einträge so, wie sie im Dropdown erscheinen sollen.
3. Markieren Sie im Kassenbuch die Zellen der Spalte Kategorie, in die künftig eingegeben wird.
4. Wählen Sie Daten > Datentools > Datenüberprüfung.
5. Auf der Registerkarte Einstellungen wählen Sie unter Zulassen die Option Liste.
6. Klicken Sie in das Feld Quelle und markieren Sie auf dem Blatt Listen die vier Einträge, ohne die Überschrift.
7. Lassen Sie Zellendropdown aktiviert, sonst erscheint kein Auswahlpfeil.
8. Bestätigen Sie mit OK.

Der Vorteil der Tabelle: Laut Microsoft werden Dropdownlisten, die auf einer Tabelle beruhen, automatisch aktualisiert, wenn Sie Einträge hinzufügen oder entfernen. Kommt die Kategorie „Mitgliedsbeiträge“ dazu, tragen Sie sie einfach unter der letzten Zeile der Tabelle ein.

Bei sehr kurzen, stabilen Listen können Sie die Werte auch direkt in das Feld Quelle schreiben, etwa Ja;Nein. Welches Trennzeichen gilt, hängt vom Listentrennzeichen der Regionseinstellungen ab; die Microsoft-Hilfe nennt je nach Seite Semikolon oder Komma. Bei deutschen Einstellungen ist es in der Regel das Semikolon. Für alles, was sich ändern kann, ist die Tabelle nach unserer Einschätzung die bessere Wahl, weil Sie dann nicht jede Regel einzeln anpassen müssen.

Schritt 2: Regeln für Betrag, Datum und Belegnummer

Ein Dropdown löst nur das Problem der Schreibweise. Für die anderen Spalten bietet das Feld Zulassen weitere Typen: Ganze Zahl, Dezimal, Liste, Datum, Zeit, Textlänge und Benutzerdefiniert. Für das Kassenbuch eignen sich folgende Regeln:

Die Regel „Dezimal“ für den Betrag fängt den Fehler aus Beleg B-008 ab: Eine Eingabe, die Excel als Text speichert, ist keine Zahl und wird abgewiesen. Die Obergrenze von 5.000 € ist eine Annahme für diesen Verein. Sie dient weniger als Verbot denn als Schutz vor grob vertippten Beträgen wie 64000 statt 640,00. Kleinere Vertipper, etwa 2850 statt 28,50, liegen innerhalb der Grenze und fallen nicht auf. Eine Zelle kann nur eine Regel haben; wer solche Fälle auffangen will, muss also zwischen einer niedrigeren Grenze und der Möglichkeit abwägen, seltene hohe Beträge einzutragen.

Die Grenzen müssen nicht fest eingetragen sein. Microsoft zeigt Beispiele, in denen die Grenzwerte aus Formeln oder Zellen kommen, etwa =HEUTE() als Anfangsdatum. Steht das Geschäftsjahr in einer Zelle auf dem Blatt Listen, ändern Sie im nächsten Jahr nur diese Zelle statt aller Regeln.

Doppelte Belegnummern mit ZÄHLENWENN sperren

Die Formel für die Belegnummer stammt aus der Microsoft-Dokumentation. ZÄHLENWENN (COUNTIF) zählt, wie oft der Wert der aktuellen Zelle im gesamten Bereich vorkommt. Ergibt das mehr als 1, ist die Nummer doppelt, und die Eingabe wird abgewiesen. Wichtig ist die Mischung aus festem und beweglichem Bezug: $A$2:$A$500 bleibt bei jeder Zelle gleich, A2 passt sich an. Markieren Sie deshalb den ganzen Bereich ab A2, bevor Sie die Regel anlegen, oder legen Sie sie für A2 an und übertragen Sie sie anschließend. Wie Sie bereits vorhandene Duplikate finden, erklärt der Artikel Duplikate finden und entfernen: vier Methoden im Vergleich.

Schritt 3: Eingabemeldung und Fehlermeldung

Eine Regel ohne Erklärung frustriert. Die Datenüberprüfung hat dafür zwei weitere Registerkarten.

Die Eingabemeldung erscheint, sobald jemand die Zelle auswählt, und erklärt vorab, was erwartet wird, etwa „Betrag in Euro ohne Währungszeichen, z. B. 28,50“. Titel und Text dürfen laut Microsoft zusammen bis zu 225 Zeichen lang sein. Kurze, konkrete Hinweise wirken nach unserer Einschätzung besser als lange Erklärungen.

Die Fehlermeldung erscheint erst nach einer ungültigen Eingabe. Hier wählen Sie unter Typ zwischen drei Stufen:

Nur „Stopp“ verhindert ungültige Einträge tatsächlich. Bei „Warnung“ und „Information“ genügt ein Klick, um die Regel zu übergehen. Für Felder, die später in Formeln ausgewertet werden, ist „Stopp“ deshalb die richtige Wahl. Wenn Sie keinen eigenen Text eintragen, zeigt Excel laut Microsoft eine allgemeine Standardmeldung mit dem Titel „Microsoft Excel“, die nicht erklärt, was erwartet wird. Formulieren Sie die Meldung lieber selbst, etwa: „Bitte eine Kategorie aus der Liste wählen. Neue Kategorien trägt die Kassenwartin auf dem Blatt Listen ein.“

Wo die Datenüberprüfung nicht greift

Die Datenüberprüfung ist eine Eingabehilfe, kein Schutz gegen jede Form falscher Daten. Microsoft nennt mehrere Situationen, in denen sie nicht wirkt. Diese sollten Sie kennen, bevor Sie sich auf die Regeln verlassen.

Kopieren und Ausfüllen umgeht die Regeln

Laut Microsoft verhindert die Datenüberprüfung nur ungültige Werte, die direkt in eine Zelle eingegeben werden. Werden Daten per Kopieren oder Ausfüllen eingefügt, erscheinen keine Meldungen. Wer also Belege aus einer anderen Datei hineinkopiert, umgeht damit alle Regeln. Abhilfe: Werte nur über Inhalte einfügen > Werte übernehmen und nach jedem größeren Import die vorhandenen Daten prüfen, wie im nächsten Abschnitt beschrieben. Microsoft nennt als weitere Möglichkeit, unter Datei > Optionen > Erweitert das Ausfüllkästchen und Drag & Drop zu deaktivieren und das Blatt anschließend zu schützen.

Bestehende Daten werden nicht geprüft

Eine neue Regel wirkt nur auf künftige Eingaben. Die acht Belege aus dem Beispiel bleiben fehlerhaft, auch wenn die Regel jetzt gilt. Excel weist laut Microsoft nicht von selbst darauf hin. Markieren Sie stattdessen die Daten und wählen Sie Daten > Datenüberprüfung (Pfeil neben der Schaltfläche) > Ungültige Daten einkreisen. Excel zieht rote Kreise um alle Zellen, die gegen die Regeln verstoßen. Korrigieren Sie einen Wert, verschwindet sein Kreis automatisch. Im Beispiel würden die Kategorien von B-003 und B-005 sowie der Text-Betrag von B-008 eingekreist.

Geschützte Blätter und freigegebene Mappen

Ist der Befehl Datenüberprüfung ausgegraut, ist das Blatt geschützt oder die Mappe freigegeben. Solange das so ist, lassen sich die Regeln nicht ändern. Die sinnvolle Reihenfolge laut Microsoft: erst alle Regeln festlegen, dann die Sperre der Eingabezellen aufheben (Zellen formatieren > Schutz), dann das Blatt schützen. Wer die Zellen gesperrt lässt, schützt das Blatt so gründlich, dass niemand mehr etwas eingeben kann. Das Blatt Listen können Sie zusätzlich ausblenden und schützen, damit die Kategorien nicht versehentlich geändert werden.

Formelfehler und manuelle Berechnung

Benutzerdefinierte Regeln beruhen auf Formeln. Liefert eine solche Formel einen Fehler wie #BEZUG!, ignoriert Excel die Überprüfung, bis der Fehler behoben ist. Auch bei manueller Berechnung können nicht berechnete Zellen dazu führen, dass Eingaben falsch geprüft werden. Kontrollieren Sie unter Formeln > Berechnungsoptionen, ob Automatisch eingestellt ist.

Typische Probleme beim Einrichten

Das Dropdown zeigt keinen Pfeil. Das Kontrollkästchen Zellendropdown auf der Registerkarte Einstellungen ist deaktiviert. Die Regel gilt trotzdem, nur sieht niemand die Auswahl.

Die Überschrift erscheint als Auswahl. Beim Markieren der Quelle wurde die Kopfzeile mit erfasst. Ändern Sie den Bereich im Feld Quelle so, dass er erst in der zweiten Zeile beginnt.

Leere Zellen werden akzeptiert, obwohl die Kategorie Pflicht ist. Bei aktivem Leere Zellen ignorieren gilt eine leere Zelle als zulässig. Die Option zu deaktivieren hilft nach unserer Einschätzung nur eingeschränkt, weil die Prüfung erst bei einer Eingabe greift: Eine Zelle, die niemand anfasst, bleibt einfach leer. Fehlende Pflichtangaben finden Sie zuverlässiger mit einer Kontrollformel wie =ANZAHLLEEREZELLEN(B2:B9).

Lange Einträge sind abgeschnitten. Die Breite der Dropdownliste richtet sich nach der Breite der Zelle. Verbreitern Sie die Spalte oder wählen Sie kürzere Bezeichnungen.

Eine Regel soll für viele Zellen geändert werden. Öffnen Sie die Datenüberprüfung in einer der Zellen und aktivieren Sie Änderungen auf alle Zellen mit den gleichen Einstellungen anwenden. Dann müssen Sie nicht jeden Bereich einzeln anpassen.

Wer Daten nicht selbst eingibt, sondern aus anderen Systemen übernimmt, braucht andere Werkzeuge: Bei regelmäßigen Exporten sorgt Power Query für einheitliche Typen, siehe Power Query: Monatsdateien aus einem Ordner zusammenführen. Für saubere Ausgangsdaten einer Auswertung lohnt außerdem ein Blick in Pivot-Tabellen: Rohdaten richtig vorbereiten und auswerten.

Checkliste für eine neue Eingabetabelle

• Zulässige Listenwerte auf einem eigenen Blatt als Excel-Tabelle anlegen, Dropdown darauf verweisen.
• Für Beträge „Dezimal“ mit plausiblen Grenzen, für Datumswerte „Datum“ mit Zeitraum wählen.
• Eindeutige Nummern mit =ZÄHLENWENN($A$2:$A$500;A2)=1 absichern.
• Eingabemeldung kurz und konkret formulieren, Fehlermeldung „Stopp“ für auswertungsrelevante Felder.
• Nach dem Einrichten und nach jedem Kopier-Import „Ungültige Daten einkreisen“ ausführen.
• Zuletzt Eingabezellen entsperren und Blatt schützen.