Change a Web query

You can change the address of the Web page you're querying, change the selection of data returned from the Web page, and change formatting and other settings for your Web query in the Edit Web Query dialog box. You can change how the query is refreshed and other properties of the data range returned by your query in the External Data Range Properties dialog box.

Change address, selection of data, or formatting

1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range).

   If you're not sure which region of the worksheet contains your query data, but you know the name of the query, click the Name box and select your query name from the list.

2. Click Edit Query on the External Data toolbar.

3. Do one or more of the following:

   Change the address for the Web page you want to query

   Select or type a new URL in the Address box.

   Change the selection of data returned from the Web page

   Click to cancel the selection of a selected table or click to select a table.

   If there are no next to tables on the page, click Show Icons at the top of the dialog box to display them.

   Change import and formatting settings

   1. Click Options.

   2. Do one of the following:

      Under Formatting, click one of the following:

      • None   Returns none of the formatting on the Web page you are querying, only text. Formatting that exists on the worksheet is applied.

      • Rich text formatting only   Returns the type of formatting that Microsoft Excel can reproduce most closely, such as font styles. Does not include HTML formatting, such as hyperlink formatting.

      • Full HTML formatting   Returns all HTML formatting that Excel supports, such as hyperlink formatting. This option clears the Preserve cell formatting check box in the External Data Range Properties dialog box.

      Under Import settings for preformatted <PRE> blocks, select one or more of the following:

      • Import <PRE> blocks into columns   Blocks of data surrounded by <PRE> tags will be imported into separate columns on the worksheet. Clear this box to return data into a single column.

      • Treat consecutive delimiters as one   If you have characters that define, or delimit, the text for each column in your <PRE> sections (such as commas), you can specify that when Excel encounters more than one of these delimiters together, they will be treated as one so that a blank column is not placed between each consecutive delimiter. This check box is available only when you select the Import <PRE> blocks into columns check box.

      • Use the same import settings for the entire section   Select to use your setting for Treat consecutive delimiters as one for all preformatted sections on the Web page. Clear this check box to use your setting for the first preformatted section only or if you want Excel to determine the best settings. This check box is available only when you select the Import <PRE> blocks into columns check box.

      Under Other Import settings, select one or more of the following:

      • Disable date recognition   Ensures that numbers on a Web page that appear similar to dates appear as numbers on the worksheet. For example, a sports standing score of 03-07 is recognized by Excel as the date March 7 unless this option is selected.

      • Disable Web query redirections   Ensures that the Web query is not redirected to a different data source than what you see on the Web page you're querying. Select this check box for compatibility with queries created in previous versions of Excel.

4. When you're done making changes in the Edit Web Query dialog box, click Import.

Change refresh options and other properties

1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range).

2. Click Data Range Properties on the External Data toolbar.

3. Under Query definition, make sure the Save query definition box is selected to allow future refreshes of the data and to have other options in the dialog box available. To prevent further refreshes of the data, clear the box.

   The Save password option is not applicable to Web queries, so appears disabled.

4. Under Refresh control, select one or more of the following:

   • Enable background refresh   Runs the query in the background so that you can continue to work in Microsoft Excel while the data is refreshing.

   • Refresh every x minutes   Allows you to set specific intervals of time in minutes at which Excel automatically refreshes the data.

   • Refresh data on file open   Automatically refreshes the data each time the file is opened.

   • Remove external data from worksheet before saving   Removes the external data, but not the query, from your worksheet before you save. The Refresh data on file open box must be selected for this option to be available.

5. Under Data formatting and layout, select one or more of the following:

   • Adjust column width   Automatically adjusts column widths to fit refreshed data.

   • Preserve cell formatting   When the data is refreshed, cell formatting changes you made in Excel are preserved. This option is automatically cleared, however, when you select Full HTML Formatting in the Options dialog box (Edit Query dialog box).

   • If the number of rows in the data range changes upon refresh   Specifies how to handle a smaller or larger data set returned to Excel when data is refreshed. You cannot select or clear this option; rather you must select one of three options in the dialog box.

   • Fill down formulas in columns adjacent to data   Copies formulas in columns to the right of the external data range to new rows of data added when the data is refreshed.

   Note   Include field names, Include row numbers, and Preserve column sort/filter/layout are not applicable to Web queries, and are disabled in the dialog box.