Dos and Don`ts beim Verknüpfen von Excel Berichten mit PowerPoint: Teil 1 Dos

Publiziert von

In dieser 2-teiligen Blogserie stellen wir die wichtigsten Dos and Don`ts zur Verknüpfung von Excel Berichten mit PowerPoint vor. Im ersten Beitrag wird die Standardlösung vorgestellt, die im Kontext eines regelmäßigen Reportings und dem damit verbundenen Bedarf einer Aktualisierung auf Knopfdruck zu empfehlen ist. Dazu werden die PowerPoint-spezifischen Automatisierungsfunktionen in den beiden Excel Add-Ins Zebra BI und chart-me XLS mit dieser Standardlösung abgeglichen. Im zweiten Beitrag werden wir die verlockenden aber für dieses Szenario nicht zielführenden Don`ts im Detail analysieren.

Ganz besonderen Dank möchte ich an Markus Wolff von unserer Partnerfirma chartisan richten, er hat uns das wirklich ausgeklügelte Know-How aus dem IBCS®-Umfeld und seine eigene umfangreiche Praxiserfahrung verständlich gemacht und uns das hier verwendete Musterdashboard RiverBoat 5 aus seinem Visualisierungstraining zur Verfügung gestellt. Vielen Dank an Holger Gerths von unserer Partnerfirma Hi-Chart, der dieses ausgeklügelte Know-How maßgeblich im letzten Jahrzehnt mit aufgebaut hat und in seinem Excel AddIn chart-me XLS systematisch umgesetzt hat. Und vielen Dank an Andrej Lapajne von unserer Partnerfirma Zebra BI, der mit seinem Webinar diese schwierige Thematik sehr griffig aufbereitet hat und in seinem Excel Add-In Zebra BI ebenfalls sehr schön umgesetzt hat.

Übersicht

  1. Die dynamische Verknüpfung mit der Excel Berichtsdatei wird am besten mit dem Befehl „Einfügen -> Inhalte einfügen -> Verknüpfung einfügen -> Microsoft Excel-Arbeitsmappe-Objekt“ erreicht. Warum diese Option im Einfügen-Dialog von PowerPoint nicht leichter zugänglich platziert ist, ist zumindest für mich nicht nachvollziehbar.
  2. Ein unterschätztes vorgelagertes Thema ist die Handhabung der Pixelgröße und Auflösung des verknüpften Objektes, hier wurde im IBCS®-Umfeld extrem viel Know-How aufgebaut das wir hier kompakt wiedergeben dürfen.
  3. Excel Add-Ins wie Zebra BI und chart-me XLS bieten für die Verknüpfung fertiger Excel Berichte nach PowerPoint wichtige Unterstützung sowohl bei der Verknüpfung selbst als auch bei der Vorbereitung des (pixelgenauen) Seitenlayouts in Form von Templates.

1. Empfohlene Standardlösung

Technisch gesehen wird mit der empfohlenen Methode Einfügen -> Inhalte einfügen -> Verknüpfung einfügen -> Microsoft Excel-Arbeitsmappe-Objekt ein sogenanntes verknüpftes OLE-Objekt in PowerPoint eingefügt. Das bedeutet, der Zellbereich aus Excel wird pixelgenau als Vektorbild (im EMF-Format) eingefügt und das Bild kann auf Knopfdruck aktualisiert werden, etwa bei Änderungen in der Excel-Quelldatei. Ist die Verknüpfung nicht aktualisierbar (bspw. wegen Pfadänderungen) oder wird die Verknüpfung entfernt, dann wird das zuletzt erzeugte Vektorbild dauerhaft angezeigt.

Erstellung des verknüpften OLE-Objekts

Am besten wird in Excel ein Namensbereich für den zu verknüpfenden Zellbereich vergeben, da damit einerseits die Nachvollziehbarkeit besser gewährleistet wird und gleichzeitig eine automatische Dynamik erreicht wird, da nachträglich eingefügte / entfernte Zeilen und Spalten damit automatisch auch in den PowerPoint-Verknüpfungsbereich aktualisiert werden. Hier wurde der Namensbereich Monatsreporting definiert, nach dem Auswählen wird der zugrunde liegende Bereich markiert und dann einfach mit Strg + C in die Zwischenablage kopiert:

In PowerPoint darf jetzt nicht einfach „Strg + V“ und auch keine der Haupt-Einfügeoptionen verwendet werden, sondern der Dialog Inhalte Einfügen … aufgerufen werden:

Weiter mit Verknüpfung einfügen und Microsoft Excel-Arbeitsmappe-Objekt auswählen (bzw. Microsoft Excel-Diagramm-Objekt wenn in Excel ein Chart anstelle eines Zellbereichs kopiert wurde, siehe dazu auch etwas weiter unten):

Eingefügt wird ein verknüpftes OLE-Objekt, das ist ein auf Knopfdruck aktualisierbares Vektorbild im EMF-Format. In bestehenden PowerPoint-Dateien ist das OLE-Objekt leicht im Kontextmenü über die beiden Einträge Verknüpftes Worksheet-Objekt und Verknüpfung aktualisieren erkennbar:

Laufende Aktualisierung

Nach einer Änderung der Daten oder des Berichtslayouts in der Excel-Quelldatei kann das eingefügte OLE-Objekt in PowerPoint bequem über das Kontextmenü (siehe oben) aktualisiert werden. Weiters wird der Benutzer beim Öffnen einer PowerPoint-Datei mit einem verknüpften OLE-Objekt gefragt, ob die Verknüpfung aktualisiert werden soll (falls nicht erwünscht, kann hier auf Abbrechen geklickt werden):

Anders als bei einer „Excel-zu-Excel“-Formelverknüpfung braucht die Excel-Quelldatei nicht geöffnet zu sein, die Aktualisierung funktioniert so oder so.

Die im Zuge des oben beschriebenen Vorgangs automatisch in PowerPoint eingefügte Verknüpfung kann im Menü Datei unter Informationen -> Verknüpfungen mit Dateien bearbeiten konfiguriert werden (der Menüpunkt ist bei einer neuen PowerPoint Datei erst vorhanden, nachdem die Datei gespeichert wurde):

Hier ist zu sehen, daß PowerPoint neben dem Dateipfad auch den Namensbereich als Datenquelle speichert, erzeugt wird ein Link vom Typ „Worksheet“. Sollte die automatische Abfrage beim Öffnen der Datei zur Aktualisierung unerwünscht sein, so kann diese hier deaktiviert werden. Soll die Datenverbindung „gekappt“ werden und das Vektorbild am aktuellen Datenstand dauerhaft eingefroren werden, so kann die Verbindung über die Option Verknüpfung aufheben entfernt werden:

Abschließend noch ein Blick auf die Erzeugung eines OLE-Objektes aus einem Chart in Excel (anstelle eines Zellbereichs).  Im Inhalte einfügen -> Verknüpfung einfügen Dialog steht jetzt (ausschließlich) die Option Microsoft Excel-Diagramm-Objekt (und nicht Microsoft Excel-Arbeitsmappe-Objekt) zur Verfügung:

Interessanterweise ist die erzeugte Verknüpfung auch hier vom Typ Worksheet, mit einem Bezug auf den Diagrammnamen im Pfad (analog zum Namensbereich):

Den Verknüpfungstyp Diagramm gibt es auch, diesen lernen wir im Teil 2 dieser Blogserie beim Copy & Paste als einen von drei Don`ts kennen!

2. Pixelformat und Auflösung handhaben

Der Zellbereich „Monatsreporting“ aus Excel wurde in der oben beschriebenen Methode paßgenau in die PowerPoint Folie eingefügt. Das ist kein Zufall und wurde excel-seitig mit einem Pixelformat von 959 zu 719 vorbereitet. Die Pixelbreiten/-höhen der Spalten/Zeilen wurden hier aus Transparenzgründen in der ersten Zeile/Spalte sichtbar gemacht:

Die PowerPoint-Folie wurde im Menü Entwurf -> Foliengröße auf das Format 4:3 eingestellt:

Das entspricht einem Pixelformat von 960 x 720, das kann aus den Seitenformatangaben in cm und der Bildschirmauflösung (meistens 96 dpi) errechnet werden:

Ist das aus Excel verknüpfte Pixelformat kleiner (nicht: kleiner gleich) als 960 x 720, dann wird das OLE-Objekt mit einem Skalierungsfaktor von 100% eingefügt:

Nur bei einem Skalierungsfaktor von 100% stimmt die optische Darstellung pixelgenau mit dem Quell-Zellbereich in Excel überein. Daher sollte kein anderer Faktor verwendet werden sondern immer die Pixelgröße im Excel-Quellbereich entsprechend vorbereitet werden. Keinesfalls sollte das Seitenverhältnis des OLE-Objekts verändert werden, da es dadurch zwangsweise zu verzerrten Darstellungen kommt.

Ist der Quell-Zellbereich größer oder gleich als die Folie, wird das Objekt auf einen Faktor zwischen 1 und 100% verkleinert. Daher wurde der Zellbereich in Excel nicht mit 960 x 720 bemessen sondern um je 1 Pixel kleiner gemacht. Vielen Dank nochmals an Markus Wolff auch für diesen sehr nützlichen Praxistipp.

3. Automatisierung

3.1 Automatisierung  im Excel Add-In Zebra BI

In Zebra BI stehen 2 PowerPoint-spezfische Exportmethoden zur Verfügung. Der Befehl PowerPoint entspricht der Einfügeoption „Einfügen -> Grafik“ in PowerPoint (siehe dazu Teil 2 dieser Blogserie) und der Befehl Verknüpfte PowerPoint entspricht der in diesem Beitrag unter Punkt 1 empfohlenen Standardmethode:

Als Optionen kann einerseits mit dem Befehl Neue Präsentation starten festgelegt werden, ob der nächste Exportauftrag in eine weitere neu angelegte PowerPoint Datei exportiert wird oder in die bereits bestehende aus dem letzten Exportauftrag. Andererseits kann die Slide Größe (entweder 4:3 oder 16:9) in Abstimmung mit dem im Excel Bericht verwendeten Pixelformat definiert werden. Beim Betätigen einer der beiden PowerPoint-Exportoptionen wendet Zebra BI folgende allgemeingültige Logik an:

  • Ist eine einzelne Zelle markiert (wie im Screenshot unten), so wird der definierte Druckbereich (Namensbereich „Druckbereich“ in der deutschen bzw. „Print Area“ in der englischen Excel Version) des Sheets exportiert. Ist in dem Sheet kein Druckbereich definiert, so wird die markierte einzelne Zelle nach PPT exportiert.
  • Ist ein Zellbereich oder ein individueller Namensbereich markiert, so wird dieser nach PPT exportiert.
  • Ist ein Chart oder eine Gruppe von Small Multiples markiert, so werden diese nach PPT exportiert.

In folgenden Fall ist ein einzelne Zelle im Sheet Report markiert und ein Druckbereich ist auch definiert (Zellbereich K2:X40):

Zebra BI erzeugt automatisch eine neue PowerPoint-Datei im Format 4:3 und fügt den Excel-Quellbereich perfekt positioniert als OLE-Objekt ein, leicht zu identifizieren über das Kontextmenü des Objekts. Die Skalierung auf 100% und die Ausrichtung links oben erfolgt ebenfalls über das Add-In, daher kann auch ein Zellbereich von genau 960 x 720 Pixel aus Excel übertragen und ohne manuelle Nachbearbeitung verwendet werden:

Zur Unterstützung bei der Gestaltung des pixelgenauen Seitenlayouts steht die Option Seite hinzufügen zur Verfügung, hier stehen derzeit 9 Standardlayouts zur Auswahl:

Diese Layouts haben alle gemeinsam, daß die Spaltenbreiten des Druckbereichs 960 Pixel und die Zeilenhöhen 720 Pixel (= PowerPoint Format 4:3) ergeben:

3.2 Automatisierung im Excel Add-In chart-me XLS

In chart-me XLS – der Test wurde mit der Version 2 und nicht der neueren Version 3 durchgeführt – steht die Option Kopieren zur Verfügung. Diese kann nur in Verbindung mit einem chart-me Template verwendet werden, da ein vordefinierter Namensbereich markiert und in die Zwischenablage kopiert wird. Das Einfügen in PowerPoint geschieht hier manuell in eine bestehende oder manuell angelegte PowerPoint-Datei (mit dem richtigen Seitenformat) nach der in diesem Beitrag eingangs beschriebenen Standardmethode (eine genaue Beschreibung findet sich im Manual zu chart-me XLS, das auch eine Quelle für diesen Blogbeitrag darstellt):

Zur Unterstützung bei der Gestaltung des pixelgenauen Seitenlayouts wird hier sehr übersichtlich in die Zielausgabe Bildschirm (= PowerPoint) und Papier (= Druck, PDF) gegliedert. Für die beabsichtigte Verknüpfung nach PowerPoint wählen wir also die Option Bildschirm:

Nach der Auswahl des gewünschten Layout-Typs werden im nachfolgenden Dialog neben der Anzahl der Botschaft- und Titelzeilen auch das gewünschte PowerPoint-Format (hier: 4:3) festgelegt:

Jetzt wird das eigentliche Template als neues Sheet in der Arbeitsmappe generiert, in der ersten Zeile und ersten Spalte werden wieder die Spaltenbreiten bzw. Zeilenhöhen dokumentiert:

Evaluierung

Einziger Nachteil der hier empfohlenen Standardmethode des verknüpften OLE-Objekts ist, daß die Schriften und Zahlen in ihren Proportionen unglücklich verändert werden, je kleiner die Schrift umso negativer der Effekt. Das ist seit vielen Office Versionen so und leider weiterhin auch in Office 2016.

Best Practices

Diese stammen allesamt aus dem IBCS®-Umfeld und werden hier von uns lediglich wiedergegeben:

  1. Verknüpfungsbereich in Excel: Namensbereich verknüpfen anstelle eines normalen Zellbereichs (wegen der Nachvollziehbarkeit und der Dynamik bei Änderungen im Zeilen-/Spaltenaufbau des Zellbereichs).
  2. Einfügemethode in PowerPoint: Einfügen -> Inhalte einfügen -> Verknüpfung einfügen -> Microsoft Excel-Arbeitsmappe-Objekt (bzw. ggfs. Microsoft Excel-Diagramm-Objekt beim Verknüpfen einer Grafik anstelle eines Zellbereichs)
  3. Der verknüpfte Zellbereich muß in 100% Skalierung angezeigt werden, da nur auf diese Weise eine pixelgenaue Übereinstimmung mit dem Excel-Quellbereich erreicht wird. Daraus folgen zwei zentrale Anforderungen:
    a. Die in PowerPoint benötigte Pixelgröße des Objektes muß in Excel vorbereitet werden. Dafür hat es sich bewährt, die Spaltenbreite und Zeilenhöhe des Verknüpfungsbereich in Pixel in einer Hilfsspalte/-zeile zu dokumentieren (und ggfs. mit einem Makro automatisch abzufragen).
    b. Der Zellbereich in Excel darf nicht größer sein als die Folie in PowerPoint. Üblicherweise entspricht das Format „4:3“ einem Pixelformat von 960 x 720 px.
  4. Die automatische Aktualisierung sollte idR deaktiviert werden und nur gezielt aktualisiert werden. Die Datenverbindung sollte – je nach spezifischer Situation – aus einer PowerPoint-Berichtsdatei vor dem Verteilen entfernt werden, damit Berichtsempfänger keine unkontrollierten Aktualisierungen auslösen können und nicht verunsichert werden.
  5. Wird nur ein (starres) Bild und keine (aktualisierbare) Verknüpfung in PowerPoint benötigt, dann kann / sollte anstelle der Option „Einfügen -> Grafik“ (Vorstellung im Teil 2 dieser Blogserie) die hier beschriebene Standardmethode verwendet werden und über die Link-Einstellungen die Verknüpfung nachträglich entfernt werden. In beiden Fällen wird eine Vektorgrafik im EMF-Format erzeugt, die Standardmethode hier hat aber den Vorteil, daß Bildgröße/-format aus Excel korrekt übernommen werden.

Quellennachweis

www.chartisan.com (primärer Know-How Transfer durch GF Markus Wolff)

zebrabi.com und zebrabi.com/powerpoint-presentations-with-zebra-bi-webinar-recording/

hi-chart.com (das Excel Add-In und das Benutzerhandbuch für „chart-me XLS“)

support.office.com/de-de/article/Einfügen-eines-Excel-Arbeitsblatts-in-PowerPoint

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Er hält Trainings und Seminare und unterstützt Fachabteilungen bei der Umsetzung ihrer Business Intelligence Anforderungen.

Weitere Beiträge

Kategorien: Dashboarding, Excel als BI Frontend, Reporting
  • Andrej Lapajne

    Hi Robert, just a quick update. We have additionally tested the Zebra BI export function for linked PowerPoint slides in German version of Excel. It works as expected, the link refers to „Druckbereich“:

    https://uploads.disquscdn.com/images/49a62807c952e1f198789e4789d4beeafbd48766b40a6980fac7ad53fa97fa1d.png

    I would definitely recommend to use Excel’s print areas instead of custom named ranges.

    Kind regards and keep up the great work with your BI blog!

  • Andrej Lapajne

    Hi Robert, Thanks for the interesting article.

    Please read some feedback regarding your remarks: „Namensbereich verknüpfen“ and „Der Standardname „Druckbereich“ (wie beim Excel Add-In Zebra BI verwendeet)“

    „Print_area“ is the standard Excel name, and if you work with Zebra BI, then the links in PowerPoint stay linked to the print area (see picture attached).

    That’s the best practice, because it’s the standard Excel behaviour. The Print_area Excel name is there exactly for this purpose and official Microsoft Excel Addins are using it in exactly the same way as Zebra BI.

    So with Zebra BI you can:
    – Change your Excel template, remove or add rows and columns any way you like. As long as you have it in your printing area it will show and refresh correctly in PowerPoint.
    – Use the export to PowerPoint (linked or not) or Print to PDF even for content that is not created with Zebra BI!
    – Pin or publish Zebra BI report pages to other tools natively (for example to Power BI)
    – export to PPT, publish to PDF or just print from the same, standard print area definition

    If you define your custom name for a range („named range“), then Zebra BI also works, exactly as you proposed.

    But it is not needed.

    Unless perhaps if there’s a bug in German version of Excel? We’ll check that.

    Also I have noticed that you have selected the Print_Area in your example. That is not needed in Zebra BI! You just click on ANY cell on your worksheet, click Export and you’ll get the PowerPoint slide linked to your print area. That’s another advantage of our approach: it all in 1 click, you don’t have to think about it.

    Please note that Zebra BI has some more „intelligence“ for exporting of linking to PowerPoint:
    – if a chart object is selected, then only the selected chart will be exported or linked to PPT
    – if a specific area is selected, then this are will be exported
    – if small multiples are selected, all charts in the multiples will be exported to PPT
    – if a named range (your custom Excel name) is selected, then the named range will be exported and the link will have your custom name! (so if you want to do this, as you suggest in your article, you can also do it, no problem)
    – if a single cell is selected (just click on any cell, anywhere on your worksheet), then Zebra BI will export the Print area, if the print area is defined (if it’s not defined, then you’ll get the single cell into PowerPoint)

    Cheers!
    Andrej

    • Robert Lochner

      Thank you Andrej for your comprehensive and very useful feedback. As an exception I revised the blog post yesterday in 2 areas:

      1. In chapter 3.1 I changed the screenshot where the print area was selected, since I`ve learned that the print area can but doesn`t have to be selected and I added the „intelligence“-issues from your comment here.

      2. In chapter „Best Practices“ I removed the sentence „Der Standardname „Druckbereich“ (wie beim Excel Add-In Zebra BI verwendeet) eignet sich für diesen Zweck nicht so gut, da dieser von PowerPoint nicht als Namensbereich verknüpft wird sondern in einen Zellbereich übersetzt wird.“ since I know now that this „print area“-Issue (see the seconde comment above) happens on my computer but is not a general issue. Therefore I agree, IT IS A GOOD IDEA to use the name range print area for the PPT-Automation in Zebra BI. Sorry for this misleading interpretation.

      Good discussion, I like that (since I learn a lot)!
      Cheers :)
      Robert

  • Robert Lochner

    Hallo Markus,
    vielen Dank für diese weitere wertvolle Praxiserfahrung!
    LG Robert

  • M. Wolff, chartisan GmbH

    Hallo Robert,

    danke für diesen Artikel. Ich glaube er kann vielen Anwendern helfen, unnötige und nervenraubende Fleißarbeiten einzusparen ;-)

    Einen wichtigen zusätzlichen Tipp möchte ich hier noch ergänzen:

    Wenn viele OLE-Verknüpfungen zwischen eine PowerPoint- und einer Excel-Datei bestehen, dann lässt sich die Zeitdauer für das Aktualisieren aller Bilder dramatisch verkürzen, wenn man zuerst die Excel-Datei öffnet und erst danach die verknüpfte PowerPoint-Datei.

    Besonders wichtig wird das, wenn die betreffende Excel-Datei recht groß ist und einige Sekunden zum Öffnen benötigt.

    Hintergrund: PowerPoint kann grundsätzlich auch Bilder aus geschlossenen Excel-Dateien holen, wie Du richtig schreibst. Dazu wird jedoch die Quelldatei kurzzeitig in einem Hintergrundprozess geöffnet und wieder geschlossen. PowerPoint wiederholt diesen Vorgang für jedes einzelne Bild, auch wenn die Bilder alle aus derselben Quelldatei kommen.

    Wenn 100 verknüpfte OLE-Bilder aktualisiert werden sollen und das Öffnen der Excel-Quelldatei zehn Sekunden dauert, dann entsteht bei geschlossener Excel-Datei eine deutlich spürbare Wartezeit. Wird die Excel-Quelldatei hingegen zuerst geöffnet, dann geht das Aktualisieren der Bilder blitzschnell.

    Beste Grüße,
    Markus

    • Robert Lochner

      Hallo Markus,
      vielen Dank für diese weitere wertvolle Praxiserfahrung!
      LG Robert