Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No. of columns in a Pivot Table
Hi,
I am new to Pivot tables and I am trying to do a report to track certain information. I created a workbook that has 19 columns and intended to do 3 worksheets (reports) using various rows/columns from the Master List worksheet. One report would be 'Active', the other 'Potential' and the last 'Finalized. Initially I was working with formulas to bring the data forward to whatever spreadsheet, however, the file was so big, it took forever to open. Pivot tables came to mind, but, I need at least 11 columns of data from the Master List on the Active report. When I tried to refrshed the report when more data was added to the Master list, I got an error code '....there are too many rows or columns. Drag at least 1 row or column field off the report......' Is there a limit to the number of columns Pivot Tables can handle? The way it is set up now, I have the Status (Active, Potential, Finalized) as the Page, showing rows of data broken into sub-catagories by region, then the detail for each beside the region. Ie: Region Portfolio Name Address Balance Due Value Est. Cost ....etc Quebec 1234 John 15 Now 25,000. 50,000 3,000. 2333 Sam 2 Hard 18,000 22,000 2,000 Subtotal Ontario etc......................... I am using Excel 2003. Perhaps I am using the wrong type of report, application? Any suggestions are welcome. Thanks -- Linda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
No. of columns in a Pivot Table
Hi,
That error message has nothing to do with the number of columns or row in you data source. Excel is limited, depending on version, but 256 is the column limit in 2003 and most earlier versions. You are getting that message because you are putting fields into the column area or row area that when considered with all the field in that area are making an output that has too many unique items. For example, suppose you put Year into the column area and you have 10 years worth of data, Excel will display 10 column fields and the grand total. Now you add Month under Year as a column field. Excel creates 12 columns under each year and runs a subtotal for each year and a grand total. Now there are 13*10+1 columns (12 months + a subtotal column for each year) = 131 columns. Now you add department above Year in the column area, suppose you have 3 departments. Now Excel tries to create, for each department 10 years with 12 months and subtotals. That is 3*13*10+1 +3 Department subtotals 397 columns! Opps, a little problem, your spreadsheet has IV or 256 columns! Hope this helps, if so click the Yes button -- Thanks, Shane Devenshire "mathel" wrote: Hi, I am new to Pivot tables and I am trying to do a report to track certain information. I created a workbook that has 19 columns and intended to do 3 worksheets (reports) using various rows/columns from the Master List worksheet. One report would be 'Active', the other 'Potential' and the last 'Finalized. Initially I was working with formulas to bring the data forward to whatever spreadsheet, however, the file was so big, it took forever to open. Pivot tables came to mind, but, I need at least 11 columns of data from the Master List on the Active report. When I tried to refrshed the report when more data was added to the Master list, I got an error code '....there are too many rows or columns. Drag at least 1 row or column field off the report......' Is there a limit to the number of columns Pivot Tables can handle? The way it is set up now, I have the Status (Active, Potential, Finalized) as the Page, showing rows of data broken into sub-catagories by region, then the detail for each beside the region. Ie: Region Portfolio Name Address Balance Due Value Est. Cost ....etc Quebec 1234 John 15 Now 25,000. 50,000 3,000. 2333 Sam 2 Hard 18,000 22,000 2,000 Subtotal Ontario etc......................... I am using Excel 2003. Perhaps I am using the wrong type of report, application? Any suggestions are welcome. Thanks -- Linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Pivot table with more than 3 columns | Excel Worksheet Functions | |||
How do I get more columns in a pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) |