Pivot table range selection changes when opening 2003 xls in Excel2007

  • Thread starter Thread starter Saqib Ali
  • Start date Start date
S

Saqib Ali

Has anyone noticed that Pivot table range selection gets messed up
when opening a Excel 2003 document in Excel 2007? This causes some of
the data to be left out from the table, which causes confusion.

How can I prevent this from happening. We don't wanna redo all the
spreadsheets in Excel 2007.....

Thanks.

saqib
http://doctrina.wordpress.com/
 
Has anyone noticed that Pivot table range selection gets messed up
when opening a Excel 2003 document in Excel 2007? This causes some of
the data to be left out from the table, which causes confusion.

How can I prevent this from happening. We don't wanna redo all the
spreadsheets in Excel 2007.....

Thanks.

saqibhttp://doctrina.wordpress.com/
Click to expand...

try sisense. They create pivot tables that are easier to use and drill
down into. http://www.sisense.com
 
Hi Saqib,

I can reproduce the problem. It seems to be a bug in Excel 2007.

Usually, I use dynamic ranges to define the data range for a Pivot Table
rather than selecting the whole column.

i.e. create a named range say 'data' with a formula like
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),7)

This returns a range starting at A1 with 7 columns and whatever number of
rows that have data in column A.

It seems to get around the problem.

You can find more information on dynamic ranges in the Excel database
tutorial at http://www.edferrero.com/ExcelTutorials/tabid/54/Default.aspx

Ed Ferrero
www.edferrero.com
 
Back
Top