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:
- 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.
- 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.
- In Excel, copy the data in the cell.
- In PowerPoint, choose Home tab, Clipboard group and click the Paste down arrow. Choose Paste Special.
- In the Paste Special dialog box, choose the Paste Link option. Keep the default option, Microsoft Excel Worksheet Object, and click OK.
- 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/
This is my standard suggestion for setting up automatic updates for relatively simple presentations. Another approach is to use an add-in like my PPTools Merge (http://www.pptools.com/merge/) or John Wilson’s similar add-in, which’d allow you to update one or many slides or presentations with data from Excel. And lots more.
Great article. I had forgotten that you can inter-link Microsoft documents.
Steve, Thanks for mentioning that!
Ellen
What if you were showing a growth percentage? How would you get the “+” sign to show up? My “-” signs show up, but none of my custom formatting is carrying over the “+” sign. Any guidance?
very useful. solved a problem we are working on right now. updating metrics daily/weekly and wanting to make them look nice but also wanting to reduce the labor required to do that. thank you very much for this article.
Nice excel tips. Do you have youtube channel because of this tutorial? If you have video tutorial, it is good mate 🙂
Yes. Thanks much!
Hello all, here every person is sharing such know-how, thus it’s good to read this website, and I used to go to
see this web site every day.
Hi, apologies for being a numpty but I can’t get the “Combine a text box with a PowerPoint shape” to work. Is there any possibility that you could make the steps required a bit clearer for non-experts like me please:
e.g.
He inserted a text box and formatted it the way he wanted
Step 1 = Insert a text box (in Excel or Powerpoint?) and format as desired.
etc
Kind regards from the UK,
Mark