We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Have you ever felt distressed when you’re using Excel, you type formulas in your spreadsheet and the formulas don’t calculate??? Or they don’t calculate and look more like a sentence than a formula? There could be several reasons why your Excel formulas won’t calculate.
3 Reasons Why Your Excel Formulas are Not Working
Watch Tutorial: 3 Reasons Why Your Excel Formulas Won’t Calculate and How to Fix Them
1. Your Formulas aren’t Working Because they are Formatted as Text
One of the most common reasons why your Excel formulas don’t work is the formatting used.
In the example below, Text formatting is the culprit.
In column A, where the formula is displayed instead of the result, the cells were formatted as text before typing the formula.
Cell C5 was formatted as Text after the formula was typed. In other words, the formula used to work, but stopped working when the formatting was changed to Text.
You can check the formatting by placing your cursor in a cell and, in your Home tab, in the Number section, you can see the formatting applied to the selected cell. The format used in your selected cell will be displayed in the window of the Number Formatting dropdown.
There are a few steps to fixing this. Depending on your spreadsheet, you may need to do only one of these steps or all of them.
- Reformat your sheet or range of cells with a Number format
- Fix cells flagged with a “Number Stored as Text” error and
- Update any formulas not calculating
Reformat Cells with Number Format:
The first thing you should do is reformat the cells to a number format.
Begin by highlighting the cells that you need to reformat.
Then, in the Home tab, go to the drop-down in the Number section and select one of the number formats. This could be General, Number, Accounting, etc.
Once you select the format, your new formatting will be applied to those cells.
For this example, I’ve selected General.
Notice how the values in cells A1 through A4 changed to a numerical value (one way you can tell is that they shifted from being aligned to the left of the cell to the right of the cell).
However, the formulas and values with the Number Stored as Text error warning sign (the green triangle on the top left of some cells) have not yet updated to the selected formatting.
Fix Cells with “Number Stored as Text” Error:
There are a few ways you can convert values with numbers stored as text to numbers:
1. Use the warning sign as a shortcut to convert values to a number format
This way you can highlight a selection to convert several at one time.
To do this, select all the cells with the error flag (the green triangle on the top left of each cell).
Then, on the first cell of your selection, click on the icon with the exclamation point and select “Convert to Number.”
2. Use the Error Checking Feature to fix the error
In the Formulas tab, in the Formula Auditing section, you’ll find a feature called Error Checking.
Select Error Checking to check all the cells on the spreadsheet that may contain an error. This way you can evaluate each cell individually and see if you want to change it.
3. Use Text to Columns to convert values to numbers
To convert your cells to numbers using Text to Columns, select only one column at a time.
Then go to your Data tab and click Text to Columns. You can use either Delimited or Fixed Width, both will work for this. Click Finish when done.
Update Formulas Displaying as Text Not Calculating:
There are a couple of ways you can fix formulas displaying as text.
- To make the formulas calculate, you can either double-click on each cell or “step into” the cell by using the F2 key on your keyboard.
This works fine for 1 or a few formulas, but, what about if you have several formulas formatted as text?
- For multiple formulas, you can use Replace All.
To use Replace All to fix your formulas that were formatted as text, you’ll need to replace your “ = “ signs with “ = “ signs.
It seems weird, but it works!
2. Your Formulas aren’t Working Properly Because the Formula Calculation Options Have Changed
Perhaps formatting is not the reason your Excel formulas won’t calculate automatically.
It may be that calculation options have been changed on your spreadsheet. This setting may have been changed for a few reasons, but it’s easy to check for and fix.
You can see the calculation options for your sheet in the Formulas tab, in the Calculation section, then click on Calculation Options to see what your sheet is set to.
If it’s not set to calculate automatically, you can change that setting here. If you prefer to calculate your sheet manually, you can also click either Calculate Now or Calculate Sheet to update your formulas.
TIP: You can also use the F9 function shortcut to force your sheet to calculate
3. Your Spreadsheet is Set to “Show Formulas”
If you open your Excel sheet and see all your formulas displayed, rather than their result, it may be that your sheet was set to Show Formulas. It’s possible that the sheet was saved with this setting still in place.
To change this, go to your Formulas tab and, in the Formula Auditing section, deselect Show Formulas.
Need to clean up how formula errors look in your spreadsheet? Take a look at how you can use the IFERROR function to replace ugly formula errors.
NEXT: Learn How to Add a Zero Before a Number in Excel (with video tutorial)
Leave a Reply