+ Reply to Thread
Results 1 to 13 of 13

Pivot Table - Column formatting not preserved/extended when table has new rows

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Pivot Table - Column formatting not preserved/extended when table has new rows

    I feel that this is a question that must have been answered a thousand times, but I cannot find a solution that works.

    I have a pivot table with a number of columns. Over time this pivot table will grow as new source data is added.

    An example column looks like this;

    Table Column2.png

    I have applied conditional formatting so (blank) uses a Custom form of ;;; so making the fields truly blank. That all works. But, when I add some new data and refresh the pivot table the formatting is broken again. See the image. The new field is the one where the margin is shown as 0.37 and not 37.00%. Also, where the table has grown by one row the new row at the bottom shows (blank).

    How do I ensure the numeric formatting and conditional formatting covers the whole table (column) as the table grows?

    I am using a macro to refresh the pivot table as I want to use a single button to refresh all pivot tables at once. The macro is as follows.

    Please Login or Register  to view this content.
    Thanks

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Hi, if column C is where your "Margin" is,
    you could add to your macro:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Thanks Erwin

    I will try this and see if I can add the Conditional Formattting in as well.

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    You can also try this:
    Please Login or Register  to view this content.
    Cheers
    Erwin

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    You can apply conditional formatting to specific fields of a pivot table (so that it will move with any cells using that field) as long as it is a value field. Is yours a value field or row/column/filter?
    Rory

  6. #6
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Thanks again - this is a good improvement on the first answer. I have tried this though, and get an error.

    Attachment 676125

    The worksheet is not protected, so I am not sure why this does not work.

  7. #7
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Your attachment is not visible, you must upload this via the attachment manager (Go Advanced)
    and it would help to upload a small, desensitized workbook .

    Cheers
    Erwin

  8. #8
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Here, hopefully is the image.

    Attachment 676130

    This did not work either - will try again (just noted the Advanced comment you made).

  9. #9
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Here is the screenshot (3rd time lucky)

    NumberFormat Error.png

  10. #10
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Well, to help you better, we really need an upload of your workbook.

    Cheers
    Erwin
    Last edited by Eastw00d; 05-05-2020 at 05:51 AM.

  11. #11
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    I have attached a version with cleaned up data (I need to hide a few details). I hope I have successfully done this.

    To test this if you click on the bottom row of the table, then Insert it will insert a row above. You can then fill in some details. Then on 'Pivots' Refresh the table.

    Thanks for your help, and sorry for the newbie lack of knowledge on the forum.

    Steve
    Attached Files Attached Files

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Try:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: Pivot Table - Column formatting not preserved/extended when table has new rows

    Thanks rorya that works perfectly. The missing part in my code was the "DataRange".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Column Formatting in Pivot Table
    By AlastairMc in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 11-23-2017, 12:18 PM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  4. Replies: 0
    Last Post: 01-06-2016, 07:00 AM
  5. Replies: 2
    Last Post: 09-04-2015, 02:47 AM
  6. Conditional Formatting rows in Pivot Table HELP!
    By kfryar in forum Excel General
    Replies: 1
    Last Post: 03-26-2014, 01:26 AM
  7. Excel 2010 pivot table formatting not preserved after update
    By MARKSTRO in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-30-2013, 01:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1