Excel PivotTable - Categories and Subcategories?

Question

I'm trying to do a fairly simple pivot table on a range of person financial transactions. Each transaction is a row in the source table, and has the following columns: Type, Description, Amount, Category.

I've got a working Pivot across these columns that can show me summed amounts for dimensions category and year, as well as dimensions month and year.

However, the number of categories I have is now too large to be useful, and so I'm looking to break these down into fewer broader categories and subcategories within those. For example, I would like to have a Level 1 category called 'Income' consisting of the Level 2 categories 'Salary', 'Contract', 'Interest'. I would then like to have the Pivot table give me a view across the higher-level categories and also allow me to drill down to see the subcategories.

What would be the best way of achieving this? Should I add another 'Category' column and then assign every transaction a Level 1 and Level 2 category? If I did that, how would I get the pivot table to initially pivot on the Level 1 category, but then allow me to dig into that to reveal the distribution of data across the Level 2 categories within?

Answer 1

You can do this with the Group feature. Just highlight the items you want in your group, e.g., Salary, Contract and Interest. Then right-click and select Group, or select it from the menu or Ribbon. You can then choose to show the detail (your original groupings) or not. Note that you can change the default name that is assigned from the new grouping from "Group1" to whatever you choose. For more info:

http://office.microsoft.com/en-us/excel-help/group-items-in-a-pivottable-report-HP010175903.aspx and this YouTube video from Debra Dalgliesh.