How to get pivot table rows to show in two columns

shadowpook

New Member
Joined
Jan 2, 2008
Messages
8
In Excel 2003, if I had a pivot table, the non-sum data (sections) would display nicely in two or more columns, now it all cascades into one column.

For example, if I wanted to see the Dept, then the subclasses within the dept, and the totals, I would set Dept and subclass as rows and the sum of the amount as the totals. When it displayed, I would get Dept in Column A and SCL in Column B, then the sum values in Column 'C'

A...............B..............C..............D
Dept 1.......SCL123.........$5.00.......
............... SCL234.........$6.00.......
................SCL345.........$20.00......
..TOTAl........................$31.00
Dept2........SCL123..........$2.00
................SCL234..........$3.00
....TOTAL..........................$5.00


Now in 2007 I can only Get Dept and SCL (any data I want to display but not sum) in column A...So I would get Dept and SCL in column A, I couldn't put SCL in column B to make it cleaner. There used to be something that I could drag so that I could get the SCL to show in Column B, but the non-sum data only cascades down
A...............B..............C..............D
Dept 1.
.SCL123....$5.00
.SCL234....$6.00
.SCL345....$20.00
Dept2
.SCL123....$2.00
.SCL234...$3.00


Any idea if I can get the data to look like it did before?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
2 ways to change:
1) Right click inside pivot table (PT), PT options, Display tab, check "Classic PT Layout"

2) In PT toolbar, Design Tab, Report Layout, choose "Show in Tabular Form". Compact Form is what you have now, Outline Form will look similar to what you had in Excel 2003, but the 2nd Row Label will be one row underneath the 1st Row Label. Both the Compact & Outline Forms will put the subtotal at the TOP (you can change subtotal to bottom: PT toolbar, Design, Subtotals: Don't show, Show at top of group, Show at bottom of group)

By the way, I've heard in Excel 2010 PTs you can opt to display the "upper" row levels all the way down (i.e. you could show your "Dept1" in every row down to "Dept2".) It will eliminate the need to GoTo/Special/Blanks, etc. when the pivot table has been copied to another sheet as values so that additional manipulation can be done.

Hope this helps.
 
Upvote 0
You are awesome!!!!!!!
I'm so happy right now. THis was SUCH a big pain!!!! This is going to save me a ton of time.

I'm excited about the upcoming 2010 to display all the way down. When I export, I always had to do that using a formula. :)

thanks!!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top