Formatting Issues

How to Display Grade Letters Using Excel Number Formatting

Huma Ashraf ChHuma Ashraf Ch Nov 10, 2026 869 views

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.

How to Display Grade Letters with Excel Number Formatting
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the Data Range

Highlight all the cells containing your numeric grades that you want to display as letters.

2
Create a New Conditional Rule

Navigate to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

3
Define the Condition

Choose 'Format only cells that contain'. Set the rule criteria to 'Cell Value', 'equal to', and enter your numeric value (for example, 7).

4
Apply Custom Formatting

Click the 'Format' button, go to the 'Number' tab, and select 'Custom'. In the Type box, enter exactly "A*" (including quotation marks). Click 'OK'.

5
Repeat for Other Grades

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").

Use Conditional Formatting to Display Grade Letters
Added Benefit: While configuring the Number format in Conditional Formatting, you can also switch to the 'Fill' or 'Font' tabs to assign specific colors to each grade level automatically.
Efficient Spreadsheet Management

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. 1. Open Your Grading Sheet: Launch WPS Spreadsheet and open the document containing your numeric grading data.
  2. 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. 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. 4. Customize and Save: Tweak your charts and formatting rules, then save the document flawlessly in your preferred format.
Fully compatible with Microsoft Excel formulas, formatting rules, and file formats (XLSX).Advanced Conditional Formatting options to dynamically label and color-code grading data.Intuitive chart creation tools that accurately map underlying numerical data even when disguised by text labels.Lightweight, fast, and completely free alternative for everyday data analysis and reporting.
microsoft office alternative - wps office

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.