Author: Oscar Cronquist Article last updated on November 30, 2021

This article explains why your formula is not working properly, there are usually four different things that can go wrong.

1. Number stored as text

The first one is numbers stored as text, demonstrated in the picture above.

Why is the SUM function in cell C7 returning 0 (zero) in the picture above? The numbers in cell range C3:C6 are stored as text. You can see that by the green arrow in each cell's top left corner.

Why is this happening?
The cell range C3:C6 was formatted as text cells before you entered the numbers in these cells.

How to solve it?
Select cell range C3:C6, press with left mouse button on the exclamation mark symbol (or exclamation point if you are American) to open a menu. Press with mouse on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.

2. Manual calculation

The SUM formula in cell C7 returns 0 (zero), why is this happening?

Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button.

In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.

3. The cell shows the formula and not the result

Cell C7 displays the formula, not the result. Why is this happening?

Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).

Press with mouse on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation.

4. Formula cell formatted as text

The cell in C7 shows the formula? I don't want that, I want to see the calculation result.

Select cell C7, press CTRL + 1 on your keyboard.

This reveals that the cell is formatted as "Text" making it show the formula and not the result. Press with mouse on "General" and then on the OK button.

Now it shows the output from the formula.