Results 1 to 15 of 15
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Format

    I've been using pivot tables for a while and one of the things that really frustrates me is the method that you have to use to format the numbers (right click, field settings, number, select formatting, ok, ok - each time for each number - <img src=/S/scream.gif border=0 alt=scream width=15 height=15>)

    I've written a fairly simple macro to perform the above but it currently only allows for one form of formatting.

    Does anyone have a macro that is more flexible? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    If not, then I will just have to write one, won't I <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Cheers,

    Tim
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    I think you can avoid the procedure you described, unless you are working XL95. Don't set the format whislt you are setting up the pivot table. When the table is in place, highlight the cells of the column you want to format, but make sure no cells outside the pivot table are included, and apply the appropraite formats. They should stick when the table is refreshed. If you have problems, there are tools on the pivot table toolbar that help you select headings in rows or columns or just data, and using those might help.

    If I remember, in XL95 it was impossible to apply formatting that would survive a data refresh, unless it was applied tediously whilst setting up the table.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Andrew:
    I use XL97 at work and at home. At work, when I try to change Formats, I get the message that I should chose Select and check Entire Table before I change the FORMAT.
    And, even then, if I change things (like add fields), I lose all the formatting. But when I do the same thing at home, I do not seem to have the same problem. I have SR 2(1) at home and I thought that I had the same at work (I need to check).
    Stephen

    But enable the

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Stephen, My experience is the same as yours, my formatting disappears as soon as I do anything to a PT. In fact I have given up on formatting until the end of a project, when I make a values only copy of the PT and format that.

    And here is another oddity I have found with PTs. I have never got the Max data function to work by going into a PT where I have used say Sum for the data, and changing it to Max. If I choose Max when first making the PT it works fine.

    Talking XL97 SR2 here.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Michael:
    I don't have the same problem with the PT MAX-Sum or Sum-MAX function. Maybe some of our problems can be fixed with re-installation? If you want to explore comparing formating problems further, post a note with a spreadsheet attached with an explanation of what is not working (e.g., are we talking about formating rows and column headers or the data in the table's cells. I will check out the PT MAX-Sum thing on my office computer.
    Stephen
    sstollma@juno.com

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Stephen,

    I don't think the SR version matters for this purpose. Right click on the table and select table Options. There is a check box for Preserve Formatting, which should be ticked. That may explain the difference between your office and home computers.

    Andrew

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Andrew, I just made a small PT, selected 1 cell, chose format cells and put a heavy border around the cell. XL came up with a message(as expected) that the Enable Selection button was not pressed, cancelled the formatting, pressed ES and reformatted with the heavy border. Worked fine. Pressed the Refresh button and my border disappeared.

    Options/Preserve formatting is ticked.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Michael,

    You certainly cannot format a single cell in XL97 PT, and hope it will retain formatting after a refresh. I suspect the reason is the PT had no way of knowing where that cell would end up on a refreshed table, as it may be displaced by additional data etc. XL2000 is more flexible, and retains at least a good deal of the formatting, if not all.
    With regard to your MAX() problems, I cannot say what is causing them, but you can have more than one PT field for any given column in the original data, so you could have a field to Sum, Average and Max etc, all coexisting on the one PT.

    Andrew

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Andrew

    I tried selecting the whole PT and changing the font (something I have not done before as I usually want to format the borders), and that was retained on a refresh. Then I put a double border around the whole PT and that disappeared on a refresh. Then I formatted single cells with different fonts and they were retained on refresh. Maybe some aspects of formatting are retained and others are not.

    Why do you think a single cell's formatting cannot be retained on refresh? Certainly PTs can change on refresh, but they often are unchanged, and I cannot see why XL should not retain the formatting in this case. And as XL retains a single cell's font on refresh, why not the border also?

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Just constructed a PT which did change on refresh, the cells I had given a different font moved down on refresh and the font moved down with them.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Michael,

    XL up to and including '97 version did not like manual formatting applied to borders, it insisted on using it's own predefined (useless) format or one of the AutoFormats if selected.

    FWIW, this seems to have been addressed in XL2000, and formatting, even on single cells seems to be retained.

    Sorry I cannot be of more help - I think you might be stuck with it.

    Andrew

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    I think that you are right Andrew. Thanks to you and Stephen for your help.

  13. #13
    New Lounger
    Join Date
    May 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Tim,

    Can you list your macro?
    I tried to write one quick and it didn't work.
    You could save me some time.

    Thanks,
    Joe

  14. #14
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Hi Joe,

    The attached file contains the macros. You will need to copy them to the macro section (I've just put them in the sheets).

    Its not the prettiest macro but it does do the job - I have yet to write the all singing - all dancing macro - maybe next year.

    Cheers,

    Tim
    Attached Files Attached Files
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  15. #15
    New Lounger
    Join Date
    May 2001
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Format

    Timbo,
    Thanks for the help.
    Joe

Posting Permissions

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