Excel 2007 Pivot Table Changes the Source Data Range

[Marilyn]

I have several Excel 2007 files with Pivot tables in the files. When the
files were created the source range for the data was (nameofsheet $A:$J) and
now all the files have changed the data source in the Pivot Tables to the
following: (name of sheet $a:1:$J$16384)

As it turns out 16384 is the maximum number of columns allowed in a pivot
table, but, I’m not sure why all my files are changing to that.

Has anyone come across this?

 

[Dan DeHaven]

Marilyn,

I haven't seen this issue but you may want to try one of two possibl
solutions that I can think of. I have Pivot Tables that contain wel
over 100K rows of data that work fine and the Excel 2007 documentatio
says a Pivot Table can have over 1 million rows

First, when you select any of the Pivot Tables you can go to the Pivo
Table "Options" menu and click on "Change Data Source" icon in th
"Data" group. From there you can reselect the range of data.

Or my prefered way to handle any large data sets it to convert them t
a table. All you have to do to do this is select on cell in your dat
range and go to the "Insert" menu, then click "Table" on the "Tables
group. All you have to do is click OK and the range now has full Tabl
functionality. Click inside the table (any cell) then click on th
"Design" "Table Tools" menu. To the far right of the ribbon you'll se
the properties group and the "Table Name:" box which will usually hav
something like "Table1". Select the box and change the name to somethin
that describes the data (like SalesData).

Then you can go to the Pivot tables that you want to direct to tha
data and in the "Change Data Source" dialog box you can simple put i
the name that you named the table.

Also, if you haven't used Tables I highly recommend them. The
immediately format the data nicely. Any formulas that reference the
will automatically change whenever you add new rows to them without yo
having to manually adjust the formulas. As well as countless othe
benefits. If you're interested just search Excel's help for "Tables"
Best feature in years.

Best of Luck,

Dan