Hide all but the last column of data in a pivot table

[SammyB]

Pivot Table Sample.xlsx

I have a simple table of sales data with Date, Client, and Amount columns. I would like to create a Pivot Table with Months as columns & Clients as rows. That is simple & it adds a column at the end with a total for each customer.

I would like another column on the right, after the totals that shows the average for each client. I have attached a sample worksheet. It shows that I can add the values twice & change sum to average, but it also displays monthly averages. How do I get rid of/hide all columns of the second instance of the data except the overall average for each client? Thanks!

[SammyB]

Looking at my attachment, I'm pointing to the wrong row to hide, should be column G (the average for Jan) not F

[HansV]

As far as I know, there is no pivot table setting to do that, so you'd have to hide the columns either manually or through VBA.

[SammyB]

Thanks, Hans! Fortunately, the real Pivot Table is on a sheet by itself, and I wrote a macro to do it:

Code: Select all

Public Sub HidePivotTableAverageColumns()
    Dim ws As Worksheet, pt As PivotTable, r As Range, pc As PivotCache, c As Range
    Dim i As Long, iLabelRow As Long
    Set ws = ActiveSheet
    Set pt = ws.PivotTables(1)
    Set r = pt.TableRange1
    For i = 1 To r.Rows.Count       ' Find the row with labels
        If r.Cells(i, 1).Text = "Row Labels" Then iLabelRow = i
    Next i
    For Each c In r.Rows(iLabelRow).Cells   ' Hide all but overall average
        If InStr(c.Text, "Average") > 0 Then
            ws.Columns(c.Column).Hidden = True
        End If
    Next c
End Sub

[HansV]

Good for you!

[rory]

FYI, if you load the data into the data model and create a pivot table from that, you can then create a column set that would only show the averages for the total.

[snb]

No big deal