I have a table of over 4K rows. The columns are: Site, Object, AD Group, SP Group

Example Security Group Table

I need to be able to display a summary of this data which shows the values in the "SP Group" column for each unique value in the "AD Group" column. This is a rough mock-up of the results I'd like to see.

Example Security Group Summary

The size of the data set means I can't just do it via sorting. My instinct is that there has to be a way to make this work with a pivot table. The challenge seems to be that I want to display text values.

I tried to do it using counts, with one type of group as the rows and the other type as columns. However, there are over 100 AD Groups and over 400 SP Groups. This does not create a usable summary.


Create a pivot table.

Drag AD Group and SP Group into the Rows area and format the pivot table to use Tabular form (via pivot table Design ribbon)

enter image description here

  • Thank you - I was hung up on needing values and completely forgot they are optional. – DeNaeL May 24 '17 at 16:04

Without having the data to test this, I'll answer off the top of my head so it might need tweaking.

I think I would do it by creating a pivot with AD Group as the rows and any old thing as the "data" - which will give you a count of something you don't care about. Then you can double click on an AD Group entry and the table will offer to expand another "level", choose SP Group for that. Then just hide or ignore the count column. Reformat the table as desired.


You may try re-order the whole file according to their AD group, after which the file will be displayed as one after one Ad group, then what you need to do is to delete the repeat AD group name. Finally, you will be there.

  • Welcome to Super User. Your answer is hard to follow. Please expand it and consider adding screen shots to clarify your meaning. – Twisty Impersonator May 24 '17 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.