How to Show Optional Decimal Places and Add Text Labels in Excel
Question details
The user wants to format cells to display whole numbers without trailing zeros, show decimal places only when they exist, and automatically append a specific text label (e.g., "in").
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting numerical data with specific measurement units for better readability, while keeping the visual layout clean by omitting unnecessary decimal zeros on whole numbers.
- Observed behavior
- The user needs numerical values to dynamically display as whole numbers with a label (e.g., "48 in") and fractional numbers with a label (e.g., "48.7 in") while maintaining their underlying numerical value.
Ensure you have selected the correct range of cells containing the numbers you want to format before opening the formatting dialog.
Apply a Custom Number Format Using 'General'
Use the Format Cells dialog to apply a custom format string that automatically adjusts decimal places and appends your desired text unit.
The 'General' number format in Excel dynamically displays decimal places only when they actually exist, successfully avoiding trailing zeros for whole numbers. By appending your desired text enclosed in quotation marks to the 'General' rule, you can create a custom unit label.
Highlight the cells or range containing the numbers you want to format with custom text labels.
Right-click the selected cells and choose 'Format Cells' from the context menu, or press the 'Ctrl + 1' shortcut on your keyboard.
In the Format Cells window, go to the 'Number' tab and select 'Custom' from the Category list on the left side.
In the 'Type' input field, enter the string: General "in" (replace "in" with your preferred unit, ensuring you keep the space before it inside the quotes if you want spacing). Click 'OK' to apply.
Format Custom Number Labels Easily with WPS Spreadsheet
WPS Spreadsheet offers full support for custom number formatting, allowing you to add text labels and manage decimal places exactly like Microsoft Excel. It provides a familiar interface to organize and format your data efficiently.
- 1. Select Cells in WPS: Open your spreadsheet in WPS Office and highlight the cells you want to format.
- 2. Access Format Cells: Right-click the selection and click 'Format Cells', or press Ctrl + 1.
- 3. Apply Custom Format: Navigate to 'Custom' under the 'Number' tab, type General "in" in the Type box, and click OK.
Frequently Asked Questions
How do I add different text labels for positive and negative numbers?
You can use semicolons to separate format conditions for positive, negative, zero, and text values. For example, entering `General "profit";-General "loss"` in the custom format type box will append different labels based on the number's mathematical sign.
Why are my whole numbers showing trailing zeros after formatting?
If your whole numbers display unnecessary zeros (e.g., 48.00 in), you likely applied a fixed decimal format like `0.00 "in"`. Change your custom format type to `General "in"` to hide unnecessary trailing zeros.
Can I sort or sum the numbers after adding text labels this way?
Yes. Because custom number formatting only changes the visual presentation and does not convert the actual cell value to text, you can continue to sort, filter, and use mathematical functions like SUM normally.
