You can change the properties of an external data range to control data that has been imported into a worksheet through a connection to an external data source, such as a database, a Web query, or a text file.
An external data range is a defined name or table name that defines the location of the data that is brought into a worksheet. When you connect to external data, Excel automatically creates an external data range. The only exception to this is a PivotTable report that is connected to a data source — a PivotTable report does not use an external data range. In Excel, you can format and lay out an external data range or use it in calculations, as with any other data.
External data range properties
An external data range has additional properties (not to be confused with query or connection properties) that you can use to control the data, such as the preservation of cell formatting and column width. You can change these external data range properties by clicking Properties in the Connections group on the Data tab. Then make your changes by doing the following:
-
For external data ranges that are created from Microsoft Query and the Data Connection Wizard, use the External Data Properties dialog box.
-
For external data ranges that are created from an imported text file or Web query that retrieves HTML data, use the External Data Range Properties dialog box.
-
For external data ranges that are created from a Web query that retrieves XML data, use the XML Map Properties dialog box.
-
Select the worksheet in which you want to search for an external data range.
-
On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range that you want.
-
Select Formulas > Name Manager.
-
In the Name Manager dialog box, click the name of the external data range, and then click Edit. You can also double-click the name.
-
In the Edit Name dialog box, type the new name for the reference in the Name box.
Note: The Close button closes only the Name Manager dialog box. You don't have to click Close in order to commit changes that you already made.
You can change the underlying query for an external data range that was created from Microsoft Query, an imported text file, a Web query, or the Data Connection Wizard.
-
Click a cell in the external data range for which you want to change the underlying query.
-
Select Data > Properties.
-
In the External Data Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
For more information about constructing and editing queries in Microsoft Query, see Microsoft Query Help.
-
Click a cell in the external data range for which you want to change the underlying query.
-
Select Data > Connections & Queries > Connections tab, right click the connection you want, and then select Properties.
-
In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
-
In the Import Text File dialog box, click Import.
-
Make changes to the imported text file in the Text Import Wizard, and then click Finish.
For more information about importing text files, see Import or export text files.
-
Click a cell in the external data range for which you want to change the underlying query.
-
Select Data > Properties.
-
Select Data > Connections & Queries > Connections tab, right click the connection you want, and then select Properties.
-
In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
-
Make changes to the Web query in the Edit Web Query dialog box, and then click Finish.
For more information about creating and editing Web queries, see Connect to a web page.
-
Click a cell in the external data range for which you want to change the underlying query.
-
Select Data > Properties.
-
In the External Data Properties dialog box, click Connection Properties
-
In the Connection Properties dialog box, click the Definition tab.
-
Do one of the following:
-
In the Command type box, click Table and then, in the Command text box, change the value to the name of an appropriate table, view, or query.
-
In the Command type box, click SQL or Default and then, in the Command text box, edit the SQL statement.
Note: Depending on how the connection was defined, the Command type box may be unavailable (it appears dimmed).
-
-
On the formula bar, click the arrow next to the Name Box, and select the name of the external data range that you want to copy.
For an Excel table, select the name of the range, and then press CTRL+A to select the table headers.
If you want to include column labels or formulas that are not part of the external data range, select the cells that contain the column labels or formulas that you want to copy. Click the arrow next to the Name Box on the formula bar, and click the name of the external data range that you want to copy.
-
Select Home > Copy.
-
Switch to the workbook in which you want to paste the external data range.
-
Click the upper-left cell of the paste area.
To ensure that the external data does not replace existing data, make sure that the worksheet has no data under or to the right of the cell that you click.
-
On the Home tab, in the Clipboard group, click Paste.
Note: If you copy only part of an external data range, the underlying query is not copied, and the copied data can't be refreshed.
You can control how to handle a smaller or larger data set that is returned to Excel when data is refreshed.
-
Select Data > Properties.
-
In the External Data Range Properties dialog box, under If the number of rows in the data range changes upon refresh, click one of the following:
-
Insert cells for new data, delete unused cells
Notes:
-
When one or more rows are added in the data source, cells directly under the external data range move down, but cells to the right of the external data range do not move.
-
When one or more rows are deleted in the data source, cells directly under the external data range move up, but cells to the right of the external data range do not move.
-
-
Insert entire rows for new data, clear unused cells
Notes:
-
When one or more rows are added in the data source, cells directly under and to the right of the external data range move down.
-
When one or more rows are deleted in the data source, cells directly under and to the right of the external data range do not move.
-
-
Overwrite existing cells with new data, clear unused cells
Notes:
-
When one or more rows are added in the data source, cells directly under the external data range are overwritten, but cells to the right of the external data range do not move.
-
When one or more rows are deleted in the data source, cells directly under and to the right of the external data range do not move.
-
-
-
Select Data > Properties.
-
Under Data formatting and layout, do one or more of the following:
-
To include field names as the first row, select the Include field names check box.
-
To add a column of row numbers, select the Include row numbers check box.
Note: This check box is not available for an imported text file, XML file, or Web query.
-
To preserve the cell formatting that you apply, select the Preserve cell formatting check box.
Note: For a Web query, this option is automatically cleared when you select Full HTML Formatting in the Options dialog box. You access the Options dialog box from the Edit Query dialog box.
-
To preserve the column widths that you set, select the Adjust column width check box.
-
To preserve the column sorting, filtering, and layout that you apply, select the Preserve column sort/filter/layout check box.
Note: This check box is not available for a Web query.
-
To preserve the column filtering that you apply, select the Preserve column filter check box.
Note: This check box is available only for a Web query that is based on XML data.
-
Freezing an external data range retains the data but not its underlying query, so a frozen external data range cannot be refreshed.
-
Click the worksheet that contains the Excel table from which you want to remove the data connection.
-
On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range from which you want to remove the data connection. The data range is then selected.
-
On the Tools tab, in the External Table Data group, click Unlink. The data range remains and still bears the same name, but the connection is deleted.
-
Click the worksheet that contains the external data range that you want to delete.
-
On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range that you want to delete.
If the external data range is an Excel table, press CTRL+A to select the entire table.
-
To delete the external data range, press DELETE.
-
To delete the underlying query, click Yes when Excel prompts you.
