How to add more than 3 col

[Koalafied]

Hello!

I have a table where I want to put more emphasize on the values by highlighting them with colours once they reach a certain value.

I want to put it into six categories:

• 95-100%
• 90-95%
• 85-90%
• 80-85%
• 75-80%
• <75%

I have tried it by using conditional formating -> colour scales

But it can only display 3 colours. Can anyone tell me how I can add another 3 colours?

Thank you sincerely in advance.

[#2]

You could use multiple conditional formats, each spanning 3 ranges?

[#3]

@Koalafied 

To add more than three colors to your color scale for conditional formatting in Excel, as noted by Mr. Jan Karel Pieterse in his message, you can use a custom formula.

Here is how you can achieve it:

1. Select the range of cells you want to apply the conditional formatting to.
2. Go to the "Home" tab in the Excel ribbon and click on the "Conditional Formatting" button.
3. Choose "Color Scales" and select the option "More Rules" at the bottom of the menu.
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format" option.
5. In the "Format values where this formula is true" field, enter the following formulas for each category:

95-100%: =AND(A1>=0.95,A1<=1)

90-95%:   =AND(A1>=0.9,A1<0.95)

85-90%:   =AND(A1>=0.85,A1<0.9)

80-85%:   =AND(A1>=0.8,A1<0.85)

75-80%:   =AND(A1>=0.75,A1<0.8)

<75%:      =A1<0.75

Note: Replace A1 with the cell reference of the first cell in your selected range.

1. Click on the "Format" button to choose the formatting style for each category. You can select the desired fill color, font color, and other formatting options.
2. Repeat steps 4-6 for each category, adjusting the formulas and formatting as needed.
3. Click "OK" to apply the conditional formatting rules.

By using custom formulas, you can create up to six categories with different colors for each range of values.

[#4]

I have set the rules up now without any error message. Somehow the coloura do not change though. Maybe because of the %-value?

[#5]

@Koalafied 

...and it works! 🙂

Attached is an example file.

[#6]

Not sure why it doesnt do that in my file 🙁

[#7]

@NikolinoDE 

[#8 · Accepted solution]

As far as I can see from the picture, they work in German. this means that the formulas do not correspond to the language.
Excel has different formula names for the same formula depending on the country.

Here are the formulas translated into German:
Für die Kategorie 95-100%: =UND($A1>=0,95;$A1<=1)
Für die Kategorie 90-95%: =UND($A1>=0,9;$A1<0,95)
Für die Kategorie 85-90%: =UND($A1>=0,85;$A1<0,9)
Für die Kategorie 80-85%: =UND($A1>=0,8;$A1<0,85)
Für die Kategorie 75-80%: =UND($A1>=0,75;$A1<0,8)
Für die Kategorie <75%: =$A1<0,75

The file I added doesn't work?
If yes, can't you copy the rules from the example file?
Additional information such as Excel version, operating system, storage medium, etc. would also help if this is not the problem.

[#9]

This works now perfectly! Thank you very much. I did not know that its different per language.

[#10]

I'm glad I could help you.
I wish you continued success with Excel!