A better pivot table

[Ecel Dumbo]

Hello Dear Friends,


Could you please refer the attached sample worksheet and kindly advise if it is possible to build a pivot table without making any changes to the structure of the main source data.


Basically I would like to present in different angles- Product wise, Employee wise, Year wise. Not sure a pivot table will do the job. This is a sample of a very big data i am working on


Any help would be greatly appreciated.


http://speedy.sh/zCK2p/A-better-pivot-table.xls

 

[Faseeh]

Hi Ecel Dumbo,


Pivot Tables works (best) with lists and this sheet contains tables, so either modify them to lists or use formula to create your desired reports.

 

[bobhc]

Good day Ecel Dumbo


By converting your data to tables you can filter as required, The same is said of Pivot tables but with slices you can do more.


https://dl.dropbox.com/u/75495784/Copy%20of%20A%20better%20pivot%20table.xls

 

[Trader273]

See if this helps. Used the often forgot about PivotTable Wizard in order to make 1 pivot over multiple data tables. I include a How-TO in the file, much easier to put in there. Also, did this pretty quick so would probably need some tweaking, but could be a good start.


https://www.dropbox.com/s/dxgosohn5pupqrn/A%20Better%20Pivot%20Using%20Pivot%20Wizard.xls

 

[Ecel Dumbo]

Thanks a lot Bobhc with your valuable time and advise. It defintely helps.


Thanks a lot to Nick M. Your method serves the purpose and i takes less time.

Could you please advise how to rename Item1 , Item2 and Item3 as product A, product B and product C in the pivot table multiple consolidation range, as shown in your worksheet. Thanks heaps for the pictorial step by step description. This defnitnely helped me.


https://www.dropbox.com/s/dxgosohn5pupqrn/A%20Better%20Pivot%20Using%20Pivot%20Wizard.xls


Thanks again. This site is wonderful

 

[Trader273]

Oops sorry about that. That's an easy fix


1) Active Pivot Table Wizard - Multiple Consolidated Ranges - I will Create Page Field

2) Highlight each data table, and hit "Add"


--Lets Use Product A as an example for the remaining:

3) In the ALL RANGES, select the first range you added that represent Product A

4) on bottom, "How Many Field Pages Do You Want?", select One

5) Type "Product A"

6) Repeat for each range, Product B, Product C


Just have re-do the formatting, and change from count to sum, etc. But now will have the choice to be filtered as makes more sense.

 

[Ecel Dumbo]

Thanks a lot Nick M. Does the job neat and clean. Hats off to u


One more small question please I have inserted a calculated item field to calculate %. How do i apply multiple formats in a pivot table. I need one column in the pivot table to appear as % and other columns to appear as $.


Thanks again for your valuable time and help.

 

[Trader273]

Not a problem at all. I havent used these too often, but since all the columns are coming from 1 field in the pivot table, whatever you set the number format (right click in pivot table-value field settings-number format) is what will be for the entire table. So if you change that to percent, all the columns would be percent. So if you do that, then just got to highlight each column that you want to format as currency or whatever. Hold down CTRL to highlight separate columns at once. Kind of a pain, but still better then making 3+ separate pivot tables.


Another option is to build the pivot table, and then use the GetPivotData function to build a separate table, but still being drive by the pivot. I don't think that's really necessary here though.