Pivot table formating 1

[ItIsHardToProgram]

My problem might or might not be simple, but it is surely quite hard to get the correct information on how to proceed.

My situation:

I have multiple pivot tables with data in it, in each pivot table there is sometime the same data, some time different data exposed.

I have formated my first pivot table to a format I thought would suit me. In fact it did suit me but I found a small problem.

**so it is clearer: when I say format, I mean cell border and font size and boldness(Isn't that a cute word).

Has I copied the sheet, and the pivot table with it, and I changed the filter in the pivot table, my formating changed, to a format that I did not mind, but that was the initial format (and not the one I created), so I figured, I would leave it as is, and keep the initial format as it was too much trouble to reformat every pivot table, + when I refresh it, it reformats.

My problem is, I noticed when I copied a pivot table, and changed it to some of the initial data that was being shown in the formated pivot table, it appeared as the format I had put for it, I tried changing it back, but as soon as I would refresh it, it would return to the format I had defined for it.

Now I can't find a way to put the format of my initial pivot table in the same format of my other pivot tables, and so when I have a pivot table that shows part of the initial pivot table data, and part of the other pivot table data, I have data that have two different formats in the same pivot table (looks really funky).

Also, I would like to have a specific date format applied to one of my column in the pivot table, but I can't seem to make that permanent either (personalized format cell to [h]:mm:ss)


This is alot of information for a small but painful problem I hope you can provide with some good solutions for me.

Thanks, its always a pleasure working with you guys.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[xlbo]

Right click on Pivot Table

Table Options

Uncheck "Autoformat table"

Format pivottables as you desire and the formats shouldbe kept

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ItIsHardToProgram]

As I said, quite simple, Thanks for your quick help.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Looks like theres still no joy, I was too hasty I guess.

Ive done the changes you asked, but as soon as I add any type of data, and I update it, that specific data is not formated as it should be, even though the WHOLE column of the worksheet is correctly formated.

Any ideas?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Oh darn, every pivot table is treated as a different entity, thus I have to select ALL data for the WHOLE pivot table in order for this to work, this leaves me with a problem still. what do I do when I add new data (this is done monthly) I would have to reformat everything, thats a big nono, how could I make this dynamic?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Turn on your macro recorder and record applying whatever format you want.

If you need help customizing to your workbook, please post back in forum707 along with your recorded code so that eaxh time your refresh your PT, the format can be restored.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Will do, I will avoid running two threads at once though, I will when I fixed my other problem. (I think I have a good idea how to go from there, although I wanted to avoid treating my pivot tables through macro...)

Thanks alot skip, your always alot of help.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[xlbo]

Rather than format the cells themselves, format the FIELD in the pivot table itself

Right click on the field header, choose "Field Settings", then "Number" and format as you desire - this should keep things tidier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ItIsHardToProgram]

Yes, this is what I have been doing though, with no success...

Its quite odd, I don't like the way pivot tables behave, but they are the only things I can use, in Excel, in order to output decent reports.

The problem I had, was simple, I would remove the autoformat, etc.... and I would format the fields. Then I would "update" the pivot table, everything was staying tight.

As soon as I added or removed any filter in the pivot table, the data would either:

-Reset to its original setting

-Keep the other settings I've had applied to the data.

Either way this was very frustrating.

I thought of having all filters "off" and formating EVERY CELL on the darned sheet.

Still no joy...


Pivot table formating is a mistery to me, so Ill have to reformat before prints, wich is not necesserely the best thing considering this wont be Extremly dynamic, but dynamic enough for me to live with it.

I'll just hope *cross fingers* I won't get corruption and ghost data, as I did last time I tried using pivot tables.

I'll of course enjoy any inputs to a fix on my situation.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.