Today, you will learn a great trick for a PowerPoint presentation. You can make your PowerPoint presentations completely dynamic. Watch how you can connect a PowerPoint text box to a data source and use text boxes as dynamic text boxes on your slides.
Set Up a Data Connection
First, you need to connect your presentation to your data. This data is often stored in a database or file. Supported databases are Microsoft Access, Microsoft SQL Server, MySQL and Oracle. Virtually any other database type on the market can be addressed via OLE DB or ODBC drivers. Files can be text files, comma separated text files, XML files or even Microsoft Excel datasheets are great for storing and manipulating data. To work with databases, you need to understand the structure of the database and you need to know SQL. So that, when you work with Excel, it will be much easier, so for this article I will use a simple Excel file. Nothing complicated. Let’s work with some data that everyone understands; beer! Yes, we are a Belgian company and like to promote some national products. We will display information about beer and pricing on our information screen. This is the Excel file I’m using. A simple file with one datasheet, where we list the names of the beers and the prices.
In DataPoint, we will set up a connection to that file. First, a connection pointing to this Excel file and then a query selecting the Excel range A1:B20 and a refresh rate of 5 seconds.
The range A1:B20 is the maximum data range to use. You can always use more than 20 rows and empty rows are ignored in DataPoint anyway. A refresh rate of 5 seconds, means that DataPoint is going back to the Excel file to look for new or updated information, every 5 seconds, and that is continuously working while the slideshow is running.
Start with a Presentation
Next step is to set up a presentation and link some text boxes to your linked Excel data. Start with a new presentation and add a slide. On that first slide, we added a new normal text box. Note that you need to use text boxes to display static information all the time. Insert a text box and type in your text or message. With this dynamic linking, we are not going to type in text anymore. No, we are going to link this text box dynamically to a data source. After the linking, we maintain the information directly in the database or data file, but not anymore in the presentation. That is presenting only!
Select the newly inserted text box. Click to activate the DataPoint menu and click the Text box button.
The DataPoint dynamic text box properties form opens.
Let me select the data connection or query where we will get the information from. Here in this article, we only have set up one connection to the Excel file, but we can have multiple data connections in the same presentation. So first, select the data connection that you want to use in this text box.
The data column combo box holds all fields or columns of the chosen data connection. Select the field that you want to link dynamically to the text box.
For the last step, set the row number. If you want to show the value of the first data row (first beer in this example), then, select 1 as row number here.
Click OK to save the selections.
As you can see, the value 4.2 is copied into the text box. Note that only the raw values of our Excel file are copied to our text box here. Any formatting in Excel is lost. So we have the flexibility to do the specific formatting here again in PowerPoint. Click DataPoint and then the Text box button again. Click to activate the Format tab.
To display the price here, set the category to number. Set the decimal places to 2 and click OK. Any new value coming in from Excel, will be formatted as a number with 2 decimal places.
Next to the price, to show the name of the beer, add a new text box and select it. Click DataPoint, Text box button here again. Use the same data connection, same row number, but here set the column name to Brand.
Next, click the Texts tab. At the prefix text box, type Beer of the month: and hit OK. The text box will now always display the first name of the beer and prefix the text with this prefix text. Without this option to add a text to it, I would have used 2 different text boxes. One for the static prefix text, and a second for the dynamic name.
The Texts tab offers some more possibilities.
- As demonstrated here, the prefix text always comes in front of the dynamic value.
- A suffix text always comes behind the variable value, e.g. to indicate C or F for a temperature.
- The option for an empty text is placed whenever to row exists at the data source, but the value is empty or nothing.
- And finally the non-existing row replacement text, will be copied into the text box whenever the chosen row number exceeds the number of rows in your data. So when you would have value 8 set as row number on the text box, and your data set is returning only 5 rows, then this text will be shown instead.
The last option on this Texts tab, Alternate text boxes every n seconds, is to let text alternate when you have texts with multiple lines. This is explained in detail at another great article. The 2 other tabs called Ticker and Rules, will not be handled here, because there is so much more to say. Here is more information on text tickers and another one on setting up dynamic rules.
Run Your Slideshow
Next time that you open this dynamic presentation, all of the linked and dynamic text boxes will be refreshed with the latest and current information. So when you change the name of the beer of the month, or you change your pricing, then this information is updated automatically on your slides. Furthermore, you can start the slide show and while it is running, it will check for updated information at your Excel file and update the information on the slides, during the slideshow, without interrupting the slideshow at all.
Generate Snapshot Presentations
You can also generate snapshot presentations out of this. A snapshot presentation is a newly generated presentation, filled with the latest information, but then disconnected from your data sources. So you start with your linked and dynamic presentation, and you generate daily or weekly snapshot presentations for statistical purposes, or for distribution to other that do not have access to your data source directly.