No. of columns in a Pivot Table

[mathel]

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

 

[ShaneDevenshire]

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