If an Excel cell shows 0 instead of the value you expect, first select it and inspect the formula bar: this reveals whether the cell contains a literal zero, a formula returning zero, or a value that is being rounded or otherwise hidden by its number format. Then check the formula’s inputs, the cell’s number format, and Excel’s calculation settings; the right fix depends on which of those is responsible.
What the zero tells you
A displayed zero is not always the same as a stored zero. Excel keeps a cell’s underlying value or formula separately from how the cell looks on the worksheet. A number format can round a small number to 0, while a formula can calculate a genuine zero or return zero because a referenced cell is empty. The formula bar and the cell’s formatting are useful first clues.
Select the affected cell and look at the formula bar above the grid. If it contains a number such as 0, the cell has a zero value. If it begins with an equals sign, Excel is displaying the result of a formula; inspect that formula and the cells it refers to. If the formula bar shows a nonzero number but the grid shows 0, investigate number formatting or display precision. If the formula bar shows a formula but the cell itself shows the formula text, that is a different display issue: see whether Show Formulas is turned on.
Before changing anything, note the cell address and, if the value matters, make a copy of the workbook. Avoid overwriting the cell with a typed number until you know whether it contains a formula. Typing over a formula removes it, even if the displayed result looks wrong.
Check whether number formatting rounds the value
The cell may hold a small nonzero value that is rounded to 0 for display. For example, a value of 0.004 shown with two decimal places appears as 0.00; with no decimal places, a value such as 0.4 may appear as 0. This changes the appearance, not necessarily the stored number or the result used in later calculations.
To reveal more digits in Excel for Microsoft 365 on Windows, select the cell and use Home > Number > Increase Decimal. Alternatively, press Ctrl+1 to open Format Cells, choose the Number category, and increase Decimal places. On Mac, Command+1 opens Format Cells; the Increase Decimal control is on the Home tab. In Excel for the web, select the cell and use the number-format controls on the Home tab.
Also check for a custom number format. Press Ctrl+1 on Windows, choose Number, and inspect the category and format; on Mac use Command+1. A custom format can scale or suppress displayed digits, and some formats deliberately show zero values as blank or as a dash. Choosing General or an appropriate Number format is a quick diagnostic, but it may change the appearance of dates, percentages, or other specially formatted data. Pick a format that suits the meaning of the value rather than applying General indiscriminately.
For instance, if a percentage cell displays 0%, increasing decimal places may reveal 0.4% or 0.04%, depending on the stored value. Percentage formatting multiplies the displayed number by 100; it does not change the underlying fraction. Confirm the units and intended precision before deciding that the value itself is incorrect.
Trace a formula that returns zero
If the formula bar starts with an equals sign, Excel may be calculating zero correctly from the inputs it currently sees. A direct reference to an empty cell is a common surprise: Excel treats the blank reference as zero in many calculations. With A1 intended to contain an amount and B1 intended to display it, a formula in B1 that points to A1 can show 0 while A1 is empty. A copied formula may also point to an unexpected blank cell if its references shifted.
For a simple illustration, with A1 blank and a formula entered in B1, =A1 returns 0. If A1 contains 18, the same formula returns 18. Check the referenced cells directly, and verify that the formula points to the intended row and column. When formulas are copied, relative references adjust; dollar signs in references such as $A$1 lock a row and column, while mixed references lock only one of them.
Use Formulas > Evaluate Formula to step through a calculation and see how Excel evaluates its parts. You can also select a reference inside the formula bar and press F9 to evaluate that selected portion, but press Esc afterward rather than Enter if you do not want to replace the formula with the evaluated value. Formulas > Trace Precedents can draw arrows to cells that feed the selected formula. These tools help identify blanks, unexpected references, and intermediate calculations that become zero.
A formula may be designed to show blank output when its input is blank. With an input in A1 and the result formula in B1, =IF(A1="","",A1) returns an empty string when A1 is blank and otherwise returns A1’s value. This is useful for cleaner reports, but it also hides a legitimate zero entered in A1 only if the condition is changed to test for zero. Keep the distinction between an empty input and a numeric zero if both have different meanings in the workbook.
Look for empty, text, or mismatched inputs
Formula results can be zero because an input is missing or stored in an unexpected form. A cell that looks blank may contain a formula returning an empty string, and a number imported from another system may be stored as text. Arithmetic formulas and functions handle text differently, so the result may not match what you expect. Check the source cells and the formula’s logic rather than assuming that the displayed zero is a formatting error.
For example, SUM ignores text in referenced cells, so a number stored as text may be excluded from a total. A formula that compares text with a number may also behave differently from one using numeric values. Look for a green error indicator, if present, and use the warning menu to convert text to numbers when that option is offered. You can also inspect alignment and cell contents, but alignment alone is not proof: formatting can override Excel’s usual alignment cues.
For a short total, with numeric entries expected in B2:B5 and the total in B6, =SUM(B2:B5) returns the sum of the numeric values in that range. If the result is 0, check that the intended values are actually within B2:B5, are numeric, and are not excluded by a different formula or filter-related setup. A filtered list by itself does not make the ordinary SUM function total only visible rows; if the goal is a visible-rows-only total, choose a suitable subtotaling approach.
Check calculation mode and refresh the result
If a formula’s inputs have changed but its displayed result has not, the workbook may be using manual calculation. On Windows, open Formulas > Calculation Options and select Automatic. Excel for Mac has the Calculation options on the Formulas tab as well. Excel for the web normally recalculates formulas automatically, though a complex workbook or a connection to external data may need separate attention.
To recalculate the active workbook, press F9 on Windows. Ctrl+Alt+F9 forces a full calculation of all formulas in open workbooks, which can help when a dependent result appears stale. Keyboard behavior can differ on Mac keyboards and function-key settings; use the Formulas tab’s calculation commands if the shortcut does not work. Recalculation will not fix an incorrect formula or missing input, but it can refresh a correct formula that has not updated.
Calculation mode can be affected by other open workbooks, and a workbook saved in manual mode may continue to use that setting when reopened. If the issue returns, check Calculation Options again and consider whether the workbook relies on external links, data connections, or volatile formulas. Do not confuse a stale result with a value that is permanently stored as zero: the formula bar and a recalculation help distinguish them.
Confirm zero-display and precision settings
Excel has an option to hide zero values on a worksheet. On Windows, go to File > Options > Advanced, then under Display options for this worksheet check Show a zero in cells that have zero value. Make sure the correct worksheet is selected in the dropdown. This setting affects whether zeros are shown; it does not turn a nonzero result into zero. If the option is off, actual zeros generally appear as blank cells, not as a displayed 0.
There is also a workbook setting named Set precision as displayed under File > Options > Advanced, in the calculation options for the workbook. It can permanently change stored values to match their displayed precision. If it has been enabled, underlying values may already have lost digits, which can affect later calculations. Do not turn it on as a quick formatting fix. If you suspect it changed important data, restore a backup or an earlier version rather than assuming that increasing decimal places will recover discarded digits.
On Mac, Excel’s preferences and advanced options are arranged differently from Windows, so use Excel’s settings or Help search to locate the worksheet display and calculation controls available in that version. In Excel for the web, desktop-only options may not be available; open the workbook in the desktop app when you need to inspect advanced calculation settings. The exact controls can vary by platform and update, but number formatting and formula inspection remain useful across versions.
When the cell shows a formula instead
Sometimes the apparent value problem is that a cell displays a formula such as =SUM(B2:B5), rather than its calculated answer. This is not the same as a zero result. On Windows, press Ctrl+` (the grave-accent key, often beside the 1 key) to toggle Show Formulas, or use Formulas > Show Formulas. On Mac, use the Show Formulas control on the Formulas tab if the keyboard shortcut differs on your keyboard layout.
If only one cell shows the formula as text while others calculate normally, the cell may be formatted as Text, or the formula may begin with an apostrophe. Change the cell to General or another suitable format, then re-enter the formula so Excel parses it. Merely changing Text to General may not convert an already-entered formula; press F2 and then Enter on Windows, or edit and confirm the cell on Mac. Check carefully before re-entering, especially in a shared workbook.
A reliable troubleshooting order
Work from the least disruptive checks toward changes that could affect data. This sequence usually identifies the cause without destroying a formula or changing the workbook’s underlying values:
- Select the cell and compare what appears in the formula bar with what appears in the grid.
- If the formula bar shows a formula, inspect its references, inputs, and intermediate results.
- If it shows a nonzero number, increase decimal places and check the number format.
- Confirm that expected inputs are present, numeric where appropriate, and in the referenced range.
- Check Calculation Options and recalculate if the result may be stale.
- Review worksheet zero-display and precision settings only when the symptoms point to those settings.
Keep a copy before changing a large range’s format, replacing formulas, or editing advanced settings. If only one cell is wrong, test a correction on that cell first. If many cells show the same symptom, compare their formulas and formats with a nearby cell that works; a copied formula or inconsistent format is often easier to spot side by side.
Frequently Asked Questions
Why does Excel show 0 when the referenced cell looks empty?
A direct reference to an empty cell can evaluate as zero. The source may also contain a formula returning an empty string or may be outside the range you intended. Inspect the referenced cell and the formula before deciding whether to display a blank instead.
How do I show more decimal places instead of 0?
Select the cell and choose Home > Number > Increase Decimal, or open Format Cells with Ctrl+1 on Windows or Command+1 on Mac and increase Decimal places. This changes the displayed precision; it does not necessarily change the stored value.
Why does Excel show zero even though my formula should calculate?
Check the formula’s cell references and inputs, then use Formulas > Evaluate Formula to step through the calculation. Confirm that numbers are numeric rather than text and that calculation mode is Automatic. Recalculate after correcting any issue.
Does turning off Show a zero make the value disappear?
That worksheet option hides cells whose value is zero; it does not change the value or make a nonzero number display as zero. On Windows, find it under File > Options > Advanced, in Display options for this worksheet.
Can I replace a formula with the value it should have returned?
You can, but typing a number over the cell removes its formula and stops it from updating when inputs change. First confirm that a fixed value is really intended, and save a copy if the formula may be needed later.
Related
- Comparison Table Excel Template: Build, Format, and Use
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
- Comparison Plot in Excel With R²: Steps and Meaning
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Maximum Cell Height: The 409-Point Limit ExplainedYes. In Microsoft Excel, the maximum height for a worksheet row is 409 points, and because a cell’s…
- How to Export Contacts in Outlook 2016 as a CSV FileTo export contacts from Outlook 2016 on Windows, open File > Open & Export > Import/Export, choose Export…
- Add Page Numbers in Word on Mac: Step-by-Step GuideTo add page numbers in Word on a Mac, open the Insert tab, choose Page Number, select where…
- Grocery List Excel Template: Free Options and SetupYes—you can get a free grocery list Excel template or make one yourself in a blank workbook. In…
- Dependent Dropdowns in Google Sheets: Setup and FixesTo make a dependent dropdown in Google Sheets, create a named range for each group of choices, then…
- Excel Sharing Violation: Causes and FixesAn Excel sharing violation usually means Excel cannot save to the workbook because another user, program, or file-system…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.