Formatting a pivot table

Of course:

I have several pivot tables that I absolutely want to keep on the same page.
These tables are formatted (centered characters, conditional formatting on certain labels, bold, and especially, column width).

However, each time the spreadsheet is refreshed, this formatting is lost, causing the tables to become too wide and the tab to become too complicated to read.

I am therefore looking for a way to freeze the formatting of these tables so that when refreshed, the values are updated and not the formatting.

I hope I have been clear enough! :)

In search of a solution to the same problem
I found that to select the entire table and properly repeat the formatting everywhere: http://office.microsoft.com/en-us/excel/HP052002211036.aspx

Otherwise, for very specific formatting: I usually copy (to the right of the same sheet) the cells linked to the Pivot Table

and that way, when I update the Pivot Table, the cells on the right are also updated.
For the final result... just hide the Pivot Table

I hope this can help
Daniel

Thank you for your response.
Nevertheless, this allows me to update the data, but not to maintain the formatting of the table...

:)
I've written quite a bit of nonsense since yesterday, but I had thought about that...

I don't quite understand what you are looking for! Can you be more specific?

@+

Yes, it's clearer now. Have you tried formatting the report that you can find in the toolbar of the pivot table?

@+

Arf... Thanks anyway!

Good evening,

In VBA then...

- Set your column widths on your template and note on a piece of paper the widths you assign to each column (the first number (the one supposed to be in number of characters), not the one in pixels).
- Right-click on the tab name of your sheet and choose 'View Code'
- Paste the following code into the code window (on the right in the editor):

Private Sub Worksheet_Activate() Columns("A:A").ColumnWidth = 20 Columns("B:C").ColumnWidth = 8 Columns("D:D").ColumnWidth = 6 Columns("E:G").ColumnWidth = 16 Columns("H:H").ColumnWidth = 4 End Sub 

This is an example, place the columns or ranges of columns that interest you and their value.
You can also do it by recording a macro. In this case, after the recording, remove all the lines where there is no Columns("x:x").ColumnWidth = , and replace Sub Macro1() with Private Sub Worksheet_Activate()
Each time you refresh, just activate another sheet and come back to it to reset the widths.

Or you can do it with the double-click event of the sheet.
If you prefer this, replace it with this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Columns("A:A").ColumnWidth = 20 .... .... cancel = true End Sub 

Eric

From memory
conditional formatting doesn't work well in a pivot table for Excel 2003

check Excel's help: there are some caveats about this

--
xkristi
I do not claim to know the truth, I willingly leave it to you...

Hi xkristi, how are you? :-)

It was so simple...
Well done ;-)
eric

:)

Thank you !!
I'm not at work right now, but I'll test all of that first thing tomorrow morning!

Thank you Dan, it's the link I was looking for!

Hello,

Click on the first cell at the top left of the table and in the pivot table toolbar, click on the red exclamation mark, refresh the data.

See you!

:)
It's me who doesn't understand what you're saying anymore... The report in the tool bar? Are you talking about automatic formatting?
If yes, yes, I tried, but:
- these formats don't correspond to what I want to do
- they are also canceled when I refresh the tables...

If your tables are one below the other, it's normal that you lose the formatting because as soon as you modify data in one of them, it adjusts the columns accordingly.

Sorry, I don't see a solution; if I find one, I'll let you know.
@+

Good evening,

I think it's mainly the column width that you're losing. The rest should be fine.
One possibility without using VBA:
- Add a blank sheet
- format the Pivot Table
- select the entire Pivot Table and use copy/paste special 'column width' into the blank sheet to save them.

After an update, you do the reverse.

Eric

Hello Eriiiiic,

Thank you for your response.
Unfortunately, I'm working on a document template, and the idea is to minimize the workload for future users as much as possible. I'm afraid that switching from one sheet to another to maintain formatting might be a bit tedious for them.
I have no experience in VBA, but could you let me know how you would do it?

Thank you!

Hi xkristi!

We're almost there! With this system (which I'm so frustrated I didn't find on my own, by the way ;)), I finally keep my column widths. But I’m losing the formatting that I was losing before (bold, centered text, etc.).
Did I happen to miss a step in what you explained to me?

a word maybe...
keep preserve the checked formatting