Ms Excel is one of the most widely used spreadsheet tools for organizing, analyzing, and calculating data. Its powerful formula system allows users to perform automatic calculations and quickly turn raw numbers into meaningful insights. However, formulas may sometimes stop working correctly due to simple issues such as incorrect settings, formatting problems, or syntax errors.
How to Fix Excel Formulas Not Working
If a formula in Excel returns an incorrect result or doesn’t update at all, don’t panic it’s often due to a simple setting. Below are some quick fixes to get your formulas working again.
Problem: Excel Is Set to Manual Calculation Mode
If the value in a cell doesn’t update when you change the data it depends on, Excel may be set to Manual Calculation mode. In this mode, formulas won’t recalculate automatically.
Fix: Change the Calculation Option from Manual to Automatic
Step 1: Open the spreadsheet that is giving you problems. Go to the Formulas tab, then select the Calculation option
Step 2: From the dropdown option, select Automatic
.png)
Problem: Cell Set to Text Format
You may have formatted the cells containing the formulas incorrectly as text. When Excel is set to text format, it ignores the applied formula and does not display the result.
Fix: Change the Cell from Text Format to either General or Number
To resolve this issue, follow the procedures below:
Step 1: Select the cell where the formula is not being calculated
Step 2: Navigate to Home, and you'll see a Dropbox with multiple data kinds in the center. Choose General or Number
Step 3: Now, click the cell once more and Select Enter. The formula will be automatically calculated, and the result will be displayed in the cell
Problem: Show Formulas Button Is Turned On
If show formulas button is turned on accidently then the applied formulas will not work.
Fix: Turn off the Show Formulas Button
The Show Formula button is intended for auditing formulas, therefore when selected, it displays the formula rather than the result. As a result, if you previously turned it on, turning it off may help remedy the problem. Here's how to go about it:
Step 1: Navigate to the Formula Tab and click the Show Formulas button under the Formula Auditing Group
Excel Formula's Not Updating
The value provided by your Excel formula does not automatically update, which means that the formula displays the old value even after you modify the values of the dependent cells. When Excel formulas do not update automatically, it is most likely because the Calculation setting has been set to Manual rather than Automatic.
To resolve this, simply return the Calculation option to Automatic:
Step 1: Navigate to the Formulas tab on the Excel ribbon, then to the Calculation group, click the Calculation Options button, and then select Automatic.
Excel Formulas Not Calculating
If your Excel formula isn't working, the reason can be that a cell displays the function rather than the calculated value, it could be due to one of the following issues.
Problem: Incorrect Excel Formula
While working with Excel, even if you are new to using Functions, a common issue that may arise is the presence of missing or extra characters. For Example, if you use (=) an equal sign or apostrophe(') in a cell can prevent calculations from being performed correctly. This problem may occur when users haven't typed the formula and copied it from other sources.
Fix: Check your Excel Formula
You can fix this problem by following the below tips:
Step 1: Select the cell with the formula and remove any unnecessary apostrophes or spaces at the beginning of the formula
Step 2: You should take care of parenthesis while using lengthy formulas. Ensure that all opening and closing parenthesis match correctly so that the opening and closing parenthesis match correctly so that calculations happen in the right order. Excel can help you with this by displaying parenthesis in different colors to make it easier to track them
Excel will show an error if there is a missing or extra parenthesis in your formula. Keep in mind these common formula problems to make your Excel experience smoother and more productive.
Problem: Wrong Characters Usage
You have to use separate function arguments to use for the excel formulas to calculate and give desired correct outputs.
Fix: Use Proper Characters to Separate Arguments
To get the desired results in Excel, it's essential to know how to separate functions and arguments correctly. Depending upon the Excel formula you're using and your regional settings, you may need to use commas or semicolons to separate arguments.
Using the wrong separator can lead to a "We Found a Problem with this Formula" error in Excel. There's a way to adjust Excel settings to avoid repeating this error:
Step 1: Open Excel's File menu
Step 2: Go to More and select Options
Step 3: Select Language from the left pane
Step 4: In the office display language section, click on the" Add a language" button
Step 5: Choose the desired language to install and click Install
Step 6: Select the newly installed language and click "Set as Preferred"
.png)
Step 7: Test your Excel Formula's to see if they are now working correctly
To sync Office settings across your device when logging in with your Microsoft account, check the Store my authoring languages in the Cloud for My Account option.
Force Excel to Recalculate
If users don't prefer to use automatic calculation settings, Excel allows users to Recalculate formulas manually. You can do this by following below methods:
For Recalculating the entire sheet, you can press F9 on your keyboard or choose to Calculate Now under the Formula tab.
You can also recalculate an active sheet by pressing Shift +F9 on your keyboard or by selecting the Calculate sheet from the Calculation group under Formula Tab.
You can also recalculate all formulas across all worksheets by pressing the Ctrl+ Alt +F9.
Check for Circular Reference
If Excel uses circular reference it might sometimes show wrong results. Most of the time, Excel will notify you about a circular reference, but it might not do it every time.
Note: Circular reference refers to the same cell where is does the calculations.
You can manually check for this type of reference if you continuously getting wrong results out of Excel formulas.
Follow the below steps:
Step 1: Open the Formulas Tab
Step 2: Go to Formula Auditing
Step 3: Select Error Checking Circular Reference
.png)
Note: Now Excel will show which cells have added a circular reference to the Formula. Go to the indicated cells and edit the formula so it doesn't include the cell showing the results.