PowerPoint Tips Blog

Helping you with presenting, PowerPoint, and speaking

  • Home
  • Blog
  • Tips
  • E-Store
  • Training
  • About
  • Affiliates
  • Advertise
  • Contact
You are here: Home / Design / Hyperlinks / Link text to an Excel cell to dynamically update data

Link text to an Excel cell to dynamically update data

December 26, 2014 by Ellen Finkelstein 9 Comments

READ LATER - DOWNLOAD THIS POST AS PDF >> CLICK HERE <<

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.

powerpoint-tips-link-text-to-excel-1

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!

3 orange large asterisksDo 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/

Share1
Tweet
Share
+14
Shares 5
READ LATER - DOWNLOAD THIS POST AS PDF >> CLICK HERE <<

Related posts:

  1. Link to a specific PowerPoint 2003, 2007 or 2010 slide from Microsoft Word or Excel
  2. Create a table from Excel data in PowerPoint
  3. How to Create a Chart-Graph in PowerPoint from Excel Data-Part III
  4. Link to other presentations and return to the original presentation

Filed Under: Hyperlinks, Shapes & text boxes Tagged With: data, Excel, link, linking, update

9
Leave a Reply

avatar
This comment form is under antispam protection
9 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
Mark DAprende mas de nosotrosDarrylMichael StewartRyan McKenzie Recent comment authors
avatar
This comment form is under antispam protection
  Subscribe  
newest oldest most voted
Notify of
Steve Rindsberg
Guest
Steve Rindsberg

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.

Vote Up0Vote Down 
8 years ago
Victor
Guest
Victor

Great article. I had forgotten that you can inter-link Microsoft documents.

Vote Up0Vote Down 
8 years ago
Ellen Finkelstein
Guest
Ellen Finkelstein

Steve, Thanks for mentioning that!
Ellen

Vote Up0Vote Down 
8 years ago
Stephanie
Guest
Stephanie

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?

Vote Up0Vote Down 
7 years ago
Ryan McKenzie
Guest
Ryan McKenzie

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.

Vote Up0Vote Down 
7 years ago
Michael Stewart
Guest
Michael Stewart

Nice excel tips. Do you have youtube channel because of this tutorial? If you have video tutorial, it is good mate 🙂

Vote Up0Vote Down 
6 years ago
Darryl
Guest
Darryl

Yes. Thanks much!

Vote Up0Vote Down 
5 years ago
Aprende mas de nosotros
Guest
Aprende mas de nosotros

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.

Vote Up0Vote Down 
4 years ago
Mark D
Guest
Mark D

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

Vote Up0Vote Down 
3 years ago
wpdiscuz   wpDiscuz

Free Video Training!

13 Techniques that Will Make Designing Your Slides EASY!

And get the PowerPoint Tips Newsletter with tips and resources for presenters. Plus 5 bonus tips!

BirdSend Email Marketing Tool
4-up-ads PresenterMedia PoweredTemplate High-Persuasion Secrets Power Pointers Quarter Hour


Recent Posts

Recent Posts

  • Using PowerPoint’s cartoon people
  • Which version of PowerPoint should you buy?
  • Add a customizable, live web feed with Cameo
  • Get a designer look with abstract images to create backgrounds for slides and shapes
  • Creating and using a strong call-to-action slide

Connect with me!

Connect with me! Twitter LinkedIn Facebook

Ellen Finkelstein, Inc. · Fairfield, IA · Tel: 515-989-1832

Privacy, Refund, and Other Legal Stuff

wpDiscuz