MS Excel 2007 Retain Pivot Table formatting 1

[RRinTetons]

I am building pivot tables from an SSAS data source. As I add or remove dimensions in the column headers I'd like them to retain the formatting (e.g. - alignment to vertical) as all other headers. Is there a way to get that done short of a bunch of VBA?

-
Richard Ray
Jackson Hole Mountain Resort

 

[AnotherHiggins]

Anywhere in the pivot table, Right click > Table Options.

Make sure that Preserve formatting is checked.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Sorry - You put 2007 right there in your subject line....

Anywhere in the pivot table, Right click > PivotTable Options > Layout&Format.

Make sure that Preserve cell formatting on update is checked. Decide whether to check/uncheck Autofit column widths on update depending on the behavior you want to see.


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[RRinTetons]

That works as long as the update doesn't add rows or columns to the previously display data. I want way that the formatting of the existing row or column headers gets carried into the new ones.

Then I'd like to work on world peace...

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Just to clarify...

I'm looking for a way to format cells based on what level in the column header hierarchy they're on. Level = 1 then format like this, level = MaxLevels then format like that, level in between format some other way, etc, etc.


-
Richard Ray
Jackson Hole Mountain Resort

 

[AnotherHiggins]

This is something that 2007 does well.

Go to the PivotTables Tools tab in the Ribbon (only visible when a cell in the PT is selected), then to Design > PivotTable Styles. There are a ton to choose from. Hopefully there's one you like.

If you want to do something like bold a header, just make sure to select the entire section. Your cursor will turn into a down-arrow and when you click it all cells that are part of that field will be selected. Here's an example of what I mean:

You can see that by clicking on Data, both "Sum of Thing1" and "Sum of Thing2" and selected. Now any formatting you apply to them will carry over to any newly-imported fields in that section.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[RRinTetons]

Ah hah!!

I like it. Thanks.


-
Richard Ray
Jackson Hole Mountain Resort