Excel Formula Problem

New Contributor

Hi, I have an excel sheet with thousands of rows four individual persons and hundreds of columns of categories. Each four consecutive columns can be clubbed as category. I want to do an analysis of this excel sheet and come up with an output as mentioned below in the sample.(Excel attached)



7 Replies



Not sure I would do this with thousands of rows & hundreds of columns (I would rather look at restructuring the sheet to enable this analysis and potentially other ones) but see attached file

best response confirmed by Haris1234 (New Contributor)


For best analysis change your structure from crosstabular form into a flat table:

Person - Category - Sub - Type - Value


Then it is just a matter of a quick pivot table.


@Haris1234 Since you mention "thousands of rows and hundreds of columns" I'd suggest you take a look at Power Query to flatten your data, as suggested by @Detlef Lewin . 


From there, you can create the desired output within Power Query, in Power Pivot (Data Model) or in a regular pivot table.


All of these are demonstrated in the attached file. 


Another Power Query + Power Pivot option (attached) in case you would need to analyse on others than A

Thank You All.
Flattening file and pivoting worked for me
formula enter in excel sheet


Species nameFrequency %DensityRelative frequency (RF)Relative density(RD)IVI=RF+RDni/N
Paspalum sp855.413.2192846#DIV/0!  
Cynodon dactylon6510.910.1088647#DIV/0!  
Dracocephalum moldavica783.912.13063764#DIV/0!  
Centella asiatica403.156.220839813#DIV/0!  
Alternanthera sp502.45#DIV/0!#DIV/0!  
cyperus sp502.85#DIV/0!#DIV/0!  
Eclipta sp50.45#DIV/0!#DIV/0!  
Spilanthes sp351.05#DIV/0!#DIV/0!