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.

---

• Learn more about external data ranges and properties

• Find an external data range

• Change the name of an external data range

• Change the underlying query for an external data range

• Copy an external data range and its underlying query

• Specify how new data is added to an external data range

• Specify data formatting and layout

• Copy formulas when an external data range expands

• Freeze data in an external data range

• Customize field names in an external data range

• Delete an external data range

---

Learn more about external data ranges and 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 (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the 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.

 Top of Page

Find an external data range

1. Select the worksheet in which you want to search for an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.).
2. On the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), click the arrow next to the Name Box, and then click the name of the external data range that you want.

 Top of Page

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.

 Top of Page

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.

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

 Note   For more information about creating and editing Web queries, see Get external data from 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).

 Top of Page

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 (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.).

   To ensure that the external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) 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 (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) is not copied, and the copied data can't be refreshed (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.).

 Top of Page

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.

 Top of Page

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.

 Top of Page

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. In Office Excel 2007, 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 (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) 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 (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.), Excel copies only those formulas that are immediately adjacent to or within the external data range.

 Top of Page

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. In Office Excel 2007, 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.

 Top of Page

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. In Office Excel 2007, 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.

   How to insert a blank row

   1. Do one of the following:
      • Insert new blank cells  Select a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert.
      • Insert a single row  Click a cell in the row immediately under where you want the new row. For example, to insert a new row above row 5, click a cell in row 5.
      • Insert multiple rows  Select rows immediately under where you want the new rows. Select the same number of rows as you want to insert.
      • Insert a single column  Click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.
      • Insert multiple columns  Select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.
   2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click one of the following: Insert Cells, Insert Sheet Rows, or Insert Sheet Columns.

      If you are inserting blank cells, click Shift cells right or Shift cells down to define where you want the existing cells to move.

    Tip    If there is formatting, you can use the Format Painter in the Clipboard group on the Home tab to choose how to set the formatting of the inserted cells, rows, or columns.

   ---

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.

 Top of Page

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.

 Top of Page