Pivot table problem (connected cells)

New Contributor


I am currently facing a problem with my pivot table in Excel. The data (base) table that I am using has some columns with connected cells and others that are separated. For the analysis, I need the pivot table to assign all singular cells of column A to the connected cells of column B. However, at the moment, the Pivot table only assigns the first cell element from column A to the connected cell element of column B.

Separating the connected cells will not solve the problem, because I need the pivot to assign all corresponding elements (cells) in column A for the one element in column B. Is there a solution to this problem?
Thank you already.



5 Replies



Can't quite visualize what you're describing. Is it possible for you to post a copy of the file, if not here in this forum then on OneDrive or GoogleDrive or equivalent. Grant access and post a link here.


Thanks for the quick reply. I created a simple case example in Excel. I hope this explains the problem better. When you download the file as an xlsx file, you should be able to access the pivot table. 


Google Drive Excel Sheet 


You'll have to unmerge C9:C10 and repeat the value "Apple" in C10. Otherwise, your source data is not a real database-like table.



@Hans Vogelaar
Yes, but is there another solution to assigning the properties (sweet & sour) to Apple? Because otherwise my amount of Apples in the table becomes untrue. The real table I am using is much bigger and the amount of entities in column B is of importance.


Add the source range to the data model when you create the pivot table:


You can then select Distinct Count as aggregation function for the value field: