This error can occur when Excel application pool account is stuck or when the document library containing the Excel file is not in a trusted location. here is the solution to resolve both options:
Recycle application pool
You will need fist to identify the Excel Calculation service:
Start IIS and select the SharePoint web services site.
- expand the site – you should see nodes with GUID numbers.
- Right click on each GUID service
- Select Explore
- You should get the folder and the web service name.
After identifying the GUID select the service and on the right nav select Advanced Settings, you should be able to identify the Application Pool for this service. now that you know which application pool you need to restart go to IIS applications pools and restart the service.
- Make sure that the setting on the document library where the excel is located is set to open document on the client:
- Document library settings
- Advanced settings
- Open document on the client
- Data refresh will happen every 5 min unless you change the settings in central admin manage service applications
- Central Administration: Manage Excel Services Application
- Make sure to add your excel file location to the trusted file location, sometimes a generic http://domainname or https://domainname should be entered.
- Click on trusted file location and change the refresh rate from 300 sec ( 5 min ) to 5 sec.