Results 1 to 2 of 2
2010-11-29, 16:17 #1
- Join Date
- Nov 2010
- Washington State
- Thanked 0 Times in 0 Posts
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.
2010-12-02, 10:33 #2
- Join Date
- Dec 2009
- Manchester, United Kingdom
- Thanked 17 Times in 16 Posts
I hope you find the answer you're looking for, but I don't think it is possible directly.
You can get a sort of result by adding a lot of extra columns to your data table, populated with formulae like "if(col=value,1,0)" for example if gender is in column C and has values M or F, add a column headed M and a column headed F. In the M column put the formula =if(C2="M",1,0) and similarly for the F column. Add one column for each possible value of each demographic variable. When you build the pivot table use these columns for the values section, nothing in the column labels section (Excel will use the actual column labels). Against each school you'll see the totals for each group.