Das Aktualisieren eines benannten Bereichs in Excel klingt einfach, doch manchmal läuft es nicht wie geplant. Wenn Sie beispielsweise eine Datenvalidierungsliste erstellen und neue Elemente hinzufügen, diese aber nicht angezeigt werden oder die Dropdown-Liste fehlerhaft und leer ist, kann das sehr frustrierend sein. Dies ist ein typisches Symptom dafür, dass der Bereich oder die Quelldaten nicht korrekt mit der Validierungsliste synchronisiert werden. Oft liegt es daran, dass der Bereichsverweis nicht aktualisiert wurde oder Excel die Liste nicht wie erwartet automatisch erweitert. Dieser Leitfaden ist hilfreich, da er zahlreiche praktische Lösungsansätze für diese Probleme aufzeigt – sei es die Konvertierung Ihrer Daten in eine Tabelle, die Aktualisierung des Namensmanagers oder die Verwendung dynamischerer Formeln. Ziel ist es, dass sich Ihre Dropdown-Listen automatisch aktualisieren, sodass Sie diese nicht manuell korrigieren müssen, wenn sich Ihre Daten ändern.
So beheben Sie Datenvalidierungsfehler in Excel
Lösung 1 – Quelldaten in eine offizielle Tabelle konvertieren
Die Umwandlung Ihrer Liste in eine Excel-Tabelle ist mit Abstand eine der einfachsten Lösungen. Dadurch passt Excel den Datenbereich automatisch an, wenn Sie Elemente hinzufügen oder entfernen. So bleiben Ihre Dropdown-Listen ohne zusätzlichen Aufwand synchronisiert.
- Warum das hilfreich ist: Tabellen werden beim Hinzufügen neuer Daten automatisch erweitert, sodass Ihre benannten Bereiche, die auf die Tabelle verweisen, immer die neuesten Einträge enthalten.
- Wann Sie es verwenden sollten: Wenn hinzugefügte Elemente nicht in Dropdown-Listen angezeigt werden oder wenn Ihr Bereich statisch ist und keine neuen Daten enthält.
- Was Sie erwarten können: Nach der Konvertierung wird Ihr benannter Bereich, der auf die Tabelle verweist, dynamisch aktualisiert, sodass Ihre Dropdown-Listen immer aktuell bleiben.
- So geht’s:
- Wählen Sie Ihre Listenelemente aus und drücken Sie dann die Eingabetaste
Ctrl+T.Überprüfen Sie, ob der Bereich korrekt ist, und aktivieren Sie das Kontrollkästchen für Überschriften, falls Ihre Daten welche enthalten. - Benennen Sie die Tabelle im Tab „Tabellenentwurf“ beispielsweise mit „ProdList“.
- Drücken Sie diese Taste
Ctrl+F3, um den Namensmanager zu öffnen. Stellen Sie sicher, dass der Bereich auf.verweist=ProdList[ColumnName].
Immer wenn Sie nun einen neuen Eintrag in dieser Tabelle hinzufügen, erscheint er automatisch in Ihren Dropdown-Menüs. Schon erstaunlich, wie einfach das ist, oder?
Lösung 2 – Aktualisieren Sie den benannten Bereich im Namensmanager.
Manchmal weigert sich Excel einfach, die Bezüge zu aktualisieren, selbst nach der Bearbeitung. Der Bereichsbezug kann an einer alten Grenze festhängen, insbesondere wenn die Datenmenge den ursprünglich festgelegten Bereich überschritten hat.
- Warum das hilfreich ist: Durch die direkte Korrektur des Bereichs im Namensmanager wird sichergestellt, dass Ihre Validierungsquelle auf die richtigen Zellen verweist.
- Wann Sie dies ausprobieren sollten: Bei Dropdown-Menüs, die veraltete Daten anzeigen oder sich beim Hinzufügen neuer Informationen nicht erweitern.
- Was Sie erwartet: Nach der Aktualisierung sollte die Liste so angepasst werden, dass sie alle aktuellen Daten enthält. Fast wie Magie.
- So geht’s:
- Drücken Sie diese Taste
Ctrl+F3, um den Namensmanager zu öffnen. - Wählen Sie den Namen aus, den Ihre Dropdown-Menüs verwenden, und bearbeiten Sie dann sorgfältig das Feld „Bezieht sich auf“
=Sheet1!$A$1:$A$10– ändern Sie beispielsweise „von“ in „zu=Sheet1!$A$1:$A$50“, wenn weitere Daten hinzugefügt werden.
Denken Sie daran, dass Sie bei manchen Konfigurationen die Datei möglicherweise schließen und wieder öffnen oder die Validierung aktualisieren müssen, damit die Änderungen wirksam werden. Ich bin mir nicht sicher, warum es funktioniert, aber manchmal hat man das Gefühl, Excel bräuchte einen kleinen Anstoß.
Lösung 3 – Verwenden Sie eine dynamische OFFSET-Formel
Wenn Ihre Daten häufig wachsen oder schrumpfen, ist ein statischer Bereich eher hinderlich. Hier kommt die OFFSET-Formel ins Spiel – sie passt sich dynamisch an die Datenmenge in Ihrer Spalte an.
- Warum das funktioniert: Es erzeugt ein bewegliches Ziel, das sich bei Datenänderungen ausdehnt oder zusammenzieht, sodass Ihre Dropdown-Menüs immer aktuell bleiben.
- Wann man es verwendet: Wenn Ihre Liste keine offizielle Tabelle ist oder Sie mehr Kontrolle darüber haben möchten, wie sich der Bereich erweitert.
- Was Sie erwarten können: Ihre Validierungsliste enthält automatisch alle nicht leeren Zellen.
- Wie:
- Öffnen Sie den Namensmanager (
Ctrl+F3) und stellen Sie „ Bezieht sich auf“ auf Folgendes ein:
=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)Diese Funktion beginnt in Zelle A1 und zählt die ausgefüllten Zellen in Spalte A. Der Bereich wird bei Bedarf angepasst. Vorsicht: Leere Zeilen können das Ergebnis verfälschen. Bei manchen Datensätzen funktioniert die Funktion einwandfrei, bei anderen weniger.
Lösung 4 – Verwenden Sie die INDIREKT-Funktion für mehr Stabilität
Befindet sich Ihre Liste auf einem anderen Tabellenblatt oder wird das Tabellenblatt umbenannt oder gelöscht, kann die Datenvalidierung fehlschlagen. Die Funktion INDIREKT bietet eine Lösung, die die Verknüpfung aufrechterhält, unabhängig davon, was mit dem Tabellenblatt geschieht.
- Warum das nützlich ist: Es erstellt eine Referenzzeichenfolge, die auch dann gültig bleibt, wenn Sie Blätter verschieben oder umbenennen.
- Wann Sie dies ausprobieren sollten: Wenn Ihre Dropdown-Menüs leer bleiben oder Referenzfehler anzeigen, nachdem Sie Tabellenblätter umbenannt oder Registerkartennamen geändert haben.
- Erwartetes Ergebnis: Die Datenvalidierung bleibt an die Liste gekoppelt, ist aber in gemischten Situationen zuverlässiger.
- Schritte:
- Wechseln Sie zum Reiter „Daten“ und öffnen Sie die Datenvalidierung.
- Geben Sie im Feld „Quelle“ Folgendes ein:
=INDIRECT("MyListName")Ersetzen Sie den Platzhalter "MyListName"durch den tatsächlichen benannten Bereich. So ist sichergestellt, dass die Funktion auch dann funktioniert, wenn das Tabellenblatt umbenannt oder verschoben wird. Achten Sie natürlich darauf, dass der Listenname einheitlich und korrekt geschrieben ist.
Lösung 5 – Benannten Bereich in der Datenüberprüfung erneut auswählen
Das ist etwas tückisch. Manchmal scheint Excel die alte Referenz im Cache zu behalten, selbst nachdem der Bereich korrigiert wurde. Durch erneutes Auswählen des Bereichs oder erneutes Eingeben des Listennamens im Validierungsdialog kann eine Aktualisierung erzwungen werden.
- Warum das hilft: Es leert den Validierungscache und zwingt Excel zur erneuten Verknüpfung.
- Wann ist es zu tun? Wenn Dropdown-Menüs auch nach der Bearbeitung des benannten Bereichs noch leer sind oder sich nicht aktualisieren.
- Was Sie erwarten können: Wenn dies funktioniert, sollten Ihre Dropdown-Menüs mit den neuesten Daten aktualisiert werden.
- Anleitung:
- Markieren Sie alle Zellen, die Dropdown-Listen enthalten.
- Gehen Sie zu Daten > Datenvalidierung.
- Löschen Sie den Inhalt des Quellfelds und geben Sie dann den Listennamen neu ein, z. B.so
=MyListName: - Klicken Sie auf OK und prüfen Sie, ob das Problem dadurch behoben wird.
Im Grunde ist es nur ein kleiner Riss in der Rüstung, aber er kann hartnäckige Probleme beheben, die auf andere Lösungsansätze nicht ansprechen.