This is my first post to the lounge. I looked in the archives and googled everywhere and haven’t found an answer.
I would like to merge two pivot tables into a single table without nested columns.
Specifically, I work in education – so as an example have a list of students, their race, their gender, their school, their zip code, and the year they enrolled.
I want to look at a count of student gender by school. I know how to build a simple table that gives me that.
(Row = building and column = gender and data = count of student ID)
I also want to look at a count of student race by school. Ditto – easy.
However, if I want to put both of those tables side by side in a single mega table, it looks like Excel forces me to view counts of the variables nested together which is more detail than I need. In this case I see: White Males, White Females, Asian Males, Asian Females, etc. when what I really want are counts of males, counts of females, counts of White, counts of Asian, etc.
This is important because once I have this base table, I will create separate tables for specific enrollment years and zip codes (using the filter) and of course I’m dealing with more than just two demographic columnar variables. Essentially, I want to minimize the number of separate tables I have to adjust with each variation.
In a nutshell: How do I get rid of the nesting “feature” in Excel 2007? I remember being able to format the layout of pivot tables in a prior version of Excel, but I can’t figure out how to do it in the current version.
Any insights would be appreciated.
Thanks
