Excel 2000 Pivot Table - Full Data to Replicate Problem

Excel 2000 Pivot Table - Full Data to Replicate Problem

Post by Neil Pen » Sat, 01 Mar 2003 09:48:59



Excel 2000

I am trying to produce a pivot table from 13 columns and 39 rows of data, but am getting the message:

"Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the pivot table, or to the page position. Alternatively right click a field , and then click Hide or Hide Levels on the shortcut menu"

The data is contained in the table below. To reproduce the problem do the following:

1. Open a new workbook in Excel.
2. Copy and paste the data below into one of the worksheets.
3. Create a new pivot table.
4. Drop EAR into the data area
5. Drag and drop the following columns into the row area:
ID
Business Unit
Company
Capex
formal bid
contact term
resource
install date
salesperson
product
%

When you drop the % column onto the pivot table the error message will appear.

The Pivot table report specification says a pivot table can have 8,000 items, with 256 data fields so I am sure I shouldn't have reached a limit.

Can anyone help?

Neil

      ID Business Unit Company Capex Formal Bid Contract Term Resource Install Date Salesperson Product EAR % Date
      001Lost (none) (none)      (none) (none)  Lost  
      4675C Business Unit1 Company 1 0 No    Person 1 P1 300,000 50 Apr-03
      4785C Business Unit 2 Company 2 0 No    Person 2 P2 750,000 30 Mar-03
      4841C Business Unit1 Company 3 0 No    Person 1 P3 270,000 30 Apr-03
      4854C Business Unit1 Company 4 0 No    Person 1 P1 419,000 90 Feb-03
      5019C Business Unit1 Company 5 0 No    Person 3 P3 200,000 50 Mar-03
      5151C Business Unit1 Company 6 250000 Yes 1 Year   Person 4 P4 250,000 90 Feb-03
      5152C  Company 7 0 No     P1 120,000 90 Feb-03
      5196C Business Unit 3 Company 8 10000 Yes 3 Years Resource 1  Person 5 P3 245,000 30 Mar-03
      5197C Business Unit 3 Company 9 0 No 3 Years   Person 5 P3 102,000 50 Mar-03
      5199C Business Unit 3 Company 10 0 No 5 Years   Person 5 P3 350,000 50 Feb-03
      5206C Business Unit1 Company 11 0 No    Person 6 P1 200,000 50 Mar-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P5 11,500 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P6 12,000 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P7 1,500 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P8 8,500 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P9 600 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P10 14,400 90 Feb-03
      5209C Business Unit1 Company 12 54000 Yes 1 Year  Mar-03 Person 7 P11 2,500 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P5 11,500 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P6 12,000 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P7 1,500 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P12 8,500 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P9 600 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P13 14,400 90 Feb-03
      5213C Business Unit1 Company 12 58500 Yes 3 Years  Mar-03 Person 7 P11 1,500 90 Feb-03
      5214C Business Unit1 Company 13 70000 Yes 3 Years  Apr-03 Person 7 P1 42,000 50 Mar-03
      5214C Business Unit1 Company 13 70000 Yes 3 Years  Apr-03 Person 7 P4 28,000 50 Mar-03
      5219C Business Unit1 Company 14 120000 Yes 3 Years  Apr-03 Person 7 P1

 
 
 

1. Pivot table in Excel 2000 vs Excel XP

Hi all.

We're developing a solution y which the final analisys of
the data is through a Pivot Table in Excel which
represents data in percentages. We managed to have the
pivot table working on Excel 2000 but when we used Excel
XP the percentages are lost in some situations (showing
then '#VALUE!').

They are only lost when we filter the data picking only
some values of the fields, but not when we select the 'All
[field]' option.

This is becoming very frustrating because we thought the
percentages were allways calculated in the cube so ?why
does changing the client affect the results?.

I'm not sure if the explanation above is very clear but i
need some help with this issue asap.

Thanks

Alejandro Mezcua

2. Job Offer

3. URGENT!: Problems refreshing Excel pivot tables with SQL Server 2000

4. Oracle DBA's VA-DC-MD

5. Excel Pivot Table: Difference between Office 2000 and XP

6. JLP "Technical Notes" : new area announcement and CFP

7. Excel 2000 Pivot Table Limit?

8. Retrieving the properties of Dimensions from Excel 2000 Pivot Tables

9. Excel 2000 Pivot Table

10. About Excel 2000 Pivot Tables ...

11. Using an OLAP Pivot Table in Excel 2000.

12. Performance Issues using Excel 2000 Pivot Table functionality and Analysis Services