Pivot table formatting

[Weaver]

Apologies if this is a repeat post, but my original isn't showing

I've got several pivot tables in several sheets whih people seem to not like using, mainly because they change their 'shape' when you update them. This is usually a result of column widths changing to fit. Is it possible to inhibit/customise this behaviour?

Also is it possible to show pivotted categories even when they're zero, rather than the table changing its shape/size?

2003 on WinXP

Cheers,

 

[tweedle]

On Each PivotTable
Right Click PivotTable Options
UnCheck the "Autofit Column Widths on Update"

Here's a snippet that will set the property en mass:

Sub SetAllPivotAutoFormatOff()
    For x = 1 To Worksheets.Count
        For y = 1 To Worksheets(x).PivotTables.Count
            Debug.Print Worksheets(x).PivotTables(y).Name
            With Worksheets(x).PivotTables(y)
                .HasAutoFormat = False
            End With
        Next y
    Next x
End Sub

I don't wholistically understand "pivoted categories", but generally pivots will display "(blank)" depending on how labels/data are constructed.
With the change above enacted, only the height of the Pivots should be changing.

 

[Weaver]

****, I thought I'd tried that - tried just about everything else!

Well at least that solves the column width problem.

It's not so much that (blank) shows or doesn't show.

Say if you have page fields which when used to filter the data, some of your row fields may not occur, they'll just disappear (see below, if you only show the EAST region, then the 'other' category would disappear), thus changing the shape of the table vertically. I'm wondering if you can prevent that also.

<TABLE style="WIDTH: 168pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=224 border=0 x:str><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1920" width=45><COL style="WIDTH: 42pt" width=56><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 5248" width=123><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 34pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=45 height=15>REGION</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=123>GROUP</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>EAST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REPORTED OTHER SITE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>EAST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REPORTED OTHER SITE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>EAST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REPORTED OTHER SITE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>EAST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REPORTED OTHER SITE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>EAST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REPORTED OTHER SITE</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>WEST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REVIEW</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>WEST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">W/DRAWN</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>WEST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">OTHER</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>WEST</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">REVIEW</TD></TR></TBODY></TABLE>
Thanks

 

[tweedle]

By definition, if one is filtering a pivot (or anything else), one is telling it to exclude non-matches, thereby changing the vertical space.
There must be more structure to this pivot as there are 5 instances of EAST REPORTED OTHER SITE which wouldn't be if these were the only pivoted row fields.

If you wanted to maintain a consistent number of row-labels for each iteration of the pivot refresh such as you would see

REGION GROUP
EAST REPORTED OTHER SITE
EAST W/DRAWN
EAST OTHER
EAST REVIEW

WEST REPORTED OTHER SITE
WEST W/DRAWN
WEST OTHER
WEST REVIEW
every time,
then you would need to maintain those entries in your source data.
You could, at this point, create an area with your row-label placeholders, then add that area to the Pivot Table data source via the Pivot Wizard, creating a pivot w/ a multi-range data source. That would give the pivot table the consistent labeling, even when those labels did not exist in your actual data. Clear as mud?

 

[Weaver]

You're right, that was by no means the full set of data in the pivot. I'm going to try your other suggestion tho - thanks.

DW