Excel Conditional Formatting Question

Posted on 2012-09-18
Last Modified: 2012-11-14
I want the Conditional Formatting Color Scale applied to the area of the attached chart H5:H57, but it won't allow me to as I have a formula that is returning NA()  The formula is returning NA() by design so that the graph will chart correctly. For some reason, I can't get both the conditional formatting color scale and the graph to chart correctly simultaneously.

Please see the attached, and thanks in advance for your help.
Question by:Cactus1994
    LVL 23

    Expert Comment

    You could add another conditional formating rule with the formula =ISNA(G5) and set the colour appropiately

    I have attached your example with this done and highlighted yellow


    Author Comment


    When I attempt to Conditionally Format with Color Scales cells G5:K57, it still doesn't work -- no formatting occurs. I do see where where you added the ISNA() rule to the Conditional Format rules, and it highlights those cells in yellow.

    The objective of my Conditional Formatting is to show the relative comparision between the columns of years on the chart, using the Color Scales option. That option just doesn't work yet when applying it to cells G5:K57 yet, and is what I'm trying to accomplish.

    Is there a rule I can manually add to the Conditonal Format rules that can replicate the Color Scales option, perhaps?

    Thanks -- hope you know an easy workaround to this. I appreciate it.

    LVL 23

    Accepted Solution

    The only think I can think would be to have a hidden sheet that contains the data to display  the chart. The hidden sheet would be linked to the data in the front sheet so it remains in sych but alter the formaul to display #N/A on the hidden table as required


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now