A cell range, Excel table, PivotTable report, or PivotChart report can all be connected to an external data source . You can refresh the data to update data from this external data source. Each time you refresh data, you see the most recent version of the information in the data source, including any changes that were made to the data.

Security Connections to external data may be currently disabled on your computer. To refresh data when you open a workbook, you must enable data connections by using the Trust Center bar, or put the workbook in a trusted location. For more information, see , , and .What do you want to do?

Automatically refresh data when a workbook is opened

You can refresh an external data range automatically when you open the workbook, and optionally save the workbook without saving the external data, so that the workbook file size is reduced.

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    excel ribbon image

  3. Click the Usage tab.
  4. Select the Refresh data on file open check box.
  5. If you want to save the workbook with the query definition but without the external data, select the Remove external data from query table before saving worksheet check box.

Note To refresh data when the workbook is opened for a PivotTable report, you can also use the Refresh data when opening the file check box under the PivotTable Data section on the Data tab of the PivotTable Options dialog box. For more information, see .

Refresh multiple external data ranges

If you have more than one workbook open, you must click Refresh All in each workbook to refresh external data.

Refresh data in an imported text file

  1. Select the worksheet with the imported text file.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.
  3. In the Import Text File dialog box, select your text file, and then click Import.

Automatically refresh data at regular time intervals

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    excel ribbon image

  3. Click the Usage tab.
  4. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

Require a password to refresh an external data range

Stored passwords are not encrypted and not recommended. If your data source requires a password to connect to it, you can require that the password is entered before the external data range can be refreshed. This procedure does not apply to data retrieved from a text file (*.txt) or a Web query (*.iqy).

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    excel ribbon image

  3. Click the Definition tab.
  4. Clear the Save password check box.

Note Microsoft Office Excel prompts for the password only the first time that the external data range is refreshed in each Excel session. The next time that you start Excel, you will be prompted for the password again if you open the workbook that contains the query and attempt a refresh operation.

Run a query in the background or while you wait

Running a query in the background enables you to use Excel while the query runs.

Note You cannot run an OLAP query in the background.

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

    excel ribbon image

  3. Click the Usage tab.
  4. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

Notes:

Check the status of or cancel a refresh operation

Refresh an offline cube file

Refreshing an offline cube file, which re-creates it with the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

  1. Click the PivotTable report that's based on the offline cube file.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

    excel ribbon image

See also: