Solved

Excel Conditional Formatting: Using Color Scale but with Additional Criteria

Posted on 2014-10-22
9
472 Views
Last Modified: 2014-10-23
I have a large spreadsheet (consolidated for this example) in which I want to use conditional formatting to color rows of data, but ONLY rows with an "s" in the "Key" column.  Is it possible to set the range for the entire spreadsheet so that I can easily add affected rows later, or I do just need to manually apply the formatting to each of those (currently highlighted) rows?

Screencap.PNG
The other way I've gotten around the issue is by creating a set of custom conditional formatting rules like the following, but it has made the spreadsheet cumbersome to edit:

=AND($B1="s",A1>=60,A1<600) --> format background dark red
=AND($B1="s",A1>=50,A1<60) --> format background red
=AND($B1="s",A1>=40,A1<50) --> format light red
etc.

Thanks!
Sample.xlsx
0
Comment
Question by:pcamis
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40396964
please find attached.
Sample.xlsx
0
 

Author Comment

by:pcamis
ID: 40396989
Thanks ProfessorJimJam.  What you provided does read whether an "s" is in the Key column, but I'm trying to implement the three color range feature of conditional formatting to show a range of color.  Visually, it should look like this (spreadsheet attached):

Screencap2.PNGSample2.xlsx
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40397016
here you go.

in accordance to condition specified in your earlier post and the colored sheet you attached in the latest post. . please find attached file.

=AND($B1="s",A1>=60,A1<600) --> format background dark red
=AND($B1="s",A1>=50,A1<60) --> format background red
=AND($B1="s",A1>=40,A1<50) --> format light red
Sample2.xlsx
0
 

Author Comment

by:pcamis
ID: 40397045
Sorry - I must still not be asking clearly.  I have already implemented a solution like what you provided above... it functions properly but because of the size of the spreadsheet and the number of rules necessary for the level of color detail that is being requested, I am hoping to use the built-in color scale option...

Conditional Formatting --> New Rule --> Format all cells based on their values --> Format Style (3 color scale)

This gives me the appearance I want, but I can't figure out how to use that rule with the additional condition that it only apply to "s" rows.  That make more sense?

Thanks again!
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40398123
pcamis,

You can't define a criteria like you're suggesting with a graded color scale conditional format.   While a dynamic named range might solve this problem, I don't think it's possible to create such a range (although I could do it manually).

However, there is a solution that involves a bit of trickery.  You create a second conditional format rule that tests for a non-"s" value in column B and give that rule precidence.  While both rules would be applied to the same overall range (that is, columns C:K for as many rows as needed), the non-s rule would prevent the 3-color rule from occurring except where the value in column B is "s".
set up of 3-color conditional format
I've attached a modified file for you to see.

Regards,
-Glenn
EE-Sample2.xlsx
0
 

Author Closing Comment

by:pcamis
ID: 40399289
Fantastic workaround, Glenn Ray - works beautifully!  Thanks very much!
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40399332
Glenn,

i am amazed with your solution. thank you. i learned something new today.

just one question, why do you have these named ranges in the workbook. i did not see any of these names used in condtional formatting.  my question is are these named ranges somehow play a role in your condtional formatting? or perhaps you just created them and did not use them?  i want to know if these named ranges play any role in your solution of condtional formatting?

Name      Refers To      Type
S_Lines      =Sheet1!$C$2:$K$2,Sheet1!$C$6:$K$6
Stest      =OFFSET(Sheet1!$B$1,MATCH("s",Sheet1!XET1:XET19,0),1,1,9)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40399778
Those were some test ranges I had been working on in an attempt to see if I could create a dynamic range that met the conditional formatting criteria.  They're not actively used

S_Lines - just a manually-designated area covering the two "s" rows
Stest - picks up the first occurrence of an "s" in column B and selected the related columns.  The XET1:XET19 section is mucked up; it should be $B$1:B$B19.  I forgot to use absolute cell referencing when I first wrote it.

-Glenn
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40400007
Thanks Glenn
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

22 Experts available now in Live!

Get 1:1 Help Now