Hide all but the last column of data in a pivot table
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Hide all but the last column of data in a pivot table
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!
You do not have the required permissions to view the files attached to this post.
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Re: Hide all but the last column of data in a pivot table
Looking at my attachment, I'm pointing to the wrong row to hide, should be column G (the average for Jan) not F
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hide all but the last column of data in a pivot table
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Re: Hide all but the last column of data in a pivot table
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
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Hide all but the last column of data in a pivot table
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.
Regards,
Rory
Rory
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Hide all but the last column of data in a pivot table
No big deal
You do not have the required permissions to view the files attached to this post.