Formula Errors

How to Fix Excel Formulas Returning Zero or a Dash

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to fix an issue where formulas referencing other worksheets return a zero or a dash instead of the calculated value.

Product: Excel
Device & OS: not provided
Scenario: Working with inter-sheet formulas or complex calculations that suddenly stop outputting the correct result.
Observed behavior: Formulas that previously worked return 0 or a dash (-). Copying the worksheets to a completely new workbook fails to resolve the issue.

Before you start

Before troubleshooting the formulas, verify that your workbook calculation options are set to 'Automatic' and check the bottom status bar of your Excel window for any 'Circular Reference' warnings.

Solution 1Recommended

Identify and Resolve Circular References

Formulas often return zero when there is a circular reference in the workbook, causing the calculation engine to halt or output null values.

A circular reference occurs when a formula directly or indirectly refers to its own cell. When this happens, Excel may stop calculating formulas across the workbook, causing cells formatted as Accounting to show a dash and General cells to show a zero.

Locate the error

Navigate to the 'Formulas' tab on the Excel ribbon, click on the arrow next to 'Error Checking', and select 'Circular References' to locate the specific cell causing the infinite loop (e.g., cell N7).

Rewrite the formula

Review the identified cell and modify the formula so it does not refer back to itself. For example, change it to a valid structure like =SUM($E$7,$H$7,$K$7)/SUM($E$6,$H$6,$K$6,$N$6)*N6.

Recalculate the workbook

Press the F9 key or click 'Calculate Now' in the Formulas tab to refresh the calculations and verify that the zeros and dashes have been replaced by the correct values.

Tip: If 'Circular References' is grayed out under the Error Checking menu, it means Excel has not detected any circular references in the currently active workbook.

Easily Troubleshoot Formula Errors with WPS Spreadsheet

WPS Spreadsheet provides intuitive error-checking tools to quickly detect circular references and formula errors. It enables you to locate broken calculation chains easily and get your workbooks running perfectly.

1. Open your workbook: Launch WPS Spreadsheet and open the file displaying zeros or dashes.
2. Find the circular reference: Go to the 'Formulas' tab in the top menu, click the 'Error Checking' drop-down, and select 'Circular References'.
3. Apply the fix: Click the cell reference listed in the menu to jump to the error, correct the formula to remove the loop, and press F9 to recalculate.

Advanced Error Checking tool to instantly locate circular references.Fully compatible with Microsoft Excel formulas, functions, and .xlsx formats.Lightweight and fast, ensuring smooth calculations even with complex inter-sheet data.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my formulas suddenly show a zero instead of a value?

This usually happens because a circular reference has broken the calculation chain, or the calculation options have accidentally been set to 'Manual' instead of 'Automatic'.

What does a dash mean in Excel instead of a zero?

If a cell is set to the 'Accounting' number format, the application displays a zero value as a dash (-). Fixing the underlying formula error will replace the dash with the proper numerical result.

How can I quickly find all circular references in my worksheet?

You can find them by checking the bottom-left status bar of your window which typically displays 'Circular References' along with the cell address. Alternatively, use the Error Checking tool in the Formulas tab.

Excel & Data Problems