Troubleshoot Excel and the Web

Some of the Web queries provided with Microsoft Excel allow you to enter parameters when you run the query. For example, the MSN MoneyCentral Investor Stock Quotes query allows you to enter stock symbols to retrieve data for specific stocks. The New Web Query dialog box does not allow you to create Web queries that prompt for parameters. To create this type of Web query, you need knowledge of HTML, and the Web page must be set up to accept parameter input. If you have this information, you can save a Web query file that returns data from the Web page you want, and you can then modify the .iqy file in a text editing program, such as Notepad. For more information, consult the author of the Web page you want to query.

You may have turned off the option to show pictures in your browser. To show pictures in Microsoft Internet Explorer 5, do the following:

1. Click Internet Options on the Tools menu, and then click the Advanced tab.
2. Under Multimedia, select the Show pictures check box.
3. Click OK.

Without scripting, you can't automatically refresh an external data range in an interactive spreadsheet. To refresh the data, you must republish the spreadsheet from Microsoft Excel with the new data that you want to use.

To make it possible to refresh an external data range that you put on a Web page, publish or save the data as a PivotTable list. Users can then refresh the PivotTable list in the browser when the source data changes. For more information, see the Microsoft Office Web Component Help for PivotTable lists or spreadsheets. To display this Help, click an interactive spreadsheet or PivotTable list on your Web page in the Web browser, and then click the Help button on that control's toolbar.

You must use Microsoft Internet Explorer version 4.01 or later if you want to export data from your Web page. You can export only interactive PivotTable lists and spreadsheets.

Which programs are available from the Edit button in the Web browser depends on how the page was published.

If clicking the Edit button in your Web browser opens an Office program that you do not want to use, close the program, start the Office program you want to use, and open the Web page from there by doing the following:

1. Click Open on the File menu.

2. Locate and select the file you want to open.

3. Click the arrow next to Open at the bottom of the dialog box, and then click Open in program name.

If you publish or save Microsoft Excel data as a Web page and get a message that says the file name cannot be accessed, one or more of the following might be the reason:

• You're trying to publish or save a Web page that has the same file name as an existing HTML file. Specify a new name for your Web page file and publish again.
• The location you're trying to publish to is unavailable, the path name is incorrect, or the file is already open. Check the path name, and then make sure no one else has the file open.
• The amount of information you're saving might have exceeded the limit that can be accurately saved, especially if you're putting interactive data on the Web. Try to save a smaller amount of data.

Upgrade to Microsoft Internet Explorer version 4.01 or later    To view a Web page that was saved with interactive data and have all the text appear correctly, you must use Microsoft Internet Explorer 4.01 or later and have the Microsoft Office Web Components installed.

Modify cells that contain automatically wrapped text    You might have formatted cells with wrapped text before you published or saved the data as a Web page. In an interactive PivotTable list or spreadsheet on a Web page, text does not wrap within cells. To prevent text from being cut off when you publish or save it, you can shorten the text, use multiple cells for text, or widen the column in your worksheet, and then republish the data.

Do not use CSS for font formatting    If you are using a browser that doesn't support cascading style sheets (CSS), do the following:

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Browsers tab.
3. Clear the Rely on CSS for font formatting check box.
4. Republish your data.

Modify cells that contain automatically wrapped text    You might have formatted cells with wrapped text before you published or saved the data as a Web page. In an interactive PivotTable list or spreadsheet on a Web page, text does not wrap within cells. To prevent text from being cut off when you publish or save it, you can shorten the text, use multiple cells for text, or widen the column in your worksheet, and then republish the data.

Widen the column the text is in    If you publish a non-interactive range of cells where text in a cell overlaps another cell, the overlapping portion may not be published. Widen the column the text is in so that it does not overlap other cells.

Realign the text    If you see text that is cut off in a cell and you are using Microsoft Internet Explorer 5, the text was longer than the length of the cell when it was published, and it was right or center aligned. You can do the following:

1. Open the original workbook (.xls file) in Excel.
2. Click the cell that contains the text that is cut off.
3. On the Format menu, click Cells, and then click the Alignment tab.
4. In the Horizontal box, click Left (Indent) and then click OK.
5. Publish or save the data as a Web page again.

Note  In Microsoft Internet Explorer version 4.01 or earlier, centered, left-aligned, and right-aligned text automatically wraps within the cell in noninteractive Web pages.

You can't use rotated or vertical text when you publish or save Microsoft Excel data as a Web page. Rotated and vertical text are converted to horizontal text.

When you publish or save Microsoft Excel data to an existing Web page, Excel always appends the data to the bottom of the page.

To move the Excel data to another position on the Web page, you can open the Web page in a design program such as Microsoft FrontPage or Microsoft Access and rearrange the items.

You cannot publish or save Microsoft Excel data as a Web page if your worksheet or workbook is password protected. To publish or save your data, remove the password, and republish your data.

If you publish or save a worksheet or section of a worksheet or a PivotTable report with interactive spreadsheet functionality or PivotTable functionality, and the worksheet contains a chart, the chart is not saved on the Web page. To make the chart appear on the Web page, you must publish with chart functionality. The source data (that is, the worksheet or PivotTable list) is then published along with the chart.

Make sure you select only the chart    You must select only the chart, not the worksheet it appears on, to publish or save the chart as an interactive chart on a Web page.

Make sure the data is on one worksheet    The chart you are trying to publish or save might be based on data ranges from two separate worksheets. Make sure all source data for your chart is on a single worksheet.

Make sure noncontiguous data is added as a new series    Before you published the chart, you might have added data that was not contiguous with the original data for the chart and added the cells as new data points, rather than as a new series. If you add data to an existing chart, and the new range is not contiguous to the original range, make sure you add the cells as a new data series, not as new data points, before you publish the chart.

Some formatting and features are not retained when you publish or save a PivotTable report as a Web page. For example, indented formats aren't published.

To view interactive data on Web pages, you must use Microsoft Internet Explorer version 4.01 or later and have the Microsoft Office Web Components installed to view interactive Web pages. You can install these components by installing Office 2003 or, if your company has an Office 2003 site license, by downloading the Office Web Components from your corporate intranet.

The data you are trying to filter might contain merged cells. In an interactive spreadsheet on a Web page, you cannot filter a list that contains merged cells. Split the merged cells in your workbook and then republish the Web page.

To select a text hyperlink without jumping to the destination, do one of the following:

• Click a cell next to the cell that contains the hyperlink, and then use the arrow keys to move onto the cell that contains the hyperlink.
• Click the cell that contains the hyperlink, hold the mouse button down for at least a second, and then release the button.
• Right-click the cell that contains the hyperlink, and then click Edit Hyperlink on the shortcut menu.

• To select the graphic for a hyperlink without jumping to the destination, either hold down CTRL and click the graphic, or click Select Objects on the Drawing toolbar, and then click the graphic.

Remove the drive letter from server addresses    The drive letter that was mapped to the shared network directory when you created the hyperlink might no longer be mapped to the directory. To make the hyperlink independent of the current drive mappings, edit the destination for the hyperlink, and make sure you specify the address by using the server and share name rather than a drive letter. For example, you could replace G:\myfile.xls with \\server\share\myfile.xls.

Make sure the destination file locations are correct    Relative paths might be incorrect if the workbook or the destination files were moved since the workbook was last saved. Make sure that the locations of the destination files are correct.

Make sure your network is running properly    You might not have network access to the destinations of the hyperlinks. Make sure that your network is running properly and that you have access to the required network resources.

To open files on Internet sites, such as World Wide Web or FTP sites, you must have access to the Internet either through a direct network connection or through a dial-in Internet service provider.

If you have this access, the site might be busy; try to open the workbook later.

Make sure the destination file is available    The destination file for the hyperlink might have been deleted, moved, or renamed. You can reconnect the hyperlink by updating the path for the hyperlink to the new location.

Make sure you have Internet access    If the destination of the hyperlink is located on the Internet or the World Wide Web, you must have access to the Internet either through a direct network connection or through a dial-in Internet service provider. If you have this access, the site might be busy; try to open the workbook later.

Make sure your network is running properly    If the destination of the hyperlink is located on your network or intranet, check your network connections, and make sure your network server is running. See your network administrator to make sure you have access to the location of the destination file.

If you copy data containing hyperlinks to a workbook or open in Microsoft Excel a Web page containing hyperlinks, the hyperlinks might not appear correctly. A single cell can contain only one hyperlink. If more than one hyperlink or a hyperlink and extra text are placed in a single cell, the hyperlinks don't work.

To make a hyperlink work correctly, add it to the cell by using the Hyperlink command on the Insert menu.

When you insert a hyperlink to a new .htm file and then click the hyperlink to go to that Web page, the gridlines in the worksheet might not appear. To turn on the gridlines, do the following:

1. Open the Web page that the hyperlink goes to in Microsoft Excel.
2. On the Tools menu, click Options.
3. Click the View tab.
4. Under Window options, select the Gridlines check box.

The server at the destination or the destination site might be busy. Try the hyperlink again later, or try updating the linked data later. Experiment with updating links and clicking hyperlinks at different times of the day to determine when you're likely to get the best response. See your network administrator for information about the server.

When you save a Web page to a Web server, Microsoft Excel by default stores all supporting files— such as bullets, background textures, and graphics— in a separate subfolder. If you want to save supporting files in the same folder as the Web page, do the following:

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Files tab.
3. Clear the Organize supporting files in a folder check box.

   Note  This option applies to the current page and any future pages that you save in Excel.

If you use a long file name (a file name with spaces or more than eight characters) to save your Web page to a file server, users with Microsoft Windows 3.1 won't be able to find or open your Web page. Windows 3.1 recognizes and supports only short file names (8.3 notation). To always save files for Web pages with short file names, do the following:

1. On the Tools menu in Microsoft Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Files tab.
3. Clear the Use long file names whenever possible check box.

When you save a Web page, Microsoft Excel by default saves all supporting files— such as bullets, background textures, and graphics— in a supporting subfolder. If you move or copy your Web page to another location, you must also move the supporting folder so that you maintain all links to your supporting files.

When you save a Web page, Microsoft Excel doesn't automatically create a backup copy. The Always create backup option does not apply to HTML files. (To locate this option, click Save as on the File menu, click Tools, and then click General Options.)

To save a backup copy of a Web workbook, save the original file, and then click Save As on the File menu. In the File name box, type a new name for the backup copy and then click Save. Close the backup copy and reopen your original file to continue editing.

If you publish or save data on the Web and add interactivity with spreadsheet functionality, and your worksheet contains formulas that reference data that is not in your selected range, you can reduce the file size by not saving the external referenced data.

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the General tab.
3. Clear the Save any additional hidden data necessary to maintain formulas check box.

   Notes

   • This option applies to the current page and any future pages that you save in Excel.
   • If you choose not to save the hidden data, formulas that reference that data are replaced by the formulas' calculated values.

If the data you publish or save contains a reference to a cell or range of cells outside of the data you publish, Microsoft Excel, by default, saves the entire worksheet but only displays the cells in your selected range.

Depending on where your data started in your worksheet, the upper-left cell on your Web page might not be cell A1. To make the upper-left cell be A1, do the following:

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the General tab.
3. Clear the Save any additional hidden data necessary to maintain formulas check box.

   Notes

   • This option applies to the current page and any future pages that you save in Excel.
   • If you choose not to save the hidden data, formulas that reference that data are replaced by the formulas' calculated values.

Check the Web page you are querying to make sure it is located in the same place and still contains the same information, and make changes if necessary. To make changes to the query:

1. Click a cell in the region of your Web query.

2. Click Edit Query on the External Data toolbar.

3. Make changes in the Edit Web Query dialog box.

When a Web page contains numbers that appear similar to dates, such as part number 01-01-23, Microsoft Excel might interpret these numbers as dates.

To have Excel interpret these numbers as numbers, click a cell in the external data range, and then click Edit Query on the External Data toolbar. In the Edit Web Query dialog box, click Options, and then select the Disable date recognition check box. Now when you run the Web query, dates on the Web page appear as dates in Excel, and numbers that are similar to dates appear correctly in Excel.

Make sure your links are working properly    A red X indicates a missing graphic. If you move or copy your Web page to a new location without moving the supporting files, the links to graphics— such as photos, bullets, and background textures— may be broken.

To automatically make the paths for the linked pictures and hyperlinks relative when you save Web pages in Microsoft Excel, do the following:

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Files tab.
3. Select the Update links on save check box.

   Notes

   • When you select this option, Excel does not actually move or copy the supporting files when you move or copy a Web page. Rather, Excel updates the addresses to the relative links on the page.
   • You must save the moved or copied Web page for the updating to occur.

You might have changed a linked picture    If you make a visual change to a linked picture, such as adding a shadow, border or outline, fill, or contrast or brightness adjustment, then Excel will create another picture with those changes for displaying in a Web page. When you view the Web page in Microsoft Internet Explorer 4 or earlier, the picture will appear with the changes but the link to the original picture will be broken. To fix the link, select the picture and click Reset Picture on the Picture toolbar.

There are several reasons why graphics may not be appearing correctly on your Web page.

Adjust your browser settings    Because different browsers support different versions of HTML and HTML extensions, text and graphics may look different in one computer's browser than they do in another. In some cases, graphic images appear as blanks because the browser is set to not display graphics. You can sometimes make missing graphics appear in your browser by adjusting the option to display graphics.

Test your page with different browsers    When you create Web pages, consider testing them in different Web browsers— some of which are available for downloading from the Web— and with different monitor settings, to make sure all of your page elements appear correctly.

Graphics might be turned off in your Web browser    To make sure that graphics are being downloaded, do the following:

1. Open your Web page in Microsoft Excel.
2. On the Tools menu, click Options, and then click the General tab.
3. Click Web Options, and then click the Browsers tab.
4. Clear the Rely on VML for displaying graphics in browsers check box.

You might have used the wrong picture format    If you link a graphic to a Web page, and the graphic format isn't supported by your browser, your graphics won't show. Make sure that the linked picture is in a graphic format that is compatible with your browser. The graphic formats JPG and GIF are compatible with most browsers.

You might have changed a linked picture    If you make a visual change to a linked picture, such as adding a shadow, border or outline, fill, or contrast or brightness adjustment, then Excel will create another picture with those changes for displaying in a Web page. When you view the Web page in Microsoft Internet Explorer 4 or earlier, the picture will appear with the changes but the link to the original picture will be broken. To fix the link, select the picture and click Reset Picture on the Picture toolbar.

Reset your video resolution when you design a Web page    Graphics and text can wrap differently, depending on the video resolution (the size of the screen elements relative to the screen size). The screen size you specify can affect the size and layout of images in a workbook, including the gradient page backgrounds. The pixel size affects the size of graphics relative to the size of text on the screen.

If your graphics and text don't look the way you expect, change the screen and pixel settings by doing the following:

1. On the Tools menu in Microsoft Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Pictures tab.
3. Under Target monitor, do one or both of the following:

   • In the Screen size box, select a screen size.

   • In the Pixels per inch box, select a pixel size.

   Note  This option applies to the current page and any future pages that you save in Excel.

Adjust your browser settings    Because different browsers support different versions of HTML and HTML extensions, text and graphics may look different in one computer's browser than they look in another. You can sometimes change the appearance of graphics and text by adjusting the custom options in your browser, such as the default text and background colors and the option to display graphics.

Test your page with different browsers    When you create Web pages, consider testing them in different Web browsers— some of which are available for downloading from the Web— and with different monitor settings, to make sure all of your page elements appear correctly.

To use Microsoft Excel interactive data on the Web, your users must have Office 2003 or access to an Office 2003 license and the Office Web Components installed.

If you or your users have the appropriate Office 2003 license, you can specify that the Web components download to the user's machine when the user opens your Web page.

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Files tab.
3. Select the Download Office Web Components check box.
4. In the Location box, type the path for the file server, such as file:\\my server\my folder.

Note  Users must have access to the file server and the Web Components cannot be downloaded through firewalls.

When you open a Web page, Microsoft Excel tries to determine the encoding used for that page. If Excel displays the wrong characters when you open the page in a Web browser, you can select the encoding that you think the page is using. Try each encoding until you can read the text.

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Encoding tab.
3. In the Reload the current document as list, select the language you think the page is encoded in.

If the wrong font for a Web page or plain text file is applied when you import a Web page into Microsoft Excel, you can select the character set that you think the page is encoded in. Then you can choose from a list of fonts that depends on which character set you chose.

1. On the Tools menu in Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Fonts tab.
3. In the Character set list, click the character set that you think the Web page uses.
4. In the Proportional font and Size lists, click a font and size for normal text.
5. In the Fixed-width font and Size lists, click a font and size for monospace text.

Note  These options apply to the current page and any future pages that you import in Excel.

If you use a browser that does not support cascading style sheets (CSS), you can reduce the size of your file when you save or publish a Web page by saving only in HTML format.

1. On the Tools menu in Microsoft Excel, click Options, and then click the General tab.
2. Click Web Options, and then click the Browsers tab.
3. Clear the Rely on CSS for font formatting check box.

   Note  This option applies to the current page and any future pages that you save in Excel.

In the Open dialog box (File menu), when you select a Hypertext Markup Language (HTML) file (a Web page) and click Open or double-click the file, the file opens in the program that the file was created in. For example, if you attempt to open in Microsoft Excel an HTML file that was created in Microsoft Word, the file opens in Word instead.

To open such an HTML file in Excel, select the file in the Open dialog box, click the arrow next to Open at the bottom of the dialog box, and then click Open in Microsoft Excel.