Make an Excel PivotTable with multiple or nested rows

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

A list with groupings (like Product Type, Country, Region or Staff pay level) can become a nested PivotTable.  The sub-totals are created with  + – signs to show/hide the groupings.

make an excel pivottable with multiple or nested rows 15436 - Make an Excel PivotTable with multiple or nested rows

We’re using the same source list as in our Basic PivotTable and Two-dimensional PivotTable examples.

Doing that is simple, just drag a second item down to the Rows area:

make an excel pivottable with multiple or nested rows 15438 - Make an Excel PivotTable with multiple or nested rows

Drill down more

Adding another row level lets you drill down even more.  In this case to the date of each order.

make an excel pivottable with multiple or nested rows 15439 - Make an Excel PivotTable with multiple or nested rows

Adding a date field will automatically group as well, in this case by Month.  You can drill down from the top level (Category) to sales by month and finally sales per day.

make an excel pivottable with multiple or nested rows 15440 - Make an Excel PivotTable with multiple or nested rows

If you don’t want the auto-grouping just choose the Remove Field option from the pull-down menu for the grouping eg ‘Months’.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address