Pivot Tables with merge cells (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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

Steve

Reply | Quote

Hello Steve,

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks Steve and Hans.

Reply | Quote