A note on Instability

Unfortunately, while PowerPivot is an amazing desktop tool, it sometimes isn’t a terribly well behaved one. If you start experiencing unhelpful and errors such as “Object not set to a reference of an Object” when working with PowerPivot there are three actions you can take which can resolve this:

Remove the Add In

Step one is to remove the Add In (via File, Options, Add Ins, Manage COM Add Ins), restart Excel and Add the Add In back in again. Try reopening your workbook and seeing if it behaves. If so, all good. If not, escalate to the next step.

Repair Office

The next option is to close all Office programs and go to Add / remove programs in the Control Panel. Locate your installation of Office, right click and choose “Change”. An option will come up to Repair. Choose this and go and get a coffee. After this process, reopen your workbook and if it works, the problem is resolved (until next time…)

Try and fix the problem in the data model

If the data model will load in some form, you may have another option, though it involves deleting data and calculations - so take a backup first!

If it loads the grid but gets stuck on a particular table, go to diagram view and hunt for calculations with error markers – and delete them. Unfortunately the code is irretrievable.

If the grid still won’t load, try deleting relationships to the problem table in diagram view. If it still doesn’t load, try deleting the table.

After that the options are getting slim, and rebuilding the model is often the most effective way to get back to square one.