Excel: Automatically Expanding PivotTable Rows
Dear Experts
I have been asked to create a spreadsheet which contains a list of costumers and their orders.
The customer can choose from a number of different fruits, such as apple, pear etc.
I have created a table for this ('orders')
On another sheet I would like to summarise all orders, grouped by fruit.
I assume the easiest way is to use a Pivot Table.
How do I set up the Pivot Table to automatically add the fruits based on the order table header; fruits might be added or removed albeit not that often. Is this possible with a Pivot Table or is there another method?
Thanks for your help.
mscola
Orders.xlsm
I have been asked to create a spreadsheet which contains a list of costumers and their orders.
The customer can choose from a number of different fruits, such as apple, pear etc.
I have created a table for this ('orders')
On another sheet I would like to summarise all orders, grouped by fruit.
I assume the easiest way is to use a Pivot Table.
How do I set up the Pivot Table to automatically add the fruits based on the order table header; fruits might be added or removed albeit not that often. Is this possible with a Pivot Table or is there another method?
Thanks for your help.
mscola
Orders.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See attached.
With this layout movement of items around in the pivot is much easier.
Pivot-fruit-example.xlsx
With this layout movement of items around in the pivot is much easier.
Pivot-fruit-example.xlsx
If so, easiest way is to convert the data range to a table rather than a standard list and then refer the pivot to the table by name rather than a fixed range.
Select a cell in the data range and press Ctrl + T to start the Insert table wizard. The range of the data should be automatically selected and check the box for "My data includes headers" is ticked, click OK.
Looking at the striped effect of your sample data, I suspect your data might already be a table. If so, the pivot should just be amended to refer to the table by name.
Select a cell in the Pivot and from the Pivot Table Tools ribbon, Design tab choose "Change Data Source". Overwrite this manually with "Table1" (assuming table is called Table1) or select the whole data range manually and once the whole table is selected it should change to the Table name.