Excel colour formatting based off value

Copper Contributor

Hey, I have a 10 point scale (1,2,3,4,5,6,7,8,9,10) and each number has a set colour attacthed to it. I then have names on the left and for example, uniform and behaviour on the top row, so each week i update each persons scale based on the 10 point system. However I need the total value of each topic per person. Like (green, green, yellow, red, orange) should equal 10+10+5+1+3=28/50. I am wanting to make it so that i can just change the number within the cell and have the colour change accordingly to the value (and also hide the text (number)) in the mean time.

4 Replies
Did you try conditional formatting? Attach a sample Excel workbook.

@ApexSurvivor 

To achieve this in Excel, you can use Conditional Formatting, as mentioned to you before, to change the cell color based on the value, and you can also hide the number by using custom number formatting. Here is how you can do it:

Step 1: Apply Conditional Formatting

  1. Select the range of cells where you will enter your 10-point scale values.
  2. Go to the Home tab on the ribbon.
  3. Click on Conditional Formatting and select New Rule.
  4. Choose Use a formula to determine which cells to format.
  5. Enter the formula to apply the formatting based on the cell value. For example:
  • For 1: =$A1=1
  • For 2: =$A1=2
  • And so on for each number up to 10.
  1. Click on the Format button, go to the Fill tab, and select the color you want for each value.
  2. Repeat the process for each number with its corresponding color.

Step 2: Hide the Text (Numbers)

  1. Select the same range of cells where you applied the conditional formatting.
  2. Right-click and select Format Cells.
  3. Go to the Number tab.
  4. Select Custom.
  5. Enter the following custom number format: ;;; (three semicolons).

Step 3: Calculate Total Value

Assuming you have your data structured with names in column A and different topics (like uniform and behavior) in columns B, C, D, etc., and the weekly ratings in rows, you can calculate the total value for each person as follows:

  1. In a new column, use the SUM function to add up the values for each person. For example, if you have ratings in columns B to F for each person:
  • In cell G2, enter: =SUM(B2:F2)
  • Drag the formula down to apply it to each person.
  1. If you want to calculate the percentage score out of the maximum possible score (assuming each topic has a maximum score of 10):
  • In cell H2, enter: =G2/(5*10) (replace 5 with the number of topics you have).
  • Format cell H2 as a percentage.
  • Drag the formula down to apply it to each person.

Example Structure:

Assuming names in column A, and topics in columns B to F:

   A        B       C       D       E       F       G     H
1  Name    Topic1  Topic2  Topic3  Topic4  Topic5  Total  Percentage
2  John      10      10      5       1       3      29     58%
3  Jane       7       6      10      9       8      40     80%

Notes:

  • Ensure your conditional formatting rules cover all possible values (1 to 10).
  • The custom number format ;;; will hide the numbers but still allow calculations.

This setup allows you to update the numbers each week and have the colors and total calculations update automatically. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

 

@Harun24HR Yes i have, unfortunately even if the function is correct it still does not update the colours. Also there is no option to attach a file?

@ApexSurvivor 

 

Go to Conditional Formatting on the Home tab of the ribbon. Select New rule > Use a formula to determine which cells to format. Then, type this one:

 

=C3=1

 

Note that the reference of C3 is not absolute. Then, select to fill with red color on the fill tab and change font color (on the font tab) with the same color you filled the cell (this will make the number not visible). Do the same procedure for all points in the scale.

 

Finally, once you have all rules set, go to manage rules in the conditional formatting options and change the range of "Applies to" to select the entire desired range of application.

 

I have created a sample sheet with a 3 point scale. Red for 1, orange for 2 and green for 3. Please see attached sample book for a better reference.

 

Martin