Did you get hit by the disappearing Excel add-in affliction recently? After a long couple of weeks, that frustrating Excel problem is finally solved. After spending countless hours on that issue (details below), I was happy to get back to more productive work, updating my website and sample files. I hope the rest of the summer goes smoothly!
Excel Dashboard Webinars
Mynda Treacy has just opened registration for her highly-rated Excel Dashboard course, and you can get 20% off if you register by Thursday, August 11th. I’ve been through the course, and was very impressed with the content, and Mynda’s presentation skills.
Mynda is also offering FREE Excel Dashboard Webinars for the next few days, so sign up for one of those, if you haven’t attended before. They’re jam-packed with Excel tips, and you’ll learn a lot in just one hour. It’s well worth your time.
Disappearing Excel Add-ins
And now, here’s the sad story of the disappearing Excel Add-ins, and how that mystery wasted countless hours of many people’s time. If you have violin music, you can play that in the background, to add to the drama.
In mid-July, I heard from someone who had been using my Pivot Power Premium (PPP) add-in for a couple of years. He had recently upgraded to Excel 2016, and the add-in wasn’t opening correctly in that version – the PPP tab didn’t appear on the Excel Ribbon.
To see the tab, he had to:
- Open the Add-in window, uncheck the PPP add-in, and close the window.
- Then, he had to open the window again, add a check mark for PPP, and close the Add-ins window.
He probably banged his head on his desk a few times too, but was too polite to mention that!
Trying to Find the Problem
We exchanged a few emails, while troubleshooting the problem, but couldn’t find a fix. He patiently tried a couple of suggestions, but nothing worked.
The PPP add-in was working correctly on my computer, using the same version of Excel. So, all I could suggest was a macro to do the steps of uninstalling and installing the add-in, until we found something better.
I figured it was a problem specific to his computer and/or Office installation, and we might fix it eventually. I promised to keep researching.
More Problem Reports
A week or so later, I heard from a few more customers, who had bought the PPP add-in or my Excel Tools add-in. All had been using the tools without problems for a long time, and the problems had just started. They had to go through the same steps as the first customer, to make the Ribbon tab appear.
Some customers were using Excel 2016, but others were using different versions. So, it obviously wasn’t machine or installation specific! A couple of customers mentioned a recent Microsoft update, so that was a clue to when and why things started going wrong.
More Failed Experiments
Another customer was willing to test things, so we tried a few more experiments, but again, nothing worked. By the way, if you have to work with the Excel add-ins more than occasionally, be sure to show the Developer tab on the Ribbon, and use the Add-ins command there. It’s much quicker than going through Excel Options.
Looking for Help
It was time to look for help with the problem, so I asked my Excel colleagues if any of them were seeing similar reports from their customers. The good news — answer was a resounding, “Yes!”. One person was even seeing it on his own computer, with someone else’s add-in. The bad news — nobody had found a solution yet. We reported the problem to Microsoft, to see if they could offer any help.
Finally a Fix
Finally, after many days of searching, and wasted hours of developer and customer time, a solution was posted in one of the Microsoft forums. A big thank you to Jon Peltier, who alerted the rest of us to the fix, which was buried in a long and confusing discussion.
What Happened and How to Fix It
The problem was triggered by a mid-July Microsoft security update, which changed the way that certain types of Excel files are handled. If files had been download from the Internet, like most add-in are, they had a security setting that was marked as Blocked.
To allow those add-in files to open automatically when Excel opens, you can follow the steps below, to manually unblock then.
- Open Windows Explorer, and open the folder where you stored the add-in file, usually the Microsoft Addins folder.
NOTE: If you aren’t sure of the Addins folder location, you can use my Excel Addin Folder Path file to find it.
- Close Excel, if it is running
- Right click on the add-in file and click Properties
- If the following warning appears at the bottom of the Properties window, click the Unblock box, to add a check mark — “This file came from another computer and might be blocked to help protect this computer”
- Click OK, to close the Properties window
- Then, start Excel, and the custom tab for the add-in should appear on the Excel Ribbon
If you had the problem of disappearing Excel add-ins, did that fix work for you?