Fix Excel Number Formatting Not Showing Dollar Signs or Decimals
Question details
The user needs a Price column in an Excel spreadsheet to display values with a dollar sign and two decimal places. Format changes between Text, General, Currency, and Accounting are not updating the display, so the user must check for a custom number format or text-stored values.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A user has a Price column that should show currency values with a dollar sign and two decimal places, but the cells are not displaying the expected format.
- Observed behavior
- The Price column displays numbers without a dollar sign or two decimal places even after changing the cells between Text, General, Currency, and Accounting formats.
Open the affected workbook in WPS Office Spreadsheet and select the Price cells. Right-click the selection and open Format Cells so you can verify which number category is active and inspect the Custom format code.
Remove an Overriding Custom Format and Apply Currency or Accounting
Use this when the Price column ignores Currency or Accounting because a custom number format is still applied.
A custom number format can override normal number categories. Inspect the Custom type box to see whether it lacks the $ symbol or two decimal places.
Select all affected Price cells in the worksheet.
Right-click the selection and click Format Cells.
On the Number tab, click Custom and inspect the Type box for an overriding numeric code.
Enter $#,##0.00 in the Type box, or switch to Currency/Accounting, set Decimal places to 2 and Symbol to $, then click OK.
Convert Text-Stored Values to Numbers and Reapply Currency Format
Use this when the values are stored as text, so number formats do not change their appearance.
Fix Dollar Sign and Decimal Display in WPS Office Spreadsheet
Use WPS Office Spreadsheet to inspect and change the number format directly, replace overriding custom formats, and convert text values to numbers so Price columns show $#,##0.00 as expected.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office and open the .xlsx file containing the Price column.
- 2. Open Format Cells: Select the Price cells, right-click, and choose Format Cells.
- 3. Apply the correct number format: On the Number tab, choose Currency or Accounting, set 2 decimal places and $ symbol, or select Custom and enter $#,##0.00.
- 4. Convert text values if needed: If values are text, use Home > Paste > Paste Special with Add to convert them to numbers, then reapply the currency format.
Frequently Asked Questions
Why does Excel still not show dollar signs after I pick Currency?
A custom number format may be overriding the Currency setting. Select the cells, right-click, choose Format Cells, go to the Custom category, and replace the existing code with $#,##0.00 or apply Currency/Accounting again.
How can I convert text prices to numbers in WPS Office?
Copy an empty cell, select the text-stored Price cells, go to Home > Paste > Paste Special, choose Add under Operation, and click OK. The values will become real numbers and can then accept a currency format.
What is the difference between Currency and Accounting formats?
Currency places the dollar sign directly next to the number, while Accounting aligns the dollar sign and decimal points in a column. Both can display two decimal places when configured in Format Cells.
Can a custom number format block decimal places in Excel?
Yes. A custom format such as 0 or General can prevent decimal places from appearing. Check the Custom category in Format Cells and replace it with a format that includes decimals, such as $#,##0.00.
