Excel 365 Conditional Formatting Data Bar

%3CLINGO-SUB%20id%3D%22lingo-sub-2014751%22%20slang%3D%22en-US%22%3EExcel%20365%20Conditional%20Formatting%20Data%20Bar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014751%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%202%20cells%20(K50%20%26amp%3B%20G30).%20I%20want%20to%20display%20a%20CF%20Data%20Bar%20in%20G30%20according%20to%20the%20increasing%20value%20of%20K50%20(which%20is%20already%20set%20to%20do%20so).%20How%20do%20I%20accomplish%20this%3F%20Everything%20that%20I%20have%20tried%2C%20so%20far%2C%20has%20been%20unsuccessful.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2014751%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2014920%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Conditional%20Formatting%20Data%20Bar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2014920%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684000%22%20target%3D%22_blank%22%3E%40dlcartin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Data%20Bar%20you%20need%20two%20values%2C%20minimum%20and%20maximum.%20By%20default%20the%20are%20calculating%20automatically%20on%20selected%20range.%20You%20may%20change%20that%20selecting%20Formula%20setting%20for%20the%20rule%20and%20calculate%20min%20and%20max%20on%20your%20own.%3C%2FP%3E%0A%3CP%3EIn%20K50%20is%20only%20one%20value.%20You%20may%20take%20it%20as%20max%20and%20zero%20as%20min%2C%20or%20use%20another%20logic%2C%20but%20in%20any%20case%20you%20shall%20define%20two%20values.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2015849%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Conditional%20Formatting%20Data%20Bar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2015849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20for%20your%20explanation.%20For%20G38%20I%20saw%20and%20set%20the%20min%20%26amp%3B%20max%20values%20according%20to%20the%20value%20of%20K50%20(between%200%20-%2050%20(in%20increments%20of%2010))%2C%20which%20should%20have%20forced%20it%20to%20work.%20In%20my%20lack%20of%20understanding%20of%20Data%20Bars%2C%20I%20abandoned%20this%20idea%20entirely%20and%20just%20used%20an%20IF%20stament%20for%20G38%20(in%20CF%2C%20of%20course)%20that%20when%20K50%20reachs%20max%20value%20to%20simply%20change%20color%2C%20which%20is%20almost%20just%20as%20good.%20Again%2C%20I%20thank%20you%20for%20your%20kindness%20in%20replying.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2015850%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Conditional%20Formatting%20Data%20Bar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2015850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684000%22%20target%3D%22_blank%22%3E%40dlcartin%3C%2FA%3E%26nbsp%3B%2C%20thank%20you%20for%20the%20update.%20As%20a%20comment%2C%20in%20CF%20you%20usually%20may%20avoid%20using%20of%20IF()%20in%20form%20of%20%3DIF(condition%2C%20TRUE%2C%20FALSE).%20The%20form%20%3Dcondition%20works%20exactly%20the%20same%20way.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have 2 cells (K50 & G30). I want to display a CF Data Bar in G30 according to the increasing value of K50 (which is already set to do so). How do I accomplish this? Everything that I have tried, so far, has been unsuccessful.

Thanks

3 Replies

@dlcartin 

For Data Bar you need two values, minimum and maximum. By default the are calculating automatically on selected range. You may change that selecting Formula setting for the rule and calculate min and max on your own.

In K50 is only one value. You may take it as max and zero as min, or use another logic, but in any case you shall define two values.

 

@Sergei BaklanThank you for your explanation. For G38 I saw and set the min & max values according to the value of K50 (between 0 - 50 (in increments of 10)), which should have forced it to work. In my lack of understanding of Data Bars, I abandoned this idea entirely and just used an IF stament for G38 (in CF, of course) that when K50 reachs max value to simply change color, which is almost just as good. Again, I thank you for your kindness in replying.

@dlcartin , thank you for the update. As a comment, in CF you usually may avoid using of IF() in form of =IF(condition, TRUE, FALSE). The form =condition works exactly the same way.