Function Problems

How to Assign Scores to Text Values and Apply Color Rules in Excel

WPS Editor Sep 28, 2026 871 views

Question details

The user needs to convert text selections into numeric values, calculate a total score, and format the resulting cell with colors based on specific risk ranges.

Assign Scores to Text Values and Apply Excel Color Rules

Product: Excel
Device & OS: not provided
Scenario: Creating a risk assessment matrix or scoring model where text inputs need numeric evaluation and visual color coding.
Observed behavior: Text values need to be mapped to scores and totaled, with the final cell changing color based on the numeric result threshold.

Before you start

Ensure you have a reference 'Key Table' set up in your worksheet that maps each possible text value to its corresponding numeric score before building your formulas.

Solution 1Recommended

Use XLOOKUP and Conditional Formatting

Combine XLOOKUP formulas to convert text to numbers, add them together for a total score, and apply color scales based on risk ranges.

By utilizing the XLOOKUP function, you can search for a text value in your key table and return its corresponding numeric score. Adding multiple XLOOKUP functions together allows you to generate a total score, which can then be color-coded using Conditional Formatting.

Create a Key Table

Set up a table named 'KeyTable' with one column for your text inputs (e.g., RESULT) and adjacent columns for their respective scores (e.g., IMPACT, URGENCY).

Write the XLOOKUP Formula

In your main assessment table, enter the formula to calculate the total score: =XLOOKUP([@IMPACT],KeyTable[RESULT],KeyTable[IMPACT]) + XLOOKUP([@URGENCY],KeyTable[RESULT],KeyTable[URGENCY]). Press Enter to apply.

Open Conditional Formatting

Select the column containing your total scores. Go to the Home tab on the ribbon and click 'Conditional Formatting', then select 'New Rule'.

Apply Color Rules

Choose 'Format only cells that contain'. Set the condition to 'Cell Value' 'greater than or equal to' and enter 8. Click Format, choose a red fill color, and click OK. Repeat this process for other thresholds (e.g., >=6 for orange, >=4 for yellow, >=3 for green).

Rule Order Matters: When using multiple 'greater than or equal to' rules, open the Conditional Formatting Rules Manager and ensure your rules are ordered from highest value (>=8) at the top to lowest value (>=3) at the bottom. Check 'Stop If True' to prevent conflicts.

Advanced Spreadsheets Made Easy

Easily Calculate Scores and Format Cells with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced lookup functions like XLOOKUP and comprehensive conditional formatting tools, making it easy to build risk matrices and scoring models efficiently.

1. Open your file: Launch WPS Spreadsheet and open the document containing your scoring matrix.
2. Apply XLOOKUP: Set up your scoring key table and use the XLOOKUP function to map text to numbers seamlessly.
3. Format Cells: Highlight the total cells, navigate to 'Home' > 'Conditional Formatting', and define your color rules using 'New Rule'.

Fully compatible with Microsoft Excel formulas, functions, and file formatsIntuitive Conditional Formatting rules manager for complex color codingFree and lightweight data analysis tool for all your calculation needs

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP instead of XLOOKUP for this task?

Yes, if your spreadsheet software version does not support XLOOKUP, you can use VLOOKUP by referencing your key table. However, XLOOKUP is generally more flexible, supports exact matches by default, and can return arrays.

Why is my conditional formatting applying the wrong color to the scores?

When using multiple 'greater than or equal to' rules (e.g., >=3, >=6, >=8), the priority order of the rules dictates the outcome. Open the Conditional Formatting Rules Manager and use the up arrow to move the highest value rule (>=8) to the top of the list.

How do I handle empty cells so they don't show an error?

You can wrap your lookup formulas in the IFERROR function, like =IFERROR(XLOOKUP(...), 0). This ensures that if a text cell is left blank, the formula will return a zero score instead of an #N/A error.

Excel & Data Problems