Pivot table formatting

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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,
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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:
Code:
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
****, 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

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,509
Messages
5,511,712
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top