Pivot Table - Max # of columns?

XPuser

New Member
Joined
Feb 6, 2011
Messages
12
I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.
Any thoughts on what I'm doing wrong?





http://msdn.microsoft.com/en-us/lib...fice2007excelPerf_BigGridIncreasedLimitsExcel
The "Big Grid" and Increased Limits in Excel 2007
PivotTables Maximum rows displayed in a PivotTable report is 1 million. Maximum columns displayed in a PivotTable report is 16,000. Maximum number of unique items within a single Pivot field is 1 million. Maximum number of fields visible in the Fields list is 16,000.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello XPuser,

Save your workbook as .xlsx. Your current file should be in .xls (Excel 97-2003 Workbook) format. Save As & select file type as "Excel Workbook"

Hope this helps
 

XPuser

New Member
Joined
Feb 6, 2011
Messages
12
Hi Haseeb,

Thanks for your response. Unfortunately that's not the problem. It seems like that should be the problem though as the 2003 version only allowed for 255 columns. I created my file new from Excel 2007 this week and saved it as an Excel Workbook (.xlsx).

I'm wondering if my file could somehow be tainted by my data. I have data copied that may have originally been produced by an older Excel version. However, i did a paste of that data into a newly created 2007 workbook (as opposed to moving the whole page in). So I wouldn't think that would be the issue.

So I'm still confused.

Ferg
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
I think the Pivot Table may be created from .xls file. Create a new PT from .xlsx file.
 

XPuser

New Member
Joined
Feb 6, 2011
Messages
12

ADVERTISEMENT

I'm also wondering if my 2007 Excel file is being viewed as 2003 file as I did the following today:

Customized the Quick Access Toolbar today by adding the "Pivot Table and Pivot Chart Wizard" - which I believe is more related to 2003 and earlier. I found it was not useful to me so I removed it.

I also have a couple of Excel Add-Ins:
1) PivotPower
2) RDB Merge
I'm wondering if either of these add-ins make my pivot table limited to old 2003 limits?

My pivot table is in the new 2007 pivot table format and not the classic view.

I've also tried refreshing my pivot and saving as .xlsx a few times now.
 

XPuser

New Member
Joined
Feb 6, 2011
Messages
12
still not giving me more than the 256 data columns (not sure why 256 vs 255 - maybe coincidence?)

Here's what I've done since:
- I created a new .xlsx file
- I cut and pasted values from the data sheet of the old file into my new .xlsx file
- I copied the entire pivot table (not the worksheet, just the pivot table) from my old workbook into my new workbook and changed the data source to point to the new data file. (not sure if that's cheating/tainting - I've already added the 256 columns so I didn't want to have to select them all again!)


Still not working for me. Maybe I just have too much data?

The error message that I am getting when I try to add one more field to the Values is "The field you are moving cannot be placed in that Pivot Table area."
 

XPuser

New Member
Joined
Feb 6, 2011
Messages
12
I also have an AbleBits add-in: Advanced Find & Replace for Microsoft Excel

I started my pivot table again in a new 2007 Excel file. I pasted in the data values from the old file and then I created a brand new pivot table. Still no luck. Seem to be stuck with 255 columns in my Excel 2007 pivot table instead of 16000 columns. Something must be making it think that I am in 2003.
 

XPuser

New Member
Joined
Feb 6, 2011
Messages
12
Re: Pivot Table - Max # of columns 256 in Excel 2007 ??

Well, as a test, I had colleague who doens't have any excel add-ins on their machine create a brand new dummy data table (with 150 rows and 300 uniquely named columns of data) and then create a brand new pivot table - all in Excel 2007.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Same problem - their pivot table would not allow more than 256 columns in the Values area.<o:p></o:p>
That dummy pivot had 2 fields pulled into the Row Labels and 256 fields pulled into the Values in the PivotTable Field List. - So 258 Columns in total. I think I can add more Row Labels with no issue – I just can’t add for fields to the Values.<o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
Maybe I am interpreting below incorrectly. Maybe I can have 16000 columns – but only 256 of those columns can be in the Values area?<o:p></o:p>
Or perhaps I have run up against some other limiter?<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
http://msdn.microsoft.com/en-us/libr...sedLimitsExcel
The "Big Grid" and Increased Limits in Excel 2007
PivotTables Maximum rows displayed in a PivotTable report is 1 million. Maximum columns displayed in a PivotTable report is 16,000. Maximum number of unique items within a single Pivot field is 1 million. Maximum number of fields visible in the Fields list is 16,000.
<o:p></o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top