Several Pivot Table Questions

[Amar Kapadia]

First off all, Merry Christmas everyone. I woke up early to see if Santa
had brought me Office 2003 Professional but still not sure as everyone is
still sleeping. So I figured I'd log on and learn something as I always do
in these forums. Anyway, I have a few questions I'd like to ask and I
apologize I haven't searched the posting for answers as of yet. Thanks in
advance for any assistance:

(small note, I use Excel XP at work so these are, I believe, specific to
Excel XP)

1) On one machine at work that has Excel 2000, when I right click on the
Pivot Table, I get a option to Add Field. Yet, right clicking in Excel XP
pivot table does not give me that option. Where can I find that in Excel
XP?? Basically, I need to make a custom calculation based on the values of
two other items in my pivot. The Help files do acknowledge their presense
in Excel XP, but gosh, just can't find it.

2) I remember in the past (Excel 97? Excel 2000?) that when I had a pivot
table generated which included a Page Field, I was able to right-click on it
and somehow break out each value in the Page Field into its own separate
worksheet. Again, in Excel XP I cannot find it. I'd give some better info,
but this computer does not have Excel 2000 or Excel 97 for me to be more
specific. Does that ring a bell? Is it in Excel XP? Heck I don't even
know what to search for in the help files.

3) Using Pivot Tables more and more, I have also the following question and
would like to know how others deal with this: If I have a huge table
comprising of over 40 columns/fields (many of which have String/text
functions and Count functions and generally really slow stuff) and upwards
of 20,000 rows Excel often takes about 5 minutes to perform all its
calculations. I admit, there are probably more efficient ways of doing what
I do, but for now it all works. Anyway, before creating my Pivot table, I
usually, Copy/Paste Special/Values into another workbook and make the Pivot
from this new "equation-free" data. Is that good practice? I just figure
it will make the interactive Pivot more responsive. Or that an unnecessary
step? I just don't want the manager who receives the data to have to wait 5
minutes when they open or change a selection.

4) Also, what is the general concensus on Save Data with Layout option?
When does on use that and when does one uncheck it for performance/size?

Thanks in advance. Hope everyone has a wonderful Christmas and a Happy New
Year.

Amar

 

[Dave Peterson]

#1. If you rightclick on the pivottable, you can see an option "Show Field
List" or if it's already visible, "Hide Field List"

With that extra toolbar visible, you'll be able to drag and drop to the pt.

#2. On the pivottable toolbar, click on Pivottable, then Show Pages.

#3. Just a guess. I figure that the more formulas you have in your workbook,
the longer any calculation takes. But pivottables are different. They don't
recalculate except when you tell them to refresh.

So I'm guessing (no testing at all!), that your workbook will be faster in
general, but the pivottable time won't change too much.

But I wouldn't trust this answer (so why did I type it????). Could you
experiment a little and see what happens?

For the most part, my pivottables are pivottables for a very short period of
time. I convert to values, and do more manipulation of the summarized data.

#4. For the pivottables that I keep pivottables, I keep that option
checked--but probably just because.

Not much help on #3 and #4. I'll be reading what others post.

 

[Ken Wright]

#1. If you rightclick on the pivottable, you can see an option "Show Field

List" or if it's already visible, "Hide Field List"

From the last part of his text on #1 I'm guessing he meant the Add Calculated
Item and Calculated Field which can be found on the Pivot table Toolbar under
the 'Formulas' button as 'Calculated Field' and 'Calculated Item'.

 

[Dave Peterson]

Well, that makes sense when you actually read the question <bg>.

Thanks for the correction.

 

[Amar Kapadia]

Dave/Ken:

Thank you both for the valuable information. I always saw that toolbar
there, but just never knew to click on the PivotTable button to show all
those other options I was needing. Now I need to learn about the difference
between Calculated Items and Calculated Fields, but I'll plunder through it
via Help and experimenting and might make a posting or two to get me by!!
Thanks again!

ps. never did get my Office 2003 Professional, but hey that is what gift
certificates are for!!

Cheers
Amar

 

[Ken Wright]

LOL