excel pivot table subtotal with no values fields
I want to have a subtotal on a pivot table that is essentially a list and has no value fields. I don't want to add one and have a bunch of 1's running down the side of the table. It seems it should be easy but not so much. the fields would be like category, first name, last name and I want a subtotal on category as an example.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Just drag category into value area
ASKER
which gives me 50 1's running down the right side of my report and an extra column just for the total. I was hoping there was a more elegant way to do it.
Yeah realised that after I'd commented.
I will have a think.
I will have a think.
Maybe you can use the subtotal function instead. To use this feature:
1) Sort the data by Category and ensure all columns have headers
2) From Data menu choose Subtotal and a popup screen will come up with various selection options:
- Subtotal by: Choose "Category"
- Calculation: Choose Count
- Various options at the bottom that are fairly self explanatory
Click OK
This will add rows between each category with a count by Category. It will also add Grouping icons in the left margin so that each group can be expanded or collapsed. Use the + and - buttons against each group to expand (+ button) or collapse (- button) individual groups or there will be buttons with numbers on at the very top left. The numbers will display the levels of expansion, level 1 will be only grand total, level 2 subtotals, level 3 detail.
Hope that helps instead.
1) Sort the data by Category and ensure all columns have headers
2) From Data menu choose Subtotal and a popup screen will come up with various selection options:
- Subtotal by: Choose "Category"
- Calculation: Choose Count
- Various options at the bottom that are fairly self explanatory
Click OK
This will add rows between each category with a count by Category. It will also add Grouping icons in the left margin so that each group can be expanded or collapsed. Use the + and - buttons against each group to expand (+ button) or collapse (- button) individual groups or there will be buttons with numbers on at the very top left. The numbers will display the levels of expansion, level 1 will be only grand total, level 2 subtotals, level 3 detail.
Hope that helps instead.
ASKER
Subtotal does not work it a pivot table. As soon as I click in it greys out.
I didn't mean on the pivot, I meant on the source data.
ASKER
3 fields, category, First Name, Last Name. Nothing in the Value Area.
I want a count of the people in the category.