RECOMMENDED: Click here to fix Windows errors and optimize system performance
Can we imagine using Microsoft Excel without formulas? I don’t think anyone can. And almost nothing compares to the frustration caused by Excel formulas that suddenly stop working. When this happens, a series of questions immediately come to mind. Why doesn’t my Excel formula work? Why doesn’t this formula automatically update its value? Is my Excel formula damaged or is it due to a malicious virus? And how can I make sure that my Excel formula is automatically recalculated and updated again? Don’t worry, chances are that your Excel works well and you will get all the answers in an instant.
Match all the opening and closing parentheses of a formula
As you know, the arguments of Excel functions are shown in brackets. In complex formulas, it may be necessary to enter several sets of parentheses, one inside the other, to specify the order in which the calculations are to be performed. When you create such a formula, be sure to combine the parentheses correctly so that you always have a right parenthesis for every left parenthesis in your formula.
Microsoft Excel displays pairs of parentheses in different colors when you enter them in a formula. If one or more parentheses are missing from your formula, Excel displays an error message and suggests a correction to equalize the pairs.
Delete Spaces in the SUM Function
The SUM function does not add values if its formula contains blanks. Instead, it displays the formula in its cell.
- To correct this, select the cell of the formula and then click on the far left of the function bar. Press the backspace key to remove all spaces before the “=” at the beginning of the function. Make sure there are no spaces elsewhere in the formula.
Widen your Formula’s Column
If the SUM formula cell displays ######, the value may not fit in the cell. Therefore, you may need to expand the column of the cell to ensure that the whole number matches.
To do this, move the cursor to the left or right of the reference field in the SUM cell column so that the cursor changes to a double arrow, as in the snapshot just below. Then hold down the left mouse button and drag the column to the left or right to expand it.
Check for Automatic Recalculation
- Look at the Formulas ribbon on the far right and click Calculation Options. In the drop-down list, make sure the Automatic option is selected.
- If this option is set to Automatic, Excel recalculates the formulas in the spreadsheet when you change the value of a cell. In other words, if you have a formula that totals your sales and you change one of the sales, Excel updates the total to show the correct total.
- If this option is set to manual, Excel recalculates the total only when you click the Calculate Now or Calculate Sheet button. If you prefer keyboard shortcuts, you can recalculate by pressing the F9 key. Manual recalculation is useful if you have a large spreadsheet that takes several minutes to recalculate. Instead of waiting impatiently for it to recalculate after every change you make, you can set recalculation to manual, make all your changes, and then recalculate all at once.
Unfortunately, if you set it to manual and forget it, your formulas will not be recalculated.
Check for Hidden Rows or Columns
If columns or rows are hidden, they are still calculated in the sheet. You must find these rows and display them if they cause unexpected totals to be calculated in your spreadsheet.
- You can find hidden rows or columns by locating the small boxes between the columns or rows on the left or top of the sheet.
- You can reveal hidden rows or columns by selecting the rows or columns between which the hidden values are located, right-clicking the mouse and choosing “Show”.
- If values with decimal places have been entered, Excel can round these values in a single cell, but not for the grand total.
- Check that the decimal places are hidden by selecting all cells containing numbers and choosing “Format Cells”. From here you can check how many decimal places are displayed under the “Number” category. Increase the number of decimals to 30 to see everything.
RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.