'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Show Zero Instead of a Dash in Excel Formula Results
Question details
The user needs formula results that evaluate to zero to display as the digit '0' rather than a dash or hyphen.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing calculated results in a spreadsheet where cells returning a value of zero display as dashes due to the cell formatting.
- Observed behavior
- When a formula returns a zero, Excel displays a dash (-) in the cell instead of the numerical digit 0, while manually entered zeros may display differently depending on the format.
Select the specific cells, rows, or columns that contain the formulas displaying dashes so you can apply the formatting adjustment to all of them at once.
Change the Number Format to Currency or General
The most straightforward solution is to change the cell format from Accounting, which defaults to dashes for zeros, to Currency or General format.
The Accounting number format is specifically designed for financial reports. It aligns currency symbols and decimal points but intentionally converts zero values to dashes to reduce visual clutter. Changing to another standard number format resolves this instantly.
Click and drag to highlight the cells containing the formula results that are currently showing dashes instead of zeros.
Go to the 'Home' tab on the Excel ribbon. Look for the 'Number' group in the middle of the ribbon.
Click the dropdown menu that currently says 'Accounting' and select 'Currency', 'Number', or 'General' from the list. The dashes will instantly change to zeros.
Modify the Custom Number Format Code
If you want to maintain the specific alignment of the Accounting format but force it to display zeros, you can edit the format's custom code.
Manage Cell Formatting Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful, intuitive formatting tools that let you fully customize how zeros, dates, and currencies are displayed. It easily handles files created in Excel without losing any formatting rules.
- 1. Open your file: Launch WPS Office and open the spreadsheet containing your formulas.
- 2. Highlight the data: Select the cells, columns, or rows where zeros are appearing as dashes.
- 3. Update the format: Go to the Home tab, click the Number Format dropdown, and choose Currency or Number to instantly reveal the zero values.
Frequently Asked Questions
Why does Excel show a dash instead of zero by default?
This happens when a cell is formatted using the 'Accounting' format. This format is designed for financial statements where displaying dashes instead of zeros makes large tables of data easier to read and reduces visual clutter.
Does the dash mean my formula is broken or returning an error?
No. The dash simply means the formula successfully calculated a result of zero, and Excel is hiding the zero based on the cell's visual formatting rules. The underlying value is still recognized as zero.
Can I hide zero values completely instead of showing a dash?
Yes. You can hide zeros completely by going to File > Options > Advanced, scrolling down to 'Display options for this worksheet', and unchecking 'Show a zero in cells that have zero value'.
Will this fix affect how negative numbers are displayed?
Changing from Accounting to Currency or Number format might slightly change how negative numbers and currency symbols are aligned. If alignment is strictly required, modifying the Custom format code is the best approach.
