Troubleshoot Excel lists

ShowFormulas are missing from my list.

If you add a new column that contains a formula to the list, and the list is linked to a SharePoint site, the formula will be converted to a calculated value.

You can retain formulas by publishing the list without linking it to Microsoft pnSTS11.

ShowThe formula is read-only and cannot be updated in a list that is linked to a SharePoint site.

You will see a formula in a list in Microsoft Excel only if the SharePoint list had been exported to Excel and contains a column that has been specified as a calculated column in a SharePoint list. This column is read-only and cannot be modified.

ShowThe Create list option is grayed out.

You are working in a shared workbook     You cannot create lists in workbooks that are shared. Additionally, the Share Workbook command is disabled if your workbook contains any lists. You must first disable workbook sharing if you want to use lists in your worksheet.

The worksheet is protected     You cannot create lists in a workbook or a worksheet that is protected. You must first unprotect the worksheet or workbook before creating lists.

The worksheets are grouped     If you have multiple worksheets selected, you cannot create a list because the worksheets have been grouped. To create a list, you must have only one worksheet selected.

ShowI can't see the list border.

If you cannot see the list border when the list is not active (that is, when a cell outside the list is selected), list borders have been hidden for inactive lists. On the Data menu, point to List, and then click Hide Border of Inactive Lists.

ShowWhen I type in a row directly under a list, the list does not automatically expand.

If you have the total row displayed in the list, the list will not expand automatically when you type in the row below the list. To expand the list, hide the total row (total row: A special row in a list that provides a selection of aggregate functions useful for working with numerical data.) or add data in the Insert row (Insert row: In a list, a special row that facilitates data entry. The Insert row is indicated by an asterisk.).

ShowI made changes to a list linked to a SharePoint list and I can't update my changes because the list has been modified on the SharePoint site.

A user has made changes to the list column on the SharePoint site, and you have made different changes to the list column in Microsoft Excel. When you synchronize the list and the Conflict and Errors dialog box is displayed, do one of the following:

  • To keep your changes, click Cancel.
  • To discard your changes, click Refresh.

ShowI can't publish a list because I have too many columns in the list.

There are limitations on the number of columns you can have of each data type when publishing a list. If you reach the following limits, Excel displays the Publish List Error alert identifying the problem column, and the phrase "[Not Assigned]" to indicate that the column could not be created because Excel cannot assign it a data type.

Data type Number of columns
Short text 64
Multi-line text 31
Number 32
Currency 32
Date/Time 16
Hyperlink 31

ShowWhen I copy and paste data in a list, and then type new data in the cell where I've pasted, I get a data validation error, even though my data conforms to the type restrictions of the column.

When you copy a cell, Microsoft Excel copies the entire cell, including formulas and their resulting values, comments, and cell formats.

For example, if you copy a number from a cell in a column that has been specified as a column of text and paste it into a cell in a column that requires a number, you will get a data validation error. If you then try to type a number into that cell to resolve the error, you will again get a data validation error. This validation error occurs because the format for the cell changed to text when you copied data to the cell from another cell that has text format. In text format cells, numbers are treated as text.

To resolve this error, change the format of the cell back to a format that is suitable for the data type. For example, to correct the problem described above, do the following:

  1. Select the cell you want to modify and click Cells on the Format menu.
  2. On the Number tab of the Format Cells dialog box, select the General format.
  3. Click OK to apply the format.

ShowI get an unexpected error when I try to publish a list.

When you publish a list, unexpected errors may occur for a number of reasons.

  • You may not be able to access the Microsoft Windows SharePoint Web site. The URL specified for the SharePoint site may be incorrect or an administrator may have configured the SharePoint site to deny access.
  • The name of the list may be too long. Give the list a shorter name before trying to publish it again.
  • You may have cancelled the publish operation while data was being transferred to the SharePoint site.
  • There may be a version conflict between Microsoft Excel and the SharePoint site.
  • There may have been a schema conflict between the list in Excel and the list on the SharePoint site.
  • The list may have too many rows. The maximum row limit is 65,534.

ShowI'm having problems moving or copying lists.

In some cases, copying and moving data in an Excel list behaves differently than in a worksheet.

  • Although you can copy and move lists inside a worksheet or to another workbook, you cannot completely copy or move a list to another application, such as Word or even another instance of Excel; only the data is preserved, not the list features.
  • If you have published a list to a SharePoint site, you cannot delete a list column in Excel—you can only delete a list column in Windows SharePoint Services. For more information on deleting a list column, click View List on Server, and then click the Windows SharePoint Services Help command.
  • When you move a column in an Excel list, the column order of the list on the SharePoint Site is not affected.
  • When you copy a list that has been published to a SharePoint site, you must first click Discard Changes and Refresh on the List toolbar to refresh the data in the Excel list with the data from the list on the the SharePoint site.
  • To easily select an entire list, position the cursor in the top left-hand corner of the list, and then click the list when the pointer changes to a down arrow pointing in a 45 degree angle.

ShowI lose list features when I work with lists in versions of Excel previous to Excel 2003.

Although in some cases you can maintain list features when you edit the list using a version of Excel previous to Microsoft Office Excel 2003, here is a scenario in which you would lose list features and disable calculated columns:

  1. You create an Excel list in a workbook, and save the workbook.
  2. The workbook is opened using a version of Excel prior to Office Excel 2003, where the list appears as a basic range of data.
  3. Changes are made to the list, such as adding or deleting columns, using arrays, merging cells, or other operations not supported in Excel lists.
  4. The workbook changes are saved.
  5. You open the workbook using Excel 2003.

 Note   Whether or not you lose list features, your data is still preserved.

ShowMy list column headers are inconsistently truncated.

The maximum length of any header name in a list is 255 characters. Furthermore, when you have duplicate header names, the duplicate name is truncated to 252 characters so that a 3-character number can be appended to the name to make it unique. In general, it's a good idea to keep column headers short and to avoid duplicates. Consider using comments to add additional descriptive information.

Applies to:
Excel 2003