Excel Formulas Not Working?

 

Excel Formulas Not Working? 5 ways to fix them

Excel formulas not working? Check out five errors that stop Excel formulas from calculating and learn how to fix them.

Microsoft Excel has been an invaluable tool since the dawn of modern computing. Every day, more than a million people use Microsoft Excel spreadsheets to manage projects, track finances, create charts, graphs, and manage time.

Unlike other applications such as Word, spreadsheet software uses mathematical formulas and data in cells to calculate values.

However, there are instances where Excel formulas do not work properly.

This article will help you solve problems with Excel formulas.

1. Calculation options are set to manual

If you can’t update the value you entered, and it returns the same as you entered, Excel’s calculation option can be set to manual and not automatic.

To fix this, change the calculation mode from manual to automatic.

1. Open the spreadsheet you are having trouble with.

2. Then from the ribbon, navigate to the Formula tab, then choose Calculation.

3. Select Calculation Options and choose Automatic from the dropdown.

Alternatively, you can set calculation options from Excel Options.

Select the Office Button in the upper left corner > Excel Options > Formulas > Workbook Calculation > Automatic

2. The cell is formatted as text

You may have accidentally formatted cells containing formulas as text. Unfortunately, when set to text format Excel skips the applied formula and displays the simple result instead.

The best way to check for formatting is to click the cell and check the Number group from the Home tab. If it displays “Text“, click on it and select General. To recalculate the formula, double-click the cell, and then press Enter on your keyboard.

3. The Show Formulas button is on

Performance formulas are often misused by people accidentally turning them on. After turning it on, applied formulas will not work. You can find this setting under the Formulas tab.

The formula button is designed for calculating formulas, so when pressed it shows the formula instead of the result. So, if you have turned it on before, turning it off should help resolve the issue.

 Here’s how to do it:

Go to the Formula tab > Formula Auditing group and click the Show Formulas button.

4. Space is entered before the equal sign

When you enter an extra equal to (‘=’) or an apostrophe (‘) in a spreadsheet cell, calculations are not performed, causing problems for users. The problem usually occurs when users try to copy a formula from the web.

However, solving this problem is simple. Go to the cell, select it and remove the apostrophe or space at the beginning of the formula.

5. Force Excel to recalculate

Excel provides its users with the option to manually recalculate formulas if they prefer not to use the automatic calculation settings. You can do this with these methods:

To recalculate the entire spreadsheet, press F9 on your keyboard or select Recalculate Now under the Formula tab. Alternatively, you can recalculate the active sheet by pressing Shift + F9 on your keyboard or by selecting Calculation Sheet from the Calculations group under the Formula tab.

You can recalculate all formulas in all worksheets by pressing the keyboard combination Ctrl + Alt + F9. Also, if you want to recalculate just one formula from the sheet, you can select the cell and press Enter.