Question Google Spreadsheet conditional formating color scale is not working for me

[mathewalbertson]

Hello! I've made a spreadsheet for my friends and I to share anime easily. One of the things we put in there is the release year for the show. I would love to have the color change depending on what year is inputted but it doesn't seem to work for me. I've watched many youtube videos and couldn't find anything about it so I decided to come here and see if I could get some help!

Here is what I'm trying to do exactly

https://imgur.com/KjHHxSV

View: https://imgur.com/KjHHxSV


Thanks for any help!

 

[Alabalcho]

YouTube is not the place to learn something...
https://support.office.com/en-us/ar...in-Excel-34402F91-C7E7-4060-944C-65D913033D18

 

[Ralston18]

Can you provide an example of the logic you need?

For example:

If the year is < current year then color year cell red.

If the year = current year then color year cell green.

If the year > current year then color year cell blue.

The conditional logic you need is, at least to me, not clear.

 

[mathewalbertson]

Can you provide an example of the logic you need?

For example:

If the year is < current year then color year cell red.

If the year = current year then color year cell green.

If the year > current year then color year cell blue.

The conditional logic you need is, at least to me, not clear.

What I want it to do is have the oldest years to be green and the newest years to be blue. Then have the color fade in the center corresponding to the year inputted. Thats what the "min value" and "max value" are supposed to be for in the conditional formatting menu on the right

 

[mathewalbertson]

YouTube is not the place to learn something...
https://support.office.com/en-us/ar...in-Excel-34402F91-C7E7-4060-944C-65D913033D18

I'm just going to say this bluntly, it seems you didn't read the title completely. It states that I'm using Google Spreadsheet, not Microsoft Excel. 2 completely different softwares even though they do look very similar.

 

[Ralston18]

For the record there is no need to be blunt.

The "softwares" are different but overall behavior is much the same. Likewise the logic. I first looked at Excel to get some sense of the necessary conditional formatting. And I did not use YouTube.

E.g. Color scales: . Excel cell color coding

Very similar to the link provided by @Alabalcho.

There are indeed too many YouTube videos out there that are poorly made, incomplete, and just downright wrong.

Remember that people are trying to help you.

That said:

I am wondering if the two missing years (cells then being blank - zero or null) are causing the problem.

Your date range covers 27 years (2019 - 1992 = 27).

What values did you apply for Minpoint, Midpoint, and Max Point? The boxes are empty.

What happens if you use the values 1992, 2006, and 2019 respectively?

You may need a rule to address a missing date.

Plus, I am fairly sure that there is an algorithm that could convert the year to some value that can be used in turn to color code or shade the cell even more so.

 

[mathewalbertson]

For the record there is no need to be blunt.

The "softwares" are different but overall behavior is much the same. Likewise the logic. I first looked at Excel to get some sense of the necessary conditional formatting. And I did not use YouTube.

E.g. Color scales: . Excel cell color coding

Very similar to the link provided by @Alabalcho.

There are indeed too many YouTube videos out there that are poorly made, incomplete, and just downright wrong.

Remember that people are trying to help you.

That said:

I am wondering if the two missing years (cells then being blank - zero or null) are causing the problem.

Your date range covers 27 years (2019 - 1992 = 27).

What values did you apply for Minpoint, Midpoint, and Max Point? The boxes are empty.

What happens if you use the values 1992, 2006, and 2019 respectively?

You may need a rule to address a missing date.

Plus, I am fairly sure that there is an algorithm that could convert the year to some value that can be used in turn to color code or shade the cell even more so.

Thanks for the help! I've tried added in the 2 black sections but that in turn did nothing. I also asked about what I put into the min and max. I am not apple to occupy the spaces since its supposed to do that on its own. I could add it in manually but then I would need to continually change the min and max whenever one out of that range is added. I've also tried doing it manually and it still didn't want to color the spaces.

 

[Ralston18]

Reminder:

Work on a copy of the spreadsheet to avoid losing data. You will be able to experiment much more freely and if all goes bad then make a new copy and try again.

Going forward.....

Are any of the other colored columns (D,E,F,G) colored by conditional formatting rules?

Referencing the column/range C8:C44. Are all those rows data - no headers?

Try a smaller test range within Column C. Just a dozen rows that are representative of the overall timeframe.

Go back and try one of the default color scales available for Conditional/Relative color formatting. Versus your custom coloring.

This: " I am not apple to occupy the spaces since its supposed to do that on its own." Even though the contents are "years" what is the actual cell format: number, date, integer? Could be that the format is blocking any automatic changes to the min, midpoint, and max values. Or preventing you from manually doing so.

Verify that the spreadsheet data rows do not need to be sorted; ascending or descending using Column C for the sort order. Should not be necessary but if a sort makes it work then that is progress.

For example, on the copy, you can delete all other columns except for C and focus on getting the desired coloring scheme to work via the Conditional format rules on the right side of the image.

 

[mathewalbertson]

Reminder:

Work on a copy of the spreadsheet to avoid losing data. You will be able to experiment much more freely and if all goes bad then make a new copy and try again.

Going forward.....

Are any of the other colored columns (D,E,F,G) colored by conditional formatting rules?

Referencing the column/range C8:C44. Are all those rows data - no headers?

Try a smaller test range within Column C. Just a dozen rows that are representative of the overall timeframe.

Go back and try one of the default color scales available for Conditional/Relative color formatting. Versus your custom coloring.

This: " I am not apple to occupy the spaces since its supposed to do that on its own." Even though the contents are "years" what is the actual cell format: number, date, integer? Could be that the format is blocking any automatic changes to the min, midpoint, and max values. Or preventing you from manually doing so.

Verify that the spreadsheet data rows do not need to be sorted; ascending or descending using Column C for the sort order. Should not be necessary but if a sort makes it work then that is progress.

For example, on the copy, you can delete all other columns except for C and focus on getting the desired coloring scheme to work via the Conditional format rules on the right side of the image.

Will try this when I have more time! Thanks for all the help, I'll let you know if it works!