Refresh connected (imported) data

A cell range, Excel table, PivotTable report, PivotChart report, text file, and Web query can all be connected to an external data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.). You can refresh the data in order to update data from this external data source. Each time you refresh the 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 the data when you open a workbook, you must enable the data connections by using the Trust Center bar, or you must place the workbook in a trusted location.

For more information, see Add, remove, or modify a trusted location for your files, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.

What do you want to do?


Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Do one of the following:

  • To refresh a specific data connection in the workbook, click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.
  • To refresh all data connections in the workbook, click Refresh All on the Data tab, in the Connections group.

 Note   If you have more than one workbook open, you must repeat the operation in each workbook.

Top of Page Top of Page

Refresh data from an imported text file

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

Top of Page Top of Page

Refresh an offline cube file

Refreshing an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), which re-creates the file by using 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 is 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

Top of Page Top of Page

Refresh data in an imported XML file

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.
  2. If the Developer tab is not available, do the following to display it:
    1. Click the File tab and then click Options.
    2. Click Customize Ribbon, and under Main Tabs, select the Developer check box.
    3. Click OK.
  3. On the Developer tab, in the XML group, click Refresh Data.

XML group

Top of Page Top of Page

Automatically refresh data when a workbook is opened

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data so that the file size of the workbook 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

  1. Click the Usage tab.
  2. Under Refresh control, select the Refresh data when opening the file check box.
  3. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

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

For more information, see Overview of PivotTable and PivotChart reports.

Top of Page Top of Page

Automatically refresh data at regular 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

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

Top of Page Top of Page

Require a password to refresh an external data range

Stored passwords are not encrypted and are not recommended. If your data source requires a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure does not apply to data that is retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  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

  1. Click the Definition tab.
  2. Clear the Save password check box.

 Note   Excel prompts you 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 then attempt a refresh operation.

Top of Page Top of Page

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

  1. Click the Usage tab.
  2. 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.

 Note   While you are recording a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) that includes a query, Excel does not run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.). Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True. For information about editing macros, see Visual Basic for Applications Help.

Top of Page Top of Page

Check the status of a refresh operation or cancel a refresh

Do one or more of the following:

Top of Page Top of Page

 
 
Applies to:
Excel 2010