CODEWITHSUNDEEP
excelpivot
David542
David542

Reputation: 110093

Multiple pivots in Excel

I am working with Excel and have a pivot table like so:

                     [ Gender, pivot ]
Year > Quarter       Male       Female
[-] 2014             64%         36%
      Q1             60%         40%
      Q2             70%         30%
      Q3             60%         40%
      Q4             60%         40%

This would have what I call two two row-aggregations (Year, Quarter) and one column-aggregation or pivot (Gender). Is there a way to have multiple pivots, for example having it with one row-aggregation (Year) and two column-aggregations (Quarter, Gender)

Year                 [Quarter > Gender ] 
                      Q1        Q2         Q3         Q4
                     M | F     M | F      M | F      M | F
2014                60 | 40   70 | 30    60 | 40    60 | 40

If this is not possible, are there other tools that would allow me to do multiple pivots? Is this a common use case in data-analysis, having multiple pivots?

Upvotes: 1

Views: 65

Answers (2)

teylyn
teylyn

Reputation: 35915

In addition to user3240704's post, when you group the dates, the smallest unit you choose in the grouping dialog will be showing with the original column name. If you group by Year, Quarter and Month on a column called "MyDate", you will see Year, Quarter and "MyDate" in the field list and can drag "Quarter" to the column labels as described. If your grouping is only on Year and Quarter, you will not see a "Quarter" field and will need to drag the "MyDate" field into the column labels field well.

In the screenshot, my date field is called "date" and grouped by Year and Quarter.

enter image description here

Upvotes: 1

M O'Connell
M O'Connell

Reputation: 487

Sure, Move the Quarter into the "Column labels" box (above values)

enter image description here

Upvotes: 1

Related Questions