Amal Nagm

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.

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.

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.
3. In the Import Text File dialog box, select your text file, and then click Import.

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.

   

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 Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

      Note The Ribbon is a component of the Microsoft Office Fluent user interface.

3. On the Developer tab, in the XML group, click Refresh Data.

   

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.

   

3. Click the Usage tab.
4. Under Refresh control, select the Refresh data when opening the file check box.
5. 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

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.

   

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

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.

   

3. Click the Definition tab.
4. 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.

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.

   

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.

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.

Check the status of a refresh operation or cancel a refresh

Do one or more of the following:

• Check the status of a query A spinning icon appears on the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. Double-click the Background Refresh icon to check the status of the query.
• Stop a background refresh To stop a query that is running in the background, double-click the Background Refresh icon 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 Enable background refresh is turned off, press ESC.