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.
What happens when you refresh external data
1. Someone initiates a refresh operation to get the most recent data.
2. Connections are made to external data sources that are used in the workbook.
3. 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.
4. 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 Servicesis configured. For more information, contact a SharePoint administrator.
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.
|
View mode When you refresh a workbook in view mode, you simply update the data displayed in the workbook. You can do this in a browser window or in Excel, in read-only (not edit) mode. In this case, 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 recently edited the workbook. |
Edit mode When you refresh a workbook in edit mode, you open the workbook for editing (either in a browser window or in Excel). The workbook is tracked as a changed file. This means that your user credentials are displayed as someone who recently edited the workbook. If you’re editing a workbook and you refresh the data, your changes are automatically saved after the data refresh has occurred. Anyone who has permissions to view the workbook will see your changes as soon as the workbook is saved. |
Defining data refresh options in Excel
You must use Excel 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 a SharePoint library.
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.
|
Refresh option |
Description |
|---|---|
|
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. |
What to do if data refresh fails
Depending on how your environment is configured, data refresh might not occur. For example, if someone created a workbook that uses secure, external data connections to on-premises servers, those data connections will probably not work in Microsoft 365. If you run into a situation where you can’t refresh the data in a browser window, try opening the workbook in Excel.
You can also contact a SharePoint administrator.
