Pivot Table Format

Topic

New Reply

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 – )

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?

If not, then I will just have to write one, won’t I

Cheers,

Tim

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Timbo,
Thanks for the help.
Joe

Reply | Quote