Results 1 to 3 of 3

How to get pivot table rows to show in two columns

This is a discussion on How to get pivot table rows to show in two columns within the Excel Questions forums, part of the Question Forums category; In Excel 2003, if I had a pivot table, the non-sum data (sections) would display nicely in two or more ...

  1. #1
    New Member
    Join Date
    Jan 2008
    Location
    Naperville IL
    Posts
    8

    Default How to get pivot table rows to show in two columns

    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?

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,020

    Default Re: How to get pivot table rows to show in two columns

    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.

  3. #3
    New Member
    Join Date
    Jan 2008
    Location
    Naperville IL
    Posts
    8

    Default Re: How to get pivot table rows to show in two columns

    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!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com