+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting in a Pivot Table

  1. #1
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Conditional Formatting in a Pivot Table

    I am applying basic conditional formatting (red cell highlight) in a pivot table, based on comparing one set of values in a column to another set of values in a different column, both columns contained within the pivot table. The conditional formatting seems to work only for some cells, but not all where the condition applies. Any suggestions? Thanks!!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Conditional Formatting in a Pivot Table

    Hello and welcome to the forum.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Re: Conditional Formatting in a Pivot Table

    thank you! Let me create a sample file and I'll have it uploaded shortly.

  4. #4
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Re: Conditional Formatting in a Pivot Table

    Thanks for the upload tip, 63falcondude. Hopefully, I have attached the file properly. Filename: Sample_pvtConditionalFormatting. My condition is simply to compare Qty to Remaining. If Qty is greater, then highlight said Qty. The formatting appears to be correctly highlighting the value of 240, when compared against a value of 24...correct. However, the conditional formatting is not identifying the value of 79, when compared against a value of 40.
    Attached Files Attached Files

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Conditional Formatting in a Pivot Table

    Based on your sample, highlight C6:C11 > New Rule > Use a formula
    =C6>D6
    Format: Change fill and font to the color of your choice > OK > OK

  6. #6
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Re: Conditional Formatting in a Pivot Table

    Thank you for the tip! Actually, I started with that approach, but whenever I refresh the pivot table with new data, those conditional cells do not align to/match the refreshed data

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Conditional Formatting in a Pivot Table

    If you upload a more representative sample, we can take a look.

  8. #8
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Re: Conditional Formatting in a Pivot Table

    will do...give me a bit. it's lots of data, so I need to think through how best to pare down and provide a meaningful sample in an actual pivot table. thanks in advance for your help!

  9. #9
    Registered User
    Join Date
    02-04-2019
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    6

    Re: Conditional Formatting in a Pivot Table

    I'm an idiot. I had failed to notice the absolute cell reference on the rows. Once removed, it works just fine. Thanks for your help!!

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Conditional Formatting in a Pivot Table

    You're welcome. Happy to help.

    When typing in the CF formula, if you click on the cells instead of typing them in, they default to being absolute.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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