Free Microsoft Excel 2013 Quick Reference

Preserve Formatting In Pivot Table

Hi Guys,

When I modify my pivot table table with things like centering columns, bold, or any other formatting, it goes back to its original state when I hit "Data Refresh." How can I make it look and stay the way I modify it without it going back?


Post your answer or comment

comments powered by Disqus
I clicked the "Preserve formatting" in the options but each time when I refresh pivot table, it automatically changes my formatting. How can I preserve my original formatting?

Thank you very much

I lose some of the number formats in pivot tables when I refresh the data. How can I make them "permanent"?


Please go through the attach file where I have mange to do the conditional formatting in pivot table. Result is I am getting color codes as required, now I want some formula which will count & sum based on color coding of conditional formatting.



Can anyone please tell me if it is possible to have conditional formatting in pivot table? For example. My data contains three columns. I want items greater than x number to be highligted in a different color. I know I can do this in the spreadsheet but how can I view this in the reflecting Pivot table. Even if I refresh the pivot table, it does not show up. I would really appreciate any help on this !! I am using Excel 2003 Thanks much !

this is frustrating...every I make a selection in one of the Page Fields the column widths change and the pivot table is not viewable on a single page. I have tried checking the box next to 'Preserve Formatting' in the Table Options view, but that does not work.

suggestions appreciated, thanks all.

Often I need to format my Pivot Tables. One way I do the formatting is by the menu option of Format --> AutoFormat when the activecell is in present in the Pivot. The dialog that is presented to us contains around 22 options to choose from.
But is there any means to add my own type of formatting options in the format dialog or any other dialog/means so that I can apply my custom formatting for pivots with a single click.

I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.

Greetings all.

I have some source data which is used to generate a pivot table. The data is conditionally formatted to color certain cells based on some other values. Once the data is transferred to the pivot table this formatting (as well as the number formatting) is lost.

I have found some code to fix the number formatting issue but can't seem to locate something similar to set the interior color of the cells in the pivot table to match that of the original source data.



I have a Data Table containing details of Sales and Payments Received, and from which I intend to create a Pivot Table for regular analysis.

The Data Table contains about 7 columns, two of which are date columns as follows:
a) Invoice Date
b) Date Paid.

The dates currently run from Jan 2007 to April 2011.

The dates are formatted as follows:
a) Invoice Date (mmm-yy) using the formula [ =Date(Year(x),Month(x),1) ]
b) Date Paid (mmm-yy) using the formula [ =Text(x,”mmm-yy”) ]

In order to format the 'Date Paid' column, I opted for the =TEXT(G2,("MMM-YY") format over the =DATE(...) format, because the =DATE(...) format was returning error for all the 'Unpaid' values included in the column.

My problem is with the Date Paid column which I drag into the Report Filter.
When I create a Pivot Table, the Report Filter drop down menu (which houses ‘Date Paid’ arranges the dates paid (which are in months/years) in alphabetical order and not in calendar/year order. Infact, I get something in the following order:


How can I format the Pivot Table so that the Report Filter Drop Down Menu will show the 'Dates Paid' (which are formatted as mmm-yy) by Year and Calendar order, instead of in the alphabetical order as shown above.

I need to explain that In the 'Date Paid' column in my Data Table, I type the word 'Unpaid' against those invoices that are currently unpaid, because I would want to be able to filter and report on both the ‘paid’ and ‘unpaid’ invoices using the Pivot Table, when the information is required.

Meanwhile, I need to explain that the reason I formatted the said ‘Date Paid’ column using =Text(x,”mmm-yy”) is because the column contains the text ‘UNPAID’ for those invoices yet unpaid. It also contains the ‘dates paid’. In my analysis, I would regularly want to know what invoices were paid in which month/year and also the invoices that are yet Unpaid. When I click ‘Unpaid’ from the Report Filter drop down menu, it comes up with the information I want. My problem is to get the Report Filter Drop Down menu to arrange the months/years in calendar order.

Someone has indicated to me that the reason I'm unable to have the dates paid arranged in year/calender order in the Report Filter drop down menu is because I used a Text format for the dates paid column. Unfortunately, the person could not suggest an alternative approach to help me achieve what I want to do.

If the problem arises from the inclusion of the text 'Unpaid' in the column, and if there is any other way I can re-arrange the Data Table to include, and enable me to also filter and analyse in the Pivot Table both the 'Paid' and 'Unpaid' invoices, I will be grateful also.

Thanks all for your anticipated help.


Dear Experts,

I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.


We have working in 2003 conditional formating of complete columns which span
a pivot table. In 2007 this has stopped working, when checking what's
happening, we can see that the range the conditional format covers is altered
as the table is refreshed. The documentation i can find states that your cant
conditional format a pivot table in 2003 but you can in 2007 but this seems
the other way around to me.

Has anyone had any luck?


I want to set conditional formating in a pivot table can you suggest is this

Hello !

Is there a way to conditionally format pivot table ???

What I have is the pivot table with few main dimensions and each of those
has 2 subdimensions - I need to have all values in subdimension 'NP' in red -
and corresponding row header - that would be great
I'll draw a rough sketch below so there is less confusion

west east north south
p np p np p np p np


so I need customer name and value in column 'np' in red for all rows where
column 'np' is not blank. This would be no problem in regular table, i used
conditional formatting in source tab for this pivot but it does not help (it
works only in source data)

any suggestions greatly appreciated

How do I conditionally format cells within a column of a pivot table based on
their relationship with another value within the pivot table? For example, I
want to format 2007 enrollment based on whether its up, down or the same as
2006 enrollment. The pivot is set up with years as column data (2007, 2006,
2005). The first row has enrollments of 10 vs. 15 last year - I want that
cell to be red. The 2nd row has enrollments of 25 this year vs. 20 last
year, I want that cell to be green.

In a perfect world, I would like to display a bar showing how up or down
enrollments are compared to last year...

I am running a Pivot table on some swim data. Even though the data is
formatted the same way "mm:ss.00", the fraction of the second is not
showing up or is not part of the numbers in the Pivot table.

Pivot table data

Back 25 Breast 25 Fly 25
00:27.00 00:28.00
00:31.00 00:33.00
00:31.00 00:36.00

00:27.00 00:28.00
00:23.00 00:25.00 00:24.00

Data the Pivot table is based on

7 CMSA-SE 00:21.87 00:21.49
6 BMAC-SE 00:22.95 00:21.91
7 BMAC-SE 00:23.13 00:22.16
6 BMAC-SE 00:27.97 00:22.63
8 BMAC-SE 00:21.07 00:22.70
7 UN-SE 00:00.00 00:22.94
6 CMSA-SE 00:26.36 00:22.97
8 BMAC-SE 00:00.00 00:23.11
8 GPAC-SE 00:00.00 00:23.14
8 BMAC-SE 00:22.86 00:23.74
8 CMSA-SE 00:22.55 00:24.52
7 CMSA-SE 00:27.15 00:26.66
8 BMAC-SE 00:25.50 00:27.30
6 CMSA-SE 00:00.00 00:28.42
6 UN-SE 00:27.18 00:28.87
6 BMAC-SE 00:30.59 00:30.27
7 BMAC-SE 00:28.24 00:31.22

Any ideas why the fractional seconds are lost?


I already set the measure to two decimal places in OLAP but when i drag the
measure to Excel, the Formating for the excel is not correct.
Let say I format the measure manually in excel but when i drag it back to
Field List and drag it again in the Pivot Table, it will remove the previous
How to make it permanent remain formating recorrectly?

I am using Excel 2003. Can i do conditional formatting in a pivot table? I want to attach the conditions to the field but then if i move the field around the pivot table i want the formatting to go with it.

I have built a Pivot Table and am having a hard time getting it to retain my standard formatting after I refresh.

I have selected "Preserve Formatting" under Pivot Table Options.

The table is holding the formatting on the body - but not for the Subtotals.
For example:
Down the Left side I have Products, in the body I have the sum of volume.
The volume retains the formatting, but where it says "ETHANE TOTAL", it will not retain this formatting.

I appreciate any help you may have.

how to change date formating-grouped by month in pivot table

I have a two sheets, one with data and one acting as a search page that refreshes a pivot table to match searchwords found in data.

Is it possible to format the data in the first spreadsheet, and have the pivot table keep that formatting?

Also, is there a size restriction on the amount of data shown in the pivot table? I noticed whilst putting a lengthy bit of information into a cell, the pivot table seemed to cut it off.

Many thanks



Is it possible to have conditional formatting on a field in a pivot table.

I can apply conditional formatting to a range of cells, but if e.g. a wider
range of data is produced the formatting doesn't grow with it. I don't want
to have the 'Grand Total' columns have the same formatting as the rest of
the sheet, but depending on what data is brought back, they're not
necessarily in the same place.

Am I making sense?!

I'm using Excel 2002




I am creating a pivot table which my data will contain the following.

I have the Store in the Page area, I want to use the date field for various
different lookups.
By Week, By Month, By Year.
I can add these columns to the worksheet but it brings in the standard
format of 2/10/06 even though I have formated it for the month or year. How
can I create these choices within the Pivot table to expand my break down of
the data?

Thank you.


I'm having trouble trying to format dates in the Row fields in a Pivot Table. The source data is set to mmm-yy but the pivot table converts this to dd-mmm-yy. Need to retain the mmm-yy format.

Thanks in anticipation

Hey guys,

I have a problem with the formatting on pivot tables.

Here's the issue.
I have a pivot table that is connected to a view in a SQL database.
That table has user names in rows.
I applied formatting of my own to all of the pivot table (like inner and outer gridlines).

The problem is whenever I open the excel , connect to the view again and refresh the data i have issues with formatting.

Such as, let´s say I have a new user called Peter. He is added to the the table as the last row, like after Andy despite the column having sort from A-Z activated.

The other issue is this new user and all the data inherent to him are not formatted accordingly (no inner or outer borders around its cells).

I have "Preserve cell formatting on update" option of the pivot table turned on but this still happens.

Can anybody help me?

Is this possible to solve?

Thank you in advance

No luck finding an answer? You could always try Google.