Microsoft Excel is a widely used application for doing automatic calculations. But, sometimes, this handy feature does not work properly. In this article, we are going to demonstrate to you 6 possible solutions to the issue of formulas that are not calculating automatically in Excel. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
6 Possible Solutions to Fix Formulas That Are Not Calculating Automatically in Excel
To demonstrate the solutions, we consider a dataset of 10 employees of a company. Their name and the income of the first two months are in the range of cells B5:D14. Now, when we tried to sum up the total for each employee, we figured out that our formula did not calculate automatically. You can also observe the situation in the image shown below,
Now, we will show you how to solve this problem.
All the operations of this article are accomplished by using Microsoft Office 365 application. So, the position of some commands may vary a little.
1. Changing Feature from Excel Options
In the first solution, we have to change some features from the Excel Options to calculate automatically by the formulas. The steps of this process are given below:
- First of all, click on the File > Options option.
- As a result, a small dialog box called Excel Options will appear.
- Now, click on the Formula tab, and change the Workbook Calculation option from Manual to Automatic.
- At last, click OK.
- You will see that the formulas are updated automatically and showed the total value.
Thus, we can say that our procedure works perfectly, and we are able to solve the problem that is formulas are not calculating automatically in Excel.
2. Modifying Calculation Options
In this approach, we will modify the Calculation Option to resolve the problem which is formulas not working in Excel. The steps of the method are given as follows:
- First, go to the Formula tab.
- After that, in the Calculation group, click on the drop-down arrow of the Calculation Options option and change the option from Manual to Automatic.
- You will notice that the formula will update automatically.
Hence, we can say that our approach works effectively, and we are able to resolve the problem that is formulas not calculating automatically in Excel.
3. Turn Off Show Formulas Command
To solve the problem, we can turn off the Show Formula command. It will help us to fix the issue, which is the Excel formula not estimating automatically. The steps to the process are shown below:
- At first, go to the Formula tab.
- Now, from the Formula Auditing group, click on the Show Formula option to turn off it.
- You will figure out that the formula will calculate value automatically.
Therefore, we can say that our method works precisely, and we are able to fix the issue.
4. Applying Proper Cell Formatting
Choosing the wrong cell formatting is another cause for Excel formulas not calculating the values automatically. The procedure to set the suitable cell formatting is explained below step-by-step:
- Firstly, select the range of cells C5:D14.
- Afterward, in the Home tab, choose a suitable format from the Number group. Here, we chose the Accounting format for our cells.
- Your problem will be solved.
So, we can say that our procedure works fruitfully, and we are able to get rid of the difficulty.
5. Uncheck Circular Reference Option
The activation of the Circular Reference option sometimes causes trouble for Excel formulas in evaluating values automatically. We must turn off this feature so that the Excel formulas can work automatically. The steps of this approach are described below:
- In the beginning, go to the Formula tab.
- Next, in the Formula Auditing group, click on the drop-down arrow of the Error Checking option, and you will find the Circular Reference command.
- Now, turn off the command.
- Hence, your Excel formula will not face any difficulty in actuation estimation.
In the end, we can say that our process works properly, and we are able to solve the problem.
6. Presence of Apostrophe in Dataset
The presence of an apostrophe sign in the dataset does not allow the Excel formula to calculate value automatically. You may notice in the image we have a similar type of trouble in our dataset. We have two trapped apostrophes in cells C11 and D8.
The steps to fix this issue are given as follows:
- At the start, select a cell where the apostrophe sign is trapped. First, we click on cell D8. Though the sign is not showing in the cell, you can figure it out by observing the data alignment.
- Now, click on the Formula Bar and press the Delete button to delete the apostrophe sign manually.
- Press Enter.
- Similarly, delete the other trapped apostrophe sign from cell C11.
Finally, we can say that our method works successfully, and we are able to resolve the trouble of formulas not calculating automatically.
That’s the end of this article. I hope that this article will be helpful for you and you will be able to fix the problem of formulas that are not calculating automatically in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!