Pivot App help

Bhh

Is there a way to create a pivot table without it turning the columns into a hierarchy? I simply want my 4 categories along the left (with no hierarchy) with years across the top. The middle is a Count of rows for each Year/Category combo.

Also, can I force the pivot to group all like rows? I am getting a row for Category 1, then a row for Category 2, then another row for Category 1, with the counts split. I can't sort by Category since a row may be in multiple categories. (I have 4 columns in my sheet for the 4 categories). Thanks!

Andy Lorance

@Bhh Pivots can be infuriating and powerful at the same time ... I feel your pain. There isn't a way to remove the primary column's outline/hierarchical setup; that's one of the pivot's primary functions. Here are a few options:

1) Try using a report instead of a pivot sheet. Then group and summarize your data from there; it will work somewhat like a pivot.

2) Create a report from your pivot sheet. The hierarchical setup visual will disappear, but may not achieve what you're after.

3) Leverage a helper column on the pivot sheet that combines the parent and children by joining ancestors (here's the formula; change the "." to whatever delimiter you like: Join(Ancestors([Primary Column]@row,"."). Create a report from your pivot sheet then use the helper column to sort/filter whatever you like. You have to show the helper column if you want to sort by it, but you can always hide it from view.

Happy pivoting!

Andy Lorance

@Bhh Pivots can be infuriating and powerful at the same time ... I feel your pain. There isn't a way to remove the primary column's outline/hierarchical setup; that's one of the pivot's primary functions. Here are a few options:

1) Try using a report instead of a pivot sheet. Then group and summarize your data from there; it will work somewhat like a pivot.

2) Create a report from your pivot sheet. The hierarchical setup visual will disappear, but may not achieve what you're after.

3) Leverage a helper column on the pivot sheet that combines the parent and children by joining ancestors (here's the formula; change the "." to whatever delimiter you like: Join(Ancestors([Primary Column]@row,"."). Create a report from your pivot sheet then use the helper column to sort/filter whatever you like. You have to show the helper column if you want to sort by it, but you can always hide it from view.

Happy pivoting!