Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

Need a better answer than I've been using for quaterly data summary effort. Had been using three pivot tables to calculate YTD spend by employee and business unit, MWBE spend by empoyee and business unit as a percent of YTD spend, and MBE spend by empoyee and business unit as a percent of YTD spend. Used to manually combine into another worksheet but there must be a better way; want this to be automatic. Sample data and desired result is attached. Pink columns are the problem calculations for me. Any suggestions?

Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

It is what I want but I'm not sure how you got the ratios to show up. Those fields aren't in the data tab. Can't pull them from the desired result tab because that was generated the way I don't want to keep doing.

Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

Click somewhere in the pivot table.
Click on Pivot Table in the Pivot Table toolbar, and select Formulas | Calculated Field...
Select MWBE Ratio in the Name dropdown list.
The Formula box will display the formula for MWBE Ratio.
Similar for MBE Ratio.

Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

Figured out how to create the calculated fields. Last portion of the question is how to get the pivot table to display in a more horizontal manner as in the solution spreadsheet. Probably simple but I don't know how. Anyone know?

Re: Mulitple calculated fields (pivot?) (Excel 2000 SP-3)

If the various summary fields are displayed below each other in your version, the gray Data button is probably immediately to the right of the Business Unit button. Drag it to the right, and drop it on the cell that contains Total. The summary fields should now be displayed next to each other.