SOLVED

# How to add more than 3 col

Copper Contributor

# How to add more than 3 col

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?

9 Replies

# Re: How to add more than 3 col

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

# Re: How to add more than 3 col

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.

# Re: How to add more than 3 col

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

# Re: How to add more than 3 col

...and it works! 🙂

Attached is an example file.

# Re: How to add more than 3 col

Not sure why it doesnt do that in my file 🙁

# Re: How to add more than 3 col

best response confirmed by Koalafied (Copper Contributor)
Solution

# Re: How to add more than 3 col

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.

# Re: How to add more than 3 col

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

# Re: How to add more than 3 col

I wish you continued success with Excel!
1 best response

Accepted Solutions
best response confirmed by Koalafied (Copper Contributor)
Solution

# Re: How to add more than 3 col

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.