The Microsoft MVP Award Program Blog

Independent Experts. Real World Answers.

PowerPoint 2010 and Excel 2010: Perfect Partners for Connecting Data to Presentations and Dashboards

PowerPoint 2010 and Excel 2010: Perfect Partners for Connecting Data to Presentations and Dashboards

  • Comments 7
Editor’s note: The following post was written by PowerPoint MVP Glenna Shaw
So maybe you’ve read my article on PowerPoint 2010 and Excel 2010: Perfect Partners for a Digital Dashboard and created a really great dashboard or you’ve got a really important presentation with a lot of charts.  And now it’s time to update the data.  Do you really want to spend your valuable time clicking on each chart or data element, clicking Edit Data and making the changes for every single item in your PowerPoint?  Is there a better way? 
The first time I asked some Microsoft gurus, “Is there a way to connect a data source to PowerPoint?” the response was “by way of Excel.”  To which I replied “Well, that’s like going around your elbow to get to your rear end (this was a phrase my father frequently applied to me because I liked to meander around when I drove places instead of taking the most direct route).”
Once everyone quit laughing, the answer remained the same, to connect data to PowerPoint you have to use Excel or one of the other applications that support “real” data connectivity.  So this is an article on how to create and use what I’ll call “elbow” or source to source files and use them to connect data to PowerPoint.
Create Your Elbow File(s)
For my example I’m going to use a SharePoint list and connect it to an Excel spreadsheet.  Your data could be an OLAP, SQL, Access or a large variety of data sources.  The important thing is that you can connect it to Excel (or Visio if that floats your boat.)  If you need to learn more about Data Connections, go to Create, edit, and manage connections to external data for Excel and Import data from Excel, SQL Server, SharePoint sites, and other external sources for Visio.
From my SharePoint Sample Data List, I used the Export to Excel button (Figure 1) to create an Excel file connected to my Sample Data (Figure 2).
image
Figure 1
image
Figure 2
In my new Excel file, I use the table of connected data to create the elements I’m going to put in my dashboard or presentation.  Since I frequently work with a large volume of data, I like to use Pivot Tables and Charts for my elements.  I also like to apply conditional formatting to some elements when appropriate (Figure 3). 
image
Figure 3
When I’ve completed creating all my elements in the file, I save it to a shared location so persons other than me can update it.  In this example, I’ve saved the file to my SharePoint site (Figure 4).  If at all possible, I prefer to have only one elbow file per presentation, but you may choose to create multiple elbow files if you’re using multiple sources for your presentation.
image
Figure4
Link Your Elbow File to PowerPoint
Now that you’ve created your elbow file, it’s time to link your data elements to your presentation or dashboard.  I’m using a dashboard for my example.
First reopen your elbow file in the full Excel (or Visio) application (Do not try and use Web Apps for this step). If prompted, enable connections so your data can be updated. You can avoid this prompt in the future my answering Yes to the trusted document question.
Linking Charts
Open your PowerPoint presentation.  Click on the desired chart in your elbow and click Copy on the toolbar (Figure 5).
image
Figure 5
Go to the PowerPoint and click Paste (Figure 6).  Resize and move chart as desired.  Since it’s a chart, it will link to the presentation by default.  Repeat for remaining charts in your elbow file.
image
Figure 6
Linking Spreadsheet Tables or Visio Diagrams
While Excel Charts will automatically link when copied and pasted to PowerPoint, it takes a few extra steps for an Excel Table.  Highlight the Excel table you wish to include in your presentation and click Copy on the toolbar (Figure 7).
image
Figure 7
In your PowerPoint, click the Paste drop down arrow, click Past Special (Figure 8).
image
Figure 8
In the Paste Special window, click Paste link, click OK (figure 9).
image
Figure 9
Your table is now linked to your presentation.  Please note: if your table changes size when it’s updated, you’ll have to delete the existing table and recopy/paste-link it when updating your file.  Also, if don’t paste-linkyour Excel table it will automatically convert to a PowerPoint table and will not update.  Use these same steps to paste-link a Visio diagram.
Set Your PowerPoint Links to Update Automatically
Save and close your elbow file.  In PowerPoint, click File, Info, Edit Links (Figure 10).
image
Figure 10
In the links window, set all your links to update automatically by highlighting them and clicking the Automatic radio button, click close (Figure 11).
image
Figure 11
Save your presentation file to a shared space if you want others to be able to update it.  I save mine to the same document library as the elbow files.
Updating Your Data
Ok, now your ready to have anyone go around your elbow (files) to update your … Smile (presentation files).
First open your elbow file(s) in Excel (or Visio), enable connections if prompted and click Data, Refresh All (Figure 12). Save the file and close Excel (or Visio.)
image
Figure 12
Make sure you open, refresh, save and close all your elbow files before you open the PowerPoint file.
Open the PowerPoint file in the full PowerPoint application and when prompted, click Update Links (Figure 13).
image
Figure 13
Your presentation will update all the data elements from your source files and you’re done.
Sharing Your Files
You can share your presentation through the Web App and it won’t prompt for updates (Figure 14).
image
Figure 14
Or you can save it as a separate file and use the same steps in Set Your PowerPoint Links to Update Automatically to break the links before distributing the file.
I hope you’ve found this article helpful.  While you do have to go around your elbow to update your data, you’ve got to admit it’s a lot better than editing all those individual elements.

About the author

Glenna Shaw

Glenna Shaw is a Most Valued Professional (MVP) for PowerPoint and the owner of the PPT Magic Web site and the Visualology blog. She is a Project Management Professional (PMP) and holds certificates in Accessible Information Technology, Graphic Design, Cloud Computing and Professional Technical Writing.  Follow Glenna on Twitter

 

About MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead,  for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade.  In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund.  Melissa lives in North Carolina and works out of the Microsoft Charlotte office.

Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post
  • Hi Glenna - I have a problem with excel links breaking when I save the powerpoint as a new file, and was wondering if you can help me. Do I need to have my excel files open when I do the save as? How do you prevent links from breaking when saving the powerpoint as a new file in a new location?

  • Glenna,

        What if you wanted to reverse the update direction.  Meaning I want the master source of the data to be Powerpoint.  If the data gets updated in Powerpoint I want to push the updates out to a Sharepoint list.

  • Hi there!

    we have prepared a simple and useful solution for that issue. Check the following video, where its presented: www.youtube.com/watch

    You will find the contact information and we will provide you that Add-in.

    more info on www.prudeon.com

    Than you will save a lot of time!!

    Good luck!

  • Hi there,

    that might be useful for you, Change of source files of PPT of  of more than one object from Excel at once. Check the following video, where its presented: www.youtube.com/watch

    You will find the contact information and we will provide you that Add-in.

    Than you will save a lot of time!!

    Good luck!

  • please inter my computer about powerpoint

  • What do I do when the Excel file name has changed (e.g. a newer version) and I want the PowerPoint to be updated to the newer version? Is there an easy way to edit those links?

  • this thing is horrible i will never get it again

Page 1 of 1 (7 items)