You are currently offline, waiting for your internet to reconnect

"Reference Not Valid" Error When You Create PivotTable

This article was previously published under Q177169
This article has been archived. It is offered "as is" and will no longer be updated.
In the versions of Microsoft Excel listed at the beginning of thisarticle, if you click PivotTable Report on the Data menu, and then clickNext to proceed to step 2 of the PivotTable Wizard, the Range referencebox may contain the word "Database."

If you click Next, you may receive the following error message:
Reference is not valid.
This problem may occur if the following conditions are true:

  • You display the data form in Microsoft Excel by clicking Form on the Data menu. -and-

  • You create the PivotTable in the same worksheet in which you previously displayed the data form.
Specifically, when you display the data form, Microsoft Excel creates aninvisible name called "Database." This invisible name is automaticallydetected by the PivotTable Wizard. However, the PivotTable Wizard cannotcorrectly use this invisible name; this name is used only by the dataform.

NOTE: This problem does not occur in earlier versions of Microsoft Excel.
To work around this problem, select the range for the list that you wantto use in the PivotTable, and then click Next.

Or, delete the invisible name Database by following these steps:

  1. Select any cell in the worksheet.
  2. Point to Name on the Insert menu, and then click Define.
  3. In the Names In Workbook box, type Database. Then, click Add.
  4. Click Delete. Then, click Close.
If you create a PivotTable, the Database name does not appear in step 2 ofthe wizard.
Microsoft has confirmed this to be a problem in the Microsoft productslisted at the beginning of this article.
You can use the data form in Microsoft Excel to see, change, add, delete,and find records in a list or database. To use the data form, select anycell in a list or database, and then click Form on the Data menu.

Normally, if the "Database" defined name exists in a worksheet, thePivotTable Wizard correctly uses the range of cells referenced by thedefined name. However, if "Database" the name was created by the dataform, the name cannot be used by the PivotTable Wizard.
XL97 pivot tables XL

Article ID: 177169 - Last Review: 12/05/2015 08:11:39 - Revision: 1.1

Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive kberrmsg kbbug KB177169