Display Excel data in a data driven PowerPoint presentation

Display Excel data in a data driven PowerPoint 10

Sometimes you want to display live information in a PowerPoint presentation. Maybe you need to display scores or results in real time to people on a television screen. A manager needs to see production figures of a factory at his desk. People on the floor need to know their targets etc. This can be accomplished with data driven presentations.

To display numbers and figures you don’t use a word processor but a spreadsheet like Microsoft Excel. There, the user can enter your raw data and make some simple or complex calculations. Of course the user don’t want to display an Excel sheet on the message boards with its grid lines etc. but the user needs presentation software like Microsoft PowerPoint. PowerPoint is the ideal software for presentations but it is static.

There is a tool DataPoint available that allows the user to create dynamic presentations with live data from Excel worksheets. Some people tend to copy and paste Excel objects in their PowerPoint but that is not done. It will even not update automatically over the network. The user will see the Excel grid lines in a presentation which is not professional looking. The user can use the data only from the Excel worksheet but do the formatting in the PowerPoint presentation. The user can emphasize which text boxes are more important by setting a color, or a more important position on the slide with arrows pointing to this value and some animation. PowerPoint, with its data from Excel, gives more control!

data in an excel worksheet ready for linking in powerpoint

Establish connection

Let me show how easily the user can display live information from an Excel worksheet in a PowerPoint and update in real time. First, the user needs to install DataPoint and then the user will start Microsoft PowerPoint. Click the DataPoint menu in PowerPoint.

Then click the List button of the Connections group.

Locate the Excel files node and click Add connection.

navigate to the excel node here in the connection list

Browse to the Excel file that the user wants to use and click OK.

Rename the connection into a meaningful name.

Connection to Excel worksheet

Click Add query in order to connect to a given worksheet and range of the selected Excel spreadsheet.

The user will see a list of all the worksheets in the Excel document. For this example select the By Product worksheet.

add link to worksheet and range and set refresh rate properties

Now set the range to A7:E1000. A7 corresponds to the first cell with the information that we want to display (see the raw Excel file earlier). The end point of the range needs a bit more explanation. E refers to column E which is the last used data column in our sheet. But when the user looks at the data that the user will see that E33 is the last data cell being used at this time. But we never know how much products or entries we will have after 1 year in this listing. So for this reason we will exaggerate a bit and set the virtual end point to cell E1000. So DataPoint will consider to use all the data in the range of cells A7 till E1000. DataPoint will automatically ignore empty rows at the end of the data.

Check the option The first row contains field names. Set the Data refresh rate to Continuous updating with an interval of 60 seconds. Every 60 seconds DataPoint will visit the worksheet and look for data updates.

Click OK to close. Rename the query and the user will see a preview of the Excel data here.

Click OK to close the connections.

Add table to slide

Insert a table on the slide. Take a table with a number of rows and 5 columns. The columns will display the customer’s name and then 4 columns for the quarterly totals.

Select the table and click DataPoint, Table to open its dynamic properties.

The data connection that is chosen as default is already correct since we only have one data connection pointing to our Excel file yet. Check the option Copy the column names to the first row of the table and notice that the Start filling data at row is automatically set to 2.

Click OK to close and see how the data of the Excel file is copied into this table.

Select all the cells with numeric data. So all cells except for the first column (for the name) and the first row (for the header info). Then click DataPoint and Table again.

excel data in copied and inserted into the table

Cell formatting

Click to select the Format tab. Set here the formatting to Number. Increase the decimal places to 2 and check Use 1000 separator for these cells. Click OK to close.

format the table cells for numbers, decimal places, use 1000 separator etc

Prepare slide show

Now for the last step, verify that the presentation is set to loop forever since we will display this on a dedicated television or computer screen. Click Set Up Slide Show in the Slide Show menu and make sure that Show Type is set to Browsed at a kiosk.

set presentation in kiosk mode slide show type

Start the slide show. The user can start the slide show by hitting F5 or by clicking the From Beginning in the Slide Show menu.

The slide show is now running. The user can now open this Excel document on another computer and edit the content. Change the sales figures, add and remove products etc. Save the document and now DataPoint will check every sixty seconds for new information. Whenever new information is detected at the source, DataPoint will automatically update the slide show without stopping the slide show as the user would have to do with a normal static and read-only presentation without DataPoint.