Sie öffnen eine Excel-Datei und erhalten die Meldung „Diese Arbeitsmappe enthält Verknüpfungen zu mindestens einer externen Quelle…“. Die Problemlösung, d.h. Suche der Verknüpfungen kostet oft viel Zeit und Nerven, manchmal lässt sich scheinbar gar nichts finden. Wir zeigen ihnen in diesem Beitrag wie Sie systematisch vorgehen können, um alle Verknüpfungen zu finden und ggf. zu löschen.

Externe Verknüpfungen in Excel – Fluch und Segen zugleich

Um Daten aus verschiedenen Excel-Dateien dynamisch zu verlinken und damit Doppeleingaben oder „copy&paste“-Orgien zu vermeiden, können sog. „externe Verknüpfungen“, d.h. Links zu anderen Excel-Dateien (= externe Bezüge) äußerst hilfreich sein. Erlauben diese doch bspw. Dateneingabe und Auswertung zu trennen oder wegen der automatischen Aktualisierung regelmäßig neue Daten schnell zu „importieren“.

Jedoch sind mit externen Verknüpfungen auch zahlreiche Nachteile verbunden. Der nervigste Punkt dabei, eine Arbeitsmappe mit externen Links, die Sie nicht finden können, obwohl Excel beim Öffnen meldet, dass diese vorhanden sind.

Leider gibt es in Excel selber keine automatische Möglichkeit alle externen Bezüge zu finden, die in einer Arbeitsmappe verwendet werden. Noch dazu können sich an ganz vielen unterschiedlichen Orten externe Links „verstecken“, so dass sich ein systematisches Vorgehen empfiehlt, um auch hartnäckige Kandidaten zu finden und zu beseitigen.

Hinweis: Voraussetzung, dass ihnen beim Öffnen einer Datei ggf. ein entsprechender Hinweis auf externe Verknüpfungen von Excel angezeigt wird ist, bei den Einstellungen (Datei => Optionen => Erweitert) unter „Allgemein“ den Punkt „Aktualisieren von automatischen Verknüpfungen anfordern“ anzuhaken.

Mögliche „Verstecke“ externer Links

Weil wir den fortgeschrittenen Excel-Anwender nicht langweilen wollen und viele von ihnen sicherlich einige „Verstecke“ bzw. Lösungsansätze bereits kennen, geben wir zunächst eine Übersicht. Wir erhöhen dabei den Schwierigkeitsgrad und erläutern weiter unten dann Details zu den einzelnen Punkten.

Systematisches Vorgehen:

1. Menü „Daten“ => „Verknüpfungen bearbeiten“
2. Namensmanager
3. Datenüberprüfung
4. Bedingte Formatierung
5. Diagramme (Titel u. Datenreihen)
6. Formen und Schaltflächen
7. Formularsteuerelemente / ActiveX-Steuerelemente
8. Eingebettete Objekte (Bilder, Textfelder, Formen, Multimedia-Objekte etc.)
9. Pivot-Tabellen
10. Kamera-Tool

Sie kennen noch weitere „Verstecke“ in Excel-Dateien, die wir in diesem Beitrag nicht angesprochen haben?

Dann schreiben Sie uns gerne eine Nachricht.

1. Menü „Daten“ => „Verknüpfungen bearbeiten“

Ein guter Startpunkt ist die Registerkarte „Daten“. Ist der Punkt „Verknüpfungen bearbeiten“ nicht ausgegraut, finden Sie dort eine Übersicht mit allen verknüpften Quellen. Selektieren Sie einen der Listeneinträge, wird unten bei Ziel der Pfad der verknüpften Datei angezeigt.

Leider wissen Sie aber immer noch nicht, wo genau die Verknüpfung in ihrer Arbeitsmappe verwendet wird. Da aber an den verknüpften Stellen i.d.R. der komplette Pfadname verlinkt ist, können Sie bspw. über die Suche (Start => Suchen und Auswählen => Suchen … oder einfach [Strg] + [F]) die Stellen ausfindig machen, in dem Sie bspw. einfach einen Teil des Pfades vorgeben (z.B. „D:\“, wenn ihre Verknüpfte Datei auf dem Laufwerk D liegt oder eindeutige Teile aus dem Dateinamen). Vergessen Sie nicht vor Start der Suche bei Option „Arbeitsmappe“ auszuwählen, damit die komplette Datei und nicht nur ein Blatt durchsucht wird.

Anschließend können Sie alle Einträge der Reihe nach „anspringen“, dort die gewünschten Änderungen durchführen (z.B. Links ersetzen oder beseitigen) bis schließlich die Schaltfläche „Verknüpfungen bearbeiten“ in der Registerkarte „Daten“ ausgegraut ist.

Einschränkungen bei versteckten Blättern (veryhidden)

Unter Umständen sehen Sie unter „Verknüpfungen bearbeiten“ externe Links, die sich aber auf ausgeblendeten bzw. versteckten Blättern befinden.

In Excel können Arbeitsblätter drei Optionen für die Eigenschaft Visible haben. Sichtbare Blätter repräsentieren die Standardsichtbarkeit für Blätter. Diese sind dann in der Tab-Leiste sichtbar und können frei ausgewählt und angezeigt werden. Ausgeblendete Blätter werden aus der Blatt-Registerkartenleiste ausgeblendet und können daher nicht direkt ausgewählt werden. Sie lassen sich jedoch aus dem Excel-Fenster wieder einblenden, indem Sie mit der rechten Maustaste auf die Blattregister klicken und „Einblenden…“ auswählen.

Komplett verborgene Tabellen (xlSheetVeryHidden) sind dagegen nur über den Visual Basic-Editor zugänglich (siehe Screenshot oben). Dort lässt sich aber nur dann die Visible-Eigenschaft (z.B. auf wieder sichtbar) ändern, wenn die Arbeitsmappe bzw. das VBA-Projekt nicht geschützt wurde. Ansonsten kommen Sie (mit legalen Mitteln) nicht an das Blatt bzw. die externe Verknüpfung heran. Ihnen bleibt dann nur die Möglichkeit, diese unter „Verknüpfungen bearbeiten“ komplett zu löschen.

2. Namensmanager

Immer wieder gerne finden sich externe Verknüpfungen im sog. Namensmanager. Diesen erreicht man schnell bspw. mit der Tastenkombination [Strg] + [F3]. Gehen Sie die Einträge in der Spalte „Bezieht sich auf“ durch, dann sehen Sie schnell externe Verweise.

Diese können Sie dann entweder innerhalb ihrer Arbeitsmappe neu verknüpfen (über den Eintrag „Bearbeiten …“) oder, falls nicht benötigt, entfernen (Menupunkt „Löschen“).

Profi-Tipp:  Benannte Bereiche bzw. Zellen versteckt?

Manche Entwickler möchten bestimmte benannte Bereiche bzw. Zellen vor neugierigen Nutzeraugen verstecken. Das verstößt zwar gegen das von Fimovi propagierte Transparenz-Prinzip von Excel-Vorlagen, ist aber dennoch möglich.

Diese kleine „Gemeinheit“ klappt entweder über ein entsprechendes Makro (hier nicht weiter vertieft) oder über den VBA-Editor ([Alt] + [F11]). Blenden Sie unter Ansicht das „Direktfenster“ ein und geben dann in dieses bspw. ein:

activeworkbook.Names("Versteck_mich").Visible = False

und bestätigen mit „Return“.

Der Beispielname „Versteck_mich“ (oder ein beliebiger anderer) muss natürlich vorher definiert worden sein. Gehen Sie anschließend in den Namens-Manager und Sie sehen nichts. Der Name ist nicht mehr sichtbar, existiert aber noch im Hintergrund, d.h. Sie können diesen auch in Formeln etc. verwenden (er wird aber auch nicht mehr vorgeschlagen, wenn Sie die Anfangsbuchstaben eintippen).

Es kann also durchaus sein, dass auch externe Verknüpfungen in derart versteckten Namen vorkommen, allerdings bekommen Sie nur dann einen „Verknüpfungen bearbeiten“ Hinweis, wenn diese dann auch in irgendwelchen Formeln verwendet werden. Grundsätzlich können Sie aber das im folgenden Abschnitt beschriebene kleine VBA-Makro nutzen, um alle auf diese Weise versteckten Namen wieder einzublenden. Einzelne Namen einblenden kann man auch über den oben erläuterten Weg über das Direktfenster, wenn man anstatt „False“ hinten auf „True“ ändert. Allerdings müssen Sie dann den Namen den Sie einblenden wollen kennen, was bei fremden Arbeitsmappen eher nicht der Fall sein dürfte.

Experten Makro:

VBA-Code um alle versteckten Namen wieder im Namens-Manager sichtbar zu machen:

      Sub Alle_Namen_zeigen()
      Dim n As Object
      For Each n In ActiveWorkbook.Names
      n.Visible = True
      Next n
      End Sub

Sofern Sie nicht wissen, wie man Makros in Excel einfügt und nutzt, bemühen Sie bitte eine Suchmaschine.

3. Datenüberprüfung

Sollten nach diesen beiden ersten Analysen immer noch externe Verknüpfungen beim Start gemeldet werden, empfiehlt sich als nächstes eine Analyse der Datenüberprüfung (Daten => Datenüberprüfung => Datenüberprüfung). Hier kann es grundsätzlich zwei mögliche „Verstecke“ geben. Zum einen bei der Option „Liste“, zum anderen im Rahmen einer individuellen Gültigkeitsprüfung „Benutzerdefiniert“.

Bei beiden Varianten ist entweder ein Zellenbereich oder eine Formel hinterlegt, die eigentlich nur Verweise innerhalb der gleichen Arbeitsmappe zulassen (sofern neu erstellt). Die externen Verlinkungen fängt man sich hier meistens über copy&paste-Aktionen aus anderen Dateien ein (Wer war nicht schon mal zu faul, eine häufig genutzte Gültigkeitsprüfung einfach aus einer vorhandenen Datei zu kopieren?). Dabei werden dann aber ggf. benannte Zellen oder Bereiche aus der anderen (alten) Datei übernommen, was später mit dem Hinweis auf eine externe Verknüpfung quittiert wird.

Wie lassen sich nun derartige externe Verknüpfungen in Datenüberprüfungen aufspüren? Tatsächlich kann dies mühselig werden, sofern sehr viele Datenüberprüfungen in einer Datei verwendet werden, da wir uns alle einzeln ansehen müssen. Jedoch hilft eine spezielle Suchoption diese zu finden.

Bei der Schaltfläche „Suchen und Auswählen“ kann einfach der Punkt „Datenüberprüfung“ ausgewählt werden. Alternativ kann mit dem Tastenbefehl „F5“ das „Gehe zu“ Menu aufgerufen werden (ebenfalls ein Unterpunkt bei „Suchen und Auswählen“), dann klicken Sie auf „Inhalte …“ und wählen dort „Datenüberprüfung“.

In beiden Fällen werden sämtliche Zellen markiert, die grundsätzlich eine Datenüberprüfung enthalten (leider nur auf dem aktuellen Arbeitsblatt, d.h. Sie müssen die Prozedur ggf. mehrfach für alle Blätter durchführen). Jetzt können Sie die Datenüberprüfungen der einzelnen Zellen auf externe Verknüpfungen prüfen und diese ggf. beseitigen.

4. Bedingte Formatierung

Der nächste Kandidat für die Suche nach externen Verknüpfungen sollten bedingte Formatierungen sein. Hier lassen sich u.a. Formeln zur Ermittlung der zu formatierenden Zellen verwenden. Wie bei den Datenüberprüfungen ausgeführt, können auch hier durch copy&paste-Aktionen Bereiche oder Namen aus anderen (externen) Dateien mit kopiert werden.

Sie kommen auch hier um eine manuelle Kontrolle (aller) bedingten Formatierungen nicht umher, können aber erneut schnell die Zellen finden, die überhaupt diese Funktionalität nutzen. Diesmal bei der Schaltfläche „Suchen und Auswählen“ den Punkt „Bedingte Formatierung“ auswählen. Alternativ über „F5“, Klick auf „Inhalte …“ und dort „Bedingte Formate“ wählen.

5. Diagramme (Titel und Datenreihen)

Werden Diagramme, gleich welcher Art, oder ein Arbeitsblatt auf dem sich ein solches befindet in eine neue Arbeitsmappe kopiert, bleiben die Bezüge zur ursprünglichen Quelle (= Quelldatenbereich) bestehen. Es entsteht somit eine (neue) externe Verknüpfung.

Will man in einer bestehenden Datei auf eine solche externe Verlinkung prüfen, geht man also die einzelnen Diagramme durch, selektiert das jeweilige Diagramm und gelangt über Maus rechts „Daten auswählen…“ in das Fenster „Datenquelle auswählen“.

Zeigt dort nicht schon oben der Diagrammdatenbereich eine externe Verknüpfung (vgl. Screenshot oben), selektiert man nacheinander die einzelnen Datenreihen und klickt auf die „Bearbeiten“-Schaltfläche. Bei Reihenname oder Reihenwerte sollten dann keine externen Verweise auftauchen bzw. Sie ändern dort z.B. auf Datenbereiche aus der geöffneten Arbeitsmappe.

6. Formen und Schaltflächen

Bei Formen bzw. Schaltflächen gibt es grundsätzlich zwei mögliche „Verstecke“ für externe Verknüpfungen. Zum einen können diese entweder mit einem Hyperlink versehen sein, um z.B. an eine bestimmte Stelle zu springen. Zum anderen kann man eine Verknüpfung mit einem Makro/VBA-Code erstellen, so dass bei einem Klick eine vordefinierte Funktion ausführt wird.

Ein Rechtsklick auf die jeweilige Schaltfläche (siehe Screenshot oben) zeigt eine eventuelle vorliegende externe Verknüpfung an. Entweder über die Option „Link bearbeiten“ oder den Punkt „Makro zuweisen…“. Es können sogar beide Punkte gleichzeitig (verschiedene) externe Links enthalten.

7. Formularsteuerelemente / ActiveX-Steuerelemente

Auch bei den Steuerelementen, die man über die Entwicklertools einfügen kann, ist häufig Makro- bzw. VBA-Code mit den jeweiligen Elementen verknüpft. Wenn sich das zugehörige VBA-Modul in einer anderen Excel-Datei befindet, besteht auch hier wieder eine externe Verknüpfung.

Genau wie bei Formen und Schaltflächen hilft ein Rechtsklick auf das Steuerelement weiter. Bei normalen Formularsteuerelementen wählt man „Makro zuweisen…“ um die Verknüpfung zu prüfen, bei ActiveX-Steuerelementen klickt man auf die Option „Code anzeigen“.

8. Eingebettete Objekte

Beispielsweise: Bilder, Textfelder, Formen, Multimedia-Objekte etc.

Werden externe Dateien als Objekte in eine Excel-Mappe eingebettet und statt der vollständigen Datei lediglich ein Symbol und eine Verknüpfung auf die Originaldatei verwendet, so spart dies Speicherplatz, resultiert aber auch wieder in einer externen Verknüpfung.

Selektiert man ein auf diese Weise eingefügtes Objekt, wird oben in der Bearbeitungszeile der Pfad zur Quelldatei angezeigt und Sie sehen schnell, wenn dieser auf eine externe Datei verweist.

Für Objekte gelten darüber hinaus auch die für Steuerelemente erläuterten Möglichkeiten. D.h. man kann per Rechtsklick entweder Links erstellen oder Verknüpfungen mit Makros. Bei Kopie eines derartigen Objektes in eine andere Arbeitsmappe, verweisen die Verknüpfungen dann häufig auf die (externe) Ursprungsdatei.

9. Pivot-Tabellen

Liegen externe Verknüpfungen in Pivot-Tabellen vor, so kann es durchaus vorkommen, dass man diese zunächst nicht bemerkt, da diese (sofern nicht über Power Query verbunden) nicht zu einem Eintrag in der Übersicht „Verknüpfungen bearbeiten“ führen. Aus diesem Grund erscheint dann auch kein entsprechender Hinweis auf externe Verknüpfungen, wenn die Arbeitsmappe geöffnet wird.

Der „Überraschungseffekt“ kommt erst, sobald die Pivot-Tabelle aktualisiert werden soll. Liegt eine externe Verknüpfung vor, erscheint regelmäßig ein Hinweis auf die fehlende Datenquelle. In diesem Fall kann man sich in den PivotTable-Tools im Register „Analysieren“ über die Schaltfläche „Datenquelle ändern“ den Pfad und Dateinamen der ursprünglichen Datenquelle anzeigen lassen und ihn dort auch ändern. Handelt es sich um eine externe Datei auf die man keinen Zugriff hat, bleibt nur auf die Aktualisierung zu verzichten oder besser, die Pivot-Tabelle ganz aus der eigenen Datei zu löschen.

10. Kamera-Tool

Ein äußerst mächtiges und hilfreiches, dennoch bei vielen unbekanntes Werkzeug ist das sog. „Kamera-Tool“. Das ist allerdings nicht verwunderlich, denn diese Funktionalität wird nicht standardmäßig in der Symbolleiste angezeigt.

Wer jedoch oft vor der Herausforderung steht, anspruchsvolle Formatierungen von Diagrammen und Tabellen z.B. in umfangreichen Dashboards umzusetzen, findet im Excel-Kamera-Tool einen leistungsstarken Helfer.

Wir können an dieser Stelle nicht auf die genaue Verwendung und Nutzung eingehen (Suchmaschine nutzen !), aber grundsätzlich geht es darum, bestimmte Zellen/Bereiche oder auch Diagramme „abzufotografieren“ und an einem beliebigen Ort in der Arbeitsmappe wieder einzufügen.

Dabei wird aber nicht nur eine „einfache“ Grafik kopiert (wie bei einem Screenshot), sondern die Grafik passt sich automatisch bei jeder Änderung der ursprünglichen Zellen, Listen, Diagramme an. Es handelt sich sozusagen um einen dynamischen Screenshot.

Der große Vorteil der verknüpften Grafik liegt in einer viel flexibleren Handhabung als bei der Verwendung normaler Zellverknüpfungen. Auf der Habenseite stehen bspw.: Freie Positionierung, beliebige Größenänderung sowie ansprechende Formatierungen.

Zurück zum Thema externe Verknüpfungen: Wird nun ein solches Bild oder ein Arbeitsblatt, das ein solches Bild enthält, in eine andere Arbeitsmappe kopiert oder verschoben, entsteht automatisch eine externe Verknüpfung auf die Ursprungsdatei.

Normalerweise sehen Sie den externen Link bei Klick auf die Grafik direkt in der Formelzeile. Außerdem sollte diese Art von externen Verknüpfungen auch unter „Daten“ => „Verknüpfungen bearbeiten“ auftauchen (siehe Punkt 1 weiter oben).

Produktivitätstipp

Externe Tools bzw. Excel Add-Ins können helfen

Wer den Artikel bis hier gelesen hat oder bereits einige leidvolle Erfahrungen mit externen Verknüpfungen in umfangreichen Arbeitsmappen gesammelt hat weiß, dass es durchaus sehr zeit- und arbeitsintensiv sein kann alle möglichen „Verstecke“ zu prüfen.

Es gibt aber ein äußerst hilfreiches Excel Add-In von Bill Manville Associates namens Findlink (http://www.manville.org.uk/software/findlink.htm , aktuell, d.h. im Januar 2024, in v13.2 als Freeware kostenlos erhältlich).

Wie der Name des Tools bereits andeutet, ist das nur in Englischer Sprache erhältliche Add-In in der Lage, alle möglichen externen Links automatisiert aufzuspüren und scheitert systembedingt, d.h. aufgrund von Einschränkungen des VBA Objekt Modells, nur bei:

• Beschriftungen von Sekundärachsen in Diagrammen
• Quellen für benutzerdefinierte Fehlerbalken in Diagrammen
• Durch Spezialfilter ausgeblendete Zeilen

In diesen (eher seltenen) Fällen ist also doch noch ein wenig Handarbeit erforderlich.