Group Field in Pivot Table Not Enough Room
I want to create a pivot table showing customer names as row headings and
total invoice values, grouped in intervals of 1000, as column headings. The data field is also the invoice value. When I drag the invoice value field to the column heading I receive the following message: The field you re dropping into the column area exceeds 255 columns. Only the first 255 will be displayed as columns. To include all of the fields data in your pivot table report, drop it into a different area, such as the data area. I choose to continue the drop and the following message appears: The Pivot table will not fit on the sheet. Do you want to show as much as possible. I choose to show as much as possible and when I check the last column (IV) the highest value is in the mid 300s. I go ahead and group the values and get data from around -2000 to +7000 which appears to be complete. I dont actually want to show a column for each value only for the grouped values and I appear to have achieved that result. However, I would like to hear from someone who knows more about pivot tables than I do that what I am doing is OK. Am I running the risk of loosing some data when the groups eventually appear? Maybe there is a limit on how many potential columns can be grouped before the data is lost. I am using 2003. Insight / reassurance would be welcome! |
Hi Mary Ann,
You should be okay. While the worksheet won't display more than 255 column headers (leaving one column for the row headers), the pivot table's cache will retain all the information. The cache stores your data and makes it quick and easy to restructure your pivot table, but it doesn't appear on the worksheet. To reassure yourself, start by orienting your invoices as rows and your customer names as columns -- in effect transposing what you've been working toward. Excel's 65536 rows might be sufficient to display all the invoices, even without grouping. Take note of some summary value, such as the sum of all the invoice amounts. Then, group the invoices and pivot the table, putting invoices in columns and customer names in rows, and check to see that the sum of all invoice amounts remains the same. If you've invoiced more than 255 customers, your company is doing just fine and you don't need any reassurance. C^2 Conrad Carlberg "Mary Ann" wrote in message ... I want to create a pivot table showing customer names as row headings and total invoice values, grouped in intervals of 1000, as column headings. The data field is also the invoice value. When I drag the invoice value field to the column heading I receive the following message: "The field you re dropping into the column area exceeds 255 columns. Only the first 255 will be displayed as columns. To include all of the field's data in your pivot table report, drop it into a different area, such as the data area. " I choose to continue the drop and the following message appears: "The Pivot table will not fit on the sheet. Do you want to show as much as possible." I choose to show as much as possible and when I check the last column (IV) the highest value is in the mid 300s. I go ahead and group the values and get data from around -2000 to +7000 which appears to be complete. I don't actually want to show a column for each value only for the grouped values and I appear to have achieved that result. However, I would like to hear from someone who knows more about pivot tables than I do that what I am doing is OK. Am I running the risk of loosing some data when the groups eventually appear? Maybe there is a limit on how many "potential" columns can be grouped before the data is lost. I am using 2003. Insight / reassurance would be welcome! |
Thanks Conrad. Useful to know that all the data is stored in the cache.
"Conrad Carlberg" wrote: Hi Mary Ann, You should be okay. While the worksheet won't display more than 255 column headers (leaving one column for the row headers), the pivot table's cache will retain all the information. The cache stores your data and makes it quick and easy to restructure your pivot table, but it doesn't appear on the worksheet. To reassure yourself, start by orienting your invoices as rows and your customer names as columns -- in effect transposing what you've been working toward. Excel's 65536 rows might be sufficient to display all the invoices, even without grouping. Take note of some summary value, such as the sum of all the invoice amounts. Then, group the invoices and pivot the table, putting invoices in columns and customer names in rows, and check to see that the sum of all invoice amounts remains the same. If you've invoiced more than 255 customers, your company is doing just fine and you don't need any reassurance. C^2 Conrad Carlberg "Mary Ann" wrote in message ... I want to create a pivot table showing customer names as row headings and total invoice values, grouped in intervals of 1000, as column headings. The data field is also the invoice value. When I drag the invoice value field to the column heading I receive the following message: "The field you re dropping into the column area exceeds 255 columns. Only the first 255 will be displayed as columns. To include all of the field's data in your pivot table report, drop it into a different area, such as the data area. " I choose to continue the drop and the following message appears: "The Pivot table will not fit on the sheet. Do you want to show as much as possible." I choose to show as much as possible and when I check the last column (IV) the highest value is in the mid 300s. I go ahead and group the values and get data from around -2000 to +7000 which appears to be complete. I don't actually want to show a column for each value only for the grouped values and I appear to have achieved that result. However, I would like to hear from someone who knows more about pivot tables than I do that what I am doing is OK. Am I running the risk of loosing some data when the groups eventually appear? Maybe there is a limit on how many "potential" columns can be grouped before the data is lost. I am using 2003. Insight / reassurance would be welcome! |
All times are GMT +1. The time now is 02:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com