Three Dimensional Pivot Table

[DomP]

Can anyone tell me if Excel can handle a three dimensional pivot table? If so, can you point me towards some documentation on how to create this and/or a couple of quick tips? I'm having a bit of difficulty.

Thanks,

Dom

 

[dhulbert]

Not sure eactly what you mean by a 3 dimensional pivot but have your tried using the page field ?

 

[DomP]

Let's say I typically create a pivot like this:

JAN, FEB, MARCH, ETC.....
2003
2004 DEATIL GOES HERE
2005

The above I consider two dimensional.

I now need to list the company down the left side (rows) but also include a breakout for each month within each year.

This I consider three dimensional.

 

[DomP]

Also, I can create a pivot that provides this information, it just doesn't do it as cleanly as I would like, Maybe what I've created is considered three dimensional by Excel but from a readability standpoint I'm not satisfied.

At this point, I don't know if I have a three dimensional pivot or a very ugly confusing two dimensional pivot ;-)

 

[xlbo]

There is no such thing as a 3D pivottable - that would be an OLAP cube. You have a hard to read 2D pivottable

What are you trying to achieve here ?

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dhulbert]

Dom

You can easily set one like this

Jan Feb Mar Apr .... Jan Feb Mar
Comp1 2003
2004
2005
Comp2 2003
2004
2005 DETAIL
Comp3 2003
2004
2005

Or else drop the company into the page slot and you can paick each individual company or all companies

 

[DomP]

Dhulbert,

Yes, that's what I've created. Readability is real important and I'm not happy with how it looks. It definitely conveys what I want but isn't quite clear enough.

 

[dhulbert]

What version of Excel are you using.
In 2002 you an right click on a filed heading or double click on a row/column item and use show detail to view another level of data.
So if you had years as your column headings you could double click on say 2003 then select months and see the months data drop in, then you just select hide to get rid of it again.

 

[DomP]

hmmmm, interesting. I'm using 2002. Let me look into this. I'm trying to avoid having to do this in an Access report (that's where the data resides). People reviewing this will only be reviewing via hard copy so even with the functionality you mention the readability may not be there but I'm going to look.

Thanks!

 

[pbrodsky]

Also in 2002/2003 you can have Excel generate a separate sheet for each value of the Page field of a pivot table. Maybe that would do what you want.