Pivot Table Data Crunching Questions

[Shatin]

I got my book from Amazon today. I have downloaded the files and am working through the examples. I have encountered a couple of problems and would appreciate your help.

p. 44
------
In Figure 3.15 and also the subsequent examples, there's a Base Field called "Years". I can't see any such base field in my file and so can't reproduce the the pivot tables.

p. 72
------
In Figure 5.1, there are two numeric fields -- Units Sold and Revenue -- that appear along the column area. However, I have not been able to reproduce this pivot table. When I tried to drag the Revenue field to the column area, I got this error message from Excel:

You cannot place a field more than 8,000 items in the column area.
If you want to use this field in the report, click OK, and then move the field to the row or page area.

I am using Excel 2003 and Windows 2000.

Thank you.

 

[crsnydertx]

PivotTables

I got my copy today and ran into the same problem with 3.15 - hope an answer will be forthcoming

 

[MrExcel]

A new file is available for Chapter 3, from figure 3.15 onwards. I had written a note to Michael that we would have to do something to accomodate that section of chapter 3, but then forgot to add the file to the downloads.

Sorry for the confusion.

Bill

 

[Guest]

With respect to the missing Years field in 3.15, the answer turns out to be very simple:

1. Create the PivotTable from the Main Data Source. Go straight to Finish to get the blank template.
2. Drag and drop the In Balance Date field to the Rows area. It will display individual days as the rows
3. Group the In Balance Date field by Months and Years.
4. Drag the resulting Months field to Columns Area, leaving the Years behind.
5. Drag Revenue field to Data area.
6. Continue as outlined on page 45.

Great book, Mr. Bill!

Chuck

 

[Shatin]

Chuck,

Thanks for solving the problem with 3.15. Have you been able to do 5.1?

1. Drag and drop "Line of Business" to Page Field.
2. Drag and drop "Market" to Row Field.
3. Now what?

 

[MrExcel]

Have you been able to do 5.1?
1. Drag and drop "Line of Business" to Page Field.
2. Drag and drop "Market" to Row Field.
3. Now what?

Then:
3. Drag & Drop Units Sold to the Data Field
4. Drag & Drop Revenue to the Data Field. This will create a situation where Sum of Units Sold appears in rows 4, 6, 8, 10, ... and Sum of Revenue appears in rows 5, 7, 9, 11.... There will also be a new field name called Data above the labels in column B.
5. Take the grey Data field from B3 and drop it on the the Total heading in cell C3. This will orient the multiple data fields as column fields instead of row fields.

Bill