How to Display Grade Letters Using Excel Number Formatting
Question details
Display numeric grades as text labels (such as A*, A, B, and C) without changing the underlying numeric values, ensuring the data can still be used for charting.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user needs to visually present grades as letters in a spreadsheet report, but requires the underlying data to remain numeric so that performance charts and average calculations function correctly.
- Observed behavior
- Excel's default custom number formatting feature limits users to only two conditional sections, which prevents assigning multiple different letter grades directly through standard cell formatting.
Ensure your numeric grading scale is clearly defined (e.g., 7 = A*, 6 = A) and your numeric data is cleaned before applying conditional rules. This prevents overlapping criteria and formula errors when assigning the letter grades.
Use Conditional Formatting to Display Grade Letters
Since custom number formats are limited to two conditions, you can use Conditional Formatting to apply separate custom text formats for each numeric grade. This keeps the underlying number intact for charts.
Conditional formatting allows you to bypass the standard custom format limitations. You can set up multiple rules, assigning a specific text display format and even customized background colors for each specific grade tier.
Highlight all the cells containing your numeric grades that you want to display as letters.
Navigate to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.
Choose 'Format only cells that contain'. Set the rule criteria to 'Cell Value', 'equal to', and enter your numeric value (for example, 7).
Click the 'Format' button, go to the 'Number' tab, and select 'Custom'. In the Type box, enter exactly "A*" (including quotation marks). Click 'OK'.
Repeat this process to add new rules for the remaining grades (e.g., if cell value equals 6, format as "A"; if 5, format as "B").

Create a Helper Column Using the SWITCH Formula
If you prefer a simpler approach without managing multiple formatting rules, you can generate the letter grades in an adjacent column using the SWITCH function.
Easily Format Grades and Charts in WPS Spreadsheet
WPS Spreadsheet offers robust formula support, including the SWITCH function, and comprehensive conditional formatting tools. You can effortlessly manage numeric grading scales, convert data accurately, and visualize your reports efficiently.
- 1. Open Your Grading Sheet: Launch WPS Spreadsheet and open the document containing your numeric grading data.
- 2. Apply Formatting or Formulas: Use the Home tab to access Conditional Formatting for direct cell transformation, or use an adjacent column to input the SWITCH formula.
- 3. Visualize Your Data: Highlight the original numeric data, click the Insert tab, and select a Chart type to visually represent the grade distribution.
- 4. Customize and Save: Tweak your charts and formatting rules, then save the document flawlessly in your preferred format.

Frequently Asked Questions
Why can't I just use standard custom number formats for all my grades?
Excel's standard custom number format only supports up to two conditional sections (e.g., [>5]"Pass";[<5]"Fail"). Because a grading scale like A*, A, B, C, D requires far more than two conditions, standard number formatting isn't sufficient, making Conditional Formatting or helper columns necessary.
Will changing the display format to text letters break my charts?
If you use Conditional Formatting to alter the display to letters, the underlying cell value remains numeric. Therefore, charts, averages, and mathematical calculations will continue to work perfectly.
Can I use the VLOOKUP function instead of SWITCH for grading?
Yes. For larger or more complex grading scales with ranges (e.g., 90-100 = A), using VLOOKUP with an approximate match is highly recommended. You create a small reference table and use a formula like =VLOOKUP(A2, TableRange, 2, TRUE) to map the numeric grades dynamically.




