Three Tips for Making your Pivot Table Formatting Stick

Few things have sent me over the edge like pivot table formatting.

You know what I’m talking about.

You spend precious time formatting your pivot table, just to have that formatting disappear when you change anything in the pivot. After several tries at forcing your custom formatting to stick, you make this face…

.

…then you begrudgingly use one of the PivotTable Styles on the Design tab.

.

Well no more. Today, I’ll give you three tips for creating sticky pivot table formatting.


 

Tip Number 1:.

You know that default setting in the PivotTable options (“Preserve cell formatting on update”)? It’s a giant turd. It seems like it should prevent the loss of formatting, but it doesn’t. Leave it checked and forget about it.

.

..

Tip Number 2:.

There is a difference between formatting a pivot table and formatting the cells around it. Don’t just start highlighting cells. You have to specifically select the area of the pivot table you want to format. For example, if you want to format a Subtotal, you’ll have to hover your mouse over the edge of the Subtotal until your cursor turns into a black arrow. When it does, click the mouse to select that area. After the area is selected, you can format away.

.

.

Tip Number 3:
Clear all filters BEFORE formatting your pivot table.
.

Here is a pivot table I formatted with a Region filter set to Southeast. Everything is lovely.

.

The minute I select another filter criteria (Southwest in this case), my formatting disappears. It seems my pivot table somehow associates formatting with the filter state it was in when that formatting was applied. In this example, I formatted the pivot table while it was filtered to Southeast. So my formatting will only show up when I select the Southeast region. Any deviation from that filter state, removes the formatting. This means if I change the filter or remove the Filter field altogether, my formatting is blown away.

.

 

To avoid this loss of formatting, you can simply clear all filtering before you format. This means ALL filtering (Filter pages, dropdowns, text filters, etc…).

.

Slicers seem to be an exception

Slicers seem to be an exception to the filtering rule. You can have a slicer in place and filtered; your pivot table formatting will not disappear. It looks as though, pivot tables don’t associated filtering from slicers with any kind of formatting state.


.

.

Well, that’s that. Three tips to finally solve that disappearing formatting problem.

Here’s a before and after of someone who used these three tips to formatting her pivot tables.

.

I told you it works.

74 thoughts on “Three Tips for Making your Pivot Table Formatting Stick

  1. Joseph

    I have a Pivot Table problem that is not fixed by these 3 tips. I have a Pivot Table where I need to show the values in Quantities as well as in Dollar Amount. When the field “Qty” is selected to the “Values” area, data must appear in format “General”. When “Amount” is selected, data must appear in “Currency” format. These tricks doesn’t solve that. SAD!

  2. Anonymous

    Hi. This didn’t work for me. I’m using Microsoft Excel 2013 (Part of Microsoft Office 365 ProPlus). I formatted the cells within the column by following the directions with use of the column/row arrow and the cells still refer to original formatting after a filter is used.

  3. Anonymous

    I’m surprised that this solve my issue.

    To avoid this loss of formatting, you can simply clear all filtering before you format. This means ALL filtering (Filter pages, dropdowns, text filters, etc…).

  4. Robin

    SWEET! This was the most fabulous piece of information, clearing the filters before applying the formatting! You’re the best!

  5. Mariana

    Hi, I have SUM rows that change formatting each time I refresh data, I tried everything you suggested and it keeps changing back to normal and clears all formatting I did.

    Any help?

  6. Todd

    I can’t tell you how psyched I am to have read this article……You have no idea how much frustration you have eliminated for me! Thank you!

  7. Barbara

    Thank you, thank you, thank you!!!!!! Is it inappropriate to tell you I love you without knowing you??? I’d like to think I’m pretty good with Excel but this has been driving me crazy for a long time (years!). I feel like I exhibited the definition of crazy (doing the same thing over and over again, expecting different results) by checking, unchecking and rechecking that stupid “preserve formatting” box! Thank you again!

  8. Humza

    Hi all!

    I’m after some advice on using Pivot Tables for operational reports. My client is using a Pivot Table to view extracted data from an external source – multiple team members use the same report, often highlighting key data points (using colour fill or bold / underlining formatting). The problem is when the Pivot Table data is refreshed, any formatting changes and comments made are lost. Can anyone think of a better solution for this team to maintain formatting changes etc – I am thinking there must be a better way to display this external data without using a Pivot Table – perhaps manually creating a dashboard instead.

    Appreciate your thoughts!!

  9. Tim Allison

    Great tips on pivot table formatting. Good use of visuals and humour and nice and concise. Thanks for the speedy resolution to another of Excel’s crazy quirks!

  10. Denise Laussade

    Like several other respondents, this did not solve my issue. The formatting (colors and justification in headers) is zapped away each time.

    However, I did find this written in a very amusing way.

    I still am as frustrated as the woman in the left-hand photo… 🙁

  11. Borut Golob

    @Joseph:
    I tried conditional formatting using formula:
    If (left(A3,3))=”Sum” then I set “Currency” format

  12. Gate

    Thanks for this post! Was still having formatting issues with a PivotChart in Excel 2010.

    After copy and pasting the PivotChart, the custom formatting no longer disappears when filtering or refreshing data.

    An easy fix,

  13. James

    If I for example change the color of the border lines in Report Filter Values, the color does not change. Or another time It maybe does. Same with any style element. There seems to be interdependencies – like, Whole Table and all the others. Which one wins? But that is the obvious one. There seem to be others, too.

  14. Karl

    You’re a genius! Thank you for solving this for us. My issue was…I was not able to format and keep the formatting in Excel 2013 pivot table where the value is in the “Row” section (not the “Values” section). Using your “arrow” method to select the set of data specific to the pivot table worked perfectly and now when I change Filters, refresh, etc, the formatting STAYS! Thank you again

  15. Neill

    Great write up! Fixed my problem after trawling multiple sites for help.

    My main issue for my format sticking was i had a slicer filter setting on, removed this and did the black arrow select for it to clear problem border formatting

  16. Alan

    I’m new to power pivots, and this issue was getting on my nerves, but thanks to the tip above it is fixed, thank you !!

  17. Bill

    I’m guessing this works great for most pivot tables, but not mine.

    I don’t have any filters and I want to format just one of the fields, a date field which is a column header, but no matter what I do, the formatting doesn’t stick.

    Your writeup was surely entertaining though.

  18. Laura

    The tiny black arrow is game changing! The hardest part was figuring out where to place the cursor so that it would appear. I hovered around the sub-total, but no luck. Finally, when I clicked my mouse into the column and then hovered over the column header, it appeared. All these years and I didn’t know about the tiny black arrow…I must have been daydreaming during that part of the lesson!

Leave a Reply

Your email address will not be published. Required fields are marked *