Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table - Reveal the Summarized Field Titles

    XL_Pivot_DisplayFields.jpgIs there a way to get Pivot Table to display the row labels for the fields it is summarizing?

    Each record in my data begins with four fields: Div #, Div Name, Dept #, and Dept Name.

    Since the data (Jobs and Employees) are at a level below the Department, the leftmost columns of the Pivot Table looks like this:
    Div # Div Name Dept # Dept Name
    001 FirstDiv 0100 FirstDeptFirstDiv
    0120 SecondDeptFirstDiv
    0130 ThirdDeptFirstDiv
    002 Second Div 0200 FirstDeptSecondDiv
    0205 SecondDeptSecondDiv
    0210 ThirdDeptSecondDiv

    I'd like the Div # and Div Name to display, but I can't find any Options to display the summarized data that repeats. I could build a standardized table to do this (and have in the past) but I think this solution would help me immensely.

    Any way to make my Pivot Table look like this?
    Div # Div Name Dept # Dept Name
    001 FirstDiv 0100 FirstDeptFirstDiv
    001 FirstDiv 0120 SecondDeptFirstDiv
    001 FirstDiv 0130 ThirdDeptFirstDiv
    002 Second Div 0200 FirstDeptSecondDiv
    002 Second Div 0205 SecondDeptSecondDiv
    002 Second Div 0210 ThirdDeptSecondDiv
    Last edited by Arcturus16a; 2014-06-12 at 12:46. Reason: Added JPG since table formatting did not appear as intended

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I do not believe that this is possible in a pivot table. You can copy the table then past values somewhere else and then fill in the blanks:
    Select the range of data to fill in the blanks
    Find and select - goto special
    Blanks [ok]
    {The blank cells will be selected}
    In one of the blank, selected cells, Enter an equal sign (=) and then select the cell above it to put in the formula
    Confirm the formula with <ctrl><enter> to fill in all the blanks with the cell contents from above.
    Select the range of cells, copy and paste-special values to remove the formulas

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Which version of Excel are you using?

    I believe it's an option in Excel 2010 (and presumably 2013 too), but unfortunately I'm not on my Excel 2010 machine at the moment. For earlier versions of Excel you'll need to use Steve's solution.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That'd be one more reason for me to upgrade Jeremy. I'm on XL 2007 SP3.

    I'll find out soon enough. I just bought a new pc for home use last week and will add MS Office 2013.

    What I'm trying to do is to summarize the daily results (Clock-Ins and Outs from our Time & Attendance system). Once summarized i want to paste the summary results into another workbook to act as a database. I've done this before with "helper" columns of formulas (like ISBLANK), but I just had to ask just in case I missed something.

    Ya never know what ya don't know.
    Last edited by Arcturus16a; 2014-06-12 at 14:59.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In XL2010 it is under (and should be similar in XL2007)
    Design - Report layout
    select "Repeat All Item Labels"
    Capture.PNG

    Steve

  6. #6
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Pivot Table - show summarized data

    Arcturus 16a,

    In Excel 2007, right click on the field and go to field settings, change from Show item labels in tabular form to show item labels in outline form and make sure both boxes are checked.
    Attached Images Attached Images
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

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