Pivot Table Data Crunching: chaps 4, 5 and 6

[Willy]

I love Pivot Table Data Crunching. It's the most comprehensive book on the subject. I would not hesitate to recomment it to others.

I have a question about some of the information in Pivot Table Data Crunching, and I hope you can help:

I'm trying to understand how the pivot tables in figures 4-13, 5-1, 6-1 and 6-5 were created. The text does not explain their creation. In fact, chapter 6 is about adding calculation fields to the table, but there was no mention of how to initially create the table. And, it appears the data in the figures do not match the data in the files that can be downloaded.

Any information will be much appreciated.
Thank you.

 

[Guest]

Fig 41.13 on book Pivot Table Data Crunching

Willy:

I had exactly the same question as you. After some frustrated intents, I finnaly found the answer. Here it is:

Open the file Chapters 1-4 Sample Data.xls

Create a Pivot Table. If you have one already created just drag out of the Pivot Table all labels. Then proceed as follows:

1. Drag "IN BALANCE DATE" label to the Row Field
2. Drag "REGION" label to Row Field, beneath In Balance Date
3. Drag "UNITS SOLD" to data field
4. Drag "REVENUE" to data field

At this point you should have two rows for every Region for each In Balance Date

To change the Sum of Units Sold and the Sum of Revenue the same row (that is, under columns) right click the Data label and select Order; then select "Move to the column"

To make the Year appear right click the label In Balance Date and then select "Group", then select Month and Year. You should see the results grouped by Years and Months and Region. To leave Years only drag out of the Pivot Table the "IN BALANCE DATE" label. (See page 91 of the book for more details).

At this point you should have the Pivot table almost as appears in fig. 4.13. Almost, becasue the the number of Units Sold does not match with the number on the book, but the revenue does. However, the number of units sold in the Pivot Table does match the total in the Main Data Source. So probably the authors use a slightly different file.

One additional tip: to swap the order of Sum of Units Sols and Sum of Revenue, right click on the cell that says "Sum of Revenue", then select Order and you will see your choice there. This option will be either, "Move to the right", or "Move to the left").

I use Excel 2002 under Windows XP.

Hope this helps.

Happy PivotTabling

Guillermo
(sorry for any orthographic mistake I sure may have)