Link text to an Excel cell to dynamically update data

Sudeep wrote me, “I need some help to update a slide containing a map with certain districts. I have the monthly sales turnover for these districts. In need to update them monthly from an Excel sheet. Can you suggest a way to automate the  process without having to punch it in a text box every month?

Sudeep doesn’t want to create an entire table of data. Instead, he wants individual text boxes that appear on the map. Each one should be linked to an Excel cell.

Here’s an example of what such a slide might look like.

One solution: Copy and Paste Link

At first, I suggested the simplest option, because I didn’t know that he wanted the data to be nicely formatted. Here it is:

1. Choose a cell that will always have the data you need. If you put monthly data in a new cell each month, create a row or column for  the current data and copy the current data there. When you link the data in PowerPoint, it will look for the same cell whenever you open the presentation.
2. Format the cell the way you want it to look in PowerPoint. One point to note is that if you don’t want a border around the data, in Excel, choose View tab, Show group and uncheck the Gridlines check box.
3. In Excel, copy the data in the cell.
4. In PowerPoint, choose Home tab, Clipboard group and click the Paste down arrow. Choose Paste Special.
5. In the Paste Special dialog box,  choose the Paste Link option. Keep the default option, Microsoft Excel Worksheet Object, and click OK.
6. The Excel cell object appears on your slide. Drag and resize it as needed. Note that the data is actually an image, so you resize the text by resizing the cell object.

When the data in the Excel changes, your PowerPoint slide will automatically update.

The problem with copy and Paste Link is that you can’t make the data look very pretty. You can give the object an outline and fill (right-click it and choose Format Object) or remove the outline and fill, but that’s about it. Also, you can control the formatting in Excel.

Another solution: Combine a text box with a PowerPoint shape

In the end, Sudeep came up with a great option himself, based on what I had suggested. He inserted a text box and formatted it the way he wanted. Then he removed the border in Excel and used the copy and Paste Link method,  putting the resulting object in front of the text box. Because the object didn’t have any border or fill, the data looked like it was in the nicely formatted text box, as I showed above on the map of Florida.

He wrote, “I could achieve the desired output by combining text box with Excel cells.  First formatting the text box as desired and then pasting(linking) a single excel cell without border and keeping it in front of the text box.”

Sudeep, thanks for the great question and solution!

Do you need to place single Excel cells on a slide? Would this solution work for you? Or, have you worked out another solution? Please leave a comment!

“101 Tips Every PowerPoint User Should Know” is for everyone who never took a course or read a book about PowerPoint! These tips will fill in the gaps, speed up your work, make presentations easier, and help you get better results. Now updated through PowerPoint 2016 and Office 365. Learn more at http://www.ellenfinkelstein.com/pptblog/101-tips/