How one character in a file name can mess up linking Excel cells or graphs to a PowerPoint slide
I recently had someone contact me asking why the linking between Excel and PowerPoint was not working properly. They had followed my advice in this article & video but it wasn’t working properly. Let me explain the scenario, what I discovered the problem was, and the solution that shows how changing one character in the file name solved the problem.
This professional, lets call them Doug, has to produce the same analysis and presentation for each client. To make things easier, he has created an Excel file he uses as a template for the calculations and graphs he will need for the presentation. He also set up a PowerPoint file that contained standard information along with the cells and graphs linked to the Excel template file.
The time-saving idea was that if he copied each of the template files to the client folder and updated the inputs in the Excel file, the PowerPoint file would be all set up for the client presentation. He quickly realized that the client’s PowerPoint file contained links to the template Excel file, not the client’s Excel file. No problem, you can just update the links in PowerPoint.
And here’s where it got strange. Every time he updated a link so it pointed to the client’s Excel file, PowerPoint displayed an image of the entire worksheet on top of that slide instead of just updating the data in the cells or the graph. What was he doing wrong?
After the initial email where he described the problem, I set up a couple of test files and was able to successfully change the link to the client Excel file. If I changed data in Excel, the slide in PowerPoint would reflect the changes. Everything seemed to be working as expected. So I asked for the files he was using. Doug sent them over. The links had been set up properly. But when I tried changing the link source I got the same problem he had reported. What was going on?
After some investigation I realized the problem. I noticed that after I changed the link source, part of the old file name was still in the link, causing PowerPoint to misinterpret where the link was in the Excel file. It was linking to the entire worksheet, not just the cells or single graph. Why was it doing this?
The source of the issue was the Excel template file name. It contained an exclamation mark at the start of the name. This is a common method used to make sure that the file is always sorted to the top of the list in File Explorer and can easily be found in a folder that contains a lot of files. The problem is that a link in PowerPoint uses the exclamation mark as a special indicator to separate the parts of the link. By including an exclamation mark, PowerPoint had not fully replaced the old file name when linking to the new source file. This caused PowerPoint to misinterpret the link and it assumed the entire worksheet was the linked object.
I removed the exclamation mark from both file names. It is just an issue with the Excel file name but I thought it best to keep the template file names consistent. I removed the previously linked items on one slide in the PowerPoint template file and replaced them with links to the new Excel template file. I copied the new client presentation template to a PowerPoint file with the client’s name. With no exclamation mark in the link, when it was changed to point to the client’s Excel file, the link was updated correctly and the client’s information appeared in the presentation. This test on one slide indicated that the exclamation mark in the file name was the problem.
I sent the solution to Doug for him to test it on his setup. Just because it worked for me didn’t ensure that it would work on his computer. It was great to read Doug’s excitement that it worked for him too. Because it worked for both linked cells and linked graphs, it was now a matter of applying the solution to the other slides in the file.
Knowing that all the links had to be redone, Doug asked a good question, is there a way to do a bulk replace of all instances of the file name in the link list. Unfortunately not in PowerPoint. There may be a plug-in or add-in that can do this, I am not sure. I looked into the XML of the PowerPoint file and it looks like it would be a lot of work because the link location seems to be associated with each slide, not in a central list that is easy to access.
The important lesson here is that if you want to link cells or graphs from an Excel file to a PowerPoint slide, make sure that the Excel file does not contain an exclamation mark. I would also avoid the square brackets and colon as those are used to denote parts of a link as well. If you stick to the regular alphabet and numbers in your Excel file name you will be fine.
Dave Paradi has over twenty years of experience delivering customized training workshops to help business professionals improve their presentations. He has written nine books and over 100 articles on the topic of effective presentations and his ideas have appeared in publications around the world. His focus is on helping corporate professionals visually communicate the messages in their data so they don’t overwhelm and confuse executives. Dave is one of less than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel and PowerPoint communities. He regularly presents highly rated sessions at national and regional conferences of financial professionals and is NASBA registered to deliver CPE credit courses to CPAs.