+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting in a pivot table

  1. #1
    Registered User
    Join Date
    07-01-2014
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    12

    Conditional formatting in a pivot table

    Hi there,

    I've run into something for which I did yet not find a satisfactory solution.

    I've a sheet with data, say: 3 main categories, which are subdivided, which are again subdivided. Then at the lowest level, I will have a column stating if that certain attribute is 'done'.

    example_rawdata.PNG

    Now I'll make a pivot table from this in order to have the collapse/uncollapse feature:

    example.PNG

    Now I want in the pivot table the colour be determined by its status as I have done manually in the picture above. For the lowest level this is easy, rule: =D4:D40="Done" and applied to: =C4:C40.

    However, for the column 'sub' this is less straightforward, since the current lay-out is greatly simplified and I want it to be dynamically changing when I add or omit new entries.

    The goal of this table is to easy have an overview of which attributes are not 'done', since this list will contain around a 500 attributes. Even when I collapse a level of detail.

    Is there someone here with a smart solution to my problem? Any help would be greatly appreciated!
    Attached Files Attached Files
    Last edited by Jeebos; 01-18-2019 at 10:10 AM. Reason: I forgot to include the excel file with the example

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conditional formatting in a pivot table

    This proposal adds two columns to both the RawData and Pivot sheets.
    On the RawData sheet Column E is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column F is populated using: =COUNTIFS(A$2:A$13,A2)=COUNTIFS(A$2:A$13,A2,D$2:D$13,"Done")
    The columns are named Sstatus and Mstatus respectively and the columns (E:F) are then added to the pivot table, although hidden by grouping.
    The formatting on the pivot table is accomplished using formulas similar to: =AND(A4<>"",F4="False")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    07-01-2014
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    12

    Re: Conditional formatting in a pivot table

    Hi JeteMc,

    Thank you for your reply and answer!

    However, your proposed method does not completely satisfy my question. I think I didn't correctly specify my problem, my bad. Now when you collapse a level, you'll lose the colouring information. I'd like to see, even when I collapse everything to the main level, which main categories are completely 'done'.

    Is that possible?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conditional formatting in a pivot table

    I imagine that you mean that you want to remove 'Sub' and 'SubSub' from the row labels field and maintain the conditional formatting. I feel that you would need something like structured references in the CF formula. As far as I know the 2010 version doesn't have that capability regarding a pivot table. Perhaps it is built into the 2016 version, if not this article on creating structured references for pivot tables via VBA may help.
    A work around that might be useful would be group columns B:C on the Pivot sheet so that the 'Sub' and 'SubSub' fields can be hidden without changing the columns containing Sstatus and Mstatus.
    Let us know if you have any questions.

+ 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. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  2. conditional formatting in pivot table
    By martinaH in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-13-2016, 09:22 AM
  3. pivot table and conditional conditional formatting
    By Veronsito in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-07-2016, 12:15 PM
  4. Replies: 9
    Last Post: 03-07-2016, 10:39 AM
  5. Replies: 2
    Last Post: 09-04-2015, 02:47 AM
  6. Conditional formatting in a pivot table
    By pdalal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2015, 08:57 AM

Tags for this Thread

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