Fix: Excel Formulas not working
Microsoft Excel is a spreadsheet application which has been around for more than three decades and is used by millions of people around the world. It features pivot tables, graphing tools, calculations, and also a macro programming language.
Microsoft Excel also enables the use of formulas through which you can select a sequence of cell values and manipulate them for calculations. If you are a frequent user of Microsoft Excel, you might come across a problem where the formulas don’t work or do not calculate. Don’t worry, this behavior is only because of some settings not set correctly in the application. Take a look.
Solution 1: Check if Formulas are formatted as Text
Cells have the option to set their data type. You can set them to text, numbers, time, dates, etc. It is possible that the cell you are trying to calculate the formula on is set as ‘Text’. We can change the data type of the cell and see if this fixes anything for us.
- Select the cell where the formula is not being calculated.
- Now navigate to ‘Home’ and here around the middle, you will a drop box consisting of various data types. Select either ‘General’ or ‘Number’.
- Now click the cell again and press Enter. This will cause the formula to be calculated automatically and the result will be visible on the cell.
Solution 2: Changing Calculation Options
By practice, changing the calculation option to manual is the best way to minimize the processor usage by the office application. When the calculation type is set to manual, the formulas are calculated not before you save the workbook. When you click the save button, it will perform all the calculation in a batch and then save your work. When the calculation type is set to automatic, all the formulas are calculated in real time. We can change the calculation options and check if this solves the problem for us.
- Click on ‘Formulas’ tab and select the ‘Calculation Options’ as ‘Automatic’.
- Restart Excel and check if the problem at hand got solved.
Solution 3: Disabling ‘Show formulas’ options
Excel also has the feature to show the formulas on the cell instead of the calculated value itself. This might be causing for your formulas to work properly but the formula will be displayed on the screen instead of the numeric value. We can try change this option and see if this helps.
- Click on the ‘Formulas’ tab and click “Show Formulas” once to disable showing the formulas.
- Re-open your spreadsheet and check if the problem at hand got solved.
Tips:
- Make sure all opening and closing parentheses match in your worksheet.
- Enter all the required arguments in the formula.
- There is a limit to not nest more than 64 functions in a formula, so make sure you don’t exceed this.
- Do not enclose numbers in double quotes.
- Make sure you are separating function arguments with proper characters. In some regions, you will use ‘,’ to separate while in some you will use ‘;’.
- The workbook and worksheet names should be enclosed in single quotes.
- If using a closed workbook, make sure that the path you write is complete.
- Press Ctrl + Alt + F9 to recalculate all open worksheets.
- You can trim and clean formulas to get rid of extra spacing.
- Keep in mind circular references to avoid getting the formula into an endless loop.
- Make sure you are using the correct way of absolute referencing.
