We help IT Professionals succeed at work.

excel pivot table subtotal with no values fields

71 Views
Last Modified: 2017-09-30
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.
Comment
Watch Question

Finance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It is a count rather than a subtotal.  
3 fields, category, First Name, Last Name.  Nothing in the Value Area.

I want a count of the people in the category.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Just drag category into value area

Author

Commented:
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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Yeah realised that after I'd commented.

I will have a think.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
Subtotal does not work it a pivot table. As soon as I click in it greys out.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
I didn't mean on the pivot, I meant on the source data.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.