Update data from external data sources in an Excel document in real time

A range of cells, an Excel table, a PivotTable report, or a PivotChart report can be connected to an external data source data source: A set of stored source information that is used to connect to a database. The data source contains the name and location of the database server, the name of the database driver, and the information required to log on to the database. ）。 You can refresh the data to update the data for this external data source. Each time you refresh the data, you can see the latest version of the information in the data source, including any changes to the data.

Security Currently, your computer may be disabling the connection to external data. To refresh the data when you open the workbook, you must use the Trust Center column to enable the data connection or to place the workbook in a trusted location.

　　Refresh data automatically when you open a workbook

You can automatically refresh the external data range when you open the workbook, and you can choose to reduce the size of the workbook file by selecting not to save the external data when you save the workbook.

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 Use Status tab.
4. Select the Refresh data when opening file check box.
5. If you want to save the query definition while saving the workbook, but do not save the external data, select the Remove external data from the query table check box before saving the worksheet.

Note to refresh the data in the PivotTable report when you open the workbook, you can also use the data in the PivotTable Data section on the Data tab of the PivotTable Options dialog box "Refresh data when opening a file."

　　Refresh multiple external data ranges

• On the Data tab, in the Connections Group, click the arrow next to refresh , and then click Refresh All.

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

　　Refresh the data in the 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. Select the text file in the Import text File dialog box, and then click Import.

　　Automatically refresh data on a regular basis

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 Use Status tab.
4. Select the refresh frequency check box, and then enter the number of minutes between each refresh operation.

　　Require password when refreshing external data range

　　The stored password is not encrypted and we do not recommend that you use it. 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 have up to 255 letters, numbers, spaces, and symbols. When you set up and enter a password, you must type the correct uppercase and lowercase letters. To connect, you can require that you enter a password before refreshing the external data range. 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.

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

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