Conditional Formatting and Sorting

[Dave Donut]

I have used conditional formatting to aid sorting in a sheet. I have colour coded if a certain word appears in cells in a column. I have then sorted by the colours. This has worked fine and the sorting and the colour coding are very useful.

However, the colour coded cells contain a lot of information, which I need to be able to read, and are quite large and the coloured cells use loads of printer ink.

I thought that I could have a similar effect by having a narrow column alongside which could be coloured instead of the large cells, but would use a fraction of the ink.

Is there a way that this could be done.

Thanks.

 

[etaf]

do you need the colours , could you use numbers in place of the colours to aid sorting ?
But you can have a narrow column with colour
SO if you currently have conditional formatting for say column C
then select column D and change the reference to the conditional formatting formulas from C to D

 

[Dave Donut]

Hi (and thanks for the reply). Colours is very useful - both to aid sorting and as a visual aid.

A narrow column with colour would be good, so if my conditioning formatting (colour fill) is set to column C, I would need cell D1 to say something like 'if column C1 contains the phrase 'Top 5' fill D1 with blue'. Essentially, I suppose I am asking if that is possible? And, if so, how?

 

[etaf]

A narrow column with colour would be good, so if my conditioning formatting (colour fill) is set to column C, I would need cell D1 to say something like 'if column C1 contains the phrase 'Top 5' fill D1 with blue'. Essentially, I suppose I am asking if that is possible? And, if so, how?Sort of

Not sure what you conditional formatting are

But if you had a formula for conditional formatting , that said = C2 < = 5 and Fill Blue with a selection of $C$2:$C$200
then we select column range for $D$2:$D$200
BUT we still use the formula
C2 < = 5
if you change the selection to use D , then I suspect the formula may change as well

How is the conditional formatting Set
Are they formulas

So then change the Selection range to D
and save
See if the C formula stays the same , IF not then edit again and change the formula to show C


OR in D1 put =C1
and then make column C the small one and the results will be in D

 

[Dave Donut]

The Conditional Formatting is....Cell Value contains 'Top 5' - this is a Specific Text - not a formula. Sorry if I wasn't clear.

 

[etaf]

ok,
So
at the moment you would have
something like
C2 = "top 5"
so change the selection range to D
and check the formula still says
C2

How do you get different colours, must have different criteria
anyway change them all
OR post a link to the spreadsheet on dropbox/one drive (if it has NO private data)

 

[Dave Donut]

Hi - thanks again. There is no formula, as such. I am using Rule Type 'Format only cells that contain' and then I select 'Specific Text' and then enter 'Top 5'. Using those options, I can't see a way to refer to another cell other than the one being formatted.

 

[etaf]

change to a formula
Then you can use an
OR()
Or a lookup table to colour
What are all the possible phrases and what colour for those phrases
Then i would make a table of those phrases , maybe a column for different colours
Then select Column D2:D1000 or whatever the range is
Now setup a new formula Rule
and use a COUNTIF( Column for this colour , $C2 ) >0. - Say all the phrases are in column Sheet2!A:A and those are to be BLUE
Now it will look at the contents of the column and if the phrase exists it will fill with the colour - BLUE
Now do another RULE
and use a COUNTIF( Column for this colour , $C2 ) >0 Say all the phrases are in column Sheet2!B:B and those are to be RED
and add a different rule per colour and column per colour

SO Assuming you have the table on a different sheet as above
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>

D2:D1000 - change range to match you range of rows needed


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


COUNTIF( Sheet2!$A$1:$A$100 , $C2 ) >0


Format [Number, Font, Border, Fill] Say BLUE
choose the format you would like to apply when the condition is true
OK >> OK

THEN
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


COUNTIF( Sheet2!$B$1:$B$100 , $C2 ) >0


Format [Number, Font, Border, Fill] Say RED
choose the format you would like to apply when the condition is true
OK >> OK

Here is a sample - BUT I have used colums M,N,O as an example - BUT these can be on a different sheet

Book5
	C	D	E	F	G	H	I	J	K	L	M	N	O
1											ON A DIFFEERENT SHEET
2	A
3	B										RED	YELLOW	GREEN
4	C										A	C	B
5	D										F	I	D
6	E										J		K
7	F
8	G
9	H
10	I
11	J
12	K
13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D12	Expression	=COUNTIF($O$3:$O$6,$C2)>0	text	NO
D2:D12	Expression	=COUNTIF($N$3:$N$6,$C2)>0	text	NO
D2:D12	Expression	=COUNTIF($M$3:$M$6,$C2)>0	text	NO

 

[Dave Donut]

Hi, Thanks again for the information. Apologies for the delay in responding - I think I need to set a little time aside to work though your solution as I am not familiar with the formulas and haven't used look up tables before. I really appreciate you taking time to help me.

 

[etaf]

ok