By Kurt Dupont
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, when you work with Excel, it will be much easier. 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.
Next, within PowerPoint, we will set up a connection to our Excel file via DataPoint, an add-in that shows up within PowerPoint. First, we will set up a connection pointing to this Excel file and then a query selecting the Excel range A1:B20 along with a refresh rate of 5 seconds. These settings can be seen in the figure, below.
Longer Range for Fewer Values, and Refresh Rates
Yes, we selected the range A1:B20 although our existing data within the Excel sheet is only within the A1:B4 range. 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 it is continuously working while PowerPoint is playing in Slide Show view.
Once your settings are in place, you can see them in the Connections dialog box within DataPoint, as shown in the figure below.
Start with a Presentation
The 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 this slide, we added a new normal text box. Note that conventionally, you need to use text boxes to display static information all the time. You insert a text box and type in your text or message. With this dynamic linking though, 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.
Select the newly inserted text box. Next, access the DataPoint tab of the Ribbon, and click the Text box button.
DataPoint’s dynamic Text box properties form opens, as shown in the figure below.
Let us 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 the row number here.
Click OK to save the selections.
As you can see, the value 4.2 is copied into the text box on the slide. 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. Access the DataPoint tab of the Ribbon again, and click the Text box button. Use the same data connection, same row number, but here set the column name to Brand.
Next, click the Texts tab of the dialog box of the figure shown above. 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 after the variable value. An example is to indicate C or F for a temperature value.
- The option for an empty text is placed whenever the 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 in our How to Set Up Alternating Text in PowerPoint 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
The next time you open this dynamic presentation, all 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 within 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 deck. 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 others that do not have access to your data source directly.
Kurt Dupont, based out of Belgium heads PresentationPoint, a company that creates several amazing PowerPoint add-ins. After his Computer Science studies, Kurt started with Andersen Consulting (Accenture nowadays) in Brussels.
After three years he moved to the Brussels Airport Terminal Company that runs the Brussels airport – this last placement inspired the start-up of Take-off (now known as PresentationPoint) in 1998.
The views and opinions expressed in this blog post or content are those of the authors or the interviewees and do not necessarily reflect the official policy or position of any other agency, organization, employer, or company.