Switching Aggregates in Pivot Fields

We’ve all been there. You create a pivot table, add your Values fields, and Excel thinks you want to Count them instead of Sum them just because you have a few blanks.

To fix it, you can click the yellow Count of Labor (for example), choose Value Field Settings, and change the aggregate. Or you can right click on any field and choose Summarize Values By and switch it to Sum. Both good options, but not good enough. I assigned Ctrl+Shft+A to this happy little customer and I’m toggling aggregates like crazy.

There’s probably a bug or two, but so far so good.

10 thoughts on “Switching Aggregates in Pivot Fields

  1. I’ve been using this code for years to change all aggregation to sum, and remove the “Sum of ” prefix. For the relatively few times I need to use count, I’ve been doing it manually, but I like your toggle solution.

    Sub SumAllDataFields()
    Dim pt As PivotTable
    Dim pf As PivotField

    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(1)
    On Error GoTo 0

    If Not pt Is Nothing Then

    pt.ManualUpdate = True

    For Each pf In pt.DataFields
    pf.Function = xlSum
    pf.Caption = Space(1) & pf.SourceName
    Next pf

    pt.ManualUpdate = False

    End If

    End Sub

  2. Nine times out of ten, it’s the Count I need (I count a lot of things), and what I’m counting is items. But they decided to make the item number an actual number, so Excel thinks I want to add up the numbers. So it’s into the Summarize Values By again.

  3. @Dick: I’m coincidentally working on an update to my InstantPivot routine at http://dailydoseofexcel.com/archives/2014/02/12/instant-pivot-just-add-water/ that will offer to replace blanks with zeros (for numerical columns) or ” ” (for text columns) in a PivotTable’s source data, which would mean you would never get this COUNT behavior.

    @Scott: Sometimes I have the same field in the Values area multiple times, which would cause your code to error out. I’m currently amending my InstantPivot routine to handle that eventuality too.

    That routine creates a PivotTable and also automatically turn the source data range into an Excel Table. If you happen to run it on an existing PivotTable, it will retrospectively turn the source data into a Table if needed, and then re-point the Pivot at that Table, plus do all the kinds of formatting outlined above and a good deal more besides.

    So I use it for new pivots and old pivots alike. One macro, one shortcut to learn, hours of giggling.

  4. @Dick: Neat idea, I really like it!

    Though in many cases, the “Count” is a good indication that the data is not as clean as I would expect it – e.g. somebody applying a pivot the whole column(s) – or having some blanks somewhere… So usually better to fix the problem at the origin than the change the symptoms… :-)

  5. Would it be possible to augment choices to include Median or ,say, 80th Percentile, etc, etc?

  6. @Peter: What, develop a solution to correct the actual cause of the problem? What madness is this! It is so much more fun to apply a symtomatic fix now and deal with the unintended consequences later!

  7. Jeremy: You can cycle through more than just Count and Sum, but you’re limited to the aggregate options. Average, Count, CountNums, Max, Min, Product, StDev/P, Sum, Var/P.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.