Pivot Tables - 3 Questions

[vfxjohn]

Recently I asked if there were any "Best Practices" for working with
pivot tables in Excel 08. As I'm just starting a new project, and I
didn't get a response to me early question, i thought I'd ask some
specific questions:

1. I currently have about 400 rows of data spread over ~30 columns.
When I need a pivot table that only references say, 2 or 3 columns of
the data, should I select only those columns, or can i simply select
the whole data set and only use the necessary columns when laying out
the table? (I'm asking in terms of what will be the most efficient
method that will not increase the likelihood of crashes down the
road. I will eventually need to look at all the data in the columns
and will have multiple pivot tables. But in no case will i ever be
using all the data in a single pivot table)

2. Is it okay to have multiple pivot tables in the same worksheet?

3. Is there a maximum recommend number of pivot tables to have in a
workbook? My last project had roughly 15 worksheets, with 20+ pivot
tables and I encountered many problems. I'm not sure if the two are
related, but if there are any known issues, I can work across multiple
files instead.


Thanks,
//john

 

[Fergal Condron]

Hi John,

Please see my answers below, as mentioned can you let me know the issues you
have encountered with pivot tables?

Recently I asked if there were any "Best Practices" for working with
pivot tables in Excel 08. As I'm just starting a new project, and I
didn't get a response to me early question, i thought I'd ask some
specific questions:

1. I currently have about 400 rows of data spread over ~30 columns.
When I need a pivot table that only references say, 2 or 3 columns of
the data, should I select only those columns, or can i simply select
the whole data set and only use the necessary columns when laying out
the table? (I'm asking in terms of what will be the most efficient
method that will not increase the likelihood of crashes down the
road. I will eventually need to look at all the data in the columns
and will have multiple pivot tables. But in no case will i ever be
using all the data in a single pivot table)

You can select the entire range and then can set the layout as needed in the
pivot table wizard.

2. Is it okay to have multiple pivot tables in the same worksheet?

Yes you can have many pivot tables and they can all link the same data
source if that is your requirement.

3. Is there a maximum recommend number of pivot tables to have in a
workbook? My last project had roughly 15 worksheets, with 20+ pivot
tables and I encountered many problems. I'm not sure if the two are
related, but if there are any known issues, I can work across multiple
files instead.

How big is your data set and what type of problems did you encounter, if
these problems are persisting can you send me a sample file to me directly?
(e-mail address removed)

Thanks,
//john

Thanks!
Fergal Condron,
Macintosh Business Unit
Microsoft Corp.
This posting is provided ³AS IS² with no warranties, and confers no rights.

 

[Pat McMillan]

One thing I would add here: With respect to referencing your data for pivot
tables, I would highly recommend referencing only the data you actually need
-- especially columns. In Excel 2008 we increased the number of rows in
Excel from roughly 65,000 to over a million! So referencing an entire column
of data in Excel 2008 is a very different thing from referencing a column of
data in Excel 2004.

Thanks,

Pat

 

[vfxjohn]

well, just now... and despite my best efforts to make duplicate copies
of my spreadsheet, both of my files are corrupt and i can no longer
open them.

"excel cannot open the file. the file might have been damaged or
modified from its original format."

all i had were two worksheets; data and pivot; and two pivot tables on
the pivot worksheet. i even saved backup copies.

what gives? how on earth can excel be saving out corrupt files? im
not using any third party add ons... the file isnt large and complex
(96KB). and it's both files! the original file and the backup!

the worst part of all this is that ive spent over an hour compiling
the data and bringing it into one worksheet. and that just turned out
to be a waste. id be better off manually putting everything
together. i just got done complimenting the MacBU team for how well
they handled the solver issue, but i cant deal with this anymore. im
switching over to Numbers and just manually dealing with all this
data. it will take me longer, but at least i wont be losing data and
work.

 

[Fergal Condron]

Hi John,

Sorry to hear of your woes as file corruption is inexcusable.
Is there any way you can send me the file even in it's corrupted state
that's of course if it not confidential?

If not, I'm just trying to run through the steps here:
You have data on one sheet, you have 2 pivot tables on another relating to
the data on the first sheet. You save as backup and both files get
corrupted, I think you mentioned that the data set is 30 columns by 400
rows?

Thanks!
Fergal Condron,
Macintosh Business Unit
Microsoft Corp.
This posting is provided ³AS IS² with no warranties, and confers no rights.