Video not loading? Try it on

I'm going to show you how you can automate entire PowerPoint presentations using the data in any Microsoft Excel workbook. I've got an example of one such presentation and on my title slide I have the presentation name, I have the date of the presentation and then I have a number of other slides which are going to be updated and which are linked to a Microsoft Excel workbook. As an example, this slide shows how many computers, servers and smartphones that a typical IT group might support. This data might get updated on a weekly, monthly or quarterly basis. On this slide I have a few Engage donut chart infographics. This slide contains a data table which has a column for the projected surplus / shortfalls for different projects and then I have a total row at the bottom. Based on the total value I have a text box which spells out whether we are predicting a $50 M surplus or shortfall or what not, but it's derived from the total.

So even with all of the data in this presentation, there is no need to manually update, this can all be automated via Engage. Let me show you what the data looks like in Excel. Assuming this will be a quarterly presentation, I've got all my data set up with ranges for Q1, Q2, Q3 and finally Q4 data. I have entered matching data for each quarter, so in Q4 there are a lot of 4's, Q3 there are a lot of 3's. All of the cells highlighted in blue are the cells that are linked to the PowerPoint presentation and feed the various PowerPoint shapes, inforgraphics and tables. Based on the total projected surplus or short fall value in the table, we create a cell with a text string that will feed that text box in the presentation and as you can see I have written a simple formula that does the comparison for me automatically.

The linked values are all formula driven, so choosing Q2 in the dropdown all of this data will now get pulled from the Q2 section of the worksheet. This causes the surplus value in the final sentence to change as well and get updated to projecting a $20 M surplus.

If I go back to my PowerPoint slide you'll see if I update this presentation by clicking Automate, Microsoft Excel, select the source file and click reload values the entire presentation has been updated. The data in the table has been updated, now we have a $20 M surplus, the donut charts have been updated, we used to have 11k computers & 11k smartphones and so on now there are a lot of 2's because this is Q2. Even the title and the date have been changed as well.

If I go back to my Excel spreadsheet and this time I'll choose Q4 which will update the title, date and all other data elements. I'll save the file and I'll go ahead and close this file and go back to PowerPoint and click on Automate, Microsoft Excel, choose the source file and I'll reload the values. In less than two seconds it's updated the entire presentation and so again you'll see these donut charts have been updated the title has been updated as well Q4 for the date has been updated and on this slide all these PowerPoint shapes have been updated as well. Finally if you go to the data table this is where you see the predicted $30 M shortfall and then you can see this text box also updated.

Now I am going to show you how easy it is for you to automate your own presentations. This works with any PowerPoint object so I'll start by adding a label text box with "Number of tablets" in it. Then I'll add another text box and enter a sample of 30 tablets, however this is a number that might change on a weekly, monthly or quarterly basis. This is a really good example of something I might want to automate so what I'll do is click on the Engage ribbon, Automate and then Microsoft Excel and in this case click on the Create a link button. Engage will then ask me to link a shape, so I'll select the shape and click on link selected shape. While you can link to any Microsoft Excel file by clicking on the Browse button, I already have an Excel file linked as a source to this presentation so I'll just reuse this one and click open. It opens the Excel file for me and what I'll do is add a number of tablets label cell and then enter a sample value of 86 in the cell beside it. I'll complete the link by using the cell picker dialog and you can see the cell is linked and the new value is written to the PowerPoint shape. To show you it's linked what I'll do now is I'll use a shortcut by selecting the source file and click on the dropdown arrow of the reload values button and click open source. This will open that source file, and if I change this to 755 tablets and save this file, I can go back to the Presentation and just click on reload values. Now we're done so now you can see that it's updated the entire presentation and it's updated this PowerPoint shape for the number of tablets.

A couple of other neat features here so if you want to go back and find the shapes that are linked. On slide 4 for example, it tells me that I have three shapes that are connected and linked so this is the first one so I've got the table linked and if I click the next arrow it brings me down to this shape over here so this textbox is also linked. Finally the third shape is the new tablets textbox that we just linked. If I were to click the Disconnect shape button, it would disconnect that shape and remove the link. Now whenever I update the presentation because I've disconnected this shape this always stays static, but of course I can reconnect it if needed.

What happens if you change the name of your Excel file? What I'm going to do is close this Excel file and change the name of it. Let's see what the system would do here so if I click on Automate, Microsoft Excel and reload the values it wouldn't find that file and prompts indicating that the file doesn't exist anymore. What I can do here is click the change source button, if for whatever reason you need to change the name of the file it doesn't lose all of the link connections that you've made you just have to change the source. I'll click on change source and select the new file, and what it does is it reconnects to this new file. If I now select this file, choose open source and I change the dropdown to Q3 and save, I can go back to the PowerPoint file and click on reload values. It will reload the values in the entire Presentation.

This next portion is an update video for the latest version of the Excel automation feature in Engage. When you click on the Engage tab and then you click Automate you'll notice that we separated the excel automation into two different features. There is the Excel ShapeLink which is what we've just covered and then there's also the Excel SlideMerge. SlideMerge works a little bit differently and there's a video on that functionality as well but SlideMerge basically lets you automate multiple slides very quickly by linking the column headers to shapes in PowerPoint and generating slides from each row, think mail merge for slides.

When you click on Excel ShapeLink it's the same user interface as before the only difference now is that you can in addition to linking infographics and shapes to cells with data in them in Excel you can also link images and also data via an API based on an image file path or URL path for the API that you put in Excel. To link an image what you want to do is just create a shape as a placeholder first so this is where the image for the city will be. I've also got another placeholder where I'll show an image of an arrow going up/down based on the change from last period for this overall score. Lets take a look at the folder on my desktop with all the different images, c:\Users\sam\Desktop\City Images is the file path and I've got a series of jpgs here based on the city name. I also have some PNG's for those arrows going up or down based on the direction of the overall score. Back in Excel here is the source data I'll be using to link up and automate this presentation. I've got different columns of data, for example in column B I have all my different cities and column C I have the image for that city. Instead of just manually typing this out what I've done is I've concatenated column B and C so I start off with the base path for my image and then concatenate the name of the city followed by the extension, ".jpg". If I change the name of the city in this cell then this path automatically updates and will be the location of the image that I use to show the image on my PowerPoint slide. Let's close this Excel spreadsheet and we'll go back to our presentation and click on Engage, Automate, Excel ShapeLink and the Create a link button. This time instead of linking up an infographic or shape to a data value what I'm going to do is select the image placeholder shape and then browse for the Results Data Excel file and after it opens I can pick the cell that I want to link up to. If I wanted to show the image of Paris I would link up cell C5 and select from the new feature dialog the type of data for this cell. If I selected Value it would return the text string that actually shows the value in that cell C5, it would show the string "C:\Users\sam\Desktop\City Images\Paris.jpg". Instead though, we want to show the image so I'm going to select Image from the dropdown. Next is a window asking you the position of that image, you can have it top left aligned, top-right, same width, same height or same size. Let's go ahead and select same size and then the automation will put in that image and there's the image of Paris.

The other way you can link an image is not from a folder location on your computer but actually a location online. Let's open a browser and find a different photo of Paris and grab the URL for this image and copy it. Next we'll paste the http url into the Excel file results data and save the file. Back in our PowerPoint presentation we click on Automate, Excel ShapeLink and select slide one to highlight all the shapes that are linked up. First we are going to disconnect that shape, you don't have to do this step you can simply overwrite it but I just wanted to show you how to do that. Next we're going to click create a link, select the image place holder and click link selected shape and click the Excel file to open up this source. In Excel select cell C16 where the image URL is located and now again instead of it being a value, select Image from the dropdown and Engage is smart enough to determine that this is a URL address starting with HTTP. Again we'll have the same options for the image position and simply select same size. Engage will open the Excel spreadsheet, read the http online address and download the image that you've instructed it too so now we have a new image for Paris.

What makes this feature so powerful is when the image that you want to show is based on a condition, lets try an example. Lets create a link to the change from last period image placeholder. In Excel column E is the overall score for this period and in column F the overall score for last period. The cell to be linked contains a formula to compare the two values and generate a file path by concatenating either up.png, down.png or nochange.png to the basepath of the images. We can select the cell, select Image and choose same size. Thats it, now what's nice is as the data changes you can calculate and show which specific image (png, jpg etc) that you want to show.

Finally lets look at how you can also in addition to linking up values like I showed you earlier you can also link up values from an API. Lets open a browser and so for this city if I wanted to type in the name of the city and get the weather so for Paris I would have 11 C A few clouds this is the API. You can use this feature to link that up to show the weather based on the URL value in excel that points to an API. Lets add a placeholder textbox for the weather value and then click Engage, Automate, Excel ShapeLink and create a link to the new placeholder shape. In Excel select the cell that has the generated weather API url, https://weather.markido.com/index.php?api=1&city=Paris. When specifying the data type choose Value URL so that the system knows that it's for an API and it will go to the API for that address which was for the city of Paris and it's going to return the API value and put it right here on my slide.