MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table - Format


Posted by Susie on May 18, 2001 8:42 AM

I have a Pivot Table with Page Field with 4 items in the drop down. My pivot table report has about 15 columns, and I want borders between the columns. When I place the borders, it's fine, but if I choose any of the entries in the drop-down list that changes the table, I lose the borders. I selected Reserve Formatting in the Table options, but that does not work, the borders disappear.


Posted by Dave Hawley on May 18, 2001 9:27 AM

Hi Susie

Have you looked at the AutoFormat.... under "Format" on the MenuBar.


Dave
OzGrid Business Applications

Posted by Susie on May 18, 2001 9:54 AM


Dave, how do I do that? When I select the pivot table and click on Format, Autoformat, I get the reports/tables format you use to set up the pivot table, i.e., Reports 1 - 10 and Tables 1 - 10.

Posted by Mark W. on May 18, 2001 10:26 AM

Susie, AutoFormat allows you to apply a pre-defined
formats (some of which are quite gawdy). That might
be more than you're seeking. Here's the deal with
the specific cell formatting (like borders) that
you're trying to do...

You must "Enable" the selection of the cells whose
format you want to change. Furthermore, if you
want the formatting changes to be effective for
all values in your Page field you must 1st set
all of your Page field items to "(All)". Now for
the enabling part...

If you choose the PivotTable | Select | Enable Selection
command (available on the PivotTable toolbar) the
"Enable Selection" icon must be depressed. You
have to look closely because the change in the icon
is very subtle.

If you follow these steps then and only then will
your formatting changes be preserved when the
"Preserve formatting" option check box is checked.

Posted by Susie on May 18, 2001 10:53 AM

Can you give me more details on this. Do I select the pivot table that I want to add borders to and then Enable Selection or Enable Selection button first and select table and then format borders???


Posted by Mark w. on May 18, 2001 11:06 AM

> Can you give me more details...

Enable Selection first. You'll notice that there
are associated tools to help you automatically
select logical sections of the PivotTable.
You'll probably want to "play" with these to
better understand their behavior. Keep in mind
that if all you want to do is format a single
cell in the PivotTable you can do that too, but
you must Enable Selection... and don't forget
about the consequences of not setting your Page
field(s) to '(All)'. Play around you can't
hurt anything! : ) : Susie, AutoFormat allows you to apply a pre-defined


Posted by Susie on May 18, 2001 11:23 AM

Enable Selection first. You'll notice that there

Thank you so much. It worked. I was having problems with it, but I figured out the problem was because I had it to insert a blank line after each entry from the Pivot Table Field Layout Menu. When I took that out, the formatting with the borders remained. I would still like to have that extra line in if you have any more suggestions. Thanks again. Susie

Posted by Mark W. on May 18, 2001 2:07 PM

> I had it to insert a blank line after each
> entry from the Pivot Table Field Layout Menu

I didn't quite follow... and you've piqued my
curiosity. Care to elaborate? : > Can you give me more details... : Enable Selection first. You'll notice that there

Posted by Susie on May 20, 2001 5:10 PM

Sure. When I created the pivot table, I went to The Pivot Table Field Layout Menu and one of the options is to insert a blank line after each entry. I just liked the look of the table with the extra line in between each item. The "Enable Selection" was not working when I added the border so I thought maybe I'd take out the line and see what happened. Then the borders I had applied using Enable Selection worked. This is the first pivot table I've ever done so this board is very helpful and I appreciate yours and everyone's help.