Troubleshoot spreadsheets
Entering and editing data and formulas
I can't change cells in the spreadsheet.
The cells might be locked and protected. At design time, you can unlock and/or unprotect the spreadsheet.
This procedure can be performed at design time only. Browser users do not have access to this option.
In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
On the toolbar, click Commands and Options
, and then click the Protection tab.
Under Sheet, clear the Protect active sheet check box.
If you want users to be able to insert, remove, or rename sheets, or use the Commands and Options dialog box, check to be sure that the Insert, remove, or rename sheets check box and the Commands and Options dialog box check box are selected.
I can't wrap text within cells in a spreadsheet.
Wrapped text within cells is not supported in the Microsoft Office Spreadsheet Component. If you published a spreadsheet from Microsoft Excel that had wrapped text in it, the text will appear on one line. To correctly display the text, you can shorten the text or use multiple cells for the text, or you can widen the column so that all of the text shows.
There is no formula bar in my spreadsheet.
Unlike Microsoft Excel, the Microsoft Office Spreadsheet Component does not provide a formula bar to edit cell contents. However, you can edit the contents of a cell by double-clicking the cell or by selecting the cell and pressing F2.
I can't set a password for a spreadsheet.
You cannot set a password for a spreadsheet. Depending on where you publish your Web page, you might be able to set a password that would be required to open the page that the spreadsheet is on. For example, you might be able to set a password for access permission on your Web server. For more information, see your Web site administrator.
I can't change a hyperlink that I published from Microsoft Excel.
Re-enter the hyperlink If you are modifying a hyperlink that was published from an Excel worksheet and was originally created by using the Insert Hyperlink dialog box in Excel, you can change the text that's displayed for the hyperlink, but you cannot change the address that the hyperlink goes to. To change the address, delete the hyperlink and then insert a new hyperlink.
Change the address in Excel and republish In the original Excel worksheet, you can modify both the displayed text and the hyperlink address. You can then republish the data to the Web. For more information, see Help in Excel.
I can't control the size of my spreadsheet.
Your spreadsheet might be set to size automatically. When the Autofit Spreadsheet check box on the Advanced tab of the Commands and Options dialog box is selected, the spreadsheet expands and shrinks on the Web page as necessary when you insert or delete rows and columns or change row heights and column widths. Spreadsheets that are published from Microsoft Excel have automatic sizing on by default. You can change whether automatic sizing is on or off at design time.
When the spreadsheet is set to the size of the viewable range and you turn automatic sizing on, the spreadsheet expands and shrinks on the Web page depending on content. It changes size as necessary when you insert and delete rows and columns, or when you change row heights or column widths.
In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
On the toolbar, click Commands and Options
Select or clear the Autofit Spreadsheet check box.
If you want to prevent the spreadsheet from expanding beyond a specified size, type a value — either in pixels or as a percentage of the entire Web page — in the Max height box or the Max width box, or in both boxes. For example, to allow the spreadsheet to expand up to half the width of the Web page, type 50% in the Max width box. To limit the width of the spreadsheet to 300 pixels, type 300 in the Max width box.
I can't control whether scroll bars are displayed or hidden in my spreadsheet.
When your spreadsheet is set to size automatically (that is, the Autofit Spreadsheet check box on the Advanced tab of the Commands and Options dialog box is selected), scroll bars appear when the spreadsheet reaches the maximum height or width set in the Commands and Options dialog box, regardless of whether you set scroll bars to be displayed or hidden. If you want to control how scroll bars are displayed, turn off automatic sizing.
When the spreadsheet is set to the size of the viewable range and you turn automatic sizing on, the spreadsheet expands and shrinks on the Web page depending on content. It changes size as necessary when you insert and delete rows and columns, or when you change row heights or column widths.
In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
On the toolbar, click Commands and Options
Select or clear the Autofit Spreadsheet check box.
If you want to prevent the spreadsheet from expanding beyond a specified size, type a value — either in pixels or as a percentage of the entire Web page — in the Max height box or the Max width box, or in both boxes. For example, to allow the spreadsheet to expand up to half the width of the Web page, type 50% in the Max width box. To limit the width of the spreadsheet to 300 pixels, type 300 in the Max width box.
Filtering, sorting, and finding data
Data does not sort correctly in a spreadsheet.
If sorting a spreadsheet does not produce the results you expect, check the following:
Check your selection before sorting When you select a range of cells to sort, the spreadsheet sorts everything within that range, and it sorts only that range. This means that if your header row is within the selection, the header row will be sorted. It also means that if you select one column of data within a list that must be sorted, only that column will be sorted and not the entire list. Try to select a single cell within the range you want to sort, but not within a header row. The spreadsheet will automatically detect the data to sort and will exclude the header row. If this does not work properly for your data, try to select the entire range you want to sort, without selecting the header row.
Check that numbers are in a numeric format If a value is sorted out of order, the cell might be formatted as text and not as a number. For example, negative numbers from some accounting systems become text when the data is imported into a spreadsheet. To apply the correct formatting, click the cell, and then change the format of the number.
Check that dates and times are formatted correctly Dates and times are treated as numbers. When you type a date or time into a cell, the cell's format changes from the General number format to a built-in date or time format. For dates and times to sort correctly, all dates and times in a column must have the same date or time format. To apply the correct formatting, click the cell, and then change the format of the number.
Remove any leading spaces Make sure that no leading spaces have been inserted before data. Remove the leading spaces before sorting the data.
Check the locale setting Sort orders vary by locale setting. Make sure that you have the proper locale setting in the Regional Settings or Regional Options in Control Panel. For information about changing the locale setting, see Windows Help.
Make sure you sort multiple columns in the correct order When you sort on more than one column, the order in which you sort determines how the data will be organized. Sort lower-level (inner) categories first, and then sort higher-level (outer) categories.
My spreadsheet list does not show all of my data.
The list might be filtered using the AutoFilter command to display specific rows. If an arrow appears to the right of the top cell in each column, do one of the following:
- Click the arrow, and then click Show All.
- Make sure the AutoFilter button
on the toolbar is not selected.
I have merged cells, and I can't filter my spreadsheet.
The Microsoft Office Spreadsheet Component does not support the ability to filter lists that contain merged cells. If your spreadsheet contains merged cells, unmerge the cells and then try to use the AutoFilter command again.
The Find Next command is not finding data in my spreadsheet.
If you have selected a range of data, the Find Next command searches only in that selection for the value you specify. To find data anywhere in the spreadsheet, select a single cell before clicking Find Next.
I published a spreadsheet from Excel, and the AutoFilter drop-down list is wrong.
In Microsoft Excel, you can create AutoFilter criteria that cannot be shown in the spreadsheet AutoFilter drop-down list. For example, there is no equivalent for "Top 10..." or "begins with" criteria in the spreadsheet.
When you publish an AutoFilter range in Excel that uses criteria that cannot be shown in the spreadsheet AutoFilter list, the filtered items are displayed correctly on the Web page, but the AutoFilter list does not reflect the chosen criteria.
You can reset the drop-down list by clicking AutoFilter on the spreadsheet toolbar to turn the filter off, and then click AutoFilter again to turn the filter on. This will also reset the displayed list.
Importing and exporting
I can't publish a password-protected spreadsheet from Excel.
From Microsoft Excel, you can publish a protected worksheet only by using a blank password. Open the original workbook in Excel, remove the password, and try to publish the worksheet again.
My spreadsheet filter is not exporting to Excel correctly.
In a spreadsheet, you can create AutoFilter criteria that can't be shown in the AutoFilter drop-down list or the Custom AutoFilter dialog box in Microsoft Excel. For example, there is no direct equivalent in Excel for a spreadsheet filter that has three unselected items. When you export the spreadsheet to Excel and the spreadsheet contains filtering criteria that Excel does not support, the entire AutoFilter range is shown in Excel.
To export your filtered data and have it display as it does in the spreadsheet, it is recommended that you not have more than two criteria set for each filter; that is, you must not have more than two check boxes with a different setting (selected or cleared) from any of the other check boxes.
Spreadsheets can contain data in columns A through ZZZ and rows 1 through 262,144. Microsoft Excel worksheets, on the other hand, can contain data only in columns A through IV and rows 1 through 65,536. To export your spreadsheet to Excel, you must delete or move data from columns beyond IV and rows beyond 65,536. If you want, you can copy the additional data to another Excel worksheet.
Working with the spreadsheet in the browser
Users cannot interact with my spreadsheet in the browser.
Users who can view but not interact with your spreadsheet might not have access to a Microsoft Office XP license. Be sure that users have a valid license.
The source data Web page is unavailable If some of the data in the spreadsheet comes from another Web page, you must make sure that Web page is available and that you and your intended users have proper access to the Web page.
The Web page is not local If the spreadsheet contains a formula that links to data on another Web page, that other page must reside on your local computer or intranet, and you must have proper access to the Web page.
Clicking the Edit button in my Web browser opens my Web page in the wrong program.
Programs that are available from the Edit button in your Web browser depend on how the page was published.
If clicking the Edit button in your Web browser opens a program that you do not want to use, quit the program, start the program you want to use, and open the Web page in that program.
Error values
I see error values instead of results for formulas.
When part of a formula cannot be calculated, or if there is a problem with the formula, the formula returns an error value, such as #NAME? or #DIV/0! To determine the cause, read about troubleshooting error values.