How to Fix a Custom Excel Number Format That Does Not Show kWh
Question details
The user needs to display "kWh" as a unit after numerical values using a custom number format, but the text is missing or appearing inconsistently across positive and negative numbers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a multi-section custom number format to append specific unit text to different types of numerical data.
- Observed behavior
- The "kWh" text suffix appears inconsistently, often only displaying for negative values while failing to appear for positive or zero values.
Select the cells containing the numerical data you want to format before modifying the custom number rules.
Apply Quoted Text Suffix in All Number Format Sections
Modify the custom format code to include the exact quoted string for each format section (positive, negative, zero) to guarantee consistent display.
Excel custom number formats are divided into up to four sections separated by semicolons: Positive values; Negative values; Zero values; and Text. If you want a text suffix like "kWh" to appear for all numbers, you must include the quoted text in every applicable section of your format code.
Select the range of cells where you want the "kWh" unit to appear alongside your numerical values.
Right-click the selected cells and choose 'Format Cells' from the context menu, or press 'Ctrl + 1' on your keyboard.
In the Format Cells window, go to the 'Number' tab and select 'Custom' from the Category list on the left.
In the 'Type' input box, enter your format ensuring the text is wrapped in double quotes in each section. For example, type: 0.0" kWh";[Red]-0.0" kWh";0" kWh". Click OK to apply.
Easily Format Unit Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced custom number formatting, making it simple to add units like kWh, kg, or USD to your data. Because it uses the exact same formatting syntax as Excel, your custom codes will work flawlessly.
- 1. Open your spreadsheet: Launch WPS Office and open the document containing your numerical data.
- 2. Access cell formatting: Select the data range, right-click, and choose 'Format Cells'.
- 3. Apply the custom format: Navigate to the 'Custom' category, input your unit format (such as 0.0" kWh"), and click OK.
Frequently Asked Questions
Why do I need double quotes around text in custom formats?
Double quotes tell the spreadsheet program to treat the enclosed characters as literal text to be displayed exactly as typed. Without quotes, the program might confuse letters like 'h' or 'm' with time formatting commands.
Does adding a text suffix like "kWh" change my cell value to text?
No. Using a custom number format changes only the visual display of the cell. The underlying value remains a pure number, meaning you can still use it in mathematical formulas and calculations.
How do I hide zero values while still displaying the text suffix for other numbers?
You can hide zero values by leaving the third section of the custom format empty. For example, using the format code 0.0" kWh";-0.0" kWh";;@ will display positive and negative values with "kWh", leave zero cells blank, and display any standard text normally.
