Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
fnov
 
Posts: n/a
Default how do I format a pivot table column - permanently?

I have tried to format the column width in a pivot table, only to find that
when I change selection in any of the fields that the column reverts back to
the original width.

Is there any way to "lock" the formatting in a pivot table?


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: how do I format a pivot table column - permanently?

Yes, there is a way to lock the formatting in a pivot table column. Here are the steps:
  1. Select the column that you want to format in the pivot table.
  2. Right-click on the column and select "Format Cells".
  3. In the "Format Cells" dialog box, select the formatting options that you want to apply to the column, such as the width, font, and alignment.
  4. Click on the "OK" button to apply the formatting to the column.
  5. Right-click on the pivot table and select "PivotTable Options".
  6. In the "PivotTable Options" dialog box, click on the "Layout & Format" tab.
  7. Under "Format", check the box next to "Autofit column widths on update".
  8. Click on the "OK" button to save the changes.

By checking the "Autofit column widths on update" option, the formatting that you applied to the column will be locked in place and will not revert back to the original width when you change the selection in any of the fields. This will ensure that your pivot table column remains formatted the way you want it to be.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

Set the pivot table to preserve formatting:
On the pivot toolbar, choose PivotTableTable Options
Add a check mark to 'Preserve formatting', click OK
Remove the check mark from AutoFormat Table
Click OK

You may have to enable selection (From the Pivot toolbar, choose
PivotTableSelect, and click on Enable Selection)

Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.

If the above suggestions don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want
to update.


fnov wrote:
I have tried to format the column width in a pivot table, only to find that
when I change selection in any of the fields that the column reverts back to
the original width.

Is there any way to "lock" the formatting in a pivot table?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #4   Report Post  
Andy Brown
 
Posts: n/a
Default

Is there any way to "lock" the formatting in a pivot table?

There's a way to "unlock" the standard formatting. Rightclick a cell of the
table, select "Wizard". Click "Options", *un*check "AutoFormat Table". Click
OK & Finish.

Rgds,
Andy


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot chart format Valeria Charts and Charting in Excel 1 January 14th 05 01:19 PM
How to create a calculated field formula based on Pivot Table resu dha17 Excel Discussion (Misc queries) 1 December 15th 04 05:39 AM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 03:56 AM
convert excel list to pivot table GI Excel Discussion (Misc queries) 0 December 6th 04 06:45 PM
How Do You Permanently Format Fields of a Pivot Table? Marcia Excel Discussion (Misc queries) 1 December 6th 04 01:55 PM


All times are GMT +1. The time now is 08:47 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"