Fields which change colour depending on % in other fields

New Contributor

Good Evening.


I am looking for ways to do the following


Have a field A2,3,4 etc, having a colour fill depending on the total % value in other fields.

As a practical example please see the following:


Column E is a simple formula: =SUM(B2:D2)/3 , which gives us the total % remaining.


After applying the solution presented I should have:


A2 Nuts, (which we have 15% stock left) show as red.

A3 Choclate (which we have 50%) show as Orange

A4 Milk (which we have 100% stock left) show as Green (fill), like this: 



(The above was obtained by just using fill, as I do not as yet know how to do this)






3 Replies

@Manicolo That's done with Conditional Formatting. You can learn more about that in the link below. 


The attached file contains a working example of what could work for you, assuming that 15% and 50% are the relevant boundaries for the colour change. 



I have been through your reply and it has been really helpful. However, one problem I am finding is if I want to copy and past that condition on multiple cells scattered around the worksheet.

Dragging the edge does not work, as it overrides the values inside the cell. Copying and pasting special is also not working for me as it keeps the original rule with the original reference field.

I am a bit stuck now.

I fear that this might not be understood easily. Let me suse your sheet as an example.

If you were to have 100 entries for A and E, how would you go about giving the column that condition?


@Manicolo Look into changing the "applied to" range. If you get stuck, please share a file via OneDrive, GoogleDocs or something similar.