How to fix #BLOCKED! errors in Excel
What to do if you see a #BLOCKED cell error message in Microsoft Excel. The fix is simple, if you can get past Microsoft’s unhelpful, er, ‘help’.
Open uṗ a workbook and see #BLOCKED in some cells.
The message ‘Check privacy settings’ is typical Microsoft message. It’s strictly correct but not very helpful. There are a lot of privacy options and they are buried deep in Excel.
A better message would be “Check Connected Services”.
What is #BLOCKED ?
The message appears in cells that use connected data services, most likely Stock and Currency Data Types. So functions such as StockHistory().
It means the Office Connected Services have been disabled so Excel can’t get the information from Microsoft to fill the cell with data.
Fixing #BLOCKED
Dive deep into Excel’s settings … File | Options | Trust Center | Privacy Options | Privacy Settings then scroll down to “Experiences that download online content”.
Check the box “Turn on experiences that download online content”.
The #BLOCKED cells should automatically update and fill with the latest yummy data.
Connected Services are ‘all or nothing’.
This error usually happens when someone turns off the ‘download online content’ or ‘All Connected Services’ options in one of the Office apps.
The Connected Services options apply across all Office apps (Word, Excel, PowerPoint and Outlook). In other words, disable the option in Word or PowerPoint and it’ll cause #BLOCKED errors in Excel.
There’s no way to fine-tune the Connected Services to particular Office programs or specific services (like, say, Linked Data Types). It’s all or nothing.
Connected Experiences now in Office 365
Excel’s Stock / Currency data (kinda, sorta) gets auto-refresh
The single cell trick to get an Excel stock or currency rate
Put the latest Stock and Fund prices in Excel workbooks