Any other Excel feature to replace clunky CF design?

[JackDanIce]

Hi,

Excuse the vague title, I have the following in a spreadsheet:

P4:Y4 are filled with formula that = "l" (lower case letter L) if Z4 is in the interval 0-10 inclusive, otherwise empty string
P4:Y4 then contain CF which are triggered if the cell value is "l" - RGB fill colours listed below, font and cell background have same RGB codes


I do not need this colouring to fill 10 cells, but it does need to reflect the score in Z4 and implied behaviour.

Similar to a Insert->Sparkline or Column, is there any Excel feature that can do this kind of gradient fill in a single object?

A formatted chart object won't suffice as I would like this to be on each row of data.

Any ideas or suggestions?

TIA,
Jack

 

[JackDanIce]

Conditional Formatting does have data bar but it only fills with a single solid colour or graduation of that same colour.

I'm looking for a data bar where the colours are mapped and allows for multi colours within a single data bar.

 

[Eric W]

If you change the formula as shown, you can use a color scale:

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z
4	1	2	3	4	5	6	7	8	9	10	10
Sheet4

Cell Formulas
Range	Formula
P4:Y4	P4	=IF(AND($Z4>0,$Z4<=10),COLUMNS($P:P),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P4:Y4	Other Type	Color scale		NO

 

[JackDanIce]

Hey @Eric W thank you for your reply. The cell formula works great and I like how you've anchored it at one end

I'm unclear how to apply the cells with CF as you've suggested, I'm stuck on finding "Other Type"
I can see how this would be easier than specifying RGB for each needed cell. What am I looking for or where to apply Other Type from the CF menu/dropdown?


PS Am I right to conclude it's not possible to do this in a single cell or a single object (not a chart), then replicate for other rows?

 

[Eric W]

To apply the CF to a range, select the range, say P7:Y10, click Conditional Formatting > Color Scales > and pick the top left one. Insert the formula as shown. You should end up with this:

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z
7	1	2	3	4	5	6	7	8	9	10	1
8											0
9	1	2	3	4	5	6	7	8	9	10	3
10	1	2	3	4	5	6	7	8	9	10	4
11
12
13		3
14			5
Sheet4

Cell Formulas
Range	Formula
P7:Y10	P7	=IF(AND($Z7>0,$Z7<=10),COLUMNS($P:P),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q14	Expression	=(R14>0)*(R14<=10)	text	NO
P14	Expression	=(R14>0)*(R14<=10)	text	NO
P7:Y10	Other Type	Color scale		NO
P13	Expression	=(Q13>0)*(A13<=10)	text	NO

The P13 cell is my attempt to put the scale in a single cell, using the Fill Effects option. It only allows 2-color ranges though, so it shows red to green. I then tried 2 formulas in 2 cells, red to yellow, then yellow to green, with the result as shown. The XL2BB doesn't seem to handle gradients, so here's what it actually looks like:

 

[JackDanIce]

I got a similar effect to work with a 3-colour scale and found the same issue using the gradient too.
I think it's only possible to do on a per cell level and using both formula and CF application.

Bit of background, I'm creating a normalised table for a client's data and each row has a column for a derived score (Z in above) but annoyingly, the paymasters, want to see the colour grading in the same table too (i.e. 10 columns after Z, then more data) - it's just a number with a 1:1 map, why not save it for the output, but No! Must be shown in data table, so be it.

I'll mark above as solution, but thank you @Eric W for looking into it