Manage external data ranges and their properties

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.

Learn more about external data ranges and their properties

An external data range (also called a query table) 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 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.

Using templates with external data ranges

If you want to share a summary or a report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a report template. A report template lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

Find an external data range

1. Select the worksheet in which you want to search for an external data range.

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

Change the name of an external data range

1. On the Formulas tab, in the Defined Names group, click Name Manager.

   

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

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

Change the underlying query for an external data range

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.

Change the underlying query for an external data range created from Microsoft Query

1. Click a cell in the external data range for which you want to change the underlying query.

2. On the Data tab, in the Connections group, click Properties.

   

3. In the External Data Properties dialog box, click Connection Properties .

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

Change the underlying query for an external data range created from an imported text file

1. Click a cell in the external data range for which you want to change the underlying query.

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

   

3. In the Workbook Connections dialog box, click Properties.

4. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.

5. In the Import Text File dialog box, click Import.

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

Change the underlying query for an external data range created from a Web query

1. Click a cell in the external data range for which you want to change the underlying query.

2. On the Data tab, in the Connections group, click Properties.

   

3. In the Workbook Connections dialog box, click Properties.

4. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.

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

Change the underlying query for an external data range created by using the Data Connection Wizard

1. Click a cell in the external data range for which you want to change the underlying query.

2. On the Data tab, in the Connections group, click Properties.

   

3. In the External Data Properties dialog box, click Connection Properties .

4. In the Connection Properties dialog box, click the Definition tab.

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

Copy an external data range and its underlying query

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

2. On the Home tab, in the Clipboard group, click Copy.

   

3. Switch to the workbook in which you want to paste the external data range.

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

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

Specify how new data is added to an external data range

You can control how to handle a smaller or larger data set that is returned to Excel when data is refreshed.

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

   

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

Specify data formatting and layout

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

   

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

     Note: This check box is only available for an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically.

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

Copy formulas when an external data range expands

Note: The following procedure applies only to an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically. All external data ranges that are created by using the user interface are created as Excel tables. These tables automatically expand when new rows are added and fill down calculated columns.

1. Enter a formula in a cell that is adjacent to the first row of data in the external data range.

   Note: The first row of data may be the first or second row in the external data range, depending on whether the first row contains headers.

2. Select the cell and double-click the fill handle to copy the formula to all rows in the external data range.

3. Click a cell in the external data range.

4. On the Data tab, in the Connections group, click Properties.

   

5. In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.

   Note: If the external data range expands when you refresh, Excel copies only those formulas that are immediately adjacent to or within the external data range.

Freeze data in an external data range

Freezing an external data range retains the data but not its underlying query, so a frozen external data range cannot be refreshed.

Freeze data in an Excel table

1. Click the worksheet that contains the Excel table from which you want to remove the data connection.

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

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

Freeze data in a range

Note: The following procedure applies only to an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically. All external data ranges that are created by using the user interface are created as Excel tables. These tables automatically expand when new rows are added and fill down calculated columns.

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

   

2. Under Query definition, clear the Save query definition check box.

Note:  When you save your workbook, the underlying query is deleted from the workbook. However, the saved database query files (.dqy or .odc files) are not deleted. If you saved the query when you created it in the Query Wizard or in Microsoft Query, the query file is saved on your computer, and you can use the query again to retrieve external data. For more information, see Microsoft Query Help.

Customize field names in an external data range

Note: The following procedure applies only to an external data range that is created by using the Data Connection Wizard or Microsoft Query (and not to an imported text file or Web query), an external data range converted from a Microsoft Office 2003 program, or an external data range created programmatically. All external data ranges that are created by using the user interface are created as Excel tables.

1. Insert a blank row above the external data range by doing one of the following:

   • Select a cell above which you want to insert the new row. Then, on the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Rows. Because you have selected one cell, one new row is inserted above the current row.

   • Click the row selector of the row above which you want to insert a new row. Then, on the Home tab, in the Cells group, click Insert. Because you have selected one entire row, one new row is inserted above the selected row.
     

2. Type the labels that you want in the cells in the blank row.

3. Click a cell in the external data range.

4. On the Data tab, in the Connections group, click Properties.

   

5. In the External Data Range Properties dialog box, under Data formatting and layout, clear the Include field names check box, and then click OK.

6. To remove the existing field names and refresh the external data range, click a cell in the external data range, and then click Refresh Data .

Note: When you retrieve data from a database, any changes to column names in Microsoft Query are retained in the external data range. For information about changing column names in the query, see Microsoft Query Help.

Delete an external data range

1. Click the worksheet that contains the external data range that you want to delete.

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

3. To delete the external data range, press DELETE.

4. To delete the underlying query, click Yes when Excel prompts you.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.