Ist dir das auch schon mal passiert: Beim Öffnen einer Excel-Datei kommt der Hinweis „Diese Arbeitsmappe enthält Verknüpfungen zu mindestens einer externen Quelle…“
Und das, obwohl in der Datei eigentlich überhaupt keine Verknüpfungen enthalten sein sollten.
Wenn du diese Situation kennst, dann weißt du wahrscheinlich auch, dass man daran fast verzweifeln kann. Wie wird man dieses Problem also los? Oder anders gefragt: Wo genau befinden sich diese kleinen Teufel eigentlich?
Eine nicht immer ganz so einfach zu lösende Aufgabe. Dieser Artikel liefert ein paar Antworten dazu.
Aller Anfang ist leicht
Ausgangspunkt der Suche ist immer die Registerkarte „Daten“. Wenn die aktuelle Arbeitsmappe externe Verknüpfungen enthält, ist hier die Schaltfläche „Verknüpfungen bearbeiten“ aktiviert.
Verknüpfungen bearbeiten
Erst wenn es keine Verknüpfungen mehr gibt, wird dieser Punkt ausgegraut dargestellt und man ist sozusagen „sauber“. Ein Klick auf die Schaltfläche öffnet ein Fenster, in dem sämtliche Verknüpfungsquellen aufgelistet sind. Markiert man einen Eintrag in der Liste, so wird unten der Speicherort angezeigt. Leider ist in vielen Fällen der Pfad so lang, dass man nur einen Teil davon erkennen kann:
Der Pfad zur verknüpften Datei
Nun weiß man zwar schon, auf welche Datei verwiesen wird und dass diese in meinem Beispiel irgendwo unter „D:\“ gespeichert ist. Wo genau die Verknüpfung in der Arbeitsmappe verwendet wird, darüber lässt einen Excel jedoch völlig im Unklaren.
Wie findet man nun alle Formeln, die diese Verknüpfung enthalten?
Dazu muss man Folgendes wissen:
Wenn die verknüpfte Datei nicht geöffnet ist (wovon wir hier ausgehen können), dann enthalten alle Formeln mit einer Verknüpfung den vollständigen Pfad zu der betreffenden Datei:
Externer Bezug direkt in einer Formel
Mit diesem Wissen und der Information aus dem Fenster „Verknüpfungen bearbeiten“ können wir die ganz normale Suchfunktion nutzen!
Rufe also das Suchfenster auf (Menüband Start | Suchen und Auswählen bzw. STRG+F) und schreibe in das Eingabefeld „D:\“. Klicke dann auf die Schaltfläche „Optionen“, um vor der Suche weitere Einstellungen vorzunehmen:
Das Suchen-Fenster
Wähle jetzt im Feld „Durchsuchen“ die Option „Arbeitsmappe“, damit die komplette Datei durchsucht wird und klicke dann auf die Schaltfläche „Alle suchen“. Je nach Umfang und Komplexität deiner Datei wirst du nach einigen Momenten eine Liste aller Zellen bekommen, die eine externe Verknüpfung enthalten:
Suche in der gesamten Arbeitsmappe
Achtung!
Es werde nur sichtbare Arbeitsblätter durchsucht, Verknüpfungen in ausgeblendeten Blättern werden nicht angezeigt! (vielen Dank an den Leser Uwe für diesen Hinweis.
Nun kannst du jeden Eintrag der Reihe nach anklicken und Excel wird die entsprechende Zelle direkt anspringen. Dort kannst du dann die notwendigen Änderungen vornehmen, wie z.B. die Verknüpfung durch den Wert ersetzen.
Wenn du damit durch bist, solltest du im Register „Daten“ überprüfen, ob die Schaltfläche „Verknüpfungen bearbeiten“ jetzt ausgegraut ist:
Es gibt keine externen Verknüpfungen mehr
Denn erst dann gibt es keine externen Verknüpfungen mehr. Sollte das nicht der Fall sein, geht die Suche in die nächste Phase…
Versteck 2: Der Namensmanager
In Excel lassen sich bekanntermaßen für Zellen oder Zellbereich eigene Namen definieren, über die diese Bereiche dann in Formeln angesprochen werden können.
Nicht ganz so bekannt ist die Möglichkeit, im Namensmanager statt einer Zelladresse eine Formel in das Bezugsfeld einzugeben und diese dann mit einem Namen zu versehen. Damit lassen sich allerhand praktische Dinge anstellen. Beispiele dafür findest du hier oder hier.
Und genau hier können sich auch externe Verknüpfungen verstecken.
Also einfach mal den Namensmanager aufrufen (Menüband Formeln | Namensmanager) und prüfen, ob einer der gelisteten Namen einen externen Bezug enthält, so wie im folgenden Bild:
Externe Bezüge im Namensmanager
Dann kannst du die notwendigen Maßnahmen ergreifen und den Bezug anpassen oder ggf. den Namen komplett löschen, wenn er nicht mehr benötigt wird.
Versteck 3: Die Datenüberprüfung
Ein weiterer Kandidat für versteckte externe Verknüpfungen ist die Datenüberprüfung (Menüband Daten | Datenüberprüfung). Hier gibt es grundsätzlich zwei mögliche „Verstecke“: „Liste“ und „Benutzerdefiniert“
Datenüberprüfung: Liste und Benutzerdefiniert
Bei beiden Optionen lässt sich ein Zellenbereich oder gar eine eigene Formel hinterlegen. Als fortgeschrittener Excel-Anwender wirst du jetzt einwenden „Ja, aber hier kann ich doch nur Bezüge innerhalb der aktuellen Arbeitsmappe setzen, nicht jedoch auf eine andere Datei!“
Das stimmt. Der Versuch, hier bei der Eingabe in eine andere Arbeitsmappe zu klicken, wird von Excel mit einem Fehler quittiert.
Aber stelle dir folgendes nicht so unwahrscheinliche Szenario vor:
Du richtest in der Datei 1 auf dem Blatt „Tabelle1“ eine Datenüberprüfung ein. Als Quelle dient eine Liste, die sich in Blatt „Tabelle2“ befindet
Zellbezug oder Formel als Referenz
Soweit alles noch im grünen Bereich.
Zu irgendeinem späteren Zeitpunkt arbeitest du an einer völlig neuen Arbeitsmappe und kommst du auf die verwegene Idee, das Blatt „Tabelle1“ aus der ersten Datei in diese Arbeitsmappe zu kopieren oder zu verschieben. Damit wird auch die Regel für die Datenüberprüfung übernommen, die nun allerdings auf einen externen Bereich verweist (und obendrein fehlerhaft ist):
Externer Bezug nach einer Kopie
Da steckt also der kleine Teufel und könnte jetzt beseitigt werden.
Doch wie findet man alle Zellen, die eine Datenüberprüfung enthalten?
Es geht leider nicht ganz so komfortabel, wie bei der oben gezeigten Suchfunktion. Ist aber trotzdem nicht so dramatisch. Hinter der Schaltfläche „Suchen und Auswählen“ verbirgt sich unter anderem die Option „Datenüberprüfung“:
Zellen mit Datenüberprüfung finden
Damit werden auf dem aktuellen Arbeitsblatt sämtliche Zellen markiert, die eine Datenüberprüfung enthalten. Nun muss man „nur“ noch nachsehen, ob sich dahinter eine externe Verknüpfung befindet. Kommen mehrere Arbeitsblätter zum Einsatz, muss die Suche auf jedem Blatt wiederholt werden.
Versteck 4: Bedingte Formatierung
Sollte die Schaltfläche „Verknüpfungen bearbeiten“ immer noch aktiv sein, bleibt ein weiteres potentielles Versteck für externe Verknüpfungen: Die bedingten Formatierungen.
Auch hier gibt es ja die Möglichkeit, Formeln als Formatierungsregeln zu hinterlegen, die wiederum Zellbezüge enthalten können:
Eigene Formel in bedingter Formatierung
Wie schon oben bei der Datenüberprüfung beschrieben sind darin nur Bezüge innerhalb der eigenen Arbeitsmappe zulässig. Aber wenn das Arbeitsblatt mit der bedingten Formatierung eben später in eine andere Arbeitsmappe kopiert oder verschoben wird, dann hat man wieder den Salat:
Externer Bezug in der Formatierungsregel
Ein externer Bezug, der obendrein auch noch ungültig ist.
Nachdem auch dieses Problem ausgemerzt wurde, sollten keine externen Verknüpfungen mehr vorhanden sein. Oder kennst du noch ein anderes Versteck? Wenn ja, dann lass es uns unten in den Kommentaren wissen.
Update im Juni 2019:
Aufgrund der vielen Tipps und Anfragen von Lesern gibt es hier noch einen zweiten Artikel zum Thema externe Verknüpfungen:
Fantastische Verknüpfungen und wo man sie in Excel findet
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,
danke für die zwei extrem hilfreichen Artikel, habe viel gelernt. Im File eines Kollegen konnte ich aber mit keiner der beschriebenen Methoden die Verknüpfung finden. Nachdem ich aber jetzt in einer letzten Verzweiflungstat doch noch die Lösung gefunden habe, hier der Hinweis auch für alle anderen:
Der Kollege hatte ein Tabellenblatt mit mehreren als Tabellen formatierten Bereichen aus einer Vorlage in mehrere gleichartige Files eingefügt. Plötzlich hatte jedes dieser Files eine nicht mehr nachvollziehbare Verknüpfung.
Das Problem: In der ursprünglichen Quelle hat er eine Formel mit Bezug auf ein externes File verwendet. Anschließend hat er den Bezug auf das externe File mittels „suchen und ersetzen“ rausgenommen, um auf das gleichnamige Tabellenblatt im File selbst zu verweisen. Daher hatte keine Formel mehr einen Bezug zu einem externen File. Es wurde aber weiterhin eine Verknüpfung unter „Verknüpfungen bearbeiten“ angezeigt.
Die Lösung: Durch das suchen und ersetzen werden die Formeln in allen Zellen der Tabelle geändert, dabei aktualisiert sich aber nicht die im Hintergrund abgespeicherte „Spaltenformel“ der Tabelle – ergo: Wenn eine neue Zeile zur Tabelle hinzugefügt wird, hat diese wieder die ursprüngliche Formel mit dem Verweis auf die externe Quelle. Das reicht für Excel, um die Verknüpfung anzuzeigen und interessanterweise versucht er sogar beim Start diese zu aktualisieren.
Vielleicht hilft das ja mal jemand anderen.
Danke an alle hier und lg, Markus
Hallo Markus,
es gibt offensichtlich nichts, was es nicht gibt 🙂 An die im Hintergrund mitlaufenden Spaltenformeln bei formatierten Tabellen muss man wirklich erst kommen.
Vielen Dank für diese wirklich hilfreiche Ergänzung!
Schöne Grüße,
Martin
Guten Tag
und wie kann man dieses Problem dann beheben ?
VG
Steffen
Hallo Steffen,
üblicherweise reicht es, wenn man in der formatierten Tabelle in einer der Formelzellen nochmal mit F2 in den Bearbeitungsmodus wechselt und dann die Formel (die ja schon keinen sichtbaren externen Bezug mehr enthält) einfach nochmal mit der Eingabetaste bestätigt. In der rechten unteren Ecke der Zelle sollte dann ein kleines Symbol auftauchen, über das man gefragt wird, ob man die Formal in alle Zellen übernehmen soll. Bestätigt man das, sollte der Spuk eigentlich vorbei sein, denn dadurch wird auch die Formel in der Hintergrundautomatik aktualisiert.
Schöne Grüße,
Martin
Hallo Martin,
der Tipp mit dem Namensmanager war Gold wert. Vielen Dank!
Gruß Thorsten
Hallo Thorsten,
freut mich zu hören, dass der Artikel geholfen hat!
Schöne Grüße,
Martin
Ich habe grad festgestellt, dass bei Punkt 1 eine wichtige Bedingung fehlt.
Die Suche in „Arbeitsmappe“ funktioniert nur in eingeblendeten Blättern. Treffer in ausgeblendeten Blättern werden nicht gefunden.
Hat mich grad ein paar graue Haare gekostet.
Hallo Uwe,
vielen Dank für den Hinweis, das habe ich jetzt oben im Artikel ergänzt.
Schöne Grüße,
Martin