Convert Excel Status Text into Completion Percentages in WPS Office Spreadsheets
Question details
The user needs to turn text status values such as Not Started, In Progress, and Completed into numeric completion percentages and show red, yellow, or green progress indicators based on percentage thresholds.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- A project tracker or task list uses text status labels, and the user wants completion percentages with color-coded progress indicators.
- Observed behavior
- Status cells currently contain text labels; the user needs a formula-based percentage value and conditional formatting for red, yellow, and green indicators.
Make sure the status text is consistent, for example exactly "Not Started", "In Progress", and "Completed", with no extra spaces. Insert one helper column next to the status column for the calculated percentage.
Use a Nested IF Formula and Conditional Formatting for Red, Yellow, and Green Percentages
Best for a simple project tracker: convert each status label to 0, 50, or 100, display it as a percentage, and apply rule-based formatting so the cell fill changes according to the percentage value.
The nested IF formula checks the status cell and returns 0 for Not Started, 50 for In Progress, and 100 for Completed. Using a custom format 0"%" displays these values as 0%, 50%, and 100% without turning 50 into 5000%. Conditional formatting then tests the underlying numeric values and applies the matching color.
Click the first result cell next to your status column, type =IF(A2="Not Started",0,IF(A2="In Progress",50,IF(A2="Completed",100,""))), and press Enter.
Select the formula cell and drag the fill handle down to apply it to all status rows in the project tracker.
With the result cells selected, press Ctrl+1, go to the Number tab, choose Custom, enter 0"%" in the Type box, and click OK. This shows 0%, 50%, and 100% while keeping the numeric values 0, 50, and 100 for accurate conditional formatting.
Select the percentage range, click Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter 50, and choose Light Red Fill with Dark Red Text or a custom red format.
With the same range selected, click Home > Conditional Formatting > Highlight Cells Rules > Between, enter 50 and 99, and choose Light Yellow Fill with Dark Yellow Text or a custom yellow format.
Click Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter 100, and choose Green Fill with Dark Green Text or a custom green format.
Convert Status Text to Completion Percentages in WPS Office Spreadsheets
WPS Office Spreadsheets can perform this task directly with familiar Excel-compatible formulas and conditional formatting. You can create the same status-to-percentage mapping and color rules without changing your workflow.
- 1. Open your workbook: Download and install WPS Office, then open WPS Office Spreadsheets and load the .xlsx project tracker file.
- 2. Add the formula column: In a blank helper column, enter =IF(A2="Not Started",0,IF(A2="In Progress",50,IF(A2="Completed",100,""))) and apply it down the column.
- 3. Display percentages correctly: Select the calculation cells, press Ctrl+1, choose Custom, and enter 0"%" so the values display as 0%, 50%, and 100% without changing the numeric scale.
- 4. Apply conditional formatting rules: Select the percentage cells and add Less Than 50 for red, Between 50 and 99 for yellow, and Equal To 100 for green via Home > Conditional Formatting.
Frequently Asked Questions
Can I use a formula without nested IFs in WPS Office?
Yes. If your version supports IFS, you can use =IFS(A2="Not Started",0,A2="In Progress",50,A2="Completed",100,TRUE,""). You can also use a lookup table with VLOOKUP or XLOOKUP if you have many status labels.
Why does my percentage show as 5000% instead of 50% when I use Percentage formatting?
The built-in Percentage format multiplies the cell value by 100. Since 50 is already on a 0–100 scale, it becomes 5000%. Use a custom number format 0"%" to display 50 as 50%, or divide the formula result by 100 and use the built-in Percentage format.
How do I make the conditional formatting use red below 50%, yellow for 50% to 99%, and green at 100%?
Select the percentage range and create three rules: Less Than 50 for red, Between 50 and 99 for yellow, and Equal To 100 for green. In WPS Office, these options are under Home > Conditional Formatting > Highlight Cells Rules.
What should I do if my status text has extra spaces or lowercase letters?
Use consistent text exactly matching the formula values, or make the formula more flexible with TRIM and UPPER. For example: =IF(TRIM(UPPER(A2))="NOT STARTED",0,IF(TRIM(UPPER(A2))="IN PROGRESS",50,IF(TRIM(UPPER(A2))="COMPLETED",100,""))).
Will this formula work when I open the workbook in Microsoft Excel?
Yes. The nested IF formula, custom number format, and conditional formatting rules are compatible with Excel .xlsx files, so the workbook will behave the same way in Microsoft Excel and WPS Office Spreadsheets.
