Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Table Problem
Hi
I use WinXP with Excel 2000 I have a problem with a pivot table The sheet that contains the raw data has 10 columns of data approx 1200 rows deep The 1st 6 columns of data is linked to another spreadsheet. The last four columns are helper columns that I have added. All columns have unique header names. This all works fine. In my pivot table I use:- 2 of my headings in the Page area. 3 of my headings in the Row area 1 of my headings in the Data area This also works fine. The problem occurs when I try to add another of my heading to the Row area. I get the following error 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 I have other Pivot tables that have more headings that the one I am having trouble with yet I dont get the same error message with these. Even why I try creating a pivot table from scratch I get the same message if I attempt to add the extra heading in the Row field. Can anyone help? Thanks John |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Table Problem
The limitations for pivot tables in Excel 2000 are much lower than those
later version of Excel. This MSKB article outlines the limits: 2000 -- Limits of PivotTables in Excel http://support.microsoft.com/kb/211517/ If you have lots of unique values in your row fields, they'll quickly multiply to a number that's higher than the row item limit. John Calder wrote: Hi I use WinXP with Excel 2000 I have a problem with a pivot table The sheet that contains the raw data has 10 columns of data approx 1200 rows deep The 1st 6 columns of data is linked to another spreadsheet. The last four columns are helper columns that I have added. All columns have unique header names. This all works fine. In my pivot table I use:- 2 of my headings in the Page area. 3 of my headings in the Row area 1 of my headings in the Data area This also works fine. The problem occurs when I try to add another of my heading to the Row area. I get the following error 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 I have other Pivot tables that have more headings that the one I am having trouble with yet I dont get the same error message with these. Even why I try creating a pivot table from scratch I get the same message if I attempt to add the extra heading in the Row field. Can anyone help? Thanks John -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Table Problem
Debra
Thanks for your prompt response. I have 1200 rows of data and each one of these would be considered unique as the 1st 6 columns of each row are linked to another spreadsheet, however my understanding is that up to 8,000 unique records are possible in Excel 2000. As I have only a maximum 1200 I would have thought that this would not be a problem. I even tried cutting the raw data back to 600 rows and I still get the error meassge. Is there anyway to overcome this? Thanks "Debra Dalgleish" wrote: The limitations for pivot tables in Excel 2000 are much lower than those later version of Excel. This MSKB article outlines the limits: 2000 -- Limits of PivotTables in Excel http://support.microsoft.com/kb/211517/ If you have lots of unique values in your row fields, they'll quickly multiply to a number that's higher than the row item limit. John Calder wrote: Hi I use WinXP with Excel 2000 I have a problem with a pivot table The sheet that contains the raw data has 10 columns of data approx 1200 rows deep The 1st 6 columns of data is linked to another spreadsheet. The last four columns are helper columns that I have added. All columns have unique header names. This all works fine. In my pivot table I use:- 2 of my headings in the Page area. 3 of my headings in the Row area 1 of my headings in the Data area This also works fine. The problem occurs when I try to add another of my heading to the Row area. I get the following error 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 I have other Pivot tables that have more headings that the one I am having trouble with yet I dont get the same error message with these. Even why I try creating a pivot table from scratch I get the same message if I attempt to add the extra heading in the Row field. Can anyone help? Thanks John -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Table Problem
The 8000 unique item limit is per field. You only have 1200 records, so
won't hit that limit. In the article I mentioned: http://support.microsoft.com/kb/211517/ it describes how the row and column limits are calculated. In all the row fields, the product of the items is about 2 billion (2^31) In your pivot table, row field 1 might have 400 unique items, row field 2 might have 300 unique items and row field 3 might have 200 items. The product is 400*300*200= 24,000,000 Then, if you try to add another row field, that has 100 unique items, the product is 400*300*200*100= 2,400,000,000 which would be over the 2 billion limit. John Calder wrote: Debra Thanks for your prompt response. I have 1200 rows of data and each one of these would be considered unique as the 1st 6 columns of each row are linked to another spreadsheet, however my understanding is that up to 8,000 unique records are possible in Excel 2000. As I have only a maximum 1200 I would have thought that this would not be a problem. I even tried cutting the raw data back to 600 rows and I still get the error meassge. Is there anyway to overcome this? Thanks "Debra Dalgleish" wrote: The limitations for pivot tables in Excel 2000 are much lower than those later version of Excel. This MSKB article outlines the limits: 2000 -- Limits of PivotTables in Excel http://support.microsoft.com/kb/211517/ If you have lots of unique values in your row fields, they'll quickly multiply to a number that's higher than the row item limit. John Calder wrote: Hi I use WinXP with Excel 2000 I have a problem with a pivot table The sheet that contains the raw data has 10 columns of data approx 1200 rows deep The 1st 6 columns of data is linked to another spreadsheet. The last four columns are helper columns that I have added. All columns have unique header names. This all works fine. In my pivot table I use:- 2 of my headings in the Page area. 3 of my headings in the Row area 1 of my headings in the Data area This also works fine. The problem occurs when I try to add another of my heading to the Row area. I get the following error 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 I have other Pivot tables that have more headings that the one I am having trouble with yet I dont get the same error message with these. Even why I try creating a pivot table from scratch I get the same message if I attempt to add the extra heading in the Row field. Can anyone help? Thanks John -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pivot Table Problem
Debra
Thanks very much for your explanation. I work for a a large organisation which uses Excel 2000, and, as I was creating this pivot table to go on a web page so that others could use it, I will need to look at alternatives. Once again, thanks for you help Debra John "Debra Dalgleish" wrote: The 8000 unique item limit is per field. You only have 1200 records, so won't hit that limit. In the article I mentioned: http://support.microsoft.com/kb/211517/ it describes how the row and column limits are calculated. In all the row fields, the product of the items is about 2 billion (2^31) In your pivot table, row field 1 might have 400 unique items, row field 2 might have 300 unique items and row field 3 might have 200 items. The product is 400*300*200= 24,000,000 Then, if you try to add another row field, that has 100 unique items, the product is 400*300*200*100= 2,400,000,000 which would be over the 2 billion limit. John Calder wrote: Debra Thanks for your prompt response. I have 1200 rows of data and each one of these would be considered unique as the 1st 6 columns of each row are linked to another spreadsheet, however my understanding is that up to 8,000 unique records are possible in Excel 2000. As I have only a maximum 1200 I would have thought that this would not be a problem. I even tried cutting the raw data back to 600 rows and I still get the error meassge. Is there anyway to overcome this? Thanks "Debra Dalgleish" wrote: The limitations for pivot tables in Excel 2000 are much lower than those later version of Excel. This MSKB article outlines the limits: 2000 -- Limits of PivotTables in Excel http://support.microsoft.com/kb/211517/ If you have lots of unique values in your row fields, they'll quickly multiply to a number that's higher than the row item limit. John Calder wrote: Hi I use WinXP with Excel 2000 I have a problem with a pivot table The sheet that contains the raw data has 10 columns of data approx 1200 rows deep The 1st 6 columns of data is linked to another spreadsheet. The last four columns are helper columns that I have added. All columns have unique header names. This all works fine. In my pivot table I use:- 2 of my headings in the Page area. 3 of my headings in the Row area 1 of my headings in the Data area This also works fine. The problem occurs when I try to add another of my heading to the Row area. I get the following error 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 I have other Pivot tables that have more headings that the one I am having trouble with yet I dont get the same error message with these. Even why I try creating a pivot table from scratch I get the same message if I attempt to add the extra heading in the Row field. Can anyone help? Thanks John -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table problem | Excel Worksheet Functions | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Pivot Table Problem | New Users to Excel | |||
Pivot Table Problem | Excel Discussion (Misc queries) | |||
Pivot Table problem | Excel Discussion (Misc queries) |