Microsoft Excel has been one of the most valuable tools since the dawn of modern computing. Over a million people use Microsoft Excel spreadsheets daily to manage projects, track finances, create charts and 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

Excel formula error

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.

  1. Open the spreadsheet you're having trouble with.
  2. Then from the ribbon, navigate to the Formulas tab, then choose Calculation.
  3. 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. If you often switch between these two modes, you can create a custom keyboard shortcut for Excel to speed up your work.

2. Cell Is Formatted as Text

Cell Is Formatted as Text Excel error

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 the cell and press Enter on your keyboard.

3. Show Formulas Button Is Turned On

Formula auditing in Excel

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.

The Show Formulas button is designed for auditing formulas, displaying the formula instead of the result when pressed. This might be a good tool if you want to better understand Excel formulas, but it stops them from working. In this case, turning it off may help resolve the issue.

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

4. Check Your Excel Formula

excel spreadsheet deals feature

Even if you're using an Excel function for beginners, a missing or an extra character might be why your Excel formula isn't working. For example, 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.

The same goes for the formula's parentheses. When writing a lengthy formula, make sure you match all parentheses, so the calculations take place in the correct order. Excel helps you by showing parentheses pairs in different colors, so you can easily follow them.

Excel will display an error message when there's a missing or extra parenthesis.

5. Use Proper Characters to Separate Arguments

You might have to separate function arguments to get the desired outcome depending on which Excel formulas you're using. You should generally separate arguments using commas, but this may vary depending on your Regional Settings.

For example, in North American countries, you must enter commas to separate arguments, while in Europe, the right character is set to a semicolon. If you've used the wrong separating character, Excel might show the "We found a problem with this formula" error. You can try both options and check which one works for you.

If you're used to using commas instead of semicolons, or vice versa, you can edit Excel settings to avoid repeating the same error.

  1. Open Excel's File menu and go to More > Options.
  2. From the left pane, select Language.
  3. In the Office display language section, click the Add a language button.
  4. Select the wanted language to install and click Install.
  5. Select it again and click Set as Preferred.
  6. Test if Excel formulas are now working.
Change language settings in Excel

To sync Office settings across your devices when logging in with your Microsoft account, check the Store my authoring languages in the cloud for my account option.

6. Force Excel to Recalculate

Excel calculate

Excel allows users to recalculate formulas manually 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, select the cell and press Enter.

7. Check for Circular References

Excel formulas might show the wrong results if it uses circular referencing, which refers to the same cell where it does the calculations. Most of the time, Excel will notify you about a circular reference, but it might not do it every time.

Fortunately, you can manually check for this type of reference if you keep getting wrong results out of Excel formulas.

Open the Formulas tab, go to Formula Auditing, and select Error Checking > Circular References. Excel will show which cells have added a circular reference to the formula.

Check Excel spreasheet for ciruclar references

Go to the indicated cells and edit the formula so it doesn't include the cell showing the results. For example, if the C13 cell shows the result for =SUM(C1:C13), change C13 to C12 within the formula to fix it.

Solve Your Excel Issues With These Fixes

The truth is, there's not much room for error when writing an Excel formula. A missing parenthesis or the wrong cell format is enough to stop Excel formulas from working as intended.

However, you should know how to fix the issue as you'll save a lot of time having Excel do the calculations for you.