CODEWITHSUNDEEP
excelpivot-table
FranckH
FranckH

Reputation: 155

Excel pivot table stacking fields

(Excel 2010) I have a range with multiple column in it and want to do something like this :

Data :
Food type - Food item - Date - Price
Vegetables - Tomatoes - 2016-01-01 - 5$
Vegetables - Tomatoes - 2016-01-02 - 7$
Vegetables - cucumber - 2016-01-01 - 6$
Vegetables - cucumber - 2016-01-02 - 8$
Fruits - Apple - 2016-01-01 - 5$
Meat - Beef - 2016-01-01 - 10$

The result I want :
Fields - Avg(Price)
Tomatoes - 6$
Cucumber - 7$
Apple - 5$
Beef - 10$
Vegetables - 6.5$
Fruits - 5$
Meat - 10$

So I want to stack the fields one over the other and not in a hierarchy like this

Vegetables
- Tomatoes
- Cucumber
Fruits
- Apple
Meat
- Beef

Any Idea on how I could do this without manipulating the data ?

Thanks

Upvotes: 0

Views: 1197

Answers (1)

pnuts
pnuts

Reputation: 59450

Copy the Price column into a column on its immediate right. Create a PivotTable from multiple consolidation ranges (eg as indicated here as far as #4) with one range starting Food Type and ending at the left-hand Price column adding a second starting at Food item and ending at the right-hand Price column. Set the VALUES area to be Average of Value, uncheck Show grand totals for rows and Show grand totals for columns if necessary, hide the Date and Food item columns, order and format to suit.

SO38076959 example

Upvotes: 1

Related Questions