Excel Conditional Formatting Question

Posted on 2012-09-18
Medium Priority
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:Tim Jackoboice
  • 2
LVL 23

Expert Comment

by:Michael Fowler
ID: 38412310
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

by:Tim Jackoboice
ID: 38414179

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

Michael Fowler earned 1500 total points
ID: 38420558
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 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