MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table - Can't believe I having a problem with this


Posted by Tim on January 29, 2002 2:16 PM

Property Cost Loan SBU
A 10,832 49,841 Cl
B 18,984 28,503 Cl
C 45,061 23,565 Cl
D 45,675 47,221 Cl
E 32,860 43,204 Cl
F 29,483 48,372 Ny
G 18,698 36,788 Ny
H 35,299 21,283 Ny
I 12,688 16,422 Ny
J 38,865 36,794 Ny

The above is a simple version my table of data. What I want to be able to do (among other things) is have the Pivot Table look like this:

Cost Loan
Cl 153,412 192,334
Ny 135,033 159,659
Total 288,445 351,993

Subtotals, grand totals, formatting aside...I can't get Cost and Loan side by side. The only way it seems to work is if I set up another Dimension called type and then put the amounts in there, defined by this new dimension. The problem is, I just doubled the amount of rows of data, one for each type. I my real example, this will multiply the number of rows by 20. I must be doing something wrong...


Posted by Mark W. on January 29, 2002 3:15 PM

Drag the 'Cost' and 'Loan' field buttons to
the DATA area of your PivotTable. Drag the 'SBU'
field button to the ROW area of your PivotTable.
Press the wizard's [ Finish ] button to produce
your PivotTable. Now all you need to do is
change the orientation of the 'Data' field so that
the 'Cost' and 'Loan' appear in separate columns.
Just click and drag the 'Data' field towards
the right until the cursor changes to include a
horizontal orientation icon and release the mouse
button.