Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables with merge cells (2003)

    Hello Everyone,

    Does anyone know if there is a way to do a pivot table with merge cells? I have a file that has merge cells and would hate to unmerge them because I need them for presentation. Thanks.

  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

    Re: Pivot Tables with merge cells (2003)

    Could you post an example of what you have and what you want? Which cells are merged?

    Steve

  3. #3
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables with merge cells (2003)

    Hello Steve,

    Here is a sample of the information that needs to go on the pivot table.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables with merge cells (2003)

    I think you'll have to create a "plain" data table that can act as source for the presentation table and as source for the pivot table. Excel can't create a pivot table from your table as it is now.

  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

    Re: Pivot Tables with merge cells (2003)

    The real issue is the that columns A-C do not a have a column label which is required. A1, B, C1 need a column label.

    The second issue only "indirectly" is caused by the merged cells. The merged cells themselves are not really the issue. The issue is that with merged cells, only the upperleft cell has anything in it, the other cells are blank. In your example
    A3= "Appliance", A4:A8 are blank
    B3="Kitchen aid", B6= "GE Prifofile", B4:B5, B7:B8 are blank.

    If you fix 1 you can create your pivot without any problem, but if you intend all the values to relate to "Appliance" and rows 4:5 to relate to Kitchen aid and 6:8 to relate to "GE Prifofile" you must indicate this in your data table.

    What you can do is keep the merged cells and create intermediate cells with the full info. In G2 and H2 enter the column labels. In G3:
    =IF(TRIM(A3)="",G2,A3)

    Copy G3 to G4:H8. Now Cols G and H are how A and B should be. now do a pivot table using cols C-H and it should be more of what you want.

    Steve

  6. #6
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables with merge cells (2003)

    Thanks Steve and Hans.

Posting Permissions

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