When you use a workbook in the browser, you are viewing data that is either native or external. Native data is stored directly in the workbook, and does not require an external data connection. External data is stored someplace else, such as on a server, and requires a connection to an external database.
In this article
Overview of data refresh
The following illustration explains the basic process of what happens when you refresh data connected to an external data source.
Someone initiates a refresh operation to get the most recent data.
Connections are made to external data source used in the workbook.
Many workbooks contain connections to multiple data sources, such as cubes that are in SQL Server Analysis Services, tables that are stored in SQL Server, data that is available through an OData data feed, and so on.
Depending on the data connection and how the environment is configured, the workbook updates to display the most recent data.
Important Depending on how your particular environment is configured, you might or might not be able to refresh the data in a workbook that you are viewing in a browser window. For example, the ability to refresh external data depends, in large part, on how Excel Services is configured. For more information, contact a SharePoint administrator.
Top of Page
What to do if data refresh fails
Depending on how your environment is configured, data refresh might not occur. For example, if someone created an Excel workbook that uses secure, external data connections to on-premises servers, those data connections will probably not work in Office 365.
If you are viewing a workbook that contains unsupported data connections in a browser window, data refresh will not occur. If this happens, you can try editing a workbook in Excel.
To edit a workbook in Excel
- Save a copy of the workbook to your computer.
- Open the workbook in Excel.
- If you get a message indicating that the workbook is in read-only mode, choose either Edit Workbook or Enable Editing. The workbook opens for editing.
You can also contact a SharePoint administrator.
Top of Page
Refreshing data in view mode and edit mode
If you are viewing a workbook in a browser window, and you want to refresh the data, you can typically do this in one of two modes: view mode or edit mode.
When you refresh a workbook in view mode, the workbook simply updates and displays the most current information. The workbook is not tracked as a changed file. This means that in the location where the workbook is stored, your user credentials are not displayed as someone who edited the workbook.
To refresh data in a browser window
- In the browser window, locate the Data menu.
- On the Data menu, choose Refresh All Connections. The workbook is updated to display the most current information.
When you refresh a workbook in edit mode, the workbook updates and it is tracked as a changed file. This means that in the location where the workbook is stored, your user credentials are displayed as someone who has edited the workbook.
In addition, if you’re editing a workbook and you refresh the data, your changes are automatically saved after data refresh occurs. Anyone who has permissions to view the workbook will see your changes as soon as the workbook is saved. Workbooks are automatically saved every few minutes.
To edit a workbook in a browser window
- In the upper left corner of the screen, locate the Edit Workbook tab.
- On the Edit Workbook tab, select Edit in Excel Web App. The workbook opens for editing in the browser window.
Top of Page
Defining data refresh options in Excel
You must use Excel 2013 to define external data connections for a workbook. You cannot define such settings while viewing a workbook in a browser window. The settings that you specify by using Excel are preserved when you publish a workbook to SharePoint Server.
To specify data refresh options, choose the Properties button for a particular data connection in Excel. The following table provides a brief description of various data refresh options that you can select.
Table 1: Data refresh options for Excel data connections.
|Refresh every __ minutes
||This option causes the workbook to attempt to refresh data at intervals of time that you specify (in minutes).
|Refresh data when opening the file
||This option causes the workbook to attempt to refresh data automatically when the workbook is opened.
|Refresh this connection on Refresh All
When selected, this option refreshes the data when you or other users click the Refresh All button.
When unselected, this option prevents this connection from refreshing data when you or others click the Refresh All button. This is useful when you want to avoid data refresh errors, such as timeout errors for a particular connection in Excel Services.