Refreshing data in a workbook in a browser window

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

The basic process of refreshing external data

Callout 1 Someone initiates a refresh operation to get the most recent data.
Callout 2 Connections are made to external data sources that are used in the workbook.
Callout 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.
Callout 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 Services is configured. For more information, contact a SharePoint administrator.

Top of Page 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.

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 occured. Anyone who has permissions to view the workbook will see your changes as soon as the workbook is saved.

Top of Page Top of Page

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 (SharePoint library: A library is a special type of SharePoint list that stores files as well as information about files. You can control how files are viewed, tracked, managed, and created in libraries.).

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 Office 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.

Top of Page Top of Page

 
 
Applies to:
Excel Online, OneDrive for Business, SharePoint Online Enterprise (E1), SharePoint Online Enterprise (E3 & E4), SharePoint Online Midsized Business, SharePoint Online Small Business, SharePoint Server 2013 Enterprise