Refresh an external data connection

You can connect your Excel workbook to an external data source, such as a SQL Server database, an OLAP cube, or even another Excel workbook. This connection can appear in your workbook as a table, PivotTable report, or PivotChart.

You can keep the data in your workbook up to date by "refreshing" the link to its source. Each time you refresh the connection, you see the most recent data, including anything that's new or has been deleted.

• Refresh a SQL Server data connection

• Refresh an OLE DB data connection

• Refresh a Windows Microsoft Data Market feed

• Refresh an Access data connection

• Refresh data connected to another workbook

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

• Refresh data from an imported text file

• Refresh Power Pivot data

• Update (refresh) data in a PivotTable

• Refresh an offline cube file

• Refresh data in an imported XML file

• Automatically refresh data when a workbook is opened

• Automatically refresh data at regular intervals

• Require a password to refresh an external data range

• Run a query in the background or while you wait

• Check the status of a refresh operation or cancel a refresh

Top of Page

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

1. Click any cell in the range or table that contains the link to the external data.

2. Click Data > Connections, and click the arrow next to Refresh All.

1. To update only the selected data, click Refresh.

You can also right-click a cell in the range or table, and then click Refresh.

1. To update all the data connections in the workbook, click Refresh All.

Note: If you have more than one workbook open, you'll need to repeat the operation in each workbook.

Top of Page

Refresh data from an imported text file

1. Click any cell in the range or table that contains the link to the imported text file.

2. On the Data tab, in the Connections group, click the arrow next to Refresh All.

1. To update only the selected data, click Refresh.

2. To update all the data connections in the workbook, click Refresh All.

You can also right-click a cell in the range or table, and then click Refresh.

1. In the Import Text File dialog box, browse to your text file and then click Import..

Top of Page

Refresh Power Pivot data

1. On the Data tab, in the Connections group, click Connections.
   

1. In the Workbook Connections dialog box, choose the connection for your Power Pivot data and click Refresh.

Top of Page

Refresh an offline cube file

Refreshing an offline cube file, which recreates 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 don't need immediate access to other files in Excel, and make sure you have adequate disk space to save the file again.

1. Click the PivotTable connected to the offline cube file.

2. Click Data > Connections, click the arrow next to Refresh All, and then click Refresh.

   

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 isn't available, do the following to display it:

   1. Click File > 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.

   

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 to shrink the size of the file.

1. Click a cell in the external data range.

2. Click Data > Connections , click the arrow next to Refresh All, and then click Connection Properties.

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.

Top of Page

Automatically refresh data at regular intervals

1. Click a cell in the external data range.

2. Click Data > Connections, click the arrow next to Refresh All, and then click Connection Properties.

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

Require a password to refresh an external data range

Stored passwords aren't encrypted, so they're not recommended. If your data source needs a password to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure doesn't apply to data 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.

It's 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. Click Data > Connections, click the arrow next to Refresh All, and then click Connection Properties.

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 you start Excel, you'll be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

Top of Page

Run a query in the background or while you wait

For very large data sets, consider running a background refresh. This returns control of Excel to you instead of making you wait several minutes or more for the refresh to finish.

1. Click a cell in the external data range.

2. Click Data > Connections, click the arrow next to Refresh All, and then click Connection Properties.

   Notes: 

   • You can't run an OLAP query in the background.

   • You can't run a query for any connection type that retrieves data for the Data Model.

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 record a macro that includes a query, Excel doesn't 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. 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

Cancel or check the status of a refresh

If your workbook is connected to a large data source, refreshing it might take a little longer than you expect. To check on the refresh, or to cancel it, do one of the following:

• Check the status of a query    A message appears on the Excel status bar to indicate that the query is running. Double-click the message to check the status of the query.

• Stop a background refresh    To stop a query that's running in the background, double-click the query status message on the status bar to display the External Data Refresh Status dialog box, and then click Stop Refresh.

• Stop a query    To stop a query from running when background refresh is turned off, press the Esc key.

Top of Page