Link Data in PowerPoint to the Source (Database / Sharepoint)

It’s very common to have PowerPoint Presentations for High level Meetings.  We even have lots of data represented in these presentations in the form of Charts / Tables.  And, this would be a recurring process to update the presentations with updated data. This takes lots of manual efforts.

We can automate process of updating the presentations with latest data to a fair extent with available out of the box features. It requires minimal development efforts. 

On a high level, we first create Excel sheet with all the required data being fetched from different sources like Databases / Sharepoint using Data Connections. This would provide auto updating of data to Excels. Also, using the data we create different views, could be charts / Pivot tables or any other as per our representation need. After our excel sheet with different charts / tables is ready, we insert these into our Presentation by maintaining the links to the Excel we created. Linking would provide auto updating of data from excel to our Objects on PowerPoint, which is actually connected to main data source (database / Sharepoint list). 

With these efforts, we are done and now our Presentation is ready and also the data in it is up to date.  Isn’t it simple to implement and saves lots of our efforts, we spend in regular intervals to update the data on it?

Now let’s try to implement the same with a simple example to understand it better. 
Here our goal is to display Sharepoint data in our Monthly Dashboards which are actually PowerPoint Presentations.

Step 1: Prepare your data connections ready to bind to actual real time data (Sharepoint List here)

• Open Sharepoint List which has our required data.
• Create a view with required fields and also if there are any filters to be applied, you can do that here.
• Now open the view we just created and in the ribbon, under ‘List’ tab, we have a button to ‘Export to Excel’.
  
  
• Click on it. A web Query file (.iqy) is downloaded. Please save into your local machine. Let’s rename this file to ‘DashboardData.iqy’

Step 2: Create excel sheet with required representations (Charts / tables) using above created Connections

• Open excel file and in ribbon, under data tab, Click on ‘Connections button’
  
• A window with available connections to the workbook is opened. Click on ‘Add..’>

• Another child window opens with all available connection files in our local machine.
• Click on ‘Browse for More’ button, a file browser window opens, now select the ‘DashboardData.iqy’ file we downloaded in step 1.
• You can see the newly added connection listed in Workbook Connections window
  
  
• With this connection you can create pivot table or Pivot charts. For example, under ‘Insert’ tab in Excel, click on ‘PivotChart’ available in ‘Tables’ section. A window to select data source is opened. Now select the above created connection.
  
  
• Now a pivot table with data from Sharepoint list is available with all fields we added in the List view of Sharepoint
• Create chart with required fields and save the Excel file
• In similar way we can create multiple charts as per our need. We can also bind data to the sheet and using Conditional Formatting, we can represent cell values as per requirement.
• After all the required charts / tables created , save the file

Step 3: Insert the tables / charts we created in excel to the PowerPoint presentations with links

• Open existing PowerPoint presentation / new one and add all the required sections / content into it.
• In the sections where we need to add the dynamic content i.e. charts / tables, we bring it form excel file.
• As an example, copy the Pivot chart we created in excel and go to the powerpoint presentation we are working on
• In ribbon, under ‘Home’ tab, we have ‘Paste Special’ button in ‘Clipboard’ section. Click on it
• A window with option to paste as object and link feature is opened
  
  
• Select ‘Paste link’ and click on Ok.
• Now move the chart object to the required section and also adjust the size etc.
• Similarly repeat the same step for all required objects
• Save the Presentation File.

With these efforts, our presentation is ready with data linked to Actual data sources.

If you need the presentation with updated data, simply open the excel we created in step 2 and refresh all the data connections in it. All objects in the presentation will be automatically updated with latest data.

To refresh the connections, open excel file and under ‘data’ tab, we have ‘Refresh all’ button. Click on it. We can also automate this using some macros.

Now it’s your turn to automate the Presentations you update regularly!!