Formatting a pivot table Solved

Question

Hello,  Is it possible to freeze the formatting of a Pivot Table? I want the updates not to revert to the initial formatting.  Thank you for your help!

xkristi · Accepted Answer

Hi
Hello to Eriic while I'm at it! lol

On the pivot table
right-click options and disable auto-formatting while keeping preserve formatting checked..

To format, it's right-click, select before formatting.

Otherwise, you'll lose everything with each refresh.

--
xkristi
I do not claim to hold the truth, I gladly leave it to you...

Ferrique · Answer

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!

identifiant-comment · Answer

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

Ferrique · Answer

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

identifiant-comment · Answer

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! :)

Ferrique · Answer

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

identifiant-comment · Answer

:)
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...

Ferrique · Answer

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.
@+

identifiant-comment · Answer

Arf... Thanks anyway!

eriiic · Answer

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

identifiant-comment · Answer

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!

eriiic · Answer

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

eriiic · Answer

Hi xkristi, how are you? :-)  It was so simple... Well done ;-) eric

identifiant-comment · Answer

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

identifiant-comment · Answer

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?

eriiic · Answer

a word maybe... keep preserve the checked formatting

identifiant-comment · Answer

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

xkristi · Answer

Hello!  Then you need to select: To format, right-click, select before formatting. If you want a column, you select the title and repeat to either have the label or the data to format... can you do that?  Then in options, uncheck "automatic formatting" otherwise it will revert to the previous format  and if you change something, go back to options to validate the formatting you've just done.  Otherwise, you'll lose everything with each refresh.  -- xkristi I do not claim to hold the truth, I willingly leave it to you...

identifiant-comment · Answer

Okay, I think I understand the problem better. It’s that I don’t use the same formatting for all my labels or for all my data. For example, I have conditional formatting on my column labels, and the text is oriented at -90°, which is not the case for the row labels. For the data, only the data of the "Total" rows and columns are in bold.  Do you think it's possible to keep a specific formatting like that?

xkristi · Answer

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...

identifiant-comment · Answer

Okay, I'll check it out and keep you updated.

identifiant-comment · Answer

Indeed xkristi, you were right!  Thank you anyway for all the information you gave me, it will help me a lot.

Dan · Answer

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

identifiant-comment · Answer

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

Mimione · Answer

Hello everyone,  I have the same issue with losing cell formatting, etc., but additionally, when I refresh my pivot table, I lose the columns or rows that have been modified in my data source table.  I'm using Excel 2010, but the file always saves for compatibility with Excel 97-2003 files.  I'm getting a bit crazy with this problem. I've browsed through all the forums, but I haven't found anything that works. I've also tried starting from scratch, but nothing helps.  Thank you for your help...

CAMBOUCAMBOU · Answer

Thank you to xkristi for pointing me in the right direction. But I'll summarize the step-by-step process for EXCEL 10 In the pivot table: RIGHT CLICK Click on: PIVOT TABLE OPTIONS Uncheck the box: AUTOFIT COLUMN WIDTH ON UPDATE Select the title of the column(s) to modify From the "Home" tab, click on FORMAT Click on COLUMN WIDTH In the designated box, enter the desired width (the default is 16.43) then click OK The width remains fixed when refreshing.

CAMBOUCAMBOU · Answer

Thanks to xkristi for putting me on the right track. But I'll summarize the step-by-step procedure for EXCEL 10 In the pivot table: RIGHT CLICK Click on: PIVOT TABLE OPTIONS Uncheck the box: AUTOFIT COLUMN WIDTH ON UPDATE Select the title of the column(s) to modify In the "Home" tab, click on FORMAT Click on COLUMN WIDTH In the box provided, enter the desired width (by default it’s 16.43) and then click OK The width remains fixed during refresh.

olive · Answer

Hello  I would say click on the header, then on the cell just below it, this selects the entire column, make sure you're positioned on the selected area (darker), right-click, select format cells, choose the desired format, and save.  The applied format will be retained even if you refresh the TDC.

Formatting a pivot table

28 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.