Excel formulas not working? Check out five errors that can stop Excel formulas from calculating and learn how to fix them.
Microsoft Excel has been one of the most valuable tools since the dawn of modern computing. Each day, over a million people use Microsoft Excel spreadsheets to manage projects, track finances, create charts, graphs, and even manage time.
Unlike other applications such as Word, the spreadsheet software uses mathematical formulas and data in cells to calculate values.
However, there are instances when the Excel formulas do not function properly. This article will help you fix problems with Excel formulas.
1. Calculation Options Set to Manual
If you can’t update the value you’ve entered, and it returns the same as you entered, Excel’s calculation option may be set to manual and not automatic.
To fix this, change the calculation mode from Manual to Automatic.
- Open the spreadsheet you’re having trouble with.
- Then from the ribbon, navigate to the Formulas tab, then choose Calculation.
- Select Calculation Options and choose Automatic from the dropdown.
Alternatively, you can adjust the calculation options from Excel options.
Choose the Office button at the top left corner > Excel options > Formulas > Workbook Calculation > Automatic.
2. Cell Is Formatted as Text
You might have accidentally formatted the cells containing the formulas as text. Unfortunately, Excel skips the applied formula when set to text format and displays the plain result instead.
The best way to check for formatting is to click on the cell and check the Number group from the Home tab. If it displays “Text,” click on that and choose General. To recalculate the formula, double-click on the cell, and then press Enter on your keyboard.
3. Show Formulas Button Is Turned On
People commonly misuse the Show Formulas button by accidentally turning it on. With this turned on, the applied formulas will not work. You can find this setting under the formulas tab.
Show Formula button is designed for auditing formulas, so it displays the formula instead of the result when pressed. Hence, if you have previously turned it on, turning it off may help resolve the issue. Here’s how to do it:
Go to the Formula Tab > Formula Auditing Group and click on the Show Formulas button.
4. Space Entered Before the Equal Sign
When you enter an additional equal to (‘=’) or apostrophe (‘) in a spreadsheet cell, calculations are not performed, causing problems for users. The issue typically occurs when users try to copy a formula from the web.
However, it is simple to fix this problem. Just head over to the cell, select it and remove the apostrophe or space at the beginning of the formula.
5. Force Excel to Re-Calculate
Excel provides its users with the option to manually recalculate formulas if they prefer not to use automatic calculation settings. You can do this with these methods:
For recalculating an entire spreadsheet, press F9 on your keyboard or choose Calculate Now under Formula Tab. Alternatively, you can recalculate an active sheet by pressing Shift + F9 on your keyboard or selecting Calculate Sheet from the Calculation group under Formula Tab.
You could also recalculate all formulas across all worksheets by pressing the Ctrl + Alt + F9 keyboard combination. Moreover, if you prefer recalculating just one formula from a sheet, you can select the cell and then press Enter.
Solve Your Excel Issues With These Fixes
When dealing with spreadsheets, there are a lot of things that can go wrong. It might be helpful to learn a few keyboard shortcuts if you work with Excel a lot.
While Excel might be your preferred application, there are various other applications similar to it, such as Google Sheets, and the above methods will work with them all.
Excel isn’t only for business. Here are several Microsoft Excel formulas that will help you solve complex daily problems.
About The Author