Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working.
This begs the query “Why is my formula not working in Excel?”
Well, Make sure numbers are not formatted as text values. Numbers formatted as text values are another common reason for Excel formulas not working. At first sight, they look like normal numbers, but Microsoft Excel perceives them as text strings and leaves out of calculations.
The most likely cause of this issue is the Calculation Option mode, and it’s a critical setting that every Excel user should know about. To check what calculation mode Excel is in, go to the Formulas tab, and click on Calculation Options. This will bring up a menu with three choices. The current mode will have a checkmark next to it.
Symptoms : A cell displays the formula, not the result. If your Excel formula is not working because a cell displays the function instead of the calculated value, it’s because one of the following reasons. Show Formulas mode is turned on.
You should be asking “Why can’t Excel read Numbers in my Formula?”
It is formatted as text; when the cells are formatted as text excel cannot read numbers and return the result for your applied formula. Change the format of the cells to General or Convert to Numbers.
Does the formula show the formula itself or the result?
Now take a look at the first possibility of formula showing the formula itself, not the result of the formula. Look at the below image where the SUM function in excel SUM Function In Excel The SUM function in excel adds the numerical values in a range of cells.