pivot table formatting

[Guest]

Using 2003 (11.5612.8107)

This is my first real work with a pivot table. In 10 minutes I was able to
create a beautiful table with exactly the desired look. However if there is
any slight change, I have to rebuild things from scratch or at least reformat
it. For example in the left most (Row field) i use Request Type. If I
change the Request Types being displayed, the format of the table comes
undone.

I have read an old post and selected Pivot Table, Table options, Preserve
Formatting. I've also clicked on Select, Enable selection. Yet if I add or
remove one of the items to display and all the column widths need resetting.

Is this normal PT behavior? It makes it unviable if I can't lock it down.

Thanks much,

Russ

 

[KC Rippstein]

There was another checkbox for "Auto Format" 2 positions above the checkbox
for "Preserve Formatting". Whenever you check "Preserve Formatting", you
need to uncheck "Auto Format" in order to stop that behavior.

 

[Guest]

AAAaaahhhh!! See it. Thanks. One more quick question. I can't tell when
Pivot Table|Select|Enable Selection is selected or unselected. It appears
two ways:

one where the little graphic to the left is all grayish, and one where the
little graphic to the left is grayish with a little bit of border color.
Which graphic indicates that Enable Selection is actually selected?

thanks

 

[Guest]

KC -

I'm struggling. I have:

* all filters set to "All"
* Talbe Options|Preserve formatting checked
* Talbe Options|Autoformat table un-checked
* can't tell whether Select|Enable selction is on or off. I've tried it
both ways

I do all that and then do some slight formatting changes - add a border,
change most columns to Wrap Text. then I change any one of the filters from
All to something less than All, and the borders disappear and the Wrap Text
goes away.

Arrgh.

Russ

 

[Guest]

FIgured it out!

In order to get the column width and wrap-text formatting to "stick" I must:

1) do NOT select the whole worksheet column. Instead hover the mouse above
the tope of the PT column until it turns into a black down arrow

-AND-

2) then right mouse click on the now highlighted column and select Format
Cells. Seems like if I use the Format Cells from the main menu the
formatting doesn't stick.

 

[KC Rippstein]

Exactly. Sorry, I was out to lunch...glad you figured it out. I was going
to mention your first point. When you are working with a pivot table,
forget the fact that it's on a worksheet...the worksheet itself is actually
rather irrelevant for the most part. Virtually all your formatting and
properties are attributed to your OBJECT (the various areas of the pivot
table itself) rather than to the worksheet. Same thing with charts,
pictures, and other objects you work with in Excel.
Good job!

 

[Guest]

KC - are you still with me?

Things are going well. I've modified my pivot table to pull from a dynamic
Named Constant so I can change the size of data inputted. Very cool.

My last bit of formatting angst is that I can't get the far right Total
column headings to keep their Bold format. Whever I press the refresh key,
the bold goes away. For these 2 fileds there is no "Black arrow" option. I
can make the arrow appear above the column, but then applying the bold format
would bold the data, and I only want the column heading to be bold.

I need to make this bullet-proof as I'm a contractor and will leave at some
point. I don't want the users to have to do anything more than paste in the
new data each month. Any ideas?

thx

 

[KC Rippstein]

Yep, I'm here.

My only suggestion is to approach it from the side instead of from the top.
Position your mouse over the row of the pivot table that has all your
"titles" for each column and format all those headers to bold. That's the
best I can offer without getting into a macro (which I'm not great at...just
got 2 VBA books for Christmas and am getting better, though).

By the way, if you really want to avoid the end user having to "refresh" the
pivot table, you can have it automatically refresh whenever the file is
opened (which does not help when you open it and then add new source
data...you still have to manually refresh then). Or you can have it refresh
any time someone selects the pivot table worksheet; just right click that
worksheet tab and select "View Code", then paste this in there:
Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub
Press Alt+Q to get out of the Visual Basic Editor. If you don't want the
pop-up window that asks the end user to allow/deny macros, try digitally
signing the worksheet. Otherwise, they just have to remember to always
allow macros. I find this more attractive than having to remember to
manually refresh the pivot table when the source data is changed. People
just forget to do it, so automating the refresh is generally a better
long-term solution.

 

[Guest]

More good ideas. thanks so much! I eventually figured a work around. why
do i have to have so many work arounds with excel? it's very cool and very
frustrating. anyway i ended up hovering the black arrow over the top of the
column and turning the whole column to bold. i then went to the left had
side of the report and hovered over the rows and unbolded the ones i don't
want to be bold. pain in the butt but once complete the formatting sticks.
darn nice looking report now!

thanks again for all the help

cheers!