So, this is a Salesforce blog BUT sometimes you come across a tidbit of off topic information that is worth sharing with others. This may be of interest to anyone who crunches data in excel and pastes those charts into PowerPoint. I ran into an issue with charts pasted into PowerPoint using the “Use Destination Formatting and Link Data” option. Googling the issue yielded less than optimal answers that just couldn’t be the only way to fix this. I found that the easiest way to “fix” it wasn’t called out anywhere. Also, imagine my surprise and disappointment at the snarky and condescending responses on many of these boards to people just asking for help not ‘tude. That’s another reason I am sharing my findings and hope it helps someone else out there.
Background – I am working on a market research related project. The major tasks at hand are collecting and collating the responses for both quantitative and qualitative questions, creating a PowerPoint with charts and analyzing commentary for call-outs and trends. Drawing on prior experience working for a premier market research company, I was able to create an excel tab in format that makes it easy to transcribe Yes/No and multiple choice questions. That input is now countable data points and in turn those become colorful charts to summarize all this great data.
Those nice charts need to make their way into a PowerPoint presentation – easy. Now this where you will find multiple schools of thought on how to do this and refreshing those linked charts. I’m going with the two most prevalent.
The first option is to copy the chart from Excel using copy>paste special>Microsoft excel chart object. The appeal of this method is that when the data changes in excel and the PowerPoint file is opened the chart updates without any additional clicks. You’re all set if you are happy with how the charts look when pasted this way. I’m not a huge fan because charts can become distorted with resizing and the color palettes do not always to translate exactly.
The second option is the one I prefer. In Excel, copy the chart and in PowerPoint >right click on the slide >pick either Use Destination Theme and Link Data OR Keep Source Formatting and Link Data.
I’ve found that PowerPoint often times does a better job determining a palate that is more visually appealing than Excel but either link data option will work.
Problem – Now, you would think by picking and option that says LINK DATA that the data would refresh on its own. Design flaw or not, it does not refresh automatically! OMG! Many people to post frantic messages on various boards asking for help. I was a bit surprised at the snarky responses on many of these boards so hopefully this helps someone later.
Ok – solution time. Most replies told people they had two options. One, paste the charts all over again and use method 1. Ugh – not pleasant if you just created a massive file! Two, refresh EACH chart one at a time. Again, ugh, you can’t be serious. Luckily there is a third!
In PowerPoint 2013: Click File> Options> Quick Access Toolbar.
Change the drop down to “All Commands” and scroll to “Edit Links to Files”>ADD>OK
The Edit Links to Files is now in the quick access menu – Click it.
Click on each chart and check the “Automatic Update” You must do this for each chart.
The next time you open the file a pop up will appear asking if you would like to update now. Click Yes.
Ok – you’re all set and you don’t need to copy and paste in all of those charts again!