Fix Excel Formula Returning Zero or a Dash When Adding Numbers
Question details
The user's formula evaluates to a zero or displays a dash instead of the expected sum when adding numbers together.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding numbers using functions like SUM or basic addition operators in a spreadsheet.
- Observed behavior
- The formula output displays as 0 or a dash (-) rather than the correct mathematical total.
Select the cell displaying the dash or zero and look at the formula bar to verify if the underlying value is actually 0. This helps determine whether it is a visual formatting issue or a true calculation error.
Adjust the Cell Number Format
Accounting and certain custom formats intentionally display zero values as a dash. Changing the format can reveal the true calculated value.
In financial and accounting contexts, a dash is often preferred over a zero to make spreadsheets look cleaner. If your formula legitimately calculates to zero but shows a dash, adjusting the display format will revert it to a standard digit.
Highlight the cell or range of cells that are returning a dash or unexpected zero.
Right-click the selected area and choose 'Format Cells' from the context menu, or press the shortcut Ctrl+1.
Navigate to the 'Number' tab. In the Category list on the left, select 'General' or 'Number'.
Click 'OK'. The dash should now display as a standard 0 if the underlying value was indeed zero.
Convert Numbers Stored as Text
If input cells are formatted as text, functions like SUM will ignore them, resulting in a zero total.
Check for Hidden Spaces and Blank Cells
Unseen spaces or improperly referenced blank cells can cause calculation disruptions.
Easily Fix Formula Errors with WPS Spreadsheet
WPS Spreadsheet provides intuitive error checking and smart formatting features that easily identify numbers stored as text, helping you sum your data accurately without unexpected zeros or dashes.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formula errors.
- 2. Identify text formatting: Highlight the range of cells you want to add. Look for the prompt icon if numbers are stored as text, and click 'Convert to Number'.
- 3. Adjust display format: Click the 'Home' tab, locate the number format dropdown, and ensure it is set to 'General' or 'Number'.
- 4. Recalculate accurately: Use the AutoSum feature under the 'Formulas' tab to quickly and accurately add the column or row.
Frequently Asked Questions
Why does my Excel cell show a dash instead of a 0?
This usually happens because the cell is formatted using the 'Accounting' number format. In Accounting formatting, zeroes are intentionally displayed as dashes to make financial reports cleaner and easier to read.
Does the SUM function ignore text values?
Yes, the SUM function automatically ignores cells containing text strings. If all cells in your referenced range are stored as text, the formula will simply return a 0. You must convert these text values to numbers to calculate them.
How can I force a spreadsheet to add cells formatted as text?
You can use the VALUE function around your cell references, or add a double negative (--) before the cell reference (e.g., =--A1 + --A2) to force the software to evaluate text strings as numeric values during the calculation.
Why is my formula showing the formula text itself instead of calculating?
The cell containing your formula is likely formatted as 'Text'. To fix this, change the cell format to 'General', then click directly into the formula bar and press Enter to force it to recalculate as a formula.
