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

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).

Figure 1

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).

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.

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).

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.

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).

Figure 7

In your PowerPoint, click the Paste drop down arrow, click Past Special (Figure 8).

Figure 8

In the Paste Special window, click Paste link, click OK (figure 9).

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).

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).

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.)

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).

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).

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 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.