Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables - saving formatting changes?? (Excel 2003)

    I have a Pivot Table set up from a large data source. None of the "Automatic Formats" of the Pivot Table are what I need. For example, I want to have most of the columns centered, and on the Auto Formats they're usually right- or left-justified; I want my "date" columns to be in M-DD-YY format, but all of the Auto Formats seem to default to MM-DD-YYYY; also I have some columns that I want to "wrap text" and Autofit the Row Heights so everything in those cells is visible, and the defaults do not do this. So I'll manually change the appearance so it looks the way I want it. The "Preserve Formatting" under table options is clicked. But whenever I refresh the data, or choose a different Page Data drop-down option, it changes everything back the the Auto Format defaults again. ARGH!!! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> . What is that "Preserve Formatting" there for, anyway? What "formatting" DOES it preserve?

    How can I format the table to look the way I want it to, so it KEEPS it that way whenever I refresh data, etc.? Is there a way?

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    I have found that you should use the format arrows to select the data to format. This preserves the formatting when things are updated and moved. If you select cells in the pivot table it does not preserve the formats. Move to the edge of the field, and when your mouse pointer changes to a black arrow, click to select the info.
    Regards,
    Rudi

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    I'm having the same problem - formats keep changing back to some default. But I don't know what format arrows are. Where do you find them? I don't have any info to select. Do you select the whole pivot table? Is the edge of the field, the edge of the pivot table?

    Thanks.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    See the attachment.
    If you move your mouse the the areas in red, you can select those areas of the pivot table, add formatting, and this will then be preserved in the Pivot Table.
    Regards,
    Rudi

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    Wow... I've been wondering this same thing for years... sometimes my formatting stayed, and sometimes it didn't! Guess I was doing it right half of the time and didn't know it. Great info! Thank you! I am wondering though if it's possible to get rid of ALL the line formats in the pivot table. When I do as you suggested, it works for all the lines except the lines that look like I have the "freeze panels" on, which of course I don't... it's the one horizontal line and one vertical line that separate the field headings from the data? Is this possible?
    Thanks!!
    LJM

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    Try the following:
    - Click in any cell of the pivot table.
    - Select Format | AutoFormat... (the entire pivot table should be selected automatically).
    - Select the very last option (labeled None).
    - Click OK.
    You should now have a pivot table without any lines, and this should persist when the pivot table is refreshed.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables - saving formatting changes?? (Excel 2003)

    Perfect! You rock Hans... thanks so much!!
    LJM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •