Pivot Table Data Source 2007 v. 2003

[Jake]

For Pivot Tables in Excel 2003 in which the Data Source consists of
entire columns, Excel 2007 selects only 16,384 rows; generally that
should suffice but I have a few pivot tables in which I need more
rows. I know it's not too difficult to change the Data Source but I'd
rather not have to double check every pivot table. Any ideas why 2007
doesn't use the whole row and how I can change that? The only thing
I've notices is the 16,384 is exactly 1/4 of the number 2003 rows,
which may just be a coincidence.

 

[Roger Govier]

Hi Jake

With over 1 million rows in Excel 2007, it would seem like overkill to add
all of them to a PT source range.
Instead, use the Insert Table method.
Select any cell in your source data range>Insert tab>Table
A dialogue box will appear showing the current range and asking whether the
table has headers. Click OK and a Dynamic range will be created with the
name Table1, Table2 etc. You can rename these to whatever you wish.
Choose Summarise with Pivot table, and the PT will be created with this
table as the source data.
The range will grow (or shrink) as you add (remove) rows from the source.

In XL2003, a similar feature exists with Data>List